PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 14.11 » Administration du serveur » Planifier les tâches de maintenance » Nettoyages réguliers

25.1. Nettoyages réguliers

Les bases de données de PostgreSQL nécessitent des opérations de maintenance périodiques, connues sous le nom de VACUUM. Pour de nombreuses installations, il est suffisant de laisser travailler le démon autovacuum, qui est décrit dans Section 25.1.6. En fonction des cas, les paramètres de cet outil peuvent être optimisés pour obtenir de meilleurs résultats. Certains administrateurs de bases de données voudront suppléer ou remplacer les activités du démon avec une gestion manuelle des commandes VACUUM, qui seront typiquement exécutées suivant un planning par des scripts cron ou par le Task Scheduler. Pour configurer une gestion manuelle et correcte du VACUUM, il est essentiel de bien comprendre les quelques sous-sections suivantes. Les administrateurs qui se basent sur l'autovacuum peuvent toujours lire ces sections pour les aider à comprendre et à ajuster l'autovacuum.

25.1.1. Bases du VACUUM

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

  1. pour récupérer ou ré-utiliser l'espace disque occupé par les lignes supprimées ou mises à jour ;
  2. pour mettre à jour les statistiques utilisées par l'optimiseur de PostgreSQL ;
  3. Pour mettre à jour la carte de visibilité qui accélère les parcours d'index seuls ;
  4. pour prévenir la perte des données les plus anciennes à cause d'un cycle de l'identifiant de transaction (XID) ou d'un cycle de l'identifiant multixact.

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 existe deux variantes de la commande VACUUM : le VACUUM standard et le 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 continuent de fonctionner de façon normale, mais la définition d'une table ne peut être modifiée avec des commandes telles que ALTER TABLE pendant le VACUUM. VACUUM FULL nécessite un verrou de type ACCESS EXCLUSIVE 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. Par conséquent, en règle générale, les administrateurs doivent 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'un nettoyage en arrière plan sur les performances  -- voir Section 20.4.4.

25.1.2. Récupérer l'espace disque

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 cohérence des accès concurrents (MVCC, voir le Chapitre 13) : la version de la ligne ne doit pas être supprimée tant qu'elle est susceptible d'être lue par une autre transaction. Mais en fin de compte, 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, sans limite, du volume occupé sur le disque. Cela se fait en exécutant un 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 rend 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 qu'un verrou exclusif sur la table peut être obtenu aisément. Par opposition, VACUUM FULL compacte activement les tables en écrivant une nouvelle version complète du fichier de la table, sans espace vide. Ceci réduit la taille de la table mais peut prendre beaucoup de temps. Cela requiert aussi un espace disque supplémentaire pour la nouvelle copie de la table jusqu'à la fin de l'opération.

Le but habituel d'un nettoyage régulier est de lancer des VACUUM standard suffisamment souvent pour éviter d'avoir recours à un VACUUM FULL. Le démon autovacuum essaie de fonctionner de cette façon, et n'exécute 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 minimale plus la quantité d'espace consommée entre deux nettoyages. Bien que VACUUM FULL puisse être utilisé pour qu'une table retrouve sa taille minimale et rendre l'espace disque au système d'exploitation, cela ne sert pas à grand chose, si cette table recommence à grossir rapidement après. 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 peut grossir au point qu'un VACUUM FULL soit vraiment nécessaire pour récupérer l'espace. L'utilisation du démon d'autovacuum minore ce problème, puisque le démon planifie les nettoyages 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 l'activité de la base est 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, de sorte à seulement éviter 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 forte, 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 nettoyages toutes les quelques minutes.) Lorsqu'il y a plusieurs bases dans une instance, il faut penser à exécuter un VACUUM sur chacune d'elles ; le programme vacuumdb peut se révéler utile dans ce contexte.

Astuce

Le VACUUM standard peut ne pas suffire quand une table contient un grand nombre d'enregistrements morts comme conséquence d'une mise à jour ou d'une suppression massive. Dans ce cas, s'il est nécessaire de récupérer l'espace disque gaspillé, plusieurs commandes sont utilisables : VACUUM FULL, CLUSTER ou une des variantes de ALTER TABLE. Ces commandes écrivent une nouvelle copie de la table et reconstruisent ses index. Toutes ces options nécessitent un verrou de type ACCESS EXCLUSIVE. Elles utilisent aussi temporairement un espace disque supplémentaire, approximativement égal à la taille de la table, car les anciennes copies de la table et des index ne peuvent pas être supprimées avant la fin de l'opération.

Astuce

Si le contenu d'une table est supprimé périodiquement, il est préférable d'envisager l'utilisation de TRUNCATE, plutôt que DELETE suivi de VACUUM. TRUNCATE supprime le contenu entier de la table immédiatement sans nécessiter de VACUUM ou VACUUM FULL pour réclamer l'espace disque maintenant inutilisé. L'inconvénient est la violation des sémantiques MVCC strictes.

25.1.3. Maintenir les statistiques de l'optimiseur

L'optimiseur de requêtes de PostgreSQL s'appuie sur des informations statistiques produites à partir du contenu des tables dans l'optique de produire 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 option de VACUUM. Il est important d'avoir des statistiques relativement à jour, ce qui permet d'éviter les choix de mauvais plans d'exécution, pénalisant les performances de la base.

Si activé, le démon autovacuum va automatiquement exécuter des commandes ANALYZE à chaque fois que le contenu d'une table aura changé suffisamment. Toutefois, un administrateur peut 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'a pas d'impact sur les statistiques des colonnes « intéressantes ». Le démon planifie des ANALYZE en fonction du nombre d'enregistrements insérés, mis à jour ou supprimés ; il n'a aucune connaissance du fait que cela générera ou pas des modifications intéressantes au niveau des statistiques.

Les lignes modifiées dans les partitions et les enfants, dans le cadre de l'héritage, ne déclenchent pas d'analyse sur la table parent. Si la table parent est vide ou rarement modifiée, elle pourrait ne jamais être traitée par l'autovacuum, et les statistiques pour l'arbre d'héritage en entier ne seront pas récupérées. Il est nécessaire d'exécuter ANALYZE manuellement sur la table parent pour conserver des statistiques à jour.

À 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 minimale et maximale des données. Par exemple, une colonne de type timestamp qui contient la date de mise à jour de la ligne aura une valeur maximale 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 y a donc une grande 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.

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, ou modifier les paramètres par défaut de la base de données en utilisant le paramètre de configuration default_statistics_target.

De plus, par défaut, il existe peu d'informations sur la sélectivité des fonctions. Néanmoins, si vous créez un objet statistique ou un index sur une fonction, des statistiques utiles seront récupérées sur la fonction, ce qui peut grandement améliorer les plans de requêtes qui utilisent l'index.

Astuce

Le démon autovacuum ne lance pas de commandes ANALYZE sur les tables distantes car il n'a aucun moyen de déterminer la fréquence à laquelle la mise à jour des statistiques serait utile. Si vos requêtes ont besoin des statistiques sur les tables distantes pour disposer d'un plan d'exécution correct, une bonne idée serait de lancer manuellement des commandes ANALYZE sur ces tables au moment adéquat.

Astuce

Le démon autovacuum n'exécute pas de commandes ANALYZE pour les tables partitionnées. Les parents seront seulement analysées si le parent lui-même est modifié. Les changements dans les tables enfants ne déclenchent pas d'analyse automatique sur la table parent. Si vos requêtes nécessitent des statistiques sur les tables parents pour être correctement planifiées, il sera nécessaire d'exécuter périodiquement un ANALYZE manuels sur ces tables pour garder des statistiques à jour.

25.1.4. Mettre à jour la carte de visibilité

