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, Contrôle d'accès simultané) : 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 (avec VACUUM) 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.

Il existe deux variantes de la commande VACUUM. La première forme, connu en tant que « vacuum fainéant » ou plus simplement VACUUM, marque les données expirées dans les tables et les index pour une utilisation future ; il ne tente pas de récupérer l'espace utilisée par cette donnée expirée sauf si l'espace est à la fin de la table et qu'un verrou exclusif de table puisse être facilement obtenu. L'espace inutilisé au début ou au milieu du fichier ne résulte pas en un raccourcissement du fichier et de l'espace redonné au système d'exploitation. Cette variante de VACUUM peut être lancé en concurrence avec les autres opérations normales de la base de données.

La seconde forme est la commande VACUUM FULL. Elle utilise un algorithme plus agressif pour récupérer l'espace consommé par les versions expirées des lignes. Tout espace qui est libéré par VACUUM FULL est immédiatement rendu au système d'exploitation. Malheureusement, cette variante de la commande VACUUM acquiert un verrou exclusif sur chaque table avant que VACUUM FULL ne la traite. Du coup, utiliser fréquemment VACUUM FULL peut avoir un effet extrêmement négatif sur les performances des requêtes concurrentes sur la base de données.

La forme standard de VACUUM est mieux utilisé dans le but de maintenir une utilisation simple de l'espace disque. Donc, vous avez besoin de redonner de l'espace disque au système d'exploitation, vous pouvez utiliser VACUUM FULL -- mais quel est l'intérêt de redonner de l'espace disque qui devra ensuite être de nouveau alloué ? Des VACUUM standard et d'une fréquence modérée sont une meilleure approche que des VACUUM FULL, même non fréquents, pour maintenir des tables mises à jour fréquemment.

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. (Quelques installations avec un taux extrêmement important de modifications de données lancent un VACUUM sur les tables très occupées jusqu'à une fois toutes les quelques minutes.) 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.

VACUUM FULL est recommandé dans les cas où vous savez que vous avez supprimé la majorité des lignes dans une table, de façon à ce que la taille de la table soit réduit de façon conséquente avec l'approche plus plus agressive de VACUUM FULL. Utilisez le VACUUM standard, et non pas VACUUM FULL, pour les nettoyages standards.

Si vous avez une table dont le contenu est supprimé sur une base périodique, considérez de le faire avec TRUNCATE plutôt qu'avec DELETE suivi par un VACUUM. TRUNCATE supprime le contenu entier de la table immédiatement sans nécessiter un VACUUM ou VACUUM FULL pour réclamer l'espace disque maintenant inutilisé.

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.

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) pourrait connaître 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 à éviter le désastre. Pour chaque base de données du groupe, PostgreSQL™ conserve la trace de l'heure du dernier VACUUM sur toute la base. Quand une base approche du problème dû au milliard de transactions, le système commence à émettre des messages d'avertissement. Si rien n'est fait, il pourra en dernier lieu stopper les opérations normales jusqu'à ce qu'une maintenance manuelle ne soit effectuée. 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:  database "mabase" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "mabase".
VACUUM

Si les messages d'avertissement émits par VACUUM restent ignorés, alors PostgreSQL™ commencera à émettre un message d'avertissement comme ci-dessus à chaque lancement d'une transaction une fois qu'il reste moins de 10 millions de transactions avant le renouveau du cycle. Si ces messages sont aussi ignorés, le système s'arrêtera et refusera d'exécuter de nouvelles transactions tant qu'il restera moins de un million de transactions jusqu'au prochain cycle :

play=# select 2+2;
ERROR:  database is shut down to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and use a standalone backend to VACUUM in "mydb".

La marge de sécurité de un million de transactions existe pour permettre à l'administrateur de récupérer ces données sans perte en exécutant manuellement les commandes VACUUM requises. Néanmoins, comme le système n'excutera pas de commandes tant qu'il ne sera pas sorti du mode d'arrêt par sécurité, la seule façon de le faire est de stopper le postmaster et d'utiliser un moteur autonome pour exécuter le VACUUM. Le mode d'arrêt n'est pas pris en compte par le moteur autonome. Voir la page de référence de postgres(1) pour des détails sur l'utilisation du moteur autonome.

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.

À partir de PostgreSQL ™ 8.1, il existe un processus serveur optionnel et séparé appelé le démon autovacuum, dont le but est d'automatiser l'exécution des commandes VACUUM et ANALYZE . Une fois activé, le démon autovacuum s'exécute périodiquement et vérifie les tables ayant un grand nombre de lignes insérées, mises à jour ou supprimées. Ces vérifications utilisent la fonctionnalité de récupération de statistiques au niveau ligne ; du coup, le démon autovacuum ne peut pas être utilisé sauf si stats_start_collector et stats_row_level sont configurés à true. De plus, il est important d'autoriser un emplacement pour le processus autovacuum lors du choix de la valeur de superuser_reserved_connections.

Une fois activé, le démon autovacuum s'exécute toutes les autovacuum_naptime secondes et détermine quelle base de données traiter. Toute base de données proche de la réinitialisation de l'identifiant de transaction est immédiatement traitée. Dans ce cas, autovacuum exécute un VACUUM sur la base complète ou un VACUUM FREEZE s'il s'agit d'une base de données modèle. Puis, il quitte. Si aucune base de données ne remplit ce critère, celle qui a été traitée le moins récemment par autovacuum est choisie. Dans ce cas, chaque table de la base de données sélectionnée est vérifiée et des commandes VACUUM ou ANALYZE individuelles sont exécutées lorsque cela est nécessaire.

Pour chaque table, deux conditions sont utilisées pour déterminer l'opération à appliquer. Si le nombre de lignes obsolètes depuis le dernier VACUUM dépasse une « limite de vacuum », la table bénéficie d'un VACUUM. La limite est définie ainsi :

limite du vacuum = limite de base du vacuum + facteur d'échelle du vacuum * nombre de lignes

où la limite de base du vacuum est autovacuum_vacuum_threshold, le facteur d'échelle du vacuum est autovacuum_vacuum_scale_factor et le nombre de lignes est pg_class.reltuples. Le nombre de lignes obsolètes est obtenu à partir du récupérateur de statistiques ; c'est un nombre à peu près précis, mis à jour après chaque instruction UPDATE et DELETE (il est seulement à peu près précis car certaines informations pourraient être perdues en cas de grosse charge). Pour ANALYZE, une condition similaire est utilisée : la limite, définie comme

limite du analyze = limite de base du analyze + facteur d'échelle du analyze * nombre de lignes

est comparée au nombre de lignes insérées, mises à jour ou supprimées depuis le dernier ANALYZE.

Les limites et facteurs d'échelle par défaut sont pris dans postgresql.conf mais il est possible de les surcharger table par table avec des entrées dans le catalogue système pg_autovacuum. Si une ligne pg_autovacuum existe pour une table particulière, les paramètres qu'il spécifie sont appliqués ; sinon les paramètres globaux sont utilisés. Voir Section 17.9, « VACUUM automatique » pour plus de détails sur les paramètres globaux.

En plus des valeurs de la limite de base et des facteurs d'échelle, il existe trois autres paramètres pouvant être configurés pour chaque table dans pg_autovacuum. Le premier, pg_autovacuum.enabled, peut être configuré à false pour instruire le démon autovacuum de laisser cette table particulière. Dans ce cas, autovacuum touchera seulement la table quand il lancera un VACUUM sur la base de données complète pour prévenir la réinitialisation de l'ID de transaction. Les deux autres paramètres, le délai du coût du VACUUM (pg_autovacuum.vac_cost_delay) et la limite du coût du VACUUM (pg_autovacuum.vac_cost_limit), sont utilisés pour configurer des valeurs spécifiques aux tables pour la fonctionnalité Délais du VACUUM basé sur le coût .

Si une des valeurs dans pg_autovacuum est négative ou si aucune ligne n'est présente dans pg_autovacuum quelque soit la table, les valeurs correspondantes de postgresql.conf sont utilisées.

Il n'y a pas de support pour créer des entrées dans pg_autovacuum, sauf en réalisant soi-même des insertions manuelles dans le catalogue. Cette fonctionnalité sera améliorée dans les prochaines versions et il est vraisemblable que la définition du catalogue changera.