21.1.1. Récupérer l'espace disque

Dans son fonctionnement normal, PostgreSQL ne supprime pas immédiatement les versions périmées des lignes après un UPDATE ou un DELETE. Cette approche est nécessaire pour la consistance des accès concurrents (voir le Chapitre 12) : la version de la ligne ne doit pas être supprimée tant qu'elle est susceptible d'être lue par une autre transaction. Mais finalement, une ligne qui est plus vieille que toutes les transactions en cours n'est plus utile du tout. La place qu'elle utilise doit être rendue pour être réutilisée par d'autres lignes afin d'éviter un accroissement constant du volume occupé sur le disque. Cela est réalisé en exécutant VACUUM.

Évidemment, une table qui subit beaucoup de mises à jour et suppressions nécessitera des nettoyages plus fréquents que les tables rarement modifiées. Il peut être pertinent de programmer périodiquement par cron des tâches spécifiques qui nettoient uniquement les tables concernées et ignorent les tables que l'on sait peu modifiées. Ceci ne sera vraiment utile que s'il y a à la fois des tables volumineuses intensément modifiées et des tables volumineuses peu modifiées. En effet, le coût supplémentaire lié au nettoyage d'une petite table ne mérite pas que l'on s'en préoccupe.

La forme standard de VACUUM est particulèrement adaptée dans le but de maintenir une occupation stabilisée de l'espace disque. La forme standard repère toutes les lignes dont la version est périmée et rend l'espace disque qu'elles occupaient afin d'être réutilisé pour la table. En revanche, il n'y a pas de réelle libération d'espace pour le système d'exploitation. Pour rendre l'espace disque au système, on pourra utiliser VACUUM FULL ; cependant, est-il réellement nécessaire de libérer un espace qu'il faudra bientôt réallouer ? Des exécutions raisonnablement fréquentes de VACUUM sont plus efficaces que quelques VACUUM FULL sporadiques pour maintenir les tables intensément modifiées.

La meilleure stratégie pour la plupart des sites est de planifier un VACUUM général sur toute la base une fois par jour, en dehors des horaires normaux de production, accompagné si nécessaire de nettoyages plus fréquents pour les tables subissant d'intenses modifications. S'il y a plusieurs bases de données dans un cluster (groupe de bases de données), ne pas oublier de nettoyer chacune d'entre elles ; l'exécutable vacuumdb peut s'avérer utile. Il est recommandé d'utiliser des VACUUM simples, pas des VACUUM FULL, pour l'entretien régulier et automatique de l'espace disque.

VACUUM FULL est recommandé quand on sait que la majorité des lignes d'une table ont été supprimées. Pour cette table, la taille stable sera réduite de façon significative par VACUUM FULL dont le mécanisme est plus poussé.

Pour les tables dont le contenu est souvent totalement supprimé, on préfèrera faire TRUNCATE plutôt que DELETE suivi de VACUUM.

21.1.2. Maintenir les statistiques du planificateur

L'optimiseur de requêtes de PostgreSQL s'appuie sur des informations statistiques sur le contenu des tables dans l'optique de générer des plans d'exécutions efficaces pour les requêtes. Ces statistiques sont collectées par la commande ANALYZE, qui peut être invoquée seule ou comme une option de VACUUM. Il est important d'avoir des statistiques relativement à jour sans quoi des mauvais choix dans les plans d'exécution pourraient pénaliser la performance de la base.

À l'instar du nettoyage pour récupérer l'espace, les statistiques doivent être plus souvent collectées pour les tables intensément modifiées que pour celles qui le sont moins. Mais même si la table est très modifiée, il se peut que ces collectes soient inutiles si la distribution probabiliste des données évolue peu. Une règle simple pour décider est de voir comment évoluent les valeurs minimum et maximum des données. Par exemple, une colonne de type timestamp qui contient la date de mise à jour de la ligne aura une valeur maximum en continuelle croissance au fur et à mesure des modifications ; une telle colonne nécessitera plus de collectes statistiques qu'une colonne qui contient par exemple les URL des pages accédées sur un site web. La colonne qui contient les URL peut très bien être aussi souvent modifiée mais la distribution probabiliste des données changera certainement moins rapidement.

Il est possible d'exécuter ANALYZE sur des tables spécifiques, voire des colonnes spécifiques ; il a donc toute flexibilité pour mettre à jour certaines statistiques plus souvent que les autres en fonction des besoins de l'application. Quoi qu'il en soit, dans la pratique, l'utilité de cette fonctionnalité est douteuse. En effet, depuis PostgreSQL 7.2, ANALYZE est une opération plutôt rapide, même pour les grosses tables, parce que la collecte se base sur un échantillon aléatoire de la table et non sur toutes les données. Il est donc probablement plus simple de l'utiliser systématiquement sur toute la base.

Pour la plupart des site, la meilleure stratégie est de programmer une collecte générale des statistiques sur toute la base, une fois par jour. Ceci peut être profitablement couplé avec un VACUUM (la nuit par exemple). Quoi qu'il en soit, les administrateurs des bases dont les statistiques changent peu pourront juger que cela est exagéré et que des exécutions moins fréquentes de ANALYZE sont bien suffisantes.

21.1.3. Éviter les cycles des identifiants de transactions

Le mécanisme de contrôle de concurrence multiversion (MVCC) de PostgreSQL s'appuie sur la possibilité de comparer des identifiants de transactions (XID) ; c'est un nombre croissant : la version d'une ligne dont le XID d'insertion est supérieur au XID de la transaction en cours est << dans le futur >> et ne doit pas être visible de la transaction courante. Comme les identifiants ont une taille limitée (32 bits à ce jour), un groupe qui est en activité depuis longtemps (plus de 4 milliards de transactions) connaîtra un cycle des identifiants de transaction : le XID reviendra à 0 et soudainement les transactions du passé sembleront appartenir au futur - ce qui signifie qu'elles deviennent invisibles. En bref, perte de données totale. (En réalité, les données sont toujours là mais c'est un piètre réconfort puisqu'elles resteront inaccessibles.)

Avant PostgreSQL 7.2, la seule parade contre ces cycles de XID était de ré-exécuter initdb au minimum tous les 4 milliards de transaction. Bien sûr, cela n'était pas satisfaisant pour les sites connaissant un trafic important, donc une nouvelle solution a été élaborée. La nouvelle approche permet à un cluster de fonctionner indéfiniment, sans initdb ni aucune sorte de réinitialisation. Le prix en est le suivant : toute table dans la base doit être nettoyée au moins une fois tous les milliards de transactions.

Dans la pratique cette exigence n'est pas onéreuse mais comme son manquement aurait pour conséquence une perte totale des données (pas seulement de l'espace disque perdu ou des performances moindres), des dispositions ont été prises pour aider les administrateurs à surveiller le temps écoulé depuis le dernier VACUUM. La suite de cette section en explique les détails.

La nouvelle approche pour la comparaison des XID distingue deux XID spéciaux, numéros 1 et 2 (BootstrapXID et FrozenXID). Ces deux XID sont toujours considérés comme plus vieux que n'importe quel autre. Les XID normaux (ceux qui sont supérieurs à deux) sont comparés sur une base modulo-2³¹. Cela signifie que pour chaque XID normal, il y en a deux milliards qui sont plus vieux et deux milliards qui sont plus récents. Une autre manière de le dire est que l'ensemble de définition des XID est circulaire et sans limite. De plus, une ligne créée avec un XID normal donné, la version de la ligne apparaîtra comme appartenant au passé pour les deux milliards de transactions qui suivront quelque soit le XID. Si la ligne existe encore après deux milliards de transactions, elle apparaîtra soudainement comme appartenant au futur. Pour éviter la disparition des données, les versions trop anciennes doivent se voir affecter le XID FrozenXID avant d'atteindre le seuil fatidique des deux milliards de transactions. Une fois qu'elles ont ce XID spécifique, elles appartiendront au passé pour toutes les transactions même en cas de cycle. Cette affectation est réalisée par VACUUM.

La politique normale de VACUUM est d'affecter FrozenXID à toute les lignes dont le XID se situe à plus de un milliard de transactions dans le passé. Elle préserve le XID original tant qu'il est utile. (En réalité, la plupart des lignes existeront et disparaîtront avant d'être << gelée >>. Avec cette méthode, l'intervalle de sécurité maximum entre les exécutions de VACUUM pour une table est d'exactement un milliard de transactions : en attendant plus longtemps, on s'expose à conserver des versions qui n'étaient pas assez vielles pour se voir affecter FrozenXID lors de la précédente exécution et qui apparaissent maintenant dans le futur du fait d'un cycle - c'est-à-dire que les données semblent perdues. (Bien sûr, elles réapparaîtront après deux nouveaux milliards de transactions mais cela n'a pas d'intérêt).

Puisque des exécutions périodiques de VACUUM sont nécessaires de toutes manières, pour les raisons évoquées ci-dessus, il est très peu probable qu'une table ne soit pas nettoyée du tout durant un milliard de transactions. Pour aider les administrateurs à assurer que cette exigence est remplie, VACUUM conserve des statistiques sur les XID dans la table système pg_database. Notamment, la table pg_database contient, pour chaque base, une colonne datfrozenxid qui est mise à jour après les VACUUM de la base (c'est-à-dire VACUUM qui ne spécifie aucune table particulière). La valeur qui est stockée est la limite en deçà de laquelle cette exécution de VACUUM a marqué la ligne comme << gelée >>. Tous les XID plus vieux que ce XID limite ont reçu le XID FrozenXID pour cette base. Pour obtenir cette information, il suffit d'exécuter la requête :

SELECT datname, age(datfrozenxid) FROM pg_database;

La colonne age calcule le nombre de transactions effectuées entre le XID limite et le XID courant.

Avec la méthode standard de gel du XID, La colonne age démarre à un milliard pour une base fraîchement nettoyée. Si l'age approche des deux milliards, la base doit de nouveau être nettoyée pour éviter les erreurs liées au cycle du XID. Il est recommandé d'exécuter un VACUUM une fois tous les demi milliard de transactions pour garder une marge de sécurité maximale. Pour aider à remplir cette exigence, chaque VACUUM émet un message si n'importe lequel des enregistrements de pg_database indique un age de plus de 1,5 milliard de transactions, par exemple :

play=# VACUUM;
WARNING:  some databases have not been vacuumed in 1613770184 transactions
HINT:  Better vacuum them within 533713463 transactions, or you may have a wraparound failure.
VACUUM

Avec l'option FREEZE, la commande VACUUM a un comportement plus poussé : les versions des lignes sont gelées si elles sont suffisamment vieilles pour être visibles de toutes les transactions en cours. En particulier, sur une base en lecture seulement, VACUUM FREEZE aura pour résultat de geler toutes les lignes de la base. Donc, tant que la base n'est pas modifiée, aucun nettoyage supplémentaire n'est nécessaire pour éviter les problèmes de cycle du XID. Cette technique est notamment utilisée par initdb pour préparer la base template0. Cela pourrait également être utilisé pour préparer n'importe quelle base créée par l'administrateur avec datallowconn = false dans pg_database, puisqu'il n'y a pas moyen d'exécuter VACUUM sur une base à laquelle on ne peut pas se connecter. On notera que VACUUM n'émet aucun message d'avertissement pour les enregistrements de pg_database où datallowconn = false afin de ne pas induire d'erreur ; c'est donc à l'administrateur de s'assurer que ces bases sont correctement gelées.