Catalogue / brandur-2015-postgres-job-queue

A long-running transaction stalls a Postgres job queue

Not named in the source. Postgres-backed job queue (Queue Classic, then Que).

Organisation
Not named in the source
Evidence
Reported from production
System
Postgres-backed job queue (Queue Classic, then Que)
Source
Postgres Job Queues & Failure By MVCC

brandur.org (the author's personal blog), published 2015-05-18

Architecture

A job queue kept in a Postgres table, run first on the Queue Classic library and then on Que, whose workers lock jobs with Postgres advisory locks.

Section: Locking algorithms

Figures, as the source states them

FigureAs statedNoteWhere
Job churn through the jobs table in the simulation50 jobs per secondRoughly; test bench simulation, not productionNear: roughly 50 jobs a second here
Time to lock a job in stable operation0.01 secondsStated as under this value; test benchSection: Slow lock time
Time to lock a job as the oldest transaction ages0.1 secondsAt times and above; test benchSection: Slow lock time
Dead rows in the jobs table at the end of the run100,000 rowsApproaching; test benchSection: Dead tuples
Jobs queued one hour into a long transaction, unpatched Que60,000 jobsTest bench chart, vanilla QueNear: Number of jobs in the queue on vanilla
Jobs queued one hour in, Que patched to reuse the last job id30,000 jobsTest bench chart, patched Que on the same test databaseNear: 30k one hour in

Records

Decision#d1

Keep jobs in Postgres so an enqueue commits or rolls back with the work that created it

brandur-2015-postgres-job-queue#d1 at corpus version 74847648e25a
Chosen

The team ran its job queue as a table in its Postgres database, first through the Queue Classic library and later through Que.

Section: Locking algorithms

Reasoning

The author's case for a database queue is transactional consistency: a job inserted by an operation that fails is undone along with it, and workers cannot pick up a job before the inserting transaction has committed.

Section: Why put a job queue in Postgres?

Cost

The queue became exposed to long-lived transactions elsewhere on the database. The author sees a job queue as the worst case of a problem that any hot Postgres table can suffer, and agrees only in part that Postgres is a poor home for a queue.

Section: Lessons learnt

Reviewer's notes

The rationale is written as the author's general argument for a database queue, not as a record of this team's original decision. Nothing measured forced the choice, so forcing_condition is empty.

The reviewer's reading, not the source's claim

Decision#d2

Switched from Queue Classic to Que, which delayed but did not remove the degradation

brandur-2015-postgres-job-queue#d2 at corpus version 74847648e25a
Chosen

Moved the job queue from the Queue Classic library to Que, a package the post calls known to be faster.

Section: Locking algorithms

What forced it

A problem with the queue in production, which the team suspected was caused by the comparatively slow way Queue Classic locks jobs.

Section: Building a test bench | Section: Locking algorithms

Outcome

The problem still existed on Que, although its better overall performance seemed to hold it off a little longer. The author says both libraries share the same root problem.

Section: Locking algorithms

Reviewer's notes

Swapping libraries did not touch the cause that f1 describes.

The reviewer's reading, not the source's claim

Failure#f1

A long-lived transaction pinned dead rows and degraded a Postgres job queue

brandur-2015-postgres-job-queue#f1 at corpus version 74847648e25a
Trigger

Long-lived transactions on a Postgres database the queue shared with other teams. The author says the problem would have taken much longer to appear had the database not been shared with teams that understood little about what they ran on it.

Section: Lessons learnt

How it failed

While an old transaction stays open, deleted job rows cannot be vacuumed because it might still see them. Lock attempts then scan through ever more dead index entries (100,000 passes per attempt by the end of the test bench run) and every job worked adds another, so locking slows, workers fall behind and the queue grows without bound.

Section: Postgres MVCC | Section: Descending the B-tree | Section: Slow lock time

Why defences did not hold

Automatic vacuuming is meant to clear dead rows, but a manual vacuum of the queue table in the test bench found 247311 dead row versions that could not be removed yet, because they were possibly still visible to another process.

Section: Dead tuples

Impact

The test bench ran a job producer, a job worker and a process that opens a transaction and sits idle in it. There the time to lock a job climbed from under 0.01 seconds to 15x that level, 0.1 seconds and above, so workers locked fewer jobs in the same time; left long enough, jobs arrived faster than they were worked and the queue ran away. The post gives no production figures.

Section: Building a test bench | Section: Slow lock time

Fix

Done on the test bench only: a patch to Que where workers re-use the id of the last job they worked, plus time-based lock jitter. Under degraded conditions it kept the queue stable for roughly twice as long, then degraded too. Advice, not stated as done by the team: a supervisor that terminates old transactions, statement_timeout, and not sharing a database across components or teams. The post does not say whether any of this reached production.

Section: Predicate specificity | Section: Lock jitter | Section: Lessons learnt

Reviewer's notes

Every quantified result is from the author's test bench. The post gives no figures for the production occurrence, and its opening scenario is a story.

The reviewer's reading, not the source's claim

The post does not say whether the patched Que, or a supervisor that terminates old transactions, was deployed in production.

The reviewer's reading, not the source's claim

Review

Extracted by
claude-sonnet-5-5
Human review
Not started. Status recorded on 2026-11-16.