Menu

VACUUM et autovacuum PostgreSQL : lignes mortes, bloat

VACUUM supprime les versions mortes des lignes que laissent UPDATE et DELETE, pour que leur espace soit réutilisé. L'autovacuum le lance pour vous ; cette page montre quand, comment le régler, et quand VACUUM FULL vaut son verrou.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

VACUUM supprime les versions mortes des lignes, les anciennes copies que UPDATE et DELETE laissent dans une table, et marque leur espace comme libre pour de nouvelles lignes. VACUUM ANALYZE met aussi à jour les statistiques utilisées par le planificateur. VACUUM FULL réécrit toute la table pour rendre l'espace au système d'exploitation. L'autovacuum, activé par défaut, lance VACUUM et ANALYZE pour vous en arrière-plan.

Après le DELETE, la table contient 700 lignes vivantes et 300 mortes ; après VACUUM, 0 ligne morte. Les vues de statistiques se mettent à jour avec un délai ; pg_stat_force_next_flush() (PostgreSQL 15+) les met à jour tout de suite pour cette démonstration.

Pourquoi PostgreSQL laisse des lignes mortes

PostgreSQL garde les anciennes versions des lignes pour que chaque transaction voie un instantané cohérent (MVCC). Un UPDATE écrit une nouvelle version de la ligne et marque l'ancienne comme expirée ; un DELETE se contente de la marquer. L'ancienne version reste là tant qu'une transaction en cours peut en avoir besoin, puis VACUUM peut la supprimer. ctid, l'emplacement physique d'une ligne, montre le déplacement :

La ligne 1 commence en (0,1), page 0 emplacement 1, et après la mise à jour elle se trouve en (0,3). L'emplacement 1 contient toujours l'ancienne version jusqu'au nettoyage. Une table souvent mise à jour produit sans cesse des lignes mortes, donc l'autovacuum y revient régulièrement.

VACUUM ou VACUUM FULL

Un VACUUM simple laisse le fichier à la même taille : l'espace libre reste dans la table pour les insertions futures. VACUUM FULL écrit une nouvelle copie compacte et supprime l'ancien fichier.

VACUUMVACUUM FULL
Verrouautorise lectures et écrituresbloque tout accès
Espaceréutilisé par la même tablerendu au système d'exploitation
Disque supplémentairenonde quoi stocker une copie complète de la table
Quandrégulièrement (l'autovacuum s'en charge)rarement, après avoir supprimé l'essentiel d'une grande table

Les options se placent entre parenthèses :

  • VACUUM (VERBOSE, ANALYZE) items; affiche ce qui a été fait et met à jour les statistiques.
  • VACUUM (PARALLEL 4) (PostgreSQL 13+) traite les index avec des workers parallèles.
  • VACUUM (SKIP_LOCKED) saute les tables qu'il ne peut pas verrouiller immédiatement.

Comment l'autovacuum décide de se lancer

L'autovacuum vérifie chaque table toutes les autovacuum_naptime. Il lance un vacuum sur la table quand ses lignes mortes dépassent autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table rows : avec les valeurs par défaut, 50 lignes plus 20 % de la table. Il lance un analyze quand les lignes modifiées depuis le dernier analyze dépassent 50 plus 10 %. Depuis PostgreSQL 13, les insertions seules déclenchent aussi un vacuum. Les valeurs par défaut dans postgresql.conf :

autovacuum = on
autovacuum_naptime = 1min
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

Sur une table de 100 millions de lignes, 20 % représente 20 millions de lignes mortes avant qu'un vacuum ne démarre. Baissez le facteur d'échelle uniquement pour les grandes tables très sollicitées :

ALTER TABLE events RESET (autovacuum_vacuum_scale_factor); remet la table sur le réglage global du serveur. Le chapitre sur le vacuum de routine couvre les autres paramètres.

Vérifier ce qu'a fait l'autovacuum

pg_stat_user_tables montre les lignes mortes et le dernier vacuum manuel et automatique de chaque table :

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Si les lignes mortes continuent d'augmenter alors que l'autovacuum tourne, cherchez les vieilles transactions ouvertes, qui l'empêchent de supprimer quoi que ce soit de plus récent que leur instantané :

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;

Gel des lignes et wraparound des identifiants de transaction

Les identifiants de transaction sont des compteurs de 32 bits qui reviennent à zéro. Pour que les anciennes lignes restent visibles, le vacuum les gèle, c'est-à-dire qu'il les marque comme plus anciennes que toute transaction. Quand l'identifiant non gelé le plus ancien d'une table dépasse autovacuum_freeze_max_age (200 millions de transactions par défaut), l'autovacuum s'exécute sur elle même s'il est désactivé.

Si le gel prend quand même beaucoup de retard, PostgreSQL cesse d'attribuer de nouveaux identifiants de transaction, et les écritures échouent jusqu'à ce qu'un vacuum rattrape le retard. Cette requête montre à quel point chaque base s'en approche :

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Questions fréquentes

VACUUM verrouille-t-il la table ?

Un VACUUM simple prend un verrou qui autorise toujours SELECT, INSERT, UPDATE et DELETE ; il n'entre en conflit qu'avec les changements de schéma, la création d'index, ANALYZE et les autres vacuums. VACUUM FULL prend un verrou ACCESS EXCLUSIVE, qui bloque toutes les requêtes sur la table jusqu'à la fin.

Faut-il désactiver l'autovacuum ?

Non. Sans lui, les lignes mortes s'accumulent, les statistiques vieillissent et la base finit par devoir refuser les écritures pour éviter le wraparound des identifiants de transaction. Réglez plutôt ses seuils table par table.

Pourquoi ma table ne rapetisse-t-elle pas après un VACUUM ?

Un VACUUM simple marque l'espace des lignes mortes comme libre pour de nouvelles lignes de la même table ; il ne rend de l'espace au système d'exploitation que si les pages vides sont à la fin du fichier. VACUUM FULL réécrit la table et rend l'espace.

Pourquoi VACUUM ne supprime-t-il pas les lignes mortes ?

Une ligne ne peut être supprimée que si aucune transaction en cours ne risque d'en avoir besoin. Une transaction ouverte depuis longtemps (souvent une session inactive qui a lancé BEGIN), un ancien slot de réplication ou une transaction préparée abandonnée les retient.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer