Keep jobs in Postgres so an enqueue commits or rolls back with the work that created it
brandur-2015-postgres-job-queue#d1at 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.
Switched from Queue Classic to Que, which delayed but did not remove the degradation
brandur-2015-postgres-job-queue#d2at 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.
A long-lived transaction pinned dead rows and degraded a Postgres job queue
brandur-2015-postgres-job-queue#f1at 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.