VACUUM, autovacuum et bloat des tables

Mis à jour le 5 juillet 2026

PostgreSQL ne met jamais à jour une ligne sur place. Chaque UPDATE écrit une nouvelle version de ligne et marque l'ancienne comme morte ; chaque DELETE se contente de marquer. L'espace est récupéré plus tard, de façon asynchrone, par VACUUM. Quand cette boucle fonctionne, vous n'y pensez jamais. Quand elle prend du retard, les tables et les index grossissent silencieusement — c'est le bloat — et chaque requête en paie le prix.

Pourquoi les lignes mortes existent : le MVCC

Avec le MVCC (contrôle de concurrence multiversion), les lecteurs ne bloquent jamais les écrivains et inversement, car chaque transaction voit un instantané cohérent : les anciennes versions de lignes sont conservées tant qu'une transaction pourrait encore en avoir besoin. Dès qu'aucune transaction active ne peut voir une version morte, celle-ci devient un déchet — mais PostgreSQL ne la récupère pas en ligne. C'est le travail de VACUUM.

Deux conséquences immédiates :

• Une charge de travail riche en UPDATE écrit à peu près autant qu'une charge riche en INSERT, plus le brassage des index.
• Une seule transaction de longue durée retient le nettoyage pour toute la base de données — VACUUM ne peut supprimer aucune version de ligne que l'instantané de cette transaction pourrait encore voir, quelles que soient les tables qu'elle a touchées. Une session idle in transaction oubliée le vendredi, c'est une base gonflée le lundi.

Ce que VACUUM fait réellement (et ne fait pas)

• Un VACUUM simple recherche les tuples morts, rend leur espace réutilisable pour les futures écritures dans la même table, met à jour la free space map et la visibility map, et gèle les anciens tuples pour prévenir le rebouclage des identifiants de transaction. Il s'exécute en parallèle du trafic normal.
• Il ne réduit pas le fichier sur disque (sauf dans le cas particulier des pages complètement vides tout à la fin de la table). Une table qui a un jour gonflé jusqu'à 50 Go reste à 50 Go sur disque même si elle est vide à 90 % à l'intérieur.
• VACUUM FULL réécrit la table dans un nouveau fichier compact et rend l'espace au système d'exploitation — mais prend un verrou ACCESS EXCLUSIVE pendant toute la réécriture. Sur une table volumineuse et sollicitée, c'est une interruption de service ; regardez pg_repack pour des reconstructions en ligne.
• ANALYZE (souvent lancé conjointement) rafraîchit les statistiques du planificateur — un travail différent qui compte lui aussi, voir lire EXPLAIN ANALYZE.

Comment autovacuum décide de s'exécuter

Le lanceur autovacuum vérifie périodiquement chaque table par rapport à un seuil :

vacuum when:  n_dead_tup  >  autovacuum_vacuum_threshold
                             + autovacuum_vacuum_scale_factor × reltuples
-- defaults:  50 + 0.20 × reltuples  (20% of the table)

Le facteur d'échelle par défaut de 20 % convient aux petites tables et est catastrophique pour les grandes : une table de 100 millions de lignes accumule 20 millions de lignes mortes avant même qu'autovacuum ne démarre. Pour les grandes tables très sollicitées, définissez une surcharge par table :

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000
);

Autovacuum est aussi délibérément bridé par des délais fondés sur le coût (autovacuum_vacuum_cost_delay / autovacuum_vacuum_cost_limit) afin de ne pas saturer les I/O. Sur du matériel moderne, les valeurs par défaut sont conservatrices ; si autovacuum s'exécute en permanence sans jamais rattraper son retard, augmenter autovacuum_vacuum_cost_limit est généralement le premier levier.

Détecter le bloat avant qu'il ne fasse mal

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;

• n_dead_tup élevé et en croissance, avec last_autovacuum ancien ou NULL → autovacuum ne suit pas (ou une transaction longue épingle l'horizon : vérifiez pg_stat_activity pour un xact_start ancien).
• Symptôme côté requête : des comptes de buffers disproportionnés par rapport aux lignes retournées dans EXPLAIN (ANALYZE, BUFFERS) — lire 40 000 pages pour 500 lignes signifie parcourir surtout de l'espace mort.
• Vérification côté taille : comparez pg_total_relation_size() dans le temps, ou utilisez l'extension pgstattuple pour obtenir un pourcentage exact d'espace mort sur une table suspecte.

Les mises à jour HOT : le repas gratuit qui vaut la peine d'être conçu

Si un UPDATE ne modifie que des colonnes qui ne sont pas indexées, et que la nouvelle version tient sur la même page, PostgreSQL effectue une mise à jour HOT (heap-only tuple) : aucune entrée d'index n'est écrite, et la version morte peut être nettoyée à bon marché au sein de la page. C'est pourquoi ajouter « juste un index de plus » sur une colonne fréquemment mise à jour peut ruiner les performances en écriture — cela transforme chaque mise à jour HOT en une mise à jour complète. Un fillfactor plus bas (par exemple 80 à 90 pour les tables riches en mises à jour) laisse de la place sur chaque page et augmente le taux de HOT.

Règles empiriques

• Ne désactivez jamais autovacuum. S'il fait mal, c'est qu'il est mal réglé, pas qu'il est inutile — et il vous protège aussi du rebouclage des identifiants de transaction, un événement qui peut arrêter la base.
• Tuez ou fixez un délai d'expiration aux transactions inactives : idle_in_transaction_session_timeout est une assurance peu coûteuse.
• Réglez par table, pas globalement : une poignée de tables très sollicitées cause généralement l'essentiel de la douleur.
• Le bloat que vous avez déjà ne disparaîtra pas tout seul : un VACUUM simple arrête la croissance ; récupérer de l'espace disque nécessite VACUUM FULL ou une reconstruction en ligne.