Maintenir une file d’attente Postgres en bonne santé

• 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 ligne
  • xmax : 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éfinie
  • idle_in_transaction_session_timeout (9.6) : termine une session restant inactive au sein d’une transaction
  • transaction_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
  1. 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
  2. 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