VACUUM et Autovacuum PostgreSQL : Guide Complet d'Optimisation

TL;DR — Résumé Rapide

Guide complète VACUUM et autovacuum PostgreSQL : workers, seuils, cost delay, surveillance du bloat, prévention du wraparound et pg_repack pour DBAs.

Le modèle de contrôle de concurrence multiversion (MVCC) de PostgreSQL conserve les anciennes versions de lignes pour servir les lecteurs concurrents. Sans nettoyage régulier, ces tuples morts s’accumulent, gonflent la taille des tables, ralentissent les scans séquentiels et menacent finalement la disponibilité de la base de données via le wraparound du Transaction ID. Ce guide couvre tous les leviers qu’un DBA peut actionner : des paramètres globaux d’autovacuum aux substitutions par table, des requêtes de surveillance du bloat à la réorganisation en ligne avec pg_repack.

Prérequis

Avant de commencer, assurez-vous d’avoir :

• PostgreSQL 13 ou ultérieur (la plupart des exemples fonctionnent depuis la version 12+ ; certaines syntaxes nécessitent la 14+).
• Accès à psql ou pgAdmin avec le rôle superuser ou pg_monitor.
• Extension pg_repack installée si vous planifiez une réorganisation en ligne (abordée plus loin).
• Familiarité avec postgresql.conf et la capacité de recharger la configuration.

MVCC et Tuples Morts

PostgreSQL ne modifie jamais une ligne sur place. Un UPDATE écrit une nouvelle version du tuple et marque l’ancienne comme morte. Un DELETE marque également la ligne comme morte sans la supprimer. Cette conception permet aux lecteurs de voir un instantané cohérent sans prendre de verrous, mais la contrepartie est l’accumulation de tuples morts sur le disque.

Les tuples morts consomment de l’espace disque, gonflent la taille physique des tables et ralentissent les scans séquentiels car PostgreSQL doit lire et ignorer chaque page même si la plupart de ses lignes sont mortes. La vue pg_stat_user_tables suit le comptage :

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Une table avec dead_pct supérieur à 10–15% est candidate à une attention immédiate.

VACUUM, VACUUM FULL et VACUUM ANALYZE

PostgreSQL intègre trois variantes de VACUUM avec des comportements très différents :

Commande	Bloque la table ?	Restitue l’espace au SO ?	Met à jour les statistiques ?	Sûr en production ?
VACUUM	Non (ShareUpdateExclusiveLock)	Non — marque l’espace réutilisable	Non	Oui
VACUUM ANALYZE	Non	Non	Oui	Oui
VACUUM FULL	Oui (AccessExclusiveLock)	Oui	Non	Avec précaution

VACUUM marque les tuples morts comme disponibles pour réutilisation. Le fichier physique ne se réduit pas — les pages libérées sont ajoutées à la Free Space Map (FSM) pour de futures insertions. C’est la commande principale sur laquelle vous devez vous appuyer.

VACUUM FULL réécrit toute la table dans un nouveau fichier heap, puis supprime l’ancien. Il restitue l’espace disque au système d’exploitation et supprime le bloat des index en les reconstruisant, mais acquiert un verrou exclusif pendant toute son exécution. Sur une table de 100 Go, cela peut représenter 30 minutes ou plus d’indisponibilité totale. N’exécutez jamais VACUUM FULL sur une table de production active sans planifier une fenêtre de maintenance — ou mieux, utilisez pg_repack.

VACUUM ANALYZE combine le nettoyage des tuples morts avec une mise à jour des statistiques, que le planificateur de requêtes utilise pour choisir des plans d’exécution optimaux.

Configuration de l’Autovacuum

L’autovacuum exécute des workers en arrière-plan qui appellent VACUUM et ANALYZE automatiquement. Les paramètres clés se trouvent dans postgresql.conf :

# Nombre de workers autovacuum concurrents (défaut : 3)
autovacuum_max_workers = 5

# Fréquence à laquelle le lanceur d'autovacuum vérifie les tables (défaut : 1min)
autovacuum_naptime = 30s

# Minimum de tuples morts avant de déclencher VACUUM (défaut : 50)
autovacuum_vacuum_threshold = 50

# Fraction de lignes vives pouvant être mortes avant de déclencher VACUUM (défaut : 0.2 = 20%)
autovacuum_vacuum_scale_factor = 0.05

# Minimum de modifications de lignes avant de déclencher ANALYZE (défaut : 50)
autovacuum_analyze_threshold = 50

# Fraction de lignes modifiées avant de déclencher ANALYZE (défaut : 0.1 = 10%)
autovacuum_analyze_scale_factor = 0.02

# Limitation E/S : délai entre opérations de buffer du vacuum en millisecondes (défaut : 2)
autovacuum_vacuum_cost_delay = 2ms

# Maximum d'unités de coût dépensées avant un délai (défaut : -1 = utilise vacuum_cost_limit = 200)
autovacuum_vacuum_cost_limit = 400

Comprendre la Formule de Déclenchement

L’autovacuum se déclenche sur une table quand :

n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

Pour une table de 10 millions de lignes avec les valeurs par défaut :

seuil = 50 + 0.20 × 10 000 000 = 2 000 050 tuples morts

Cela signifie que 20% de la table doit être morte avant que l’autovacuum se déclenche. Pour une table à forte écriture, c’est trop tardif. Réduire autovacuum_vacuum_scale_factor à 0.01 ou même 0.005 est approprié pour les grandes tables fréquemment mises à jour.

Après avoir modifié postgresql.conf, rechargez la configuration (pas de redémarrage nécessaire) :

SELECT pg_reload_conf();

Surveillance du Bloat

Les vues pg_stat_user_tables et pg_stat_activity sont vos principaux outils de surveillance.

Requête de Tableau de Bord des Tuples Morts

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Trouver les Workers Autovacuum en Cours d’Exécution

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

Substitutions d’Autovacuum par Table

Les paramètres globaux affectent toutes les tables. Les grandes tables fréquemment mises à jour ont besoin de seuils plus serrés. Appliquez des paramètres de stockage par table avec ALTER TABLE :

-- Déclencher le vacuum quand 1% des lignes sont mortes (au lieu du défaut de 20%)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 0
);

-- Vérifier les substitutions
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

C’est le changement le plus impactant que vous puissiez faire pour une table active. Le facteur d’échelle global reste conservateur pour les petites tables, tandis que la table active reçoit un vacuum agressif.

Prévention du Wraparound de Transaction ID

PostgreSQL utilise des IDs de transaction (XIDs) 32 bits. Après environ 2,1 milliards de transactions, le compteur se réinitialise. Les tuples plus anciens que l’horizon de wraparound pourraient redevenir visibles ou invisibles, causant une corruption de données. PostgreSQL se protège en forçant un freeze via l’autovacuum avant que l’horizon soit atteint.

Vérification de l’Âge XID

SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Quand xid_age dépasse autovacuum_freeze_max_age (défaut : 200 millions), l’autovacuum forcera un freeze vacuum sur cette table. Surveillez au niveau de la base de données :

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

Configurez une alerte quand l’âge XID de n’importe quelle base dépasse 1,5 milliard.

pg_repack : Réorganisation en Ligne des Tables

Quand VACUUM ne peut pas récupérer l’espace physique et que VACUUM FULL est inacceptable en raison du verrouillage, pg_repack est la solution. Il reconstruit la table en utilisant une table fantôme et des triggers, puis les permute atomiquement — tout en maintenant la table originale entièrement disponible.

Installation de pg_repack

# Ubuntu/Debian
sudo apt install postgresql-15-repack

-- Dans la base de données cible
CREATE EXTENSION pg_repack;

Réorganisation d’une Table en Ligne

# Réorganiser une seule table avec du bloat
pg_repack -d mydb -t orders

# Réorganiser toutes les tables d'une base de données
pg_repack -d mydb

# Simulation (montrer ce qui se passerait)
pg_repack -d mydb -t orders --dry-run

---

Comparaison pg_repack vs VACUUM FULL :

Aspect	pg_repack	VACUUM FULL
Verrou pendant la reconstruction	Aucun	AccessExclusiveLock (durée complète)
Espace disque requis	2× taille de la table temporairement	2× taille de la table temporairement
Reconstruction des index	Oui (concurrent)	Oui (bloquant)
Nécessite une clé primaire	Oui	Non
Sûr en production	Oui	Uniquement en fenêtre de maintenance

Bloat des Index et REINDEX CONCURRENTLY

Les index accumulent du bloat indépendamment de la table. Reconstruisez les index sans interruption de service :

-- Reconstruire un index unique de manière concurrente
REINDEX INDEX CONCURRENTLY orders_created_at_idx;

-- Reconstruire tous les index d'une table de manière concurrente (PostgreSQL 14+)
REINDEX TABLE CONCURRENTLY orders;

Interaction avec PgBouncer et Autovacuum

PgBouncer en mode de pooling de transactions peut faire en sorte que des connexions applicatives inactives maintiennent des transactions ouvertes qui fixent le xmin le plus ancien. Configurez idle_in_transaction_session_timeout = '5min' dans PostgreSQL pour terminer automatiquement les transactions obsolètes.

SELECT slot_name, xmin, catalog_xmin, age(xmin) FROM pg_replication_slots;

Vérifiez également les slots de réplication, car ils peuvent fixer le xmin indéfiniment.

Pièges Courants

1. Définir autovacuum = off globalement. Ne faites jamais cela en production. Sans autovacuum, l’arrêt par wraparound est inévitable.

2. Exécuter VACUUM FULL en heures de pointe. Le verrou exclusif bloque tout. Planifiez-le en fenêtre de maintenance ou utilisez pg_repack.

3. Ignorer les transactions longues. Si une transaction longue maintient un xmin ancien, VACUUM ne peut pas supprimer les tuples morts quels que soient les paramètres.

4. Configurer autovacuum_max_workers trop élevé. Chaque worker consomme des connexions et des E/S. Ajustez d’abord cost_delay et cost_limit.

5. Utiliser pg_repack sur des tables avec des triggers différés. pg_repack est incompatible avec les triggers différés. Vérifiez avec \d+ tablename dans psql avant d’exécuter.

Résumé

Les performances saines de PostgreSQL dépendent de la capacité de VACUUM à suivre le rythme de votre charge d’écriture :

• Réduisez autovacuum_vacuum_scale_factor pour les grandes tables à forte écriture — le défaut de 20% est trop permissif.
• Surveillez n_dead_tup et last_autovacuum en continu via pg_stat_user_tables.
• Vérifiez xid_age régulièrement ; alertez au-dessus de 1,5 milliard pour prévenir les urgences de wraparound.
• Utilisez des substitutions par table (ALTER TABLE SET) plutôt que d’assouplir les paramètres globaux pour toutes les tables.
• Remplacez VACUUM FULL par pg_repack pour la réorganisation en ligne sans interruption.
• Reconstruisez les index avec REINDEX CONCURRENTLY quand le bloat d’index affecte les performances des requêtes.

Articles Connexes

• Optimisation des performances PostgreSQL : Configuration et requêtes
• Gestion des pools de stockage ZFS sur Linux
• Docker Compose PostgreSQL : Configuration et bonnes pratiques