La commande VACUUM de PostgreSQL™ doit traiter chaque table régulièrement pour plusieurs raisons :

Chacune de ces raisons impose de réaliser des opérations VACUUM de différentes fréquences et portées, comme expliqué dans les sous-sections suivantes.

Il y a deux variantes de la commande VACUUM : VACUUM standard et VACUUM FULL. VACUUM FULL peut récupérer davantage d'espace disque mais s'exécute beaucoup plus lentement. Par ailleurs, la forme standard de VACUUM peut s'exécuter en parallèle avec les opérations de production des bases. Des commandes comme SELECT, INSERT, UPDATE et DELETE continueront de fonctionner de façon normale, bien que vous ne pourrez plus modifier la définition d'une table avec des commandes telles que ALTER TABLE pendant qu'elle sera en cours de VACUUM. VACUUM FULL nécessite un verrou exclusif sur la table sur laquelle il travaille, et ne peut donc pas être exécuté en parallèle avec une autre activité sur la table. Un autre inconvénient de VACUUM FULL est que, bien qu'il réduise la taille des tables, il ne réduit pas la taille des index en proportion ; en réalité, il peut rendre les index plus grands. En règle générale, par conséquent, les administrateurs devraient s'efforcer d'utiliser la commande standard VACUUM et éviter VACUUM FULL.

VACUUM génère un nombre important d'entrées/sorties, ce qui peut entraîner de mauvaises performances pour les autres sessions actives. Des paramètres de configuration peuvent être ajustés pour réduire l'impact d'une opération VACUUM en arrière plan sur les performances  -- voir Section 18.4.3, «  Report du VACUUM en fonction de son coût  ».

Avec PostgreSQL™, les versions périmées des lignes ne sont pas immédiatement supprimées après une commande UPDATE ou DELETE. Cette approche est nécessaire pour la consistance des accès concurrents (voir le Chapitre 13, 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.

La forme standard de VACUUM élimine les versions d'enregistrements morts dans les tables et les index et marque l'espace comme réutilisable. Néanmoins, il ne rendra pas cet espace au système d'exploitation, sauf dans le cas spécial où des pages à la fin d'une table deviennent totalement vides et on peut facilement obtenir un verrou exclusif sur la table. Par opposition, VACUUM FULL compacte activement les tables en déplaçant les enregistrements vers des pages plus proches du début de la table. Il est par conséquent capable de forcer les pages de la fin de la table à être entièrement vides, grâce à quoi il pourra les rendre au système d'exploitation. Toutefois, si de nombreux enregistrements doivent être déplacés, l'opération peut durer très longtemps. Par ailleurs, déplacer un enregistrement demande de créer des entrées d'index dupliquées pour lui de façon temporaire (l'entrée pointant vers sa nouvelle adresse doit être crée avant que l'ancienne entrée ne puisse être supprimée) ; la conséquence est que déplacer un grand nombre d'enregistrement peut faire fortement grossir l'index.

Le but habituel d'un vacuum régulier est de lancer des VACUUM standards suffisamment souvent pour éviter d'avoir recours à VACUUM FULL. Le démon autovacuum essaie de fonctionner de cette façon, et n'exécutera jamais de VACUUM FULL. Avec cette approche, l'idée directrice n'est pas de maintenir les tables à leur taille minimale, mais de maintenir l'utilisation de l'espace disque à un niveau constant : chaque table occupe l'espace équivalent à sa taille minimum plus la quantité d'espace consommée entre deux vacuums. Bien que VACUUM FULL puisse être utilisé pour retourner une table à sa taille minimale et rendre l'espace disque au système d'exploitation, cela ne sert pas à grand chose, si cette table recommence à grossir dans un futur proche. Par conséquent, cette approche s'appuyant sur des commandes VACUUM exécutées à intervalles modérément rapprochés est une meilleure approche que d'exécuter des VACUUM FULL espacés pour des tables mises à jour de façon intensive.

Certains administrateurs préfèrent planifier le passage de VACUUM eux-mêmes, par exemple faire le travail de nuit, quand la charge est faible. La difficulté avec cette stratégie est que si une table a un pic d'activité de mise à jour inattendu, elle pourrait grossir au point qu'un VACUUM FULL soit vraiment nécessaire pour récupérer l'espace. L'utilisation du démon d'autovacuum mitige ce problème, puisque le démon planifie les vacuum de façon dynamique, en réponse à l'activité de mise à jour. Il est peu raisonnable de désactiver totalement le démon, sauf si votre base a une activité extrêmement prévisible. Un compromis possible est de régler les paramètres du démon afin qu'il ne réagisse qu'à une activité exceptionnellement lourde de mise à jour, afin d'éviter seulement de perdre totalement le contrôle de la volumétrie, tout en laissant les VACUUM planifiés faire le gros du travail quand la charge est normale.

Pour ceux qui n'utilisent pas autovacuum, une approche typique alternative est de planifier un VACUUM sur la base complète une fois par jour lorsque l'utilisation n'est pas grande, avec en plus des opérations de VACUUM plus fréquentes pour les tables très impactées par des mises à jour, de la façon adéquate. (Certaines installations avec énormément de mises à jour peuvent exécuter des VACUUM toutes les quelques minutes.) Si vous avez plusieurs bases dans un cluster, n'oubliez pas d'exécuter un VACUUM sur chacune d'elles ; le programme vacuumdb pourrait être utile.

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 les performances de la base.

Le démon d'autovacuum, si activé, va automatiquement exécuter des commandes ANALYZE à chaque fois que le contenu d'une table aura changé suffisamment. Toutefois, des administrateurs peuvent préférer se fier à des opérations ANALYZE planifiées manuellement, en particulier s'il est connu que l'activité de mise à jour de la table n'aura pas d'impact sur les statistiques des colonnes « intéressantes ». Le démon planifie des ANALYZE uniquement en fonction du nombre d'enregistrements insérés, mis à jour ou supprimés 

À 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, il est généralement mieux de simplement analyser la base entière car il s'agit d'une opération rapide. ANALYZE utilise un système d'échantillonage des lignes d'une table, ce qui lui évite de lire chaque ligne.

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.) Pour éviter ceci, il est nécessaire d'exécuter un VACUUM sur chaque table de chaque base au moins au moins une fois à chaque milliard de transactions.

La raison pour laquelle un VACUUM périodique résout le problème est que PostgreSQL™ distingue un ID de transaction spécial, FrozenXID. Ce XID est toujours considéré comme plus âgé que les XID normaux. Les XID normaux 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 des anciens XID est réalisée par VACUUM.

vacuum_freeze_min_age contrôle l'âge que doit avoir une valeur XID avant qu'elle soit remplacée par FrozenXID. Les valeurs plus importantes de ces deux paramètres préservent l'information transactionnelle plus longtemps alors que les valeurs plus petites augmentent le nombre de transactions qui peuvent survenir avant un nouveau VACUUM de la table.

VACUUM ignore habituellement les pages qui n'ont pas de lignes mortes, mais ces pages pourraient toujours avoir des versions de lignes avec d'anciennes valeurs XID. Pour s'assurer que tous les anciens XID ont été remplacés par FrozenXID, un parcours complet de la table est nécessaire. vacuum_freeze_table_age contrôle quand VACUUM fait cela : un parcours complet est forcé si la table n'a pas été parcourue complètement pendant vacuum_freeze_table_age - vacuum_freeze_min_age transactions. En le configurant à zéro, cela force VACUUM à toujours parcourir toutes les pages, ignorant de ce fait la carte de visibilité.

Le temps maximum où une table peut rester sans VACUUM est de deux millions de transactions moins vacuum_freeze_min_age quand VACUUM a parcouru la table complètement pour la dernière fois. Si elle devait rester sans VACUUM après cela, des pertes de données pourraient survenir. Pour s'assurer que cela n'arrive pas, autovacuum est appelé sur chaque table qui pourrait contenir des XID plus âgés que ne l'indique le paramètre de configuration autovacuum_freeze_max_age. (Ceci arrivera même si autovacuum est désactivé.)

Ceci implique que, si aucune opération de VACUUM n'est demandée sur une table, l'autovacuum sera automatiquement déclenché une fois toutes les autovacuum_freeze_max_age moins vacuum_freeze_min_age transactions. Pour les tables qui ont régulièrement l'opération de VACUUM pour réclamer l'espace perdu, ceci a peu d'importance. Néanmoins, pour les tables statiques (ceci incluant les tables qui ont des INSERT mais pas d'UPDATE ou de DELETE), il n'est pas nécessaire d'exécuter un VACUUM pour récupérer de la place et donc il peut être utile d'essayer de maximiser l'interval entre les autovacuums forcés sur de très grosses tables statiques. Évidemment, vous pouvez le faire soit en augmentant autovacuum_freeze_max_age soit en diminuant vacuum_freeze_min_age.

Le maximum efficace pour vacuum_freeze_table_age est 0.95 * autovacuum_freeze_max_age ; un paramétrage plus haut que ça sera limité à ce maximum. Une valeur plus importante qie autovacuum_freeze_max_age n'aurait pas de sens car un autovacuum de préservation contre la ré-utilisation des identifiants de transactions serait déclenché, et le multiplicateur 0,95 laisse un peu de place pour exécuter un VACUUM manuel avant que cela ne survienne. Comme règle d'or, vacuum_freeze_table_age devrait être configuré à une valeur légèrement inférieure à autovacuum_freeze_max_age, laissant suffisamment d'espace pour qu'un VACUUM planifié régulièrement ou pour qu'un autovacuum déclenché par des activités normales de suppression et de mise à jour puissent être activés pendant ce laps de temps. Le configurer de façon trop proche pourrait déclencher des autovacuum de protection contre la ré-utilisation des identifiants de transactions, même si la table a été récemment l'objet d'un VACUUM pour récupérer l'espace, alors que des valeurs basses amènent à des parcours complets de table plus fréquents.

Le seul inconvénient à augmenter autovacuum_freeze_max_age (et vacuum_freeze_table_age avec elle) est que le sous-répertoire pg_clog du cluster prendre plus de place car il doit stocker le statut du COMMIT pour toutes les transactions depuis autovacuum_freeze_max_age. L'état de COMMIT utilise deux bits par transaction, donc si autovacuum_freeze_max_age et vacuum_freeze_table_age ont une valeur maximum permise de deux milliards, pg_clog peut grossir jusqu'à la moitié d'un Go. Si c'est rien comparé à votre taille de base totale, configurer autovacuum_freeze_max_age à son maximum permis est recommandé. Sinon, le configurer suivant ce que vous voulez comme stockage maximum dans pg_clog. (La valeur par défaut, 200 millions de transactions, se traduit en à peu près 50 Mo de stockage dans pg_clog.)

Un inconvénient causé par la diminution de vacuum_freeze_min_age est que cela pourrait faire que VACUUM travaille sans raison : modifier le XID de la ligne d'une table à FrozenXID est une perte de temps si la ligne est modifiée rapidement après (ce qui fait qu'elle obtiendra un nouveau XID). Donc ce paramètre doit être suffisamment important pour que les lignes ne soient pas gelées jusqu'à ce qu'il soit pratiquement certain qu'elles ne seront plus modifiées. Un autre inconvénient en diminuant ce paramètre est que les détails sur la transaction exacte qui a inséré ou modifié une ligne seront perdus plus tôt. Cette information est quelque fois utile, particulièrement lors d'une analyse de ce qui s'est mal passé sur une base après un problème. Pour ces deux raisons, baisser ce paramètre n'est pas recommandé sauf pour les tables entièrement statiques.

Pour tracer l'âge des plus anciens XID de la base, VACUUM stocke les statistiques sur XID dans les tables systèmes pg_class et pg_database. En particulier, la colonne relfrozenxid de la ligne pg_class d'une table contient le XID final du gel qui a été utilisé par le dernier VACUUM pour cette table. Il est garanti que tous les XID plus anciens que ce XID ont été remplacés par FrozenXID pour cette table. De façon similaire, la colonne datfrozenxid de la ligne pg_database de la base est une limite inférieure des XID normaux apparaissant dans cette base -- c'est tout simplement le minimum des valeurs relfrozenxid par table dans cette base. Pour examiner cette information, le plus simple est d'exécuter des requêtes comme :

SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
SELECT datname, age(datfrozenxid) FROM pg_database;

La colonne age mesure le nombre de transactions à partir du XID final vers le XID de transaction en cours.

VACUUM parcourt habituellement seulement les pages qui ont été modifiées depuis le dernier VACUUM mais relfrozenxid peut seulement être avancé quand la table est parcourue complètement. La table est parcourue entièrement quand relfrozenxid est agée de plus de vacuum_freeze_table_age transactions, quand la commande VACUUM FREEZE est utilisée ou quand toutes les pages se trouvent nécessiter un VACUUM pour supprimer les versions mortes des lignes. Après que VACUUM ait parcouru la table complète age(relfrozenxid) devrait être un peu plus grande que le paramètre vacuum_freeze_min_age qui a été utilisé (la différence étant due au nombre de transactions démarrées depuis que VACUUM a commencé son travail). Si aucun parcours de table complet ne se trouve exécuté via un VACUUM sur cette table, lorsque autovacuum_freeze_max_age est atteint, un autovacuum sera rapidement forcé sur la table.

Si, pour une certaine raison, l'autovacuum échoue à effacer les anciens XID d'une table, le système commencera à émettre des messages d'avertissement comme ceci quand les plus anciens XID de la base atteignent les 10 millions de transactions à partir du point de réinitialisation :

WARNING:  database "mydb" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

(Une commande VACUUM manuelle devrait résoudre le problème, comme suggéré par l'indice ; mais notez que la commande VACUUM doit être exécutée par un superutilisateur, sinon elle échouera à mettre à jour les catalogues systèmes et ne pourra donc pas faire avancer le datfrozenxid de la base.) Si ces avertissements sont ignorés, le système s'arrêtera et refusera d'exécuter toute nouvelle transaction dès qu'il n'en restera qu'un million avant la réinitialisation :

ERROR:  database is not accepting commands 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'exécute pas de commandes tant qu'il n'est pas sorti du mode d'arrêt par sécurité, la seule façon de le faire est de stopper le serveur et d'utiliser un moteur simple utilisateur pour exécuter le VACUUM. Le mode d'arrêt n'est pas pris en compte par le moteur simple utilisateur. Voir la page de référence de postgres(1) pour des détails sur l'utilisation du moteur simple utilisateur.

PostgreSQL ™ dispose d'une fonctionnalité optionnelle mais hautement recommandée appelée autovacuum, dont le but est d'automatiser l'exécution des commandes VACUUM et ANALYZE . Une fois activé, autovacuum 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 ; du coup, autovacuum ne peut pas être utilisé sauf si track_counts est configuré à true. Dans la configuration par défaut, l'autovacuum est activé et les paramètres liés sont correctement configurés.

Le « démon autovacuum » est constitué de plusieurs processus. Un processus démon permanent appelé autovacuum launcher (autrement dit le lanceur d'autovacuum), qui est en charge de lancer un processus travailleur (autovacuum worker) pour toutes les bases de données. Le lanceur distribuera le travail dans le temps mais essaiera de lancer un nouveau travailleur sur chaque base de données chaque autovacuum_naptime secondes. Un travailleur sera lancé pour chaque base de données, avec un maximum de autovacuum_max_workers processus fonctionnant en même temps. S'il y a plus de autovacuum_max_workers bases à traiter, la prochaine base de données sera traitée dès qu'un autre travailleur a terminé. Chaque processus travailleur vérifiera chaque table de leur base de données et exécutera un VACUUM et/ou un ANALYZE suivant les besoins.

Le paramètre autovacuum_max_workers limite le nombre maximum de travailleurs pouvant être lancés à tout moment. Si plusieurs grosses tables deviennent toutes éligibles pour un VACUUM dans un court espace de table, tous les travailleurs de l'autovacuum pourraient ne s'occuper que de ces tables pour un très long moment. Ceci aurait pour conséquences que les autres tables et bases ne seraient plus l'objet d'opérations de VACUUM jusqu'à la disponibilité d'un travailleur. Il n'y a pas de limite au nombre de travailleurs sur une seule base mais les travailleurs tentent d'éviter de répéter le travail qui a déjà été réalisé par d'autres travailleurs. Notez que le nombre de travailleurs en cours d'exécution ne comptent pas dans les limites max_connections et superuser_reserved_connections.

Les tables dont la valeur de relfrozenxid est plus importante que autovacuum_freeze_max_age sont toujours l'objet d'un VACUUM (cela s'applique aux tables dont le 'freeze max age' a été modifié par les paramètres de stockage ; voyez plus bas). Sinon, 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). Si la valeur de relfrozenxid pour la table est supérieure à vacuum_freeze_table_age, la table complète est parcourue pour geler les anciennes lignes et pour avancer relfrozenxid, sinon seules les pages qui ont été modifiées depuis le dernier VACUUM sont parcourues par l'opération de VACUUM.

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 ou mises à jour 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 ; voir Paramètres de stockage pour plus d'informations. Si un paramètre a été modifié via les paramètres de stockage, cette valeur est utilisée ; sinon les paramètres globaux sont utilisés. Voir Section 18.9, « Nettoyage (vacuum) automatique » pour plus d'informations sur les paramètres globaux.

En plus des valeurs de la limite de base et des facteurs d'échelle, il existe six autres paramètres autovacuum pouvant être configurés pour chaque table via les paramètres de stockage. Le premier paramètre, 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 devra le faire pour prévenir la réinitialisation de l'ID de transaction. Deux autres paramètres, le délai du coût du VACUUM (autovacuum_vacuum_cost_delay) et la limite du coût du VACUUM (autovacuum_vacuum_cost_limit), sont utilisés pour configurer des valeurs spécifiques aux tables pour la fonctionnalité Report du VACUUM en fonction de son coût . autovacuum_freeze_min_age, autovacuum_freeze_max_age et autovacuum_freeze_table_age sont utilisés pour configurer des valeurs par table, respectivement vacuum_freeze_min_age, autovacuum_freeze_max_age et vacuum_freeze_table_age.

Lorsque plusieurs processus autovacuum sont en cours d'exécution, la limite de coût est « répartie » parmi tous les processus pour que l'impact total sur le système soit identique quelque soit le nombre de processus en cours d'exécution.