Ubuntu 24.04 PostgreSQL autovacuum « lenteur mystérieuse » : comment le régler en toute sécurité

octobre 21, 2025 • février 3, 2026 • Lecture : 32 min • Views: 7

Cet article vous a aidé ?

Autovacuum est censé être le concierge discret. Sur Ubuntu 24.04, il se présente parfois comme une fanfare : pics de latence, sessions « idle in transaction » qui s’accumulent, disques occupés mais rien « n’utilise le CPU », et votre équipe applicative jure qu’elle n’a rien changé.

Le piège est de supposer que l’autovacuum est soit « correct », soit « la cause ». En pratique, c’est un amplificateur de symptômes. Il transforme la bloat existante, les mauvais schémas de requêtes et les plafonds I/O en douleur très visible. La bonne nouvelle : vous pouvez le rendre prévisible sans transformer votre base en expérience scientifique.

À quoi ressemble la « lenteur mystérieuse » en production

La lenteur d’autovacuum ne se manifeste que rarement par « le vacuum prend du temps ». Elle se manifeste par tout le reste qui devient lent.

Des requêtes en lecture qui étaient rapides commencent à générer plus d’I/O aléatoire. Vous voyez shared read augmenter, le ratio de cache chuter, et votre p99 s’effondrer.
Les tables à fort écrit montrent une bloat du heap et une bloat des index. L’ensemble de travail ne tient plus en RAM. Votre SSD commence à se comporter comme un disque magnétique.
Les workers d’autovacuum semblent « actifs » mais le débit est faible. Les wait events montrent de l’I/O ou des verrous, et la vue de progression du vacuum semble bloquée.
Occasionnellement un comportement du type « cannot vacuum … because it contains tuples still needed » : ce n’est pas une erreur, mais un signe que des transactions de longue durée empêchent le nettoyage.
Et le classique : la pression de wraparound se glisse. Soudain le vacuum cesse d’être optionnel et devient existentiel.

Une vérité sèche : autovacuum n’est pas lent parce qu’il est paresseux. Il est lent parce qu’il est poli — à moins que vous ne le forciez à ne pas l’être.

Feuille de diagnostic rapide (faire ceci en priorité)

Ceci est la séquence « j’ai besoin d’indications en 10 minutes ». Faites-la dans l’ordre. Chaque étape réduit la catégorie de goulet d’étranglement avant que vous ne touchiez aux réglages.

1) Le problème est-il I/O, verrous, ou wraparound ?

Si l’I/O système est saturé et que les wait events Postgres indiquent des attentes I/O → vous êtes principalement lié au stockage.
Si autovacuum est bloqué par des verrous ou ne peut pas avancer à cause de transactions longues → vous êtes principalement lié aux transactions/verrouillage.
Si datfrozenxid a un âge élevé → vous êtes en mode prévention de wraparound. La performance devient secondaire à la survie.

2) Identifiez les tables les plus chaudes, pas juste « autovacuum »

Autovacuum agit au niveau de la table. Une table de 200 Go avec un mauvais schéma d’updates peut créer l’illusion que « toute la base est lente ». Trouvez la table.

3) Vérifiez si autovacuum est sous-dimensionné ou intentionnellement bridgé

Workers, cost delay et cost limit déterminent l’agressivité du vacuum. Sur de nombreux systèmes de production, les valeurs par défaut sont sûres mais trop douces pour des taux d’écriture modernes.

4) Validez les suspects de « pinning »

Les transactions longues, les slots de réplication logical, et les sessions abandonnées peuvent empêcher le nettoyage. Si vous ne réglez pas cela, peaufiner autovacuum revient à acheter une serpillière plus rapide pour un sol qui est encore inondé.

Blague #1 : Autovacuum est le seul employé qui nettoie les dégâts des autres et se fait quand même accuser pour l’odeur.

Comment autovacuum consomme réellement son temps

Vacuum n’est pas une seule tâche. C’est un ensemble de tâches qui concurrencent votre charge :

Scannage du heap : lecture des pages de la table pour trouver les tuples morts et marquer l’espace réutilisable.
Nettoyage des index : suppression des entrées d’index mortes (sauf contournement selon les choix et heuristiques d’index vacuum).
Gelage (freezing) : marquage des anciens IDs de transaction comme gelés pour prévenir le wraparound.
Mises à jour de la visibility map : permettre les index-only scans en enregistrant les pages all-visible/all-frozen.
ANALYZE : mise à jour des statistiques du planificateur (autovacuum exécute souvent aussi analyze).

La « lenteur mystérieuse » survient souvent quand le travail bascule d’un nettoyage incrémental à un nettoyage de rattrapage. Une fois que la bloat croît, le vacuum lit et écrit plus de pages, touche davantage les index, et crée plus d’I/O aléatoire. Cela augmente la latence pour tout le reste, ce qui accroît la durée des transactions, ce qui génère plus de tuples morts… et le cercle se nourrit.

Qu’est-ce qui rend le vacuum lent même lorsqu’il « tourne »

Délai basé sur le coût : vacuum dort intentionnellement. C’est un comportement normal.
Saturation I/O : vacuum effectue de véritables lectures/écritures mais le stockage est à sa limite.
Usure du cache de buffers : vacuum remplace des pages chaudes, rendant les lectures applicatives plus souvent des misses.
Conflits de verrous : VACUUM régulier ne prend pas de verrous lourds, mais il nécessite un mode de verrou qui peut être bloqué par certains DDL et par des cas limites. VACUUM FULL est une bête différente et peut ruiner votre après-midi.
Transactions de longue durée : vacuum ne peut pas supprimer des tuples encore visibles par d’anciens snapshots.

Spécificités d’Ubuntu 24.04 qui comptent

Ubuntu 24.04 n’est pas intrinsèquement « pire » pour Postgres, mais c’est une baseline Linux moderne avec des valeurs par défaut modernes — certaines utiles, d’autres surprenantes.

Noyau et pile I/O : vous êtes typiquement sur un noyau 6.x. Les defaults de l’ordonnanceur I/O et le comportement NVMe sont généralement bons, mais des cgroups mal configurés ou des voisins bruyants peuvent quand même affamer Postgres.
Systemd : les services peuvent tourner avec des contrôles de ressources que vous n’avez pas définis intentionnellement. CPUQuota/IOWeight peuvent produire la confusion « vacuum lent mais rien n’est à fond ».
Transparent Huge Pages (THP) : souvent encore activé par défaut sur les systèmes généralistes. Il peut provoquer des à-coups de latence. Ce n’est pas un réglage d’autovacuum, mais ça peut faire paraître autovacuum coupable.
Systèmes de fichiers et options de montage : ext4 vs XFS vs ZFS ont des comportements différents sous la charge mixte lecture/écriture du vacuum. Autovacuum n’est pas spécial — il touche juste beaucoup de pages.

Faits et contexte intéressants (parce que l’histoire se répète)

Ce sont des petites informations concrètes qui vous aident à raisonner sur autovacuum sans superstition.

Autovacuum est devenu standard dans PostgreSQL 8.1 après avoir été un add-on. Avant cela, de nombreux systèmes pourrissaient à moins que des humains n’exécutent vacuum régulièrement.
MVCC est la raison de l’existence du vacuum : Postgres garde d’anciennes versions de lignes pour la concurrence. Le nettoyage est différé par conception.
Vacuum ne « réduit » pas les tables dans le cas général. Il rend l’espace réutilisable à l’intérieur du fichier ; la taille du fichier reste souvent la même sauf méthodes plus invasives.
Le wraparound n’est pas théorique : les IDs de transaction sont en 32-bit et finiront par envelopper. Si vous ne vacuum/gel pas, Postgres se protégera en forçant des vacuums agressifs et finira par refuser les écritures.
Les hot updates peuvent réduire le churn d’index, mais seulement si les colonnes mises à jour ne sont pas indexées. Mettez à jour la mauvaise colonne et vous ferez brouter rapidement les index.
Les visibility maps permettent les index-only scans. Un vacuum qui met à jour les visibility maps peut accélérer les lectures plus tard, même si cela coûte de l’I/O maintenant.
Le délai basé sur le coût existe pour protéger la latence, pas pour vous ennuyer. Il a été conçu à l’époque où les disques étaient plus lents et les environnements partagés courants.
Les facteurs d’échelle d’autovacuum étaient pensés pour des « tables normales ». Les très grandes tables avec fort churn nécessitent souvent des overrides par table ; les défauts évoluent trop linéairement pour certains workloads.
La précision d’ANALYZE affecte indirectement l’impact du vacuum : de mauvaises stats mènent à de mauvais plans, qui créent des transactions plus longues, qui génèrent plus de tuples morts et plus de travail pour vacuum.

Tâches pratiques : commandes, sorties et décisions (12+)

Ce sont les vérifications sur le terrain que j’exécute réellement. Chaque tâche inclut : commande, sortie d’exemple, ce que cela signifie, et la décision à prendre.

Task 1: Confirm Postgres version and cluster layout

cr0x@server:~$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-1.pgdg24.04+1)

Signification : Vous êtes sur des outils clients PG 16.x. Sur Ubuntu, vous pouvez avoir plusieurs clusters/versions.

Décision : Assurez-vous de régler la bonne instance et d’éditer le bon fichier de configuration pour ce cluster.

cr0x@server:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Signification : Cluster unique, PG16 main, chemins standards du packaging Debian/Ubuntu.

Décision : Vous savez où chercher les logs et quel port viser.

Task 2: Is autovacuum even enabled, and what are the global knobs?

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW autovacuum; SHOW autovacuum_max_workers; SHOW autovacuum_naptime; SHOW autovacuum_vacuum_cost_limit; SHOW autovacuum_vacuum_cost_delay;"
 autovacuum
------------
 on
(1 row)

 autovacuum_max_workers
-----------------------
 3
(1 row)

 autovacuum_naptime
-------------------
 1min
(1 row)

 autovacuum_vacuum_cost_limit
-----------------------------
 -1
(1 row)

 autovacuum_vacuum_cost_delay
-----------------------------
 2ms
(1 row)

Signification : Autovacuum est activé, seulement 3 workers, 1 minute entre les vérifications. Cost limit -1 signifie « utiliser vacuum_cost_limit ».

Décision : Si vous avez beaucoup de tables actives et de la bloat, 3 workers est souvent trop peu. Mais n’augmentez pas aveuglément — identifiez d’abord si vous êtes bloqué ou limité par l’I/O.

Task 3: Find active autovacuum workers and what they’re waiting on

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT pid, datname, relid::regclass AS table, phase, wait_event_type, wait_event, now()-xact_start AS xact_age \
FROM pg_stat_progress_vacuum v \
JOIN pg_stat_activity a USING (pid) \
ORDER BY xact_age DESC;"
 pid  | datname |        table        |   phase   | wait_event_type | wait_event | xact_age
------+--------+---------------------+-----------+-----------------+------------+----------
 8421 | appdb  | public.events       | scanning  | IO              | DataFileRead | 00:12:41
 8534 | appdb  | public.sessions     | vacuuming indexes | CPU      |            | 00:05:10
(2 rows)

Signification : Un worker attend de l’I/O sur des lectures ; un autre consomme du CPU pour le vacuum des index.

Décision : Si vous voyez beaucoup d’attentes IO, augmenter les cost limits peut simplement accroître la contention. Considérez d’abord le débit de stockage et le cache.

Task 4: Check for blocked vacuum due to locks

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT a.pid, a.wait_event_type, a.wait_event, a.query, l.relation::regclass AS rel, l.mode, l.granted \
FROM pg_stat_activity a \
JOIN pg_locks l ON l.pid=a.pid \
WHERE a.query ILIKE '%autovacuum%' AND NOT l.granted;"
 pid | wait_event_type |  wait_event   |                 query                 |      rel       |      mode       | granted
-----+-----------------+---------------+---------------------------------------+----------------+-----------------+---------
(0 rows)

Signification : Aucune preuve que les workers autovacuum attendent des verrous non accordés en ce moment.

Décision : Ne chassez pas des fantômes de verrous. Passez au pinning des transactions et à l’I/O.

Task 5: Check for long-running transactions that pin vacuum

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT pid, usename, application_name, state, now()-xact_start AS xact_age, wait_event_type, wait_event \
FROM pg_stat_activity \
WHERE xact_start IS NOT NULL \
ORDER BY xact_age DESC \
LIMIT 10;"
 pid  | usename | application_name |        state        | xact_age  | wait_event_type | wait_event
------+--------+------------------+---------------------+-----------+-----------------+-----------
 7712 | app    | api              | idle in transaction | 03:17:09  | Client          | ClientRead
 9120 | app    | batch            | active              | 00:42:11  |                 |
(2 rows)

Signification : Une session « idle in transaction » maintient un snapshot depuis des heures. Vacuum ne peut pas supprimer les tuples encore visibles pour elle.

Décision : Corrigez le bug applicatif (commit/rollback manquant). À court terme, terminez la session si c’est sûr ; sinon, le réglage d’autovacuum n’aidera pas.

Task 6: Check wraparound risk (you care about this even when performance is “fine”)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT datname, age(datfrozenxid) AS xid_age, age(datminmxid) AS mxid_age \
FROM pg_database \
ORDER BY xid_age DESC;"
  datname  | xid_age  | mxid_age
-----------+----------+----------
 appdb     | 145000000 | 1800000
 postgres  |   2300000 |  120000
 template1 |   2300000 |  120000
 template0 |   2300000 |  120000
(4 rows)

Signification : appdb a un âge XID beaucoup plus élevé ; c’est là que le vacuum/freeze doit suivre.

Décision : Si xid_age approche de votre marge de sécurité autovacuum_freeze_max_age, priorisez le progrès de freezing sur le throttling « agréable ».

Task 7: Identify tables with the most dead tuples and vacuum urgency

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT relid::regclass AS table, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum, vacuum_count, autovacuum_count \
FROM pg_stat_user_tables \
ORDER BY n_dead_tup DESC \
LIMIT 15;"
        table        | n_live_tup | n_dead_tup |      last_autovacuum       | last_vacuum | vacuum_count | autovacuum_count
---------------------+------------+------------+----------------------------+-------------+--------------+------------------
 public.events       | 120000000  | 48000000   | 2025-12-30 08:10:01+00     |             |            0 |               91
 public.sessions     |  90000000  | 22000000   | 2025-12-30 08:20:14+00     |             |            0 |              143
(2 rows)

Signification : Des nombres massifs de tuples morts ; autovacuum s’exécute fréquemment mais n’arrive pas à rattraper. Classique échec de rattrapage.

Décision : Envisagez des seuils autovacuum par table et des réglages de vacuum plus agressifs, ainsi que des corrections applicatives/requêtes.

Task 8: Check if the table is “vacuumed a lot” but still bloated (approximate bloat clues)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT schemaname, relname, n_live_tup, n_dead_tup, \
pg_size_pretty(pg_total_relation_size(relid)) AS total_size, \
pg_size_pretty(pg_relation_size(relid)) AS heap_size, \
pg_size_pretty(pg_indexes_size(relid)) AS index_size \
FROM pg_stat_user_tables \
ORDER BY pg_total_relation_size(relid) DESC \
LIMIT 10;"
 schemaname |  relname  | n_live_tup | n_dead_tup | total_size | heap_size | index_size
------------+-----------+------------+------------+------------+-----------+-----------
 public     | events    | 120000000  | 48000000   | 180 GB     | 120 GB    | 60 GB
 public     | sessions  |  90000000  | 22000000   | 110 GB     | 70 GB     | 40 GB
(2 rows)

Signification : Gros heap et gros index ; le ratio de tuples morts est élevé. Le vacuum crée probablement beaucoup d’I/O.

Décision : Si la table est sujette aux updates, évaluez la faisabilité des HOT updates et la conception des index. Réglage d’autovacuum seul ne suffira pas.

Task 9: See if autovacuum is being throttled (cost delay in effect)

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW vacuum_cost_limit; SHOW vacuum_cost_delay;"
 vacuum_cost_limit
-------------------
 200
(1 row)

 vacuum_cost_delay
-------------------
 0
(1 row)

Signification : Le delay global est 0, mais autovacuum a son propre délai (souvent 2ms). Le comportement effectif dépend des paramètres d’autovacuum vus plus haut.

Décision : Si vous êtes I/O-limité, diminuer les délais peut nuire à la latence. Si vous êtes en retard sur le nettoyage, vous devrez peut-être augmenter le cost limit et/ou réduire le délai prudemment.

Task 10: System-level I/O reality check (is the disk the limiter?)

cr0x@server:~$ iostat -x 1 5
Linux 6.8.0-41-generic (server)  12/30/2025  _x86_64_ (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.15    0.00    4.33   18.90    0.00   64.62

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await wareq-sz  aqu-sz  %util
nvme0n1         820.0  52480.0     0.0   0.00    9.20    64.0     610.0  48800.0   12.10    80.0    18.4   99.0

Signification : Le NVMe est à ~99% d’utilisation avec un await élevé. Votre stockage est saturé. « Vacuum lent » est essentiellement de la physique.

Décision : Vous pouvez toujours régler autovacuum, mais la solution majeure est de réduire l’amplification d’écriture, augmenter la RAM/ratio de cache, améliorer le débit du stockage, ou répartir l’I/O.

Task 11: Check filesystem mount options quickly

cr0x@server:~$ findmnt -no TARGET,SOURCE,FSTYPE,OPTIONS /var/lib/postgresql
/var/lib/postgresql /dev/mapper/vg0-pgdata ext4 rw,relatime,errors=remount-ro

Signification : ext4 avec relatime. Rien d’évidemment exotique.

Décision : Si vous voyez des systèmes de fichiers réseau ou des options sync étranges, stoppez et réévaluez. Vacuum déteste les fsync lents.

Task 12: Inspect autovacuum logging to get ground truth timing

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW log_autovacuum_min_duration;"
 log_autovacuum_min_duration
----------------------------
 -1
(1 row)

Signification : Autovacuum ne journalise pas les durées, vous déboguez à l’aveugle.

Décision : Mettez log_autovacuum_min_duration = '30s' (ou même 0 temporairement durant un incident) pour capturer ce qui prend réellement du temps.

cr0x@server:~$ sudo tail -n 5 /var/log/postgresql/postgresql-16-main.log
2025-12-30 08:20:14.902 UTC [8534] LOG:  automatic vacuum of table "appdb.public.sessions": index scans: 1
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  pages: 0 removed, 842113 remain, 500000 skipped due to pins, 0 skipped frozen
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  tuples: 0 removed, 0 remain, 0 are dead but not yet removable
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  buffer usage: 21000 hits, 180000 misses, 40000 dirtied
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  avg read rate: 45.0 MB/s, avg write rate: 10.0 MB/s, I/O timings: read 220000.000 ms, write 90000.000 ms

Signification : « Skipped due to pins » est la piste fumante : quelque chose maintient des snapshots/tuples. Notez aussi les durées I/O — les lectures sont lentes et dominantes.

Décision : Corrigez d’abord le pinning (transactions longues, replication slots), puis considérez le débit I/O et le réglage du coût.

Task 13: Check replication slots (common vacuum pin)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn \
FROM pg_replication_slots;"
   slot_name   | slot_type | active | restart_lsn | confirmed_flush_lsn
---------------+-----------+--------+-------------+---------------------
 analytics_cdc | logical   | f      | 2A/90000000 | 2A/100000000
(1 row)

Signification : Un slot logique inactif peut retenir du WAL. Ce n’est pas la même chose que geler des tuples, mais cela peut créer de la pression disque et prolonger le temps de récupération après un redémarrage. Cela corrèle souvent avec des transactions longues dans les consommateurs CDC.

Décision : Si le slot est abandonné, supprimez-le. S’il est nécessaire, réparez le consommateur pour qu’il avance.

Task 14: Check for temp file storms and query plan issues (the “vacuum is blamed” classic)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp \
FROM pg_stat_database \
ORDER BY temp_bytes DESC;"
 datname | temp_files |  temp
---------+------------+--------
 appdb   |      18234 | 96 GB
(1 row)

Signification : Gros usage temporaire. Vos disques sont occupés, mais pas nécessairement à cause du vacuum.

Décision : Si l’utilisation de temp coïncide avec les périodes lentes, ajustez work_mem par requête, corrigez les tris/joints, et réduisez le churn de fichiers temporaires. Sinon, le tuning du vacuum ne déplacera pas l’aiguille.

Stratégie de réglage sûre : quoi changer et dans quel ordre

Le réglage d’autovacuum est un jeu de compromis : débit vs latence, maintenance en arrière-plan vs requêtes au premier plan. L’approche sûre est d’augmenter la capacité avec des garde-fous et de cibler les pires tables, plutôt que d’augmenter globalement tout.

Une idée paraphrasée de Werner Vogels (CTO d’Amazon) : Tout échoue éventuellement ; concevez et opérez comme si l’échec était normal, pas exceptionnel.

Principe 1 : Traitez le « ne peut pas suivre » comme un problème structurel

Si les tuples morts croissent plus vite que le vacuum ne peut les supprimer, vous avez l’une de ces situations :

Trop peu de workers / trop de throttling
Plafond I/O (stockage, cache ou voisins bruyants)
Pinning (transactions longues, idle in transaction, réplication/slots)
Schéma d’écriture qui crée de la bloat plus rapidement que n’importe quel vacuum raisonnable (certaines patterns UPDATE-heavy, opportunités HOT manquées, index gonflés)

Faire du tuning d’autovacuum ne résout que le premier point. Les autres nécessitent des changements opérationnels et de schéma/app.

Principe 2 : Préférez les paramètres par table pour les tables volumineuses ou pathologiques

Les paramètres globaux sont des instruments rugueux. Les grandes tables avec fort churn sont là où les valeurs par défaut échouent. Les paramètres de stockage par table permettent d’être agressif là où c’est nécessaire et prudent partout ailleurs.

Exemple : si public.events est énorme et constamment mis à jour, vous pouvez réduire le scale factor pour que l’autovacuum se déclenche plus tôt, et éventuellement augmenter le cost limit pour cette table seulement.

cr0x@server:~$ sudo -u postgres psql -X -c "\
ALTER TABLE public.events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 50000);"
ALTER TABLE

Signification : Le vacuum se déclenche après ~1% de changements + un seuil de base. C’est plus tôt que les valeurs par défaut et cela empêche la bloat incontrôlée.

Décision : Utilisez ceci lorsque vous voyez « autovacuum s’exécute souvent mais toujours trop tard ». Vous voulez qu’il s’exécute plus tôt et réalise moins de travail à chaque fois.

Principe 3 : Augmentez les workers prudemment ; il est facile de faire un DOS

autovacuum_max_workers est tentant. Plus de workers peut améliorer le débit, mais cela multiplie l’I/O et l’usure du cache. Sur un stockage rapide avec RAM adéquate, cela peut être bénéfique. Sur un stockage saturé, cela peut transformer un système lent en un système effondré.

Conseils de base que j’applique sur de nombreux systèmes de production :

Commencez par passer de 3 à 5 workers sur des systèmes modérés si vous avez beaucoup de grandes tables.
Montez plus haut seulement avec la preuve que le stockage a de la marge et que les wait events ne sont pas dominés par l’I/O.
Fixez une limite si vous partagez des disques avec d’autres services ou avez des SLOs de latence stricts.

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_max_workers = 5;"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Signification : Nombre de workers augmenté ; configuration rechargée.

Décision : Surveillez l’I/O (iostat), la latence et les vues de progression pendant 30–60 minutes. Si le p99 se dégrade fortement, revenez en arrière ou augmentez les delays de coût.

Principe 4 : Utilisez le tuning par coût pour modeler l’impact, pas pour « accélérer le vacuum » aveuglément

Le modèle de coût du vacuum tente de limiter combien d’I/O le vacuum effectue en forçant des pauses. Les deux réglages qui importent le plus :

autovacuum_vacuum_cost_limit : combien de travail avant de dormir
autovacuum_vacuum_cost_delay : combien de temps dormir

Si le vacuum prend du retard et que vous avez de la marge I/O, augmentez le cost limit et/ou réduisez le delay. Si le vacuum cause de la latence applicative, faites l’inverse.

Un « premier mouvement » conservateur sur les systèmes SSD modernes est généralement :

Augmenter modérément autovacuum_vacuum_cost_limit (par ex., 200 → 1000)
Garder un délai petit mais non nul (par ex., 2ms est acceptable ; 0ms peut être agressif)

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Signification : Autovacuum peut effectuer plus de travail par unité de temps, mais cède toujours périodiquement.

Décision : Si vous êtes déjà I/O-saturé, ne faites pas cela globalement. Préférez le réglage par table ou corrigez la limite I/O sous-jacente.

Principe 5 : Ne réglez le freezing que lorsque vous comprenez le profil d’âge XID

Il y a deux modes d’échec courants :

Trop timide : le travail de freeze n’arrive pas à suivre, vous vous approchez du wraparound et le vacuum devient urgent et perturbateur.
Trop agressif : vous forcez un gel lourd trop souvent sur de grandes tables, augmentant l’amplification d’écriture.

Décidez en fonction de age(relfrozenxid) mesuré pour vos plus grandes tables et de l’âge au niveau base.

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT c.oid::regclass AS table, age(c.relfrozenxid) AS xid_age \
FROM pg_class c \
JOIN pg_namespace n ON n.oid=c.relnamespace \
WHERE n.nspname='public' AND c.relkind='r' \
ORDER BY xid_age DESC \
LIMIT 10;"
       table       | xid_age
-------------------+----------
 public.events      | 142000000
 public.sessions    | 120000000
(2 rows)

Signification : Ces tables poussent l’âge XID. Le travail de freeze doit se produire ici.

Décision : Envisagez autovacuum_freeze_max_age par table ou une cadence de vacuum plus agressive pour ces tables — après avoir traité le pinning.

Principe 6 : N’oubliez pas le partenaire caché : analyze

Autovacuum exécute souvent aussi analyze. Si les statistiques sont obsolètes, les plans deviennent étranges, ce qui change les patterns d’écriture et la durée des transactions. Cela rétroalimente le vacuum.

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT relid::regclass AS table, last_analyze, last_autoanalyze, n_mod_since_analyze \
FROM pg_stat_user_tables \
ORDER BY n_mod_since_analyze DESC \
LIMIT 10;"
      table       | last_analyze |      last_autoanalyze      | n_mod_since_analyze
------------------+--------------+----------------------------+--------------------
 public.events     |              | 2025-12-29 22:10:01+00     |            24000000
 public.sessions   |              | 2025-12-30 00:05:44+00     |            11000000
(2 rows)

Signification : Modifications massives depuis le dernier analyze ; les stats peuvent être en retard par rapport au churn.

Décision : Réduisez le scale factor d’analyze par table sur les tables à fort churn. Cela peut stabiliser les plans et réduire les pics de durée des transactions.

Principe 7 : Ne réglez pas autovacuum isolément (oui, je l’ai dit)

Voici où l’ingénierie stockage rencontre la réalité Postgres :

Vacuum lit beaucoup de pages. Si votre working set tient à peine en RAM, vacuum va éjecter des pages chaudes et amplifier les misses.
Vacuum écrit (updates de visibility map, freezing, nettoyage d’index). C’est de la pression fsync et WAL.
Sur volumes cloud, les limites IOPS/débit peuvent faire s’effondrer les workloads « random I/O heavy » de manière dramatique.

Donc : vérifiez les limites I/O et la taille mémoire avant de « réparer autovacuum ».

Trois mini-histoires d’entreprise (anonymisées)

Mini-histoire 1 : Incident causé par une mauvaise hypothèse

L’entreprise gérait un service d’abonnement avec un cluster Postgres chargé. Après la migration vers Ubuntu 24.04 et une nouvelle version mineure de Postgres, ils ont observé des pics de latence nocturnes. La conclusion de l’astreinte a été immédiate : « Nouveau noyau, autovacuum plus lent. »

Ils ont augmenté autovacuum_max_workers et réduit le cost delay à zéro globalement. Les pics se sont transformés en plateau soutenu de misère. Le CPU semblait correct, mais les disques étaient épinglés. Le tableau de bord indiquait « vacuum en cours en permanence », ce qui renforçait la narration que vacuum était le coupable.

Puis quelqu’un a finalement regardé pg_stat_activity et a vu une connexion d’un outil batch legacy restée idle in transaction pendant des heures. Avant, cela « passait » car le dataset était plus petit. Après croissance, le même bug est devenu un pin pour le vacuum.

Tuer cette session a permis au vacuum de commencer immédiatement à supprimer les tuples morts au lieu de sauter des pages pinées. Le pic de la nuit suivante a disparu sans aucun réglage agressif.

La mauvaise hypothèse n’était pas technique — elle était sociologique : blâmer le changement récent parce qu’il était récent. La correction était ennuyeuse : trouver le pin, corriger le client, puis réévaluer les paramètres de vacuum en fonction du débit réel.

Mini-histoire 2 : Une optimisation qui a mal tourné

Une équipe fintech avait une table de type ledger très sujette aux updates. Ils ont constaté de la bloat et ont décidé de « aider » autovacuum en mettant des seuils extrêmement agressifs et un cost limit élevé — globalement. Leur logique : si vacuum tourne en permanence, la bloat ne pourra pas s’accumuler.

Ce qu’ils ont obtenu fut une catastrophe silencieuse. Autovacuum a commencé à fouiller les gros index pendant les heures de pointe, détruisant le cache. Les endpoints API en lecture ont commencé à manquer le cache et à frapper le stockage. La latence a grimpé, les timeouts ont augmenté, et les retries applicatifs ont généré encore plus d’écritures.

L’astreinte a essayé d’ajouter des workers d’autovacuum. Cela a multiplié l’I/O et empiré le churn du cache. Le système n’était pas sous-vacuumé ; il n’était pas dimensionné pour le nouveau niveau de travaux d’arrière-plan en compétition avec le trafic au premier plan.

La correction finale a été un ciblage par table : vacuum agressif uniquement sur la table chaude, et seulement pendant des fenêtres à faible trafic en utilisant des schedules manuels pour les pires périodes de bloat. Ils ont aussi ajusté les index pour améliorer les HOT updates, ce qui a réduit la quantité d’index vacuuming nécessaire.

La leçon : vous pouvez absolument régler autovacuum pour en faire un générateur de charge. Postgres vous le permettra. Il est poli à ce sujet.

Mini-histoire 3 : Une pratique ennuyeuse mais correcte qui a sauvé la mise

Un fournisseur SaaS faisait tourner du Postgres multi-tenant avec des exigences d’uptime strictes. Ils avaient un rituel : chaque semaine, un petit script collectait les durées de vacuum, les tables avec le plus de tuples morts, les tendances d’âge XID, et des instantanés d’utilisation I/O. Rien de sophistiqué. Juste de la constance.

Une semaine, le script a signalé que age(datfrozenxid) augmentait plus vite que d’habitude sur une base de données d’un seul tenant. Pas d’incident encore, pas d’alertes déclenchées. Juste une tendance anormale.

Ils ont enquêté et trouvé un slot de réplication longue durée pour un pipeline analytics qui avait été désactivé mais pas supprimé. La rétention de WAL gonflait l’utilisation disque, les checkpoints devenaient plus lourds, et autovacuum se battait contre plus de contention I/O qu’avant. Le vacuum n’était pas « cassé » ; il était surclassé.

Ils ont supprimé le slot abandonné, la pression WAL a chuté, et autovacuum est redevenu prévisible. Personne n’a remarqué en externe. Le meilleur type d’incident est celui qu’on n’a jamais à expliquer.

Oui, c’était ennuyeux. C’est pour ça que ça a marché.

Erreurs courantes : symptôme → cause racine → correctif

1) Symptom: autovacuum “runs all the time” and dead tuples still climb

Cause racine : le vacuum est piné par des transactions longues ou des sessions « idle in transaction », ou il scanne de façon répétée mais saute des pages à cause de pins.

Correctif : Trouvez et éliminez les snapshots longs. Imposer des timeouts de statement et d’idle-in-transaction. Confirmez les pins via les logs autovacuum (« skipped due to pins ») et pg_stat_activity. Le réglage des workers n’aidera pas tant que les pins persistent.

2) Symptom: vacuum appears slow; `pg_stat_progress_vacuum` phase is “scanning” forever

Cause racine : scannage du heap limité par l’I/O sur une table bloatée, souvent avec des misses de cache et un stockage saturé.

Correctif : Réduire les causes de bloat (patterns d’update, index), garantir le débit stockage, envisager d’augmenter le cost limit seulement si le stockage a de la marge, et régler les seuils par table pour vacuumer plus tôt.

3) Symptom: latency spikes correlate with autovacuum starting

Cause racine : vacuum éjecte des pages chaudes et augmente l’I/O aléatoire ; trop de workers ou des coûts agressifs pendant les pics.

Correctif : Limitez l’impact d’autovacuum : augmentez le cost delay, baissez le cost limit, réduisez le nombre de workers, et préférez une agressivité par table uniquement là où c’est nécessaire. Envisagez des vacuum manuels hors-pointe pour les tables pathologiques.

4) Symptom: sudden emergency vacuum behavior, warnings about wraparound

Cause racine : le travail de freeze a pris du retard ; vous êtes près de autovacuum_freeze_max_age. Souvent causé par autovacuum désactivé, pinning, ou des tables trop grandes avec des seuils par défaut.

Correctif : Traitez comme priorité 0. Enlevez les sessions pinantes. Augmentez temporairement les ressources de vacuum et exécutez des vacuums manuels ciblés. Ensuite, ajustez les réglages de freeze et les seuils pour ne plus jamais approcher du précipice.

5) Symptom: “vacuum causes high WAL and replication lag”

Cause racine : le gel intensif et les mises à jour de visibility map peuvent générer du WAL ; des réglages agressifs amplifient cela. Les checkpoints peuvent aussi être mis à rude épreuve.

Correctif : Lissez la maintenance : évitez les tempêtes de vacuum en vacuumant plus tôt et plus petit ; ajustez les paramètres de checkpoint ; assurez-vous que la réplica a l’I/O pour suivre ; utilisez des paramètres par table plutôt que l’agression globale.

6) Symptom: vacuum is blocked by locks occasionally

Cause racine : DDL concurrent, ou opérations qui prennent des verrous plus forts (comme VACUUM FULL, REINDEX sans CONCURRENTLY) qui interfèrent.

Correctif : Évitez les DDL lourds en heure de pointe. Utilisez les variantes concurrentes quand c’est possible. Réservez VACUUM FULL aux fenêtres de maintenance planifiées et seulement si vous avez prouvé que c’est nécessaire.

Blague #2 : VACUUM FULL, c’est comme déménager pour retrouver la télécommande — efficace, mais votre week-end est parti.

Checklists / plan étape par étape

Step-by-step: from incident to stable tuning

Vérification de sécurité pour le freeze : interrogez age(datfrozenxid) et les tables principales par age(relfrozenxid). Si vous êtes proche des seuils de wraparound, arrêtez d’optimiser et commencez à prévenir la panne.
Vérification du pinning : trouvez les transactions longues et les « idle in transaction ». Corrigez ou terminez les contrevenants, et définissez des timeouts pour prévenir la récurrence.
Visibilité d’autovacuum : activez log_autovacuum_min_duration (par ex., 30s) pour voir les « skipped due to pins », l’utilisation des buffers, et les durées I/O.
Top offenders : listez les tables par tuples morts et par taille totale. Votre tuning doit cibler les 3–5 pires tables, pas tout le cluster.
Marge stockage : vérifiez avec iostat -x si vous avez de la marge I/O. Si vous êtes saturé, augmenter workers/cost aggravera probablement la latence.
Seuils par table : réduisez autovacuum_vacuum_scale_factor sur les très grosses tables à fort churn pour que le vacuum se lance plus tôt et en plus petites tâches.
Workers : augmentez modérément autovacuum_max_workers si vous avez beaucoup de tables et de la marge.
Tuning du coût : ajustez cost limit/delay pour équilibrer débit vs latence. Préférez par table quand c’est possible.
Tuning d’analyze : pour les tables très modifiées, réduisez le scale factor d’analyze. Cela évite des régressions de plan qui rendent la vie du vacuum plus difficile.
Validez : comparez avant/après : tendance des tuples morts, durées de vacuum, p95/p99 de latence, ratio cache hit, attente I/O.
Garde-fous : définissez des timeouts sensés (idle in transaction, statement timeout quand approprié), et des règles opérationnelles pour le DDL.
Revisitez le schéma : réduisez les index inutiles, évitez de mettre à jour des colonnes indexées inutilement, et envisagez le partitioning pour les très grosses tables append/update.

Operational checklist: the “don’t wake me up again” set

Journalisation des durées d’autovacuum activée à un seuil raisonnable.
Dashboards pour : tuples morts par table principale, âge XID, progression du vacuum, utilisation I/O, temp bytes, latence/LSN des slots de réplication.
Timeout idle-in-transaction défini (là où c’est sûr).
Runbook pour terminer les contrevenants évidents (avec règles d’escalade).
Politiques autovacuum par table documentées pour les tables à fort churn.
Politique DDL : pas de VACUUM FULL en heures ouvrées ; opérations concurrentes préférées.

FAQ

1) Should I just disable autovacuum and run manual VACUUM at night?

Non. C’est comme ça qu’on obtient de la bloat, des stats mauvaises, et finalement la pression de wraparound. Utilisez le vacuum manuel comme outil ciblé, pas comme stratégie principale.

2) Why does vacuum sometimes “skip due to pins”?

Parce qu’une transaction a encore besoin de voir d’anciennes versions de lignes. Causes typiques : requêtes de longue durée, « idle in transaction », ou certains patterns de réplication/CDC. Corrigez le pin ; vacuum ne peut pas outrepasser les règles MVCC.

3) Is increasing `autovacuum_max_workers` always safe?

C’est sûr dans le sens où Postgres ne va pas exploser immédiatement, mais cela peut absolument dégrader la latence en saturant le stockage et en accélérant le churn du cache. Augmentez les workers seulement après avoir confirmé la marge I/O et réglé les problèmes de pinning.

4) What’s the safest first tuning change?

Activez la journalisation des durées d’autovacuum (log_autovacuum_min_duration) et ajustez les scale factors par table sur les plus grandes tables à fort churn pour que le vacuum se déclenche plus tôt. Ces changements améliorent l’observabilité et réduisent les « tempêtes de vacuum ».

5) Why is autovacuum slow on SSD? SSDs are fast.

Les SSD sont rapides, pas infinis. Vacuum peut générer des lectures/écritures aléatoires mixtes plus le WAL. Si votre workload consomme déjà la plupart des IOPS/débit, vacuum se bat pour le même budget. De plus, le churn de cache peut rendre le « stockage rapide » inutile si tout manque la RAM.

6) Does `VACUUM (ANALYZE)` help with mystery slowness?

Parfois. Si le problème vient de stats obsolètes menant à des plans catastrophiques, oui. Si le problème est du pinning ou une saturation I/O, ça ne résoudra pas la cause racine, mais cela peut réduire la volatilité des plans après correction du problème sous-jacent.

7) Why do my indexes bloat even when table bloat looks okay?

Les updates sur des colonnes indexées créent des entrées d’index mortes. Même avec HOT updates, si les colonnes mises à jour sont indexées (ou si la ligne ne tient pas sur la même page), HOT ne s’applique pas et les index s’usent. Corrigez en réduisant les index inutiles et en évitant de mettre à jour des colonnes indexées quand c’est possible.

8) How do I know if autovacuum is the cause of latency, or just correlated?

Regardez les wait events et les métriques I/O système pendant le pic. Si les requêtes applicatives attendent de l’I/O et que vacuum consomme une grande part des lectures/écritures, c’est probablement causal. Si temp bytes, des tris, ou un job batch coïncident avec les pics, vacuum peut n’être que présent sur la scène.

9) Can I tune autovacuum differently per table?

Oui, et vous devriez le faire pour les grandes tables à fort churn. Utilisez des paramètres de stockage par table comme autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, et des réglages de coût par table si nécessaire.

10) What if I’m on a managed platform or container with cgroups limits?

Dans ce cas, vous devez vérifier les quotas CPU et I/O. Vacuum peut être bridgé par la plateforme même si la VM semble « idle ». Vérifiez systemd et les settings cgroup et alignez-les avec vos besoins de maintenance.

Conclusion : prochaines étapes à déployer cette semaine

Si autovacuum est « mystérieusement » lent sur Ubuntu 24.04, supposez qu’il vous dit la vérité sur l’une des trois choses suivantes : vous êtes piné, vous êtes I/O-limité, ou vous êtes sous-dimensionné/bridgé.

Rendez visible : définissez log_autovacuum_min_duration sur une valeur utile et commencez à lire ce que vacuum dit sur les pins et les durées I/O.
Éliminez le pinning à la source : supprimez les « idle in transaction », corrigez les snapshots de longue durée, et nettoyez les slots de réplication abandonnés.
Ciblez les pires tables : réduisez les scale factors de vacuum/analyze sur les grosses tables à fort churn pour que le vacuum se déclenche plus tôt et en plus petites unités.
Ensuite seulement ajustez workers et paramètres de coût, par petites incréments, en surveillant l’attente I/O et le p99 de latence.
Rendez-le ennuyeux : suivez les tendances des tuples morts, de l’âge XID, et des durées d’autovacuum. L’ennui, c’est la stabilité. La stabilité, c’est la performance.