🔝 Retour au Sommaire

16.10.1. VACUUM : Récupération d'espace et prévention XID wraparound

Introduction : Pourquoi VACUUM existe-t-il ?

PostgreSQL utilise un mécanisme appelé MVCC (Multiversion Concurrency Control) pour gérer la concurrence entre les transactions. Ce mécanisme permet à plusieurs utilisateurs de lire et modifier des données simultanément sans se bloquer mutuellement.

Le principe de MVCC en quelques mots

Quand vous mettez à jour ou supprimez une ligne dans PostgreSQL, la base de données ne supprime pas immédiatement l'ancienne version de la ligne. Au lieu de cela :

L'ancienne version est marquée comme obsolète (on dit qu'elle devient un "tuple mort" ou dead tuple)
Une nouvelle version de la ligne est créée (pour un UPDATE)
Les transactions en cours peuvent encore voir l'ancienne version si nécessaire

Conséquence importante : Les anciennes versions s'accumulent et occupent de l'espace disque !

Exemple concret

Imaginons une table clients :

-- État initial
id | nom      | email
1  | Alice    | alice@exemple.com

-- Vous faites un UPDATE
UPDATE clients SET email = 'alice.nouveau@exemple.com' WHERE id = 1;

-- En réalité, PostgreSQL conserve temporairement :
-- Ancienne version (marquée comme morte) :
1  | Alice    | alice@exemple.com

-- Nouvelle version (visible) :
1  | Alice    | alice.nouveau@exemple.com

Si vous faites 1000 mises à jour sur cette même ligne, vous aurez 1000 versions "mortes" qui encombrent votre base !

C'est là qu'intervient VACUUM.

Qu'est-ce que VACUUM ?

VACUUM est une opération de maintenance essentielle qui :

Récupère l'espace disque occupé par les lignes mortes (dead tuples)
Prévient un problème critique appelé "transaction ID wraparound"
Met à jour les statistiques de visibilité des pages

Analogie simple

Imaginez votre base de données comme un grand classeur :

Quand vous modifiez un document, vous ne déchirez pas l'ancien, vous le rayez et ajoutez le nouveau
Avec le temps, le classeur se remplit de documents rayés qui prennent de la place
VACUUM est comme une opération de nettoyage qui retire physiquement les documents rayés pour récupérer de l'espace

1. Récupération d'espace disque

Pourquoi l'espace disque augmente-t-il ?

Les opérations qui créent des lignes mortes :

UPDATE : Crée une nouvelle version, l'ancienne devient morte
DELETE : Marque la ligne comme morte (elle n'est pas immédiatement supprimée !)
Transactions annulées (ROLLBACK) : Les lignes créées/modifiées deviennent mortes

Comment VACUUM récupère l'espace

Quand vous exécutez VACUUM, PostgreSQL :

Parcourt les tables pour identifier les lignes mortes
Marque l'espace comme réutilisable (mais ne le rend pas immédiatement au système d'exploitation)
Met à jour les index pour retirer les références aux lignes mortes
Permet la réutilisation de cet espace pour de nouvelles insertions

Les différents types de VACUUM

a) VACUUM simple (standard)

VACUUM;  -- Sur toute la base de données  
VACUUM nom_table;  -- Sur une table spécifique

Caractéristiques :

Récupère l'espace mais ne le rend pas au système d'exploitation
L'espace est marqué comme "réutilisable" dans la table
Rapide et non bloquant (la table reste accessible en lecture/écriture)
Peut tourner en arrière-plan

Quand l'utiliser : C'est la maintenance quotidienne normale, souvent gérée automatiquement par autovacuum.

b) VACUUM FULL

VACUUM FULL nom_table;

Caractéristiques :

Réécrit complètement la table et ses index
Rend l'espace au système d'exploitation
Très lent et bloquant (pose un verrou exclusif, aucune lecture/écriture possible)
Nécessite de l'espace disque temporaire (jusqu'à 2× la taille de la table)

Quand l'utiliser : Seulement en cas de "bloat" extrême (table très gonflée) et en dehors des heures de production.

⚠️ Attention : VACUUM FULL est une opération lourde, à utiliser avec précaution !

c) VACUUM ANALYZE

VACUUM ANALYZE nom_table;

Combine VACUUM avec ANALYZE (mise à jour des statistiques du planificateur de requêtes).

Avantage : Optimise à la fois l'espace disque ET les performances des requêtes.

Exemple de situation réelle

-- Vous avez une table "logs" avec beaucoup de suppressions quotidiennes
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insertion de 1 million de lignes
INSERT INTO logs (message)  
SELECT 'Message ' || i FROM generate_series(1, 1000000) AS i;  

-- Suppression de 900 000 lignes anciennes
DELETE FROM logs WHERE id < 900000;

-- À ce stade, vous avez 900 000 lignes mortes !
-- La table occupe toujours l'espace des 1 million de lignes

-- Solution : VACUUM
VACUUM ANALYZE logs;

-- Résultat : L'espace des 900 000 lignes est maintenant réutilisable
-- Les nouvelles insertions utiliseront cet espace "libéré"

Vérifier le "bloat" (gonflement) d'une table

Le bloat est le ratio entre l'espace réellement utilisé et l'espace occupé sur disque.

-- Voir la taille d'une table
SELECT pg_size_pretty(pg_total_relation_size('logs'));

-- Requête simplifiée pour estimer le bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_pct
FROM pg_stat_user_tables  
WHERE n_dead_tup > 1000  
ORDER BY n_dead_tup DESC;

Indicateurs :

dead_ratio_pct > 20% → Il est temps de lancer un VACUUM
dead_ratio_pct > 50% → Situation critique, VACUUM urgent

2. Prévention du XID Wraparound (Transaction ID Wraparound)

Qu'est-ce qu'un Transaction ID (XID) ?

Chaque transaction dans PostgreSQL reçoit un identifiant unique appelé Transaction ID (XID).

