Guide d'optimisation VACUUM et autovacuum PostgreSQL

TL;DR — Résumé Rapide

Maîtrisez VACUUM et autovacuum PostgreSQL pour éliminer le bloat, prévenir le wraparound XID et maximiser les performances de votre base de données.

VACUUM dans PostgreSQL est l’une des opérations de maintenance les plus incomprises et pourtant les plus critiques dans tout déploiement PostgreSQL. Sans réglage approprié, les tables accumulent silencieusement des tuples morts qui font gonfler le stockage, dégradent les performances des requêtes et, dans le pire des cas, déclenchent un wraparound catastrophique d’ID de transaction qui met toute la base de données hors ligne. Ce guide couvre tout ce dont vous avez besoin pour configurer autovacuum correctement et maintenir les bases de données de production en bonne santé.

Prérequis

• PostgreSQL 13 ou supérieur (la plupart des exemples s’appliquent depuis PostgreSQL 10+)
• psql ou un client SQL avec accès niveau DBA
• Capacité à éditer postgresql.conf ou utiliser ALTER SYSTEM
• Familiarité de base avec les concepts de stockage PostgreSQL

Pourquoi VACUUM existe : MVCC et tuples morts

PostgreSQL utilise le Contrôle de Concurrence Multi-Version (MVCC) pour gérer les lectures et écritures concurrentes sans verrouillage. Chaque UPDATE ou DELETE ne modifie pas les données en place — il marque l’ancienne version de la ligne comme morte et écrit une nouvelle version. Cela rend les lectures non bloquantes, mais laisse derrière des tuples morts : des versions de lignes qui ne sont plus visibles par aucune transaction mais occupent toujours de l’espace disque.

Avec le temps, les tuples morts causent trois problèmes graves :

1. Bloat de table : Le fichier physique de la table grossit même si le nombre logique de lignes reste constant. Une table avec 10 millions de lignes actives pourrait occuper l’espace de 50 millions sur disque.
2. Bloat d’index : Les index B-tree accumulent des pointeurs vers des tuples morts, augmentant leur taille et le coût d’analyse.
3. Wraparound d’ID de transaction : PostgreSQL utilise des ID de transaction 32 bits. Après environ 2 milliards de transactions, les ID bouclent. PostgreSQL force un arrêt d’urgence sur toute la base de données si VACUUM ne peut pas geler les anciennes transactions à temps.

VACUUM résout les trois problèmes en analysant les pages de tables, marquant les tuples morts comme espace réutilisable et faisant avancer l’horizon de gel pour les anciennes transactions.

Types de VACUUM comparés

Commande	Verrou	Récupération d’espace	Cas d’usage
VACUUM	Aucun (partage avec lectures/écritures)	Marque l’espace pour réutilisation, ne rétrécit pas le fichier	Maintenance routinière, autovacuum
VACUUM ANALYZE	Aucun	Idem + met à jour les statistiques du planificateur	Après insertions/suppressions en masse
VACUUM FULL	Exclusif (table verrouillée)	Réécrit la table, rend l’espace au SO	Récupération ponctuelle de bloat sévère
VACUUM FREEZE	Aucun	Force le gel de tous les anciens XIDs	Prévention du wraparound

Utilisez VACUUM FULL uniquement comme étape de récupération ponctuelle sur des tables très gonflées — il maintient un verrou exclusif qui bloque toutes les lectures et écritures pendant son exécution. Pour des réécritures de tables en ligne, utilisez pg_repack.

Le démon autovacuum : paramètres principaux

Autovacuum s’exécute comme un démon d’arrière-plan qui lance des processus worker pour vider les tables automatiquement. Les paramètres clés dans postgresql.conf :

# À quelle fréquence le launcher vérifie s'il y a du travail
autovacuum_naptime = 1min

# Maximum de workers autovacuum concurrents
autovacuum_max_workers = 3

# Déclenche vacuum quand tuples morts > threshold + scale_factor * n_live_tup
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2       # 20% de la table

# Déclenche ANALYZE quand les tuples modifiés dépassent ceci
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1      # 10% de la table

# Gèle les lignes de cette ancienneté de transaction
autovacuum_freeze_max_age = 200000000

Le point critique de réglage est autovacuum_vacuum_scale_factor. La valeur par défaut de 0.2 signifie qu’autovacuum se déclenche quand 20% des lignes d’une table sont mortes. Pour une table de 100 lignes, c’est acceptable. Pour une table de 50 millions de lignes, autovacuum ne se déclenchera pas avant que 10 millions de tuples morts se soient accumulés.

Valeurs globales recommandées pour les systèmes à forte écriture :

autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 5
autovacuum_naptime = 30s

Paramètres autovacuum par table

La technique de réglage la plus puissante consiste à définir des paramètres autovacuum par table :

-- Pour une table à très forte écriture (ex. : log d'événements/audit)
ALTER TABLE evenements SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2
);

-- Pour une table de consultation quasi-statique (vacuum moins agressif)
ALTER TABLE codes_pays SET (
    autovacuum_vacuum_scale_factor = 0.5,
    autovacuum_vacuum_threshold = 1000
);

Surveillance du bloat de tables

Avec pg_stat_user_tables

-- Top 20 tables par nombre de tuples morts
SELECT
    schemaname,
    relname AS nom_table,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS pct_morts,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Détecter les tables proches du wraparound XID

SELECT
    schemaname,
    relname,
    age(relfrozenxid) AS age_xid,
    pg_size_pretty(pg_total_relation_size(oid)) AS taille_table
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = relnamespace
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Toute table avec age_xid supérieur à 1,5 milliard nécessite une attention immédiate.

Mesurer le bloat réel avec pgstattuple

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    dead_tuple_count,
    round(dead_tuple_percent, 1) AS pct_morts,
    free_space,
    round(free_percent, 1) AS pct_libre
FROM pgstattuple('public.commandes');

Prévention du wraparound XID

Surveillez l’âge au niveau de la base de données :

SELECT
    datname,
    age(datfrozenxid) AS age_xid,
    pg_size_pretty(pg_database_size(datname)) AS taille_bd
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Récupération d’urgence si une base est proche du wraparound :

vacuumdb --all --freeze --verbose --analyze

VACUUM FULL vs pg_repack

Pour les systèmes de production qui ne peuvent pas se permettre de temps d’arrêt, utilisez pg_repack :

sudo apt install postgresql-16-repack
pg_repack -h localhost -U postgres -d mabd -t commandes
pg_repack -h localhost -U postgres -d mabd --only-indexes -t commandes

Problèmes courants et résolution

Autovacuum tué par des transactions longues : Recherchez les transactions obsolètes :

SELECT pid, now() - xact_start AS duree, query, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duree DESC
LIMIT 10;

“Autovacuum: preventing wraparound” dans les logs : Autovacuum est en mode urgence, ignorant le throttle de coût. Ne bloquez ni ne tuez ces processus vacuum.

Référence de réglage par taille de base

Taille de BD	autovacuum_max_workers	autovacuum_vacuum_scale_factor	autovacuum_vacuum_cost_limit
< 10 Go	3 (défaut)	0.05	200 (défaut)
10–100 Go	4–5	0.02	400
100 Go – 1 To	6–8	0.01	600
> 1 To	8–10 + par table	0.005 sur tables actives	800

---

Comparatif entre systèmes de bases de données

Fonctionnalité	PostgreSQL VACUUM	MySQL OPTIMIZE TABLE	SQL Server Index Rebuild	Oracle Segment Shrink
Verrous requis	Aucun (régulier), Exclusif (FULL)	Exclusif	En ligne (Enterprise)	Exclusif de ligne
Outil de réécriture en ligne	pg_repack	pt-online-schema-change	Rebuild en ligne	SHRINK SPACE COMPACT
Automatique	Démon autovacuum	Manuel ou event scheduler	Maintenance automatique d’index	Non automatique
Risque de wraparound	Oui (XID)	Non	Non	Non
Visibilité du bloat	pg_stat_user_tables, pgstattuple	information_schema	sys.dm_db_index_physical_stats	DBA_SEGMENTS

Résumé

• VACUUM récupère l’espace des tuples morts des mises à jour et suppressions MVCC ; sans lui, les tables gonflent et le wraparound XID finit par arrêter la base.
• Autovacuum est la solution automatique mais nécessite un réglage — la valeur par défaut autovacuum_vacuum_scale_factor = 0.2 est trop élevée pour les grandes tables.
• Utilisez la configuration par table avec ALTER TABLE SET pour les tables à forte écriture.
• Surveillez pg_stat_user_tables et age(datfrozenxid) dans pg_database.
• Utilisez pg_repack plutôt que VACUUM FULL sur les tables de production pour éviter les temps d’arrêt.
• Maintenez age(datfrozenxid) bien en dessous de 1,5 milliard sur toutes les bases.

Articles Connexes

• Guide d’optimisation des index PostgreSQL
• Connection Pooling PostgreSQL avec PgBouncer
• Surveillance PostgreSQL avec pg_stat_statements
• Configuration PostgreSQL Docker pour la production