- Récapitulatif des causes et des solutions à un problème chronique lors de l’exploitation d’une job queue dans Postgres : l’accumulation de dead tuples, le bloat de table qui en découle et la dégradation des performances
- Dans une table de file d’attente, la plupart des lignes suivent un cycle court insertion-lecture-suppression ; sa taille reste donc stable mais son débit cumulé est très élevé
- En raison de l’architecture MVCC de Postgres, les lignes supprimées ne sont pas retirées immédiatement : elles restent sous forme de dead tuples devant être nettoyés, tâche assurée par autovacuum
- Si des transactions longues ou des requêtes analytiques qui se chevauchent figent l’horizon MVCC, autovacuum ne peut plus nettoyer les dead tuples et les performances de la file se dégradent
- La fonctionnalité Traffic Control de PlanetScale (extension Insights) est présentée comme un moyen concret de résoudre ce problème grâce à des limitations de ressources par classe de requêtes
Caractéristiques d’une charge de travail de file d’attente
- La particularité d’une table de file d’attente est que la majorité des lignes sont transitoires : elles sont insérées, lues une fois, puis supprimées
- La taille de la table reste à peu près constante, mais le débit cumulé est énorme
- L’un des principaux avantages de placer la job queue dans Postgres est de pouvoir synchroniser l’état des jobs et d’autres logiques DB dans une même transaction
- si le job échoue, toute la transaction est annulée
- avec un service de file externe, synchroniser l’état transactionnel de l’application devient plus complexe
Exemple de table de file et comportement des workers
- Schéma de base présenté dans l’article
CREATE TABLE jobs (
id BIGSERIAL PRIMARY KEY,
run_at TIMESTAMPTZ DEFAULT now(),
status TEXT DEFAULT 'pending',
payload JSONB
);
CREATE INDEX idx_jobs_fetch ON jobs (run_at) WHERE status = 'pending';
- Le worker ouvre une transaction et verrouille le plus ancien job pending avec
FOR UPDATE SKIP LOCKED pour éviter un traitement en double
- En cas de succès, il exécute
DELETE puis COMMIT ; en cas d’échec, un rollback rend la ligne de nouveau visible pour un autre worker
- Cette transaction doit être la plus courte possible : plus elle reste ouverte, plus elle bloque vacuum (l’exemple de l’article part de workers sous la milliseconde)
Le problème n’est pas la performance brute
- Il est déjà documenté que Postgres peut gérer des job queues de grande ampleur ; la capacité en elle-même n’est pas le problème
- Le vrai sujet est la cohabitation avec d’autres charges de travail concurrentes sur la même base
- La bonne santé de la table de file dépend non seulement de sa configuration, mais aussi du comportement de toutes les transactions qui tournent sur la même instance Postgres
- L’article se concentre sur le trafic de requêtes concurrentes sur le primaire (l’impact des réplicas et des replication slots est traité séparément)
Le vrai problème : le nettoyage des dead tuples
- Postgres utilise le MVCC pour conserver plusieurs versions d’une même ligne : une ligne supprimée n’est pas retirée immédiatement, mais marquée comme supprimée, puis devient invisible aux nouvelles transactions
- Ces lignes restantes sont des dead tuples, nettoyés par une opération de vacuum
- Les dead tuples n’apparaissent pas dans les résultats d’un
SELECT, mais ils continuent d’avoir un coût
- Sequential scan : l’exécuteur lit les dead tuples dans les heap pages, vérifie leur visibilité puis les écarte
- Index scan (la méthode utilisée dans la file avec
ORDER BY run_at LIMIT 1) : l’index B-tree accumule des références vers des dead tuples et doit donc parcourir aussi des entrées pointant vers des lignes qui ne sont plus visibles
- Chaque dead index entry provoque des I/O supplémentaires ; l’application ne le voit pas directement, mais le coût augmente fortement avec le nombre de dead tuples
- Le rythme de nettoyage dépend de
autovacuum_naptime (1 minute par défaut), et le déclenchement de autovacuum_vacuum_threshold et autovacuum_vacuum_scale_factor
Mécanisme interne des dead tuples
- Trois métadonnées de ligne sont essentielles
ctid : position physique du tuple dans le heap (page, offset)xmin : identifiant de transaction (XID) ayant inséré la lignexmax : identifiant de transaction ayant supprimé/verrouillé la ligne ; 0 signifie qu’elle n’est pas marquée comme supprimée
- Même lorsqu’il ne faut retourner que 3 lignes pending, l’exécuteur peut devoir scanner les 6 dead tuples supprimés auparavant avant de ne renvoyer que 3 lignes
- De même, si une entrée feuille de l’index pointe vers un tuple du heap devenu mort, le travail gaspillé pendant le scan s’accumule
- Si la base nettoie les dead tuples moins vite qu’elle n’en crée, elle entre dans une trajectoire d’échec
- Un cluster Postgres bien réglé peut absorber des dizaines de milliers d’opérations de file par seconde
Quand autovacuum devient impuissant
- Principales causes d’échec d’autovacuum pour nettoyer les dead tuples
- un verrou de table spécifique bloque le cleanup
- des réglages autovacuum inadaptés
- le cas le plus fréquent : une transaction active empêche la récupération des dead tuples
- Postgres ne vacuum pas les dead tuples qui pourraient encore être visibles par une transaction active
- la plus ancienne transaction active fixe la coupure : c’est l’horizon MVCC
- tant que cette transaction n’est pas terminée, tous les dead tuples postérieurs à son snapshot sont conservés
- Une seule transaction de 2 minutes peut figer l’horizon pendant 2 minutes
- Le même schéma d’échec peut aussi être provoqué par des requêtes de durée moyenne qui se chevauchent
- par exemple, trois requêtes analytiques de 40 secondes lancées en décalage toutes les 20 secondes : aucune n’expire individuellement, mais il y en a toujours une active, donc l’horizon n’avance jamais
- Quand on suit la philosophie “Just use Postgres” et qu’on met plusieurs workloads sur la même base, le problème n’est pas la rapidité du traitement des jobs elle-même, mais le fait que des requêtes lentes qui se chevauchent repoussent le nettoyage des dead tuples
Outils existants et limites
- Options de tuning d’autovacuum :
autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit
- Timeouts pour limiter les requêtes longues
statement_timeout (Postgres 7.3) : termine une instruction SQL qui dépasse la durée définieidle_in_transaction_session_timeout (9.6) : termine une session restant inactive au sein d’une transactiontransaction_timeout (17.0) : termine une transaction active ou inactive qui dépasse la durée configurée
- Ces timeouts ne visent que la durée d’exécution d’une requête isolée ; ils ne limitent ni la concurrence ni le coût d’exécution, et ne conviennent donc pas pour bloquer des workloads qui figent durablement l’horizon MVCC
- Il faut pouvoir distinguer les classes de trafic et laisser les workloads prioritaires tranquilles tout en régulant l’usage des ressources des workloads moins prioritaires
Database Traffic Control™
- Fonctionnalité développée par PlanetScale dans le cadre de l’extension Insights, réservée à PlanetScale Postgres
- Elle sert à contrôler finement les performances et l’usage des ressources des requêtes individuelles
- Avec un Resource Budget, on attribue une limite de ressources à des requêtes ciblées, qui peuvent ensuite être bloquées si elles la dépassent
- La stratégie consiste à limiter le nombre et la fréquence des requêtes lentes qui se chevauchent, afin de laisser à autovacuum suffisamment d’espace pour nettoyer les dead tuples à un rythme correct
- Les requêtes bloquées ne sont pas rejetées définitivement : l’application doit impérativement prévoir une logique de retry
- L’idée est de lisser la vitesse d’exécution tout en conservant le même volume de travail global
Démo et contexte
- L’article s’inspire du billet de blog de Brandur Leach publié en 2015, "Postgres Job Queues & Failure By MVCC"
- il y documentait un mode de défaillance critique des job queues basées sur Postgres
- avec un banc de test montrant concrètement qu’une transaction non refermée peut figer l’horizon MVCC et empêcher le cleanup
- Le banc de test original est publié sous le nom
brandur/que-degradation-test
Reproduction du problème (avec Postgres 18)
- Le test original reposait sur Ruby + le gem Que v0.x + Postgres 9.4
- L’auteur l’a réécrit en TypeScript + Bun pour isoler et valider le comportement au seul niveau SQL
- Même motif de recursive CTE que Que, même schéma, même rythme du producteur, même durée de travail, même nombre de workers et même motif de long-runner
- Exécution sur un cluster PlanetScale PS-5 (à partir de 5 $/mois)
- Résultat : dégradation visible mais encore gérable
- le test original entraînait la base dans une death spiral en 15 minutes, mais sur PS-5 la file des workers est restée proche de 0 pendant 15 minutes
- cependant, les dead tuples augmentaient linéairement, signe que le même problème réapparaîtrait avec plus de temps
- grâce aux améliorations de nettoyage des index B-tree (bottom-up deletion face au churn de versions, suppression de dead index tuples fondée sur les scans, etc.), le phénomène est atténué mais pas supprimé
Tentative d’amélioration : SKIP LOCKED + traitement par lots
- Deux améliorations modernes absentes en 2015
FOR UPDATE SKIP LOCKED — remplace l’ensemble du recursive CTE par un simple SELECT, en sautant les lignes déjà verrouillées par d’autres workers- Batch processing (10 jobs par transaction) — un seul lock acquisition permet d’en traiter 10, ce qui amortit le coût du scan d’index
- Conditions identiques : 8 workers, producteur à 50 jobs/s, travail de 10 ms, démarrage du long-runner après 45 secondes
- Principaux résultats
| Indicateur |
original (recursive CTE) |
enhanced (SKIP LOCKED + batch) |
| Baseline lock time |
2–3ms |
1.3–3.0ms |
| End lock time (typical) |
10–34ms |
9–29ms |
| Worst spike |
84.5ms (dead tuple 33k) |
180ms (dead tuple 24k) |
| Queue depth |
0–100 (oscillating) |
0 (la plupart du temps) |
| Dead tuples at end |
42,400 |
42,450 |
| Throughput |
~89/s |
~50/s |
- La courbe de dégradation est presque identique : dans les deux cas, on scanne le même index B-tree et on rencontre les mêmes dead tuples
- L’écart de throughput ne vient pas de la stratégie de verrouillage, mais de la conception du test (le worker CTE prend les jobs plus vite que le producteur, tandis que le worker batch vide la file puis repart en backoff sleep)
- Conclusion : une architecture de file qui mettait la base à genoux en 15 minutes il y a 10 ans tient aujourd’hui beaucoup plus longtemps, mais le problème de fond demeure — en passant à 500 jobs/s, le problème réapparaît plus vite
Résolution avec Traffic Control
- Moyens de contrôle fournis par Resource Budget
- Server share & burst limit : part des ressources serveur et vitesse de consommation
- Per-query limit : durée maximale d’exécution d’une requête, en secondes, selon sa consommation de serveur
- Maximum concurrent workers : proportion par rapport aux processus worker disponibles
- Le ciblage des requêtes se fait principalement via les métadonnées de balises SQLCommenter (par exemple
action=analytics)
- Au lieu d’un long-runner détectable par
idle_in_transaction_session_timeout, l’article provoque la dégradation avec un scénario plus réaliste : des requêtes analytiques actives qui se chevauchent, un cas que les session timeouts ne peuvent pas arrêter
- Le Maximum concurrent workers des requêtes
action=analytics est limité à 1 worker (25 % de max_worker_processes) → une seule requête analytique peut s’exécuter à la fois
- Pour forcer une death spiral dans la fenêtre de 15 minutes, le producteur est poussé à 800 jobs/s
- Depuis EC2, deux exécutions de la charge "enhanced" sont lancées sur la même base PlanetScale
- 800 jobs/s
- 3 requêtes analytiques de 120 secondes exécutées en continu, décalées pour toujours se chevaucher
- pendant 15 minutes
- Comparaison des résultats
| Indicateur |
Traffic Control désactivé |
Traffic Control activé |
| Queue backlog |
155,000 jobs |
0 jobs |
| Lock time |
300ms+ |
2ms |
| Dead tuples at end |
383,000 |
0–23,000 (cycling) |
| Analytics queries |
3 concurrent, overlapping |
1 at a time, 2 retrying |
| VACUUM effectiveness |
Blocked (horizon figé) |
Normal (fenêtre de nettoyage entre les requêtes) |
| Outcome |
Death spiral |
Completely stable |
- Traffic Control limite directement la concurrence d’un workload donné, un niveau de contrôle impossible à obtenir avec le seul tuning d’autovacuum ou les timeouts
- Les rapports analytiques ont continué à s’exécuter dans la limite de la capacité disponible, avec 15 exécutions terminées sur les 15 minutes, tandis que la file est restée saine tout du long
En résumé
- Le problème des dead tuples MVCC dans les files basées sur Postgres n’est pas un vestige de 2015
- Le Postgres moderne offre une marge bien plus confortable grâce aux améliorations B-tree et à
SKIP LOCKED, mais le mécanisme de fond reste le même
- si VACUUM ne peut pas nettoyer les dead tuples, ils s’accumulent
- si des transactions longues ou qui se chevauchent figent l’horizon MVCC, VACUUM ne peut pas les nettoyer
- Dans un environnement “Just use Postgres” où file, analytique et logique applicative partagent la même base, c’est une condition d’exploitation courante, pas un risque théorique
- La forme dangereuse n’est pas un crash spectaculaire, mais un état d’équilibre qui se dégrade silencieusement : le lock time augmente lentement, les jobs ralentissent et aucune alerte ne se déclenche
- Les outils de timeout de Postgres ne permettent ni de distinguer les classes de workload ni de limiter la concurrence
- Si la file tourne aux côtés d’autres workloads, la mesure la plus efficace consiste à garantir que VACUUM puisse suivre, et Traffic Control simplifie cela
1 commentaires
Commentaires Hacker News
Postgres a toujours le problème du vacuum horizon. Il s’agit du phénomène où des requêtes de longue durée empêchent le vacuum sur des tables qui changent rapidement. Ce problème est bien connu depuis 2015. Postgres de base ne dispose pas de bons outils pour le résoudre, mais la version personnalisée de l’entreprise de l’auteur a une fonctionnalité qui le corrige. En conclusion, mélanger des traitements longs de type OLAP et des traitements rapides de type file d’attente sur la même instance Postgres reste peu judicieux. Selon les besoins, utiliser une file de messages comme 0MQ ou RMQ peut être une solution plus simple
L’article était correct, mais il y a quelques points à relever.
SELECT * FROM jobs WHERE status='pending' ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKED;existe bel et bien, mais on peut l’atténuer en ajoutant une colonne monotone avec un index. De cette façon, il n’est pas nécessaire de tenir compte des tuples morts, on gaspille seulement de l’espace et les performances de lecture se dégradent moins. En revanche, garantir la monotonie en cas d’écritures concurrentes dépend de la conception de l’applicationC’est l’auteur. N’hésitez pas si vous avez des questions
Cela ressemble à de la publicité, mais j’aurais aimé avoir au moins un peu d’explication sur la manière dont la solution technique fonctionne
Postgres peut vraiment faire énormément de choses. Les gens choisissent Kafka ou SQS, alors qu’en réalité beaucoup de tâches peuvent être gérées correctement avec Graphile Worker
Dans Postgres, le problème devient bien plus grave quand on met à jour des lignes. Si l’on se limite aux insertions et suppressions, on peut tenir assez longtemps