C'est un nombre entier 32 bits
Plage : de 0 à environ 4 milliards (2^32)
Utilisé pour déterminer la visibilité des lignes (quelle transaction peut voir quelle version d'une ligne)

Le problème du wraparound

Imaginez un compteur qui va de 0 à 4 milliards :

Transaction 1 → XID = 1  
Transaction 2 → XID = 2  
...
Transaction 4 000 000 000 → XID = 4 000 000 000  
Transaction 4 000 000 001 → XID = ... 0 (retour au début !)

Le drame : Quand le compteur fait le tour (wraparound), PostgreSQL ne peut plus déterminer correctement quelles données sont "anciennes" ou "récentes" !

Conséquence catastrophique : Perte de données

Si le wraparound se produit sans intervention :

PostgreSQL ne peut plus savoir quelles lignes doivent être visibles
Les anciennes lignes pourraient soudainement apparaître comme "futures"
Perte totale de cohérence des données

Pour éviter ce désastre, PostgreSQL arrêtera automatiquement la base de données avant le wraparound.

Comment VACUUM prévient le wraparound

VACUUM "gèle" (freeze) les anciennes lignes :

Freezing : VACUUM marque les anciennes transactions comme "infiniment dans le passé"
Une ligne "gelée" est visible par toutes les transactions futures, quel que soit leur XID
Cela libère des XIDs pour de nouvelles transactions

Illustration simplifiée

Ligne créée par XID 100 :
├─ Initialement visible uniquement pour XID > 100
└─ Après VACUUM (freeze) → visible par TOUTES les transactions (XID quelconque)

Paramètres critiques

PostgreSQL a des seuils de sécurité :

-- Voir les paramètres de wraparound
SHOW autovacuum_freeze_max_age;  -- Par défaut : 200 millions de transactions  
SHOW vacuum_freeze_min_age;      -- Par défaut : 50 millions de transactions

Explications :

autovacuum_freeze_max_age : Nombre maximum de transactions avant qu'un VACUUM "freeze" soit déclenché automatiquement
vacuum_freeze_min_age : Âge minimum d'une transaction avant qu'elle puisse être "gelée"

Surveiller l'état du wraparound

-- Vérifier l'âge des transactions par table
SELECT
    schemaname,
    tablename,
    age(relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_stat_user_tables  
JOIN pg_class ON relname = tablename  
ORDER BY age(relfrozenxid) DESC  
LIMIT 10;

Interprétation :

xid_age < 100 millions → Situation normale
xid_age > 150 millions → Surveiller de près
xid_age > 180 millions → Action urgente requise

Que faire en cas d'alerte wraparound ?

Vacuum manuel immédiat :
```
VACUUM FREEZE nom_table;
```
Pour toute la base :
```
VACUUMDB --all --freeze
```

En cas de blocage imminent, PostgreSQL affiche des messages d'avertissement dans les logs :

WARNING: database "mydb" must be vacuumed within 1000000 transactions
ERROR: database is not accepting commands to avoid wraparound data loss

Autovacuum : Le gardien automatique

Heureusement, vous n'avez généralement pas à lancer VACUUM manuellement !

Qu'est-ce que l'Autovacuum ?

Autovacuum est un processus d'arrière-plan qui :

Lance automatiquement VACUUM sur les tables qui en ont besoin
Surveille le XID wraparound et lance des VACUUM préventifs
Ajuste dynamiquement sa fréquence selon l'activité de la base

Configuration par défaut

-- Voir si autovacuum est activé
SHOW autovacuum;  -- Devrait être 'on' (activé par défaut)

-- Paramètres principaux
SHOW autovacuum_vacuum_threshold;         -- 50 lignes mortes minimum  
SHOW autovacuum_vacuum_scale_factor;      -- 0.2 (20% de la table)  
SHOW autovacuum_vacuum_cost_delay;        -- 2 ms (throttling)  
SHOW autovacuum_naptime;                  -- 1 min (fréquence de réveil)

Comment autovacuum décide-t-il de lancer un VACUUM ?

Formule simplifiée :

Seuil = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × nombre_total_de_lignes)

Exemple :

Table avec 1 000 000 de lignes
Seuil = 50 + (0.2 × 1 000 000) = 200 050 lignes mortes
Autovacuum se déclenche quand il y a plus de 200 050 lignes mortes

Nouveautés PostgreSQL 18 : Autovacuum amélioré

1. Ajustements dynamiques

PostgreSQL 18 ajuste automatiquement le nombre de workers autovacuum selon la charge :

-- Nouveau paramètre dans PG 18
SHOW autovacuum_worker_slots;  -- Allocation dynamique de workers

2. Nouveau paramètre : autovacuum_vacuum_max_threshold

SHOW autovacuum_vacuum_max_threshold;  -- Nouveau dans PG 18

Ce paramètre permet de plafonner le seuil de déclenchement pour les très grandes tables, évitant d'attendre trop longtemps avant le VACUUM.

Surveiller l'activité de l'autovacuum

-- Voir les autovacuum en cours
SELECT
    pid,
    usename,
    query_start,
    state,
    query
FROM pg_stat_activity  
WHERE query LIKE '%autovacuum%'  
  AND query NOT LIKE '%pg_stat_activity%';

-- Historique des derniers autovacuum (nouveau dans PG 18)
SELECT
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count
FROM pg_stat_all_tables  
WHERE schemaname = 'public'  
ORDER BY last_autovacuum DESC NULLS LAST;

Bonnes pratiques

1. Faire confiance à l'autovacuum

Dans 95% des cas, autovacuum suffit :

✅ Activé par défaut
✅ Fonctionne en arrière-plan
✅ S'adapte à la charge

Ne désactivez JAMAIS autovacuum sauf si vous savez exactement ce que vous faites !

2. Quand lancer un VACUUM manuel

Situations où un VACUUM manuel est justifié :

Après une grosse opération de suppression/mise à jour (ex : purge de 50% de la table)
Avant une opération critique (migration, export)
En cas d'alerte XID wraparound

3. Surveiller régulièrement

Mettez en place un monitoring sur :

Le bloat des tables (ratio dead tuples)
L'âge XID (age(relfrozenxid))
L'activité autovacuum (logs, pg_stat_activity)

4. Ajuster autovacuum pour les tables à forte activité

Pour une table très sollicitée :

-- Rendre autovacuum plus agressif sur une table spécifique
ALTER TABLE logs SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- 5% au lieu de 20%
    autovacuum_vacuum_threshold = 1000      -- Plus réactif
);

5. VACUUM FULL : En dernier recours

N'utilisez VACUUM FULL que si :

Le bloat est extrême (>50%)
Vous pouvez vous permettre un downtime (verrou exclusif)
Vous avez l'espace disque nécessaire (2× la taille de la table)

Alternative moderne : pg_repack (extension qui réorganise sans verrou exclusif)

Résumé

VACUUM en 5 points clés

Récupération d'espace : Libère l'espace occupé par les lignes mortes (UPDATE, DELETE)
Prévention XID wraparound : Évite une catastrophe de perte de données
Autovacuum : Processus automatique qui gère tout cela pour vous
VACUUM simple : Non bloquant, à utiliser régulièrement (ou via autovacuum)
VACUUM FULL : Bloquant, seulement en cas de bloat extrême

Checklist de maintenance

✅ Vérifier que autovacuum est activé :

SHOW autovacuum;  -- Doit être 'on'

✅ Surveiller les tables à forte activité :

SELECT * FROM pg_stat_user_tables WHERE n_dead_tup > 10000;

✅ Surveiller l'âge XID :

SELECT max(age(relfrozenxid)) FROM pg_class WHERE relkind = 'r';
-- Alerte si > 150 millions

✅ Logs d'autovacuum : Configurer log_autovacuum_min_duration = 0 pour tracer tous les autovacuum

Concepts avancés (Bonus)

Visibility Map et FSM

PostgreSQL utilise deux structures annexes pour optimiser VACUUM :

Visibility Map (VM) :
- Carte des pages où toutes les lignes sont visibles par toutes les transactions
- Permet à VACUUM de sauter les pages propres
- Optimise les scans et les index-only scans
Free Space Map (FSM) :
- Carte de l'espace libre dans chaque page
- Permet d'insérer rapidement de nouvelles lignes dans l'espace récupéré

VACUUM VERBOSE : Mode bavard

Pour voir ce que fait VACUUM en détail :

VACUUM VERBOSE nom_table;

Affiche :

Nombre de pages scannées
Nombre de lignes mortes récupérées
Espace libéré
Temps d'exécution

Conclusion

VACUUM est une opération fondamentale de PostgreSQL qui garantit :

🚀 Performances optimales en récupérant l'espace disque
🛡️ Stabilité de la base en prévenant le XID wraparound
🤖 Automatisation via autovacuum pour une maintenance sans intervention

En tant que développeur ou DevOps débutant, retenez :

Autovacuum fait le travail pour vous, ne le désactivez pas
Surveillez vos tables à forte activité (bloat et XID age)
VACUUM manuel uniquement après de grosses opérations de masse
VACUUM FULL est une arme à double tranchant, à utiliser avec précaution

PostgreSQL 18 apporte des améliorations significatives à l'autovacuum, le rendant encore plus intelligent et réactif. Avec une bonne compréhension de VACUUM, vous éviterez 99% des problèmes de maintenance courants !

Prochaines étapes recommandées :

Découvrir ANALYZE (16.10.2) : Mise à jour des statistiques du planificateur
Explorer les stratégies de monitoring (Chapitre 14)
Approfondir la configuration autovacuum pour vos cas d'usage spécifiques

⏭️ ANALYZE : Mise à jour des statistiques du planificateur

FilesExpand file tree

10.1-vacuum.md

Latest commit

History