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

Astuce

Même si il n'est pas très productif de règler précisément la fréquence de ANALYZE pour chaque colonne, il peut être intéressant d'ajuster le niveau de détail des statistiques collectées pour chaque colonne. Les colonnes très utilisées dans les clauses WHERE et dont la distribution n'est pas uniforme requièrent des histogrammes plus précis que les autres colonnes. Voir ALTER TABLE SET STATISTICS .

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.) 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 .

Le comportement du VACUUM est contrôlé par le paramètre de configuration vacuum_freeze_min_age : tout XID plus ancien que vacuum_freeze_min_age transactions est remplacé par FrozenXID. Les valeurs plus importantes que vacuum_freeze_min_age préserve l'information transactionnelles plus longtemps alors que les valeurs plus petites augmentent le nombre de transactions qui peuvent survenir avant un nouveau VACUUM de la table.

Le temps maximum où une table peut rester sans VACUUM est de deux millions de transactions moins vacuum_freeze_min_age qui a été utilisé lors du dernier VACUUM. Si elle devait rester sans VACUUM après cela, des pertes de données pourraient survenir. Pour s'assurer que cela n'arrive pas, l'autovacuum décrit dans Section 22.1.4, « Le démon auto-vacuum » 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 placer 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 seul inconvénient à augmenter autovacuum_freeze_max_age 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 a une valeur maximum permise d'un peu moins que deux billion, 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. Immédiatement après un VACUUM , age(relfrozenxid) devrait être un peu plus que le paramètre vacuum_freeze_min_age qui a été utilisé (plus par le nombre de transactions commencées depuis l'exécution de VACUUM ). Si age(relfrozenxid) dépasse autovacuum_freeze_max_age, un autovacuum sera bientôt 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 full-database VACUUM in "mydb".

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 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 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.

À 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.

Le démon autovacuum, si activé, s'exécute toutes les autovacuum_naptime secondes. À chaque exécution, il sélectionne une base à traiter et vérifie chaque table de cette base. Les commandes VACUUM et ANALYZE sont exécutées si nécessaire.

Les tables dont la valeur de relfrozenxid est plus importante que autovacuum_freeze_max_age sont toujours l'objet d'un VACUUM. Sinon, deux conditions sont utilisées pour déterminer l'opération qui s'applique. 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 du catalogue système pg_autovacuum . Si une ligne pg_autovacuum existe pour une table particulière, les paramètres qu'elle indique sont appliqués ; sinon les paramètres globaux sont utilisés. Voir Section 17.9, « Nettoyage 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 cinq 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 devra le faire pour prévenir la réinitialisation de l'ID de transaction. Les deux paramètres suivants, 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é Report du VACUUM en fonction de son coût . Les deux derniers paramètres, (pg_autovacuum. freeze_min_age ) et (pg_autovacuum. freeze_max_age ), sont utilisés pour configurer des valeurs par table, respectivement vacuum_freeze_min_age et autovacuum_freeze_max_age.

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.