Autovacuum, Checkpoints et Write Storms : comment éviter les pics d'I/O qui paralysent PostgreSQL

Vous avez déjà observé des latences qui s'envolent brutalement sur votre application, sans raison apparente ? Les files de traitement qui explosent, les timeouts en cascade, et un PostgreSQL qui semble soudainement à bout de souffle ? Il y a de bonnes chances que l'interaction entre l'autovacuum et les checkpoints soit en cause. C'est précisément ce que Jeremy Schneider analyse dans son article Zero autovacuum_cost_delay, Write Storms, and You, en complément du travail de Shaun Thomas sur les write storms.

Décortiquons le problème, et surtout, voyons comment le détecter et l'éviter en production.

Comprendre l'interaction autovacuum / checkpoints

PostgreSQL utilise deux mécanismes de fond essentiels pour maintenir la cohérence et les performances de la base :

• L'autovacuum : il nettoie les dead tuples laissés par les opérations UPDATE et DELETE, et met à jour les statistiques du planificateur. Il est conçu pour s'exécuter discrètement, en s'auto-limitant via le paramètre autovacuum_cost_delay (une pause entre chaque "coût" d'I/O consommé).
• Les checkpoints : ils écrivent périodiquement sur disque toutes les pages sales du shared buffer, garantissant la durabilité des données. Un checkpoint mal dimensionné ou trop fréquent génère un pic d'écriture massif — c'est ce qu'on appelle un write storm.

Le piège classique : un DBA bien intentionné règle autovacuum_cost_delay = 0 pour accélérer le vacuum sur une table volumineuse. L'autovacuum devient alors agressif, il lit et écrit sans aucune pause, génère une pression I/O considérable, et entre en compétition directe avec un checkpoint qui se déclenche au même moment. Résultat : un pic d'I/O brutal qui dégrade l'ensemble des requêtes applicatives.

🔍 Détecter le problème : les vues pg_stat_* indispensables

Avant toute intervention, il faut observer. PostgreSQL expose des métriques précieuses que trop peu de développeurs consultent en routine.

Surveiller l'activité de l'autovacuum

-- Voir les tables en cours de vacuum et leur progression
SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Voir les workers autovacuum actifs en ce moment
SELECT
  pid,
  datname,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

Surveiller les checkpoints

-- Fréquence et durée des checkpoints
SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_clean,
  buffers_backend
FROM pg_stat_bgwriter;

Un ratio checkpoints_req / checkpoints_timed élevé indique que les checkpoints sont forcés (par saturation du WAL) plutôt que planifiés — signal d'alarme.

Wait events : le point de départ universel

Comme le souligne Jeremy Schneider, commencer par les wait events est la bonne approche :

-- Top des wait events actifs
SELECT
  wait_event_type,
  wait_event,
  count(*)
FROM pg_stat_activity
WHERE state != 'idle'
GROUP BY 1, 2
ORDER BY 3 DESC;

Si vous voyez IO / DataFileWrite ou BufferIO dominer, vous êtes probablement face à une contention d'I/O liée à un checkpoint ou à un autovacuum trop agressif.

⚙️ Valeurs recommandées et alternatives à autovacuum_cost_delay = 0

Mise à zéro de autovacuum_cost_delay, c'est l'option nucléaire. Elle est parfois justifiée (maintenance planifiée, table en fin de vie), mais jamais en production normale.

Les paramètres à connaître

Recommandations pratiques

• Ne jamais mettre autovacuum_cost_delay = 0 en production sans monitoring renforcé et fenêtre de maintenance identifiée.
• Pour accélérer le vacuum sur une table spécifique sans impacter le reste, utilisez un VACUUM manuel avec un cost_delay réduit plutôt que de modifier le paramètre global.
• Sur des serveurs NVMe modernes (faible latence disque), descendre autovacuum_cost_delay à 1ms ou 2ms est souvent suffisant pour rester réactif sans être agressif.
• Augmenter autovacuum_cost_limit (ex: 400 à 800) permet d'accomplir plus de travail par cycle sans supprimer les pauses.

-- Surcharger les paramètres pour une table spécifique (sans redémarrage)
ALTER TABLE ma_grosse_table
  SET (autovacuum_cost_delay = 5,
       autovacuum_cost_limit = 400);

📋 Playbook d'intervention en production

Vous constatez des pics de latence inexpliqués ? Voici la séquence d'investigation recommandée :

1. Vérifier les wait events — voir la requête ci-dessus, identifier si l'I/O domine.

2. Consulter pg_stat_bgwriter — checkpoints forcés ? Temps d'écriture anormalement long ?

3. Identifier les tables à fort taux de dead tuples — n_dead_tup élevé signale un vacuum en retard.

4. Vérifier si un autovacuum tourne actuellement — pg_stat_activity avec filtre sur autovacuum:.

5. Corréler avec les logs PostgreSQL — activer log_autovacuum_min_duration = 0 temporairement pour tout loguer :

SET log_autovacuum_min_duration = 0; -- session courante uniquement

6. Agir avec précision — modifier les paramètres au niveau de la table concernée plutôt que globalement, pour un impact chirurgical.

7. Ajuster checkpoint_completion_target — une valeur de 0.9 (au lieu du défaut 0.9 déjà dans les versions récentes) étale l'écriture des checkpoints sur 90% de l'intervalle, réduisant les pics.

Conclusion

L'autovacuum et les checkpoints sont deux piliers invisibles mais critiques de PostgreSQL. Leur interaction peut générer des write storms dévastateurs, surtout quand on tente de « forcer » l'un sans considérer l'impact sur l'autre.

La leçon centrale de l'article de Jeremy Schneider est aussi une bonne pratique générale : ne jamais modifier un paramètre de tuning sans monitoring en place. Les vues pg_stat_* sont votre premier allié — consultez-les avant, pendant, et après toute intervention.

Pour aller plus loin, l'article original de Jeremy Schneider (postgr.es/p/8wk) et celui de Shaun Thomas sur les checkpoints sont des lectures complémentaires incontournables pour quiconque opère PostgreSQL en production.