🐘 Cas 4 – Autovacuum bloquant

Rôle d’autovacuum

• Supprimer les tuples morts pour éviter les bloat.
• Mettre à jour les stats (ANALYZE).
• Avancer les relfrozenxid pour éviter le wraparound.

-- Activé ?
SHOW autovacuum;

-- Vue synthétique
SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
          

Freeze & risque de wraparound

• Chaque table possède un relfrozenxid.
• Si une table n’est pas vacuumée assez souvent, PostgreSQL peut bloquer les écritures pour forcer un VACUUM.

SELECT c.relname,
       age(c.relfrozenxid) AS xid_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
  AND c.relkind = 'r'
ORDER BY xid_age DESC
LIMIT 20;
          

Les tables avec un xid_age très élevé sont candidates à un VACUUM (freeze) manuel dans une fenêtre de maintenance.

Bloat estimé (sans extension)

Estimation grossière basée sur les stats catalogue.

WITH stats AS (
  SELECT
    schemaname,
    relname,
    n_live_tup,
    pg_total_relation_size(relid) AS total_bytes,
    pg_relation_size(relid)       AS heap_bytes
  FROM pg_catalog.pg_statio_user_tables
)
SELECT schemaname, relname,
       pg_size_pretty(heap_bytes)  AS heap_size,
       pg_size_pretty(total_bytes) AS total_size,
       round(100*(total_bytes - heap_bytes)::numeric
                / GREATEST(total_bytes,1), 1) AS bloat_pct
FROM stats
ORDER BY bloat_pct DESC
LIMIT 20;
          

Bloat d’index

SELECT schemaname, relname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
          

Pour une mesure plus fine (ratio tuples vs pages), on peut utiliser l’extension pgstattuple en environnement de test.

Paramètres globaux

SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_naptime;
SHOW autovacuum_max_workers;
          

• Réduire les *_scale_factor pour les grosses bases très actives.
• Augmenter autovacuum_max_workers si beaucoup de tables actives et suffisamment de CPU/I/O.

Overrides par table

Pour les tables énormes ou très sensibles :

ALTER TABLE orders
  SET (autovacuum_vacuum_scale_factor = 0.02,
       autovacuum_analyze_scale_factor = 0.02,
       autovacuum_vacuum_cost_limit = 4000);
          

• Permet d’augmenter la fréquence de vacuum sur cette table sans toucher au reste du cluster.
• Penser à documenter ces overrides (tables critiques).

Principes

• Autovacuum gère le quotidien, mais ne suffit pas pour :
  • gros bloat historique,
  • changements de pattern de charge,
  • upgrade majeure, migration de stockage.
• Planifier des fenêtres :
  • hebdo : VACUUM/ANALYZE ciblés,
  • mensuel : REINDEX concurrent sur les index critiques,
  • annuel : opérations lourdes (CLUSTER, vacuum full de certaines tables).

Outils pratiques

• Jobs cron ou scheduler (pg_cron, systemd timers, outils maison).
• Table de pilotage listant pour chaque table :
  • dernière date de maintenance lourde,
  • niveau de bloat,
  • fenêtre préférée (nuit, week-end).

Le but est d’éviter que quelqu’un ne lance un REINDEX ou VACUUM FULL, « en prod à 10h du matin », faute de plan de maintenance clair.

0–5 minutes : comprendre

1. Vérifier dans pg_stat_activity s’il y a des VACUUM/ANALYZE longs.
2. Regarder pg_stat_progress_vacuum pour identifier la table concernée.
3. Confirmer l’impact : I/O saturé, verrous, temps de réponse.

SELECT pid, now() - query_start AS age, query
FROM pg_stat_activity
WHERE query ILIKE '%VACUUM%';
          

Décider : annuler ou laisser finir ?

• Si la table est critique pour le freeze (xid_age très élevé) → essayer de laisser finir ou basculer le trafic.
• Si c’est un VACUUM/REINDEX opportuniste lancé hors fenêtre :
  • cancellation via pg_cancel_backend(),
  • replanification en dehors des heures de pointe.

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid = ;