Files d'attente PostgreSQL : pourquoi FOR UPDATE SKIP LOCKED génère du bloat et comment PgQue le résout

Le problème que tout le monde ignore en production

Si vous avez déjà implémenté une file d'attente dans PostgreSQL, vous avez très probablement écrit quelque chose comme ça :

-- Producteur
INSERT INTO jobs (payload) VALUES (...);

-- Consommateur
WITH claimed AS (
  SELECT id, payload FROM jobs
  WHERE state = 'pending'
  ORDER BY id
  LIMIT 100
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET state = 'processing'
WHERE id IN (SELECT id FROM claimed)
RETURNING id, payload;

-- Après traitement
DELETE FROM jobs WHERE id = ANY($1);

Cette approche fonctionne en développement. Elle fonctionne en benchmark. Elle fonctionne pendant le premier mois de production. Puis elle cesse de fonctionner — et la raison est toujours la même.

Le bloat silencieux : la mécanique MVCC contre vous

PostgreSQL utilise MVCC (Multi-Version Concurrency Control) : chaque UPDATE ou DELETE ne modifie pas la ligne en place, il crée une nouvelle version et marque l'ancienne comme « morte ». Ces dead tuples sont normalement récupérés par l'autovacuum.

Mais voilà le piège : autovacuum ne peut supprimer une dead tuple que si aucune transaction active ne peut encore la voir. Ce seuil est déterminé par le xmin global — l'identifiant de la transaction la plus ancienne encore ouverte sur le cluster.

Dans une file d'attente à fort débit avec SKIP LOCKED, chaque job traité génère :

• 1 dead tuple à la mise à jour vers processing
• 1 dead tuple à la suppression finale

Si une seule transaction longue tient le xmin (une requête analytique lente, une connexion idle-in-transaction, un abonné de réplication logique en retard, un standby physique avec hot_standby_feedback=on), autovacuum est bloqué. Les dead tuples s'accumulent. La table enfle. Les index enflent. Les performances s'effondrent. La table nécessite un VACUUM FULL bloquant pour retrouver sa taille normale.

Ce n'est pas un bug dans votre code. C'est une conséquence structurelle de l'utilisation de mutations fréquentes sur des lignes à courte durée de vie dans un moteur MVCC.

L'approche PgQue : zéro mutation sur le hot path

PgQue est un portage en pur SQL et PL/pgSQL de PgQ, le moteur de file d'attente développé à l'époque de Skype par Marko Kreen (circa 2007). La nouveauté de PgQue v0.1 n'est pas algorithmique — l'algorithme est inchangé — c'est qu'il ne requiert aucune extension C ni aucun daemon externe, ce qui le rend compatible avec les offres Postgres managées.

L'idée centrale : remplacer les mutations par des snapshots et des diffs.

Plutôt que de marquer des lignes comme « en cours de traitement » avec un UPDATE, PgQue fonctionne ainsi :

1. Les producteurs ne font qu'insérer (INSERT) des événements dans une table append-only. Aucun UPDATE, jamais.
2. La file est découpée en batches via des snapshots de transaction PostgreSQL. Chaque batch est délimité par deux snapshots : celui du début et celui de la fin de la fenêtre.
3. Les consommateurs obtiennent un batch en lisant les événements dont l'xid (transaction ID) est compris entre ces deux snapshots — un simple diff entre deux états connus.
4. Une fois le batch consommé et acquitté, les données sont archivées ou purgées en masse, pas ligne par ligne.

Le résultat : le hot path ne contient aucun UPDATE, aucun DELETE, aucun SELECT ... FOR UPDATE SKIP LOCKED. Les tables d'événements ne génèrent pas de dead tuples pendant le traitement normal. Le problème de bloat est résolu à la racine, pas contourné.

Ce que ça change concrètement

Cette approche par snapshots présente plusieurs avantages opérationnels importants :

Pas de sensibilité au xmin global. Puisqu'il n'y a pas de dead tuples à récupérer sur les tables actives, une transaction longue ailleurs dans le cluster ne dégrade pas les performances de la file d'attente.

Pas de contention de verrous. SKIP LOCKED élimine les deadlocks mais crée de la contention sur les lignes visibles par plusieurs workers. Avec des snapshots, chaque consommateur lit une plage d'événements déjà délimitée — pas de compétition.

Comportement prévisible sous charge. Le throughput ne s'effondre pas progressivement à mesure que les tables gonflent. La dégradation n'est pas cachée pendant des semaines avant de devenir critique.

Compatibilité étendue. L'absence d'extension C et de daemon signifie que PgQue peut être déployé sur RDS, Cloud SQL, Supabase, ou tout autre Postgres managé où vous n'avez pas accès au système de fichiers du serveur.

Conclusion

L'approche FOR UPDATE SKIP LOCKED est bien documentée, facile à implémenter et souvent suffisante. Mais dans des architectures à fort débit ou avec des connexions longues vivant sur le même cluster, elle transfère un problème structurel de PostgreSQL — le bloat MVCC — directement dans vos tables de jobs.

PgQue propose une alternative qui résout ce problème à la source en s'appuyant sur les primitives natives de PostgreSQL différemment : les snapshots de transactions plutôt que les verrous row-level.

L'algorithme est vieux de presque vingt ans. Ce qui est nouveau, c'est qu'il est désormais accessible sans aucune dépendance externe, ce qui le rend pertinent pour une large gamme d'architectures modernes — y compris celles que nous déployons chez MulerTech sur des infrastructures Symfony/PostgreSQL.

📖 Source originale : PgQue: Two Snapshots and a Diff par Christophe Pettus.