PostgreSQL VACUUM : maîtrisez l'autovacuum, le bloat et le tuning pour des bases performantes

Article inspiré de la présentation d'Elizabeth Garrett Christensen : Postgres Vacuum Explained: Autovacuum, Bloat and Tuning.

Pourquoi le VACUUM est critique pour vos applications Symfony/PHP

Dans une application web PHP/Symfony qui tourne en production, votre base PostgreSQL accumule silencieusement des tuples morts (dead tuples). Chaque UPDATE ou DELETE ne supprime pas physiquement la ligne : PostgreSQL conserve l'ancienne version pour garantir la cohérence transactionnelle (MVCC). Sans maintenance régulière, ces données obsolètes gonflent vos tables et vos index, un phénomène appelé bloat.

Conséquences concrètes : des requêtes plus lentes, une consommation d'I/O en hausse, et une facture infrastructure qui s'alourdit inutilement. Bonne nouvelle : PostgreSQL embarque un mécanisme automatique — l'autovacuum — et quelques outils complémentaires pour garder tout cela sous contrôle.

1. Mesurer avant d'agir : les métriques essentielles

Avant tout réglage, il faut savoir où vous en êtes. Voici les commandes psql à lancer dès maintenant.

Identifier les tables qui accumulent des dead tuples

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Un dead_ratio_pct supérieur à 10 % est un signal d'alarme. Au-delà de 20 %, vos performances sont probablement déjà dégradées.

Mesurer le bloat avec pgstattuple

Pour une analyse précise du bloat réel (pas seulement estimé), activez l'extension pgstattuple :

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  table_len,
  tuple_count,
  dead_tuple_count,
  dead_tuple_percent,
  free_space,
  free_percent
FROM pgstattuple('ma_table');

⚠️ pgstattuple effectue un scan complet de la table. À utiliser en dehors des pics de charge en production.

Surveiller l'activité de l'autovacuum en temps réel

SELECT
  pid,
  datname,
  relid::regclass AS table_name,
  phase,
  heap_blks_scanned,
  heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

2. Comprendre et tuner l'autovacuum

L'autovacuum se déclenche selon deux paramètres combinés pour chaque table :

seuil de déclenchement = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

Par défaut :

• autovacuum_vacuum_threshold = 50 (dead tuples minimum)
• autovacuum_vacuum_scale_factor = 0.2 (20 % de la table)

Pour une table de 10 millions de lignes, l'autovacuum ne se déclenche qu'après 2 000 050 dead tuples. C'est beaucoup trop pour une table critique.

Régler par table (recommandé)

Plutôt que de modifier les paramètres globaux dans postgresql.conf, ciblez les tables sensibles :

ALTER TABLE commandes SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.005
);

Ici, l'autovacuum se déclenchera dès 1 % de dead tuples sur la table commandes, bien plus réactif.

Augmenter le nombre de workers autovacuum

Si vous avez de nombreuses tables actives, augmentez le nombre de workers dans postgresql.conf :

autovacuum_max_workers = 5          # défaut : 3
autovacuum_vacuum_cost_delay = 2ms  # défaut : 2ms (réduit l'impact I/O)
autovacuum_vacuum_cost_limit = 400  # défaut : 200 (accélère le vacuum)

Le paramètre autovacuum_vacuum_cost_limit contrôle la "vitesse" de l'autovacuum. L'augmenter accélère le nettoyage mais génère plus d'I/O. À ajuster selon votre infrastructure.

3. VACUUM FULL vs pg_repack : choisir le bon outil

Quand le bloat est déjà sévère, l'autovacuum ne suffit pas à récupérer l'espace disque : il marque l'espace comme réutilisable, mais ne rend pas les octets au système de fichiers. Deux options s'offrent à vous.

VACUUM FULL

VACUUM FULL ANALYZE ma_table;

Avantages : intégré à PostgreSQL, très efficace pour récupérer l'espace.

Inconvénient majeur : pose un verrou exclusif sur la table pendant toute la durée de l'opération. Inacceptable en production sur des tables critiques.

pg_repack : la solution sans interruption de service

pg_repack reconstruit la table en arrière-plan sans verrou exclusif prolongé :

# Installation
apt install postgresql-16-repack

# Exécution
pg_repack -h localhost -U postgres -d ma_base -t ma_table

Quand utiliser quoi ?

4. Checklist de monitoring pour éviter la régression

Mettez en place ces vérifications régulières — idéalement intégrées dans votre pipeline de monitoring (Datadog, Grafana, Prometheus avec postgres_exporter) :

Quotidiennement :

• dead_ratio_pct < 10 % sur toutes les tables critiques (pg_stat_user_tables)
• Vérifier que last_autovacuum date de moins de 24h sur les tables à fort volume
• Aucun autovacuum bloqué (pg_stat_progress_vacuum)

Hebdomadairement :

• Analyse du bloat via pgstattuple sur les tables majeures
• Revue des tables n'ayant pas été vacuumées depuis plus de 7 jours

Mensuellement :

• Audit des paramètres autovacuum par table (pg_class, pg_options_to_table)
• Vérification de l'âge des transactions (age(datfrozenxid)) pour prévenir le wraparound

-- Surveiller le risque de transaction ID wraparound
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  2147483648 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;

Conclusion : un investissement qui se traduit directement en gains business

Un autovacuum bien configuré et un monitoring proactif du bloat, c'est concrètement :

• Moins d'I/O sur vos serveurs de base de données
• Des requêtes plus rapides pour vos utilisateurs
• Une infrastructure plus petite (et moins chère) pour le même niveau de service
• Zéro surprise liée au transaction ID wraparound en production

Chez MulerTech, nous intégrons systématiquement ces pratiques dans nos projets Symfony dès la phase de mise en production. La maintenance de base de données n'est pas optionnelle : c'est une ligne de défense contre la dégradation progressive de vos performances.

📚 Source originale : Elizabeth Garrett Christensen — Postgres Vacuum Explained: Autovacuum, Bloat and Tuning (via Snowflake Blog / PostgreSQL)