La commande VACUUM maintient le contenu de la carte de visibilité de chaque table, pour conserver la trace de chaque bloc contenant seulement des lignes connues pour être visibles par toutes les transactions actives (ainsi que les futures transactions, jusqu'à la prochaine modification de la page). Cette carte a deux buts. Tout d'abord, le nettoyage peut ignorer ce type de blocs à la prochaine exécution du VACUUM comme il n'y a rien à nettoyer dans ces blocs.

Ensuite, il permet à PostgreSQL de répondre à certaines requêtes en utilisant seulement l'index, et donc sans faire référence à la table sous-jacente. Comme les index dans PostgreSQL ne contiennent pas d'informations sur la visibilité des lignes, un parcours d'index normal récupère la ligne de la table pour chaque entrée d'index correspondante, ce qui permet de vérifier si la ligne correspondante est bien visible par la transaction en cours. Un parcours d'index seuls vérifie en premier lieu la carte de visibilité. S'il est connu que toutes les lignes du bloc sont visibles, la lecture de la table peut être évitée. Ceci est très utile sur les gros ensembles de données où la carte de visibilité peut éviter des accès disques. La carte de visibilité est bien plus petite que la table, donc elle peut facilement être mise en cache même quand la table est très grosse.

25.1.5. É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) : 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), une instance qui est en activité depuis longtemps (plus de 4 milliards de transactions) pourrait connaître un cycle complet 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 restent inaccessibles.) Pour éviter ceci, il est nécessaire d'exécuter un VACUUM sur chaque table de chaque base au moins une fois tous les deux milliards de transactions.

La raison pour laquelle un nettoyage régulier résout le problème est qu'un VACUUM marquera les lignes comme gelées, indiquant qu'elles ont été insérées par une transaction suffisamment ancienne pour que les effets de cette transaction soient visibles à coup sûr par toutes les transactions actuelles et futures. Les XID normaux sont comparés sur une base modulo-232. 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 ce fait, pour une ligne créée avec un XID normal donné, cette version de la ligne apparaît comme appartenant au passé pour les deux milliards de transactions qui suivront quelque soit le XID de ces transactions. Si cette version de ligne existe toujours après deux milliards de transactions, elle apparaîtra soudainement comme appartenant au futur. Pour empêcher cela, PostgreSQL réserve un XID spécial, appelé FrozenTransactionId, qui ne suit pas les règles normales de comparaison de XID et qui est toujours considéré comme plus ancien que chaque XID normal. Les versions de lignes gelées sont traitées comme si la XID d'insertion était FrozenTransactionId, pour qu'elles apparaissent dans le passé pour les autres transactions normales, quelque soit les soucis de cycle d'identifiants de transactions, et donc ces versions de lignes seront valides jusqu'à leur suppression, quelque soit la durée que cela représente.

Note

Dans les versions de PostgreSQL antérieures à la 9.4, le gel était implémenté en remplaçant le XID d'insertion d'une ligne avec FrozenTransactionId, qui était visible dans la colonne système xmin de la ligne. Les nouvelles versions ajoutent un drapeau, préservant le xmin original de la ligne pour une utilisation ultérieure (notamment pour du débugage). Néanmoins, il est toujours possible d'avoir des lignes pour lesquelles xmin vaut FrozenTransactionId (2) dans les bases de données antérieures à la version 9.4 traitées par pg_upgrade.

De plus, les catalogues systèmes pourraient contenir des lignes avec xmin égale à BootstrapTransactionId (1), indiquant qu'elles ont été insérées lors de la première phase d'initdb. Comme FrozenTransactionId, cet XID spécial est traité comme étant plus ancien que tout autre XID normal.

vacuum_freeze_min_age contrôle l'âge que doit avoir une valeur XID avant que des lignes comportant ce XID ne soient gelées. Augmenter la valeur de ce paramètre peut permettre d'éviter un travail inutile si les lignes à geler vont bientôt être modifiées. Diminuer ce paramètre augmente le nombre de transactions qui peuvent survenir avant un nouveau nettoyage de la table.

VACUUM utilise la carte de visibilité pour déterminer les blocs à parcourir sur une table. Habituellement, il ignore les blocs qui n'ont aucune ligne morte même si ces blocs pourraient toujours avoir des versions de lignes avec des identifiants très anciens de transactions. De ce fait, les VACUUM normaux ne vont pas toujours geler chaque ancienne version de ligne dans la table. De temps en temps, VACUUM réalise un vacuum agressif, ignorant seulement les blocs contenant aucune ligne morte et aucune valeur XID ou MXID non gelé. vacuum_freeze_table_age contrôle quand VACUUM se comporte ainsi : les blocs ne contenant que des lignes vivantes mais non gelées sont parcourus si le nombre de transactions exécutées depuis le dernier parcours de ce type est plus grand que vacuum_freeze_table_age moins vacuum_freeze_min_age. Configurer vacuum_freeze_table_age à 0 force VACUUM à utiliser cette stratégie plus agressive pour tous les parcours.

Le délai maximum où une table peut rester sans nettoyage est de deux millions de transactions moins vacuum_freeze_min_age lors du dernier VACUUM agressif. Si elle devait rester sans nettoyage 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 lignes non gelées dont les XID ont un âge plus avancé que 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 bénéficient régulièrement de l'opération de VACUUM pour réclamer l'espace perdu, ceci a peu d'importance. Néanmoins, pour les tables statiques (en incluant les tables qui ont des insertions mais pas de mises à jour et de suppressions), 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 le délai 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 que autovacuum_freeze_max_age n'aurait pas de sens car un autovacuum de préservation contre le cycle 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 VACUUM agressifs plus fréquents.

Le seul inconvénient à augmenter autovacuum_freeze_max_age (et vacuum_freeze_table_age avec elle) est que les sous- répertoires pg_xact et pg_commit_ts de l'instance prendront plus de place car ils doivent stocker le statut et l'horodatage (si track_commit_timestamp est activé) 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 maximale permise de deux milliards, pg_xact peut grossir jusqu'à la moitié d'un Go et pg_commit_ts jusqu'à 20 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_xact et dans pg_commit_ts. (La valeur par défaut, 200 millions de transactions, se traduit en à peu près 50 Mo de stockage dans pg_xact et à peu près 2 Go de stockage pour pg_commit_ts.)

Un inconvénient causé par la diminution de vacuum_freeze_min_age est que cela pourrait faire que VACUUM travaille sans raison : geler une version de ligne 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.

Pour tracer l'âge des plus anciens XID non gelés 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 agressif pour cette table. Il est garanti que tous les XID plus anciens que ce XID ont été remplacés par FrozenXID pour cette table. Toutes les lignes insérées par des transactions dont le XID est plus ancien que ce XID sont garanties d'avoir été gelées. De façon similaire, la colonne datfrozenxid de la ligne pg_database de la base est une limite inférieure des XID non gelés 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 c.oid::regclass as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

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.

Habituellement, VACUUM parcourt seulement les blocs qui ont été modifiés depuis le dernier nettoyage mais relfrozenxid peut seulement être avancé quand tous les blocs d'une table pouvant contenir des XID gelés sont parcourus. Ceci survient quand relfrozenxid a plus de vacuum_freeze_table_age transactions antérieures, quand l'option FREEZE de VACUUM est utilisée ou quand tous les blocs qui ne sont pas encore gelés nécessitent un nettoyage pour supprimer les versions de lignes mortes. Quand VACUUM parcourt chaque bloc d'une table qui n'est pas déjà entièrement gelé, il doit configurer age (relfrozenxid) à une valeur un peu au-dessus de la configuration utilisée pour vacuum_freeze_min_age (plus par le nombre de transactions démarrées depuis le lancement de VACUUM). Si aucun VACUUM avec avancement de relfrozenxid n'est lancé sur la table, une fois arrivé à autovacuum_freeze_max_age, un autovacuum est 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 40 millions de transactions à partir du point de réinitialisation :

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

(Une commande VACUUM manuelle devrait résoudre le problème, comme suggéré par le message HINT ; mais notez que la commande VACUUM doit être exécutée par un super-utilisateur, sinon elle échouera à mettre à jour les catalogues, ce qui l'empêchera d'avancer le champ datfrozenxid de la base.) Si ces avertissements sont ignorés, le système refusera d'assigner de nouveaux identifiants de transactions dès qu'il n'en restera que trois millions avant la réinitialisation :

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
    

Dans ce cas, toute transaction en cours peut continuer, mais seules des transactions en lecture seule peuvent démarrées. Les opérations qui modifient les enregistrements de la base de données ou qui tronquent des relations échoueront. La commande VACUUM peut toujous être exécutée normalement. Contrairement ce qu'indique le message, il n'est ni nécessaire ni dsouhaité d'arrêter le processus postmaster ou d'entrer en mode utilisateur-seul pour restaurer le fonctionnement normal. À la place, suivez ces étapes :

  1. Terminez les anciennes transactions préparées. Vous pouvez les trouver en récupérant les lignes de la vue pg_prepared_xacts dont age(transactionid) est grand. De telles transactions doivent être validées ou annulées.
  2. Terminez les transactions ouvertes s'exécutant depuis longtemps. Vous pouvez les trouver en récupérant les lignes de la vue pg_stat_activityage(backend_xid) ou age(backend_xmin) est grand. De telles transactions doivent être validées ou annulées. Si ce n'est pas possible, les sessions associées doivent être fermées en utilisant la fonction pg_terminate_backend.
  3. Supprimez les anciens slots de réplication. Utilisez pg_stat_replication pour trouver les slots dont age(xmin) ou age(catalog_xmin) est grand. Dans de nombreux cas, ces slots ont été créées pour de la réplication vers un serveur qui n'existe plus ou qui est arrêté depuis longtemps. Si vous supprimez un slot pour un serveur qui existe toujours ou pourrait de nouveau se connecter à ce slot, cette réplication pourrait nécessiter d'être reconstruite.
  4. Exécutez VACUUM dans la base cible. Un VACUUM sur la base complète est le plus simple ; pour réduire le temps nécessaire pour l'opération, il est aussi possible d'exécuter des commandes VACUUM manuelles sur les tables ayant le champ relminxid le plus ancien. N'utilisez pas VACUUM FULL dans ce cas car il nécessite un identifiant de transaction, ce qui fera échouer l'opération, sauf dans le mode super-utilisateur où il consommera un identifiant de transaction et augmentera de ce fait le risque d'une réutilisation des identifiants de transaction. N'utilisez pas non plus VACUUM FREEZE parce qu'il fera plus que la quantité minimal de travail requis pour restaurer le fonctionnement normal.
  5. Une fois que le fonctionnement est de nouveau normal, assurez-vous que l'autovacuum est correctement configuré dans la base de données cible pour éviter des problèmes futures.

Note

Dans les version sprécédentes, il était parfois nécessaire d'arrêter le processus postlaster et de lancer la commande VACUUM en mode utilisateur-simple. Dans les scénarios habituels, ceci n'est plus nécessaire et doit être éviter autant que possible car cela implique d'arrêter le système. C'est aussi plus risqué car cela désactive les protections sur la réutilisation des identifiants de transaction, prévues pour éviter les pertes de données. La seule raison pour utiliser le mode utilisateur-simple est si vous souhaitez utiliser TRUNCATE ou DROP sur des tables inutiles pour éviter de lancer VACUUM sur celles-ci. La marge de sécurité de trois millions de transactions existe pour permettre à l'administrateur de le faire. Voir la page de référence postgres pour des détails sur l'utilisation du mode utilisateur-simple.

25.1.5.1. Multixacts et cycle

Les identifiants multixact sont utilisés pour supporter le verrouillage de lignes par des transactions multiples. Comme l'espace est limité dans un en-tête de ligne pour y stocker des informations, cette information est codée sous la forme d'un « identifiant de transaction multiple », ou ID multixact pour faire court, à chaque fois qu'il y a plus d'une transaction cherchant à verrouiller en parallèle une ligne. Les informations sur les identifiants de transactions inclus dans tout identifiant multixact sont enregistrées séparément dans le sous-répertoire pg_multixact et seul l'identifiant multixact apparaît dans le champ xmax de l'en-tête de ligne. Comme les identifiants de transactions, les identifiants multi-transactions sont implémentés avec un compteur 32 bits et le stockage correspondant, ce qui nécessite une gestion attentive, un nettoyage du stockage et la gestion du cycle (plus exactement de la ré-utilisation des identifiants). Il existe un espace de stockage séparé qui détient la liste des membres dans chaque multixact, qui utilise aussi un compteur sur 32 bits et qui doit aussi être géré.

Quand VACUUM parcourt une partie d'une table, il remplacera tout ID multixact qu'il rencontre, plus âgé que vacuum_multixact_freeze_min_age, par une valeur différente, qui peut être la valeur zéro, un identifiant de transaction ou un nouvel identifiant multixact. Pour chaque table, pg_class.relminmxid enregistre le plus ancien identifiant multixact possible apparaissant déjà dans un enregistrement de cette table. Si cette valeur est plus ancienne que vacuum_multixact_freeze_table_age, un vacuum agressif est forcé. Comme indiqué dans la section précédente, un vacuum agressif signifie que seuls les blocs connus pour être entièrement gelés sont ignorés. mxid_age() peut être utilisé sur pg_class.relminmxid pour trouver son âge.

Les VACUUM agressifs, quelqu'en soit la cause, permettent d'avancer la valeur pour cette table. Comme toutes les tables de toutes les bases sont parcourues et que leurs plus anciennes valeurs multixact sont avancées, le stockage sur disque pour les anciens multixacts peut être supprimé.

Comme moyen de sécurité supplémentaire, un VACUUM agressif surviendra pour toute table dont l'âge en identifiant multixact est supérieur à autovacuum_multixact_freeze_max_age. De plus, si le stockage occupé par les membres multixacts dépasse 2 Go, des parcours de vacuum agressif surviendront plus fréquemment pour toutes les tables, en commençant par celles qui ont le plus ancien multixact-age. Ces deux types de parcours agressifs de tables surviendront seulement si l'autovacuum est désactivé spécifiquement.

Similaire au cas de l'identifiant de transaction, si l'autovacuum échoue à effacer les anciens identifiants MultiXact d'une table, le système commencera à emetre des messages d'avertissement quand les plus anciens identifiants MultiXact de la base attendront les 40 millions de transactions du point de réutilisation. Et, tout comme dans le cas des identifiants de transactions, si ces messages sont ignorés, le système refusera de générer de nouveaux identifiants MultiXact une fois qu'il en restera moins de trois millions.

Quand les identifiants MultiXact sont épuisés, le retour à une situation normale s'obtient de la même façon que quand des identifiants de transactions sont épuisés. Suivez les mêmes étapes que dans la section précédente mais avec les différences suivantes :

  1. Les transactions standards et préparées peuvent être ignorées s'il n'y a pas de risques qu'elles apparaissent dans une MultiXact.
  2. L'information MXID n'est pas directement visible dans les vues systèmes telles que pg_stat_activity ; néanmoins, rechercher les anciens identifiants de transactions est toujours une bonne facçon de déterminer les transactions pouvant poser des soucis de réutilisation des identifiants de MultiXact.
  3. L'épuisement d'identifiants de transactions bloquera toutes les transactions en écriture, tout spécifiquement celles qui imliquent des verrous de ligne nécessitant un identifiant MultiXact.

25.1.6. Le démon auto-vacuum

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. Quand il est 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 être utilisé que 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) est en charge de lancer des processus de traitement (autovacuum worker) pour toutes les bases de données. Le lanceur distribue le travail dans le temps en lançant un nouveau processus de traitement sur chaque base de données chaque autovacuum_naptime secondes. (Du coup, si l'installation a N bases de données, un nouveau autovacuum worker sera lancé tous les autovacuum_naptime/N secondes.) Un maximum de autovacuum_max_workers processus autovacuum worker est autorisé à s'exécuter 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 processus de traitement a terminé. Chaque processus de traitement vérifie chaque table de la base de données de connexion et exécute un VACUUM et/ou un ANALYZE suivant les besoins. log_autovacuum_min_duration peut être utilisé pour superviser l'activité des processus autovacuum worker.

Si plusieurs grosses tables deviennent toutes éligibles à un VACUUM dans un court espace de temps, tous les processus de traitement pourraient avoir à exécuter des VACUUM sur ces tables pendant un long moment. Ceci pourraient avoir pour résultat que d'autres tables et d'autres bases de données ne puissent pas être traitées tant qu'un processus de traitement ne sera pas disponible. Il n'y a pas de limite sur le nombre de processus de traitement sur une seule base, mais ils essaient d'éviter de répéter le travail qui a déjà été fait par d'autres. Notez que le nombre de processus de traitement en cours d'exécution n'est pas décompté des limites max_connections et superuser_reserved_connections.

Les tables dont la valeur de relfrozenxid est supérieure à autovacuum_freeze_max_age font toujours l'objet d'un VACUUM (cela s'applique aussi aux tables dont le freeze max age a été modifié avec les paramètres de stockage ; voir 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.

La table est aussi traitée si le nombre de lignes insérées depuis le dernier VACUUM a dépassé la limite d'insertion définie d'après cette formule :

limite insertion vacuum = limite insertion base vacuum + facteur échelle insertion vacuum * nombre de lignes
    

où la limite d'insertion de base du VACUUM correspond au paramètre autovacuum_vacuum_insert_threshold, et le facteur d'échelle d'insertion du VACUUM correspond au paramètre autovacuum_vacuum_insert_scale_factor. De tels VACUUM permettent de marquer des portions de la table comme tout visible et permettent aussi de geler les lignes, ce qui peut réduire le travail requis par les VACUUM suivant. Pour les tables recevant des opérations INSERT mais aucune ou très peu d'opérations UPDATE/DELETE, il peut être bénéfique de diminuer la valeur du paramètre autovacuum_freeze_min_age pour la table car cela permet le gel des lignes par des VACUUM rapides. Le nombre de lignes obsolètes et le nombre de lignes insérées sont obtenus via le collecteur de statistiques ; ce décompte moyennement précis est mis à jour par chaque opération UPDATE, DELETE et INSERT. (C'est seulement moyennement précis car certaines informations pourraient être perdues en cas de fortes charges.) Si la valeur du champ relfrozenxid pour la table est supérieure à vacuum_freeze_table_age, un VACUUM agressif est réalisé pour geler les anciennes lignes et avancer la valeur de relfrozenxid, sinon seules les blocs qui ont été modifiés 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, mises à jour et supprimées depuis le dernier ANALYZE.

Les tables partitionnées ne contiennent pas de lignes et ne sont donc pas traitées par l'autovacuum. (L'autovacuum traite les partitions comme n'importe quel autre table.)) Malheureusement, ceci signifie que l'autovacuum n'exécute pas d'ANALYZE sur les tables partitionnées, et ceci peut occasionner des plans non optimaux pour les requêtes qui référencent les statistiques de la table partitionnée. Vous pouvez contourner ce problème en exécutant manuellement ANALYZE sur les tables partitionnées quand elles sont peuplées la première fois, et à chaque fois que la distribution des données changent dans les partitions.

Les tables temporaires ne peuvent pas être accédées par l'autovacuum. De ce fait, les opérations appropriées de VACUUM et d'ANALYZE devraient être traitées par des commandes SQL de session.

Les limites et facteurs d'échelle par défaut sont pris dans postgresql.conf, mais il est possible de les surcharger table par table (ainsi que beaucoup d'autres paramètres de contrôle de l'autovacuum) ; voir Paramètres de stockage pour plus d'informations. Si une configuration a été modifiée via les paramètres de stockage d'une table, cette valeur est utilisée lors du traitement de cette table. Dans le cas contraire, les paramètres globaux sont utilisés. Voir Section 20.10 pour plus de détails sur les paramètres globaux.

Quand plusieurs autovacuum workers travaillent, les paramètres de délai de coût de l'autovacuum (voir Section 20.4.4) sont « réparties » parmi tous les processus pour que l'impact total en entrée/sortie sur le système soit identique quelque soit le nombre de processus en cours d'exécution. Néanmoins, tout autovacuum worker traitant des tables et dont les paramètres de stockage autovacuum_vacuum_cost_delay ou autovacuum_vacuum_cost_limit ont été configurés spécifiquement ne sont pas considérés dans l'algorithme de répartition.

Les processus de traitement de l'autovacuum ne bloquent généralement pas les autres commandes. Si un processus tente d'acquérir un verrou qui entre en conflit avec le verrou SHARE UPDATE EXCLUSIVE détenu par autovacuum, la demande de verrou interrompra l'autovacuum. Pour les modes de verrou entrant en conflit, voir Tableau 13.2. Néanmoins, si l'autovacuum s'exécute pour empêcher une réutilisation des identifiants de transaction (c'est-à-dire si le texte de la requête de l'autovacuum dans la vue pg_stat_activity se termine avec le texte (to prevent wraparound)), l'autovacuum n'est pas automatiquement interrompu.

Avertissement

Exécuter régulièrement des commandes qui acquièrent des verrous en conflit avec un verrou SHARE UPDATE EXCLUSIVE (par exemple ANALYZE) peut gêner fortement le traitement réalisé par l'autovacuum.