Postgres as a job queue: SKIP LOCKED and where it breaks

You have work that should not happen inside the request: send the email, resize the image, call the slow third-party API. Something has to hold those jobs until a worker gets to them. You already have Postgres. The question is whether it can be the queue, and for how long.

enqueue:  INSERT INTO jobs ...                 (same transaction as the business write)
claim:    SELECT ... FOR UPDATE SKIP LOCKED
finish:   UPDATE jobs SET status = 'done'      (or DELETE)

Why a table and not a broker

The strongest argument is the first line above. The job is inserted in the same transaction as the row it is about. Either both commit or neither does. With Kafka or Redis there are two writes to two systems, and every "the order exists but the email never went out" incident is that second write failing.

Other things you get for free:

Nothing new to run. No broker, no cluster, no second backup strategy.
Jobs are rows. Cancel a job with UPDATE. Reprioritise with UPDATE. Find out why the queue is stuck with SELECT.
Delayed and scheduled jobs are a run_at column and a WHERE run_at <= now().

And why not the usual alternatives:

Kafka is a log, not a queue. Consumers acknowledge an offset, not a message, so you cannot skip, cancel or reorder one job once it sits in a partition. It is excellent at a firehose and bad at "retry this one job in ten minutes".
Redis is fast and the list and stream APIs are pleasant, but durability is a configuration choice (how often the append-only file is fsynced), the enqueue is not atomic with your database write, and locking across Redis nodes is harder than it looks. Kleppmann's essay on distributed locking in the references is the standard warning.

So for most applications a jobs table in Postgres is the right first answer. The rest of this post is about making the claim step correct, and then about what happens when it is hammered.

Claiming a job, three attempts

Attempt 1: just select it

BEGIN;
SELECT * FROM jobs WHERE status = 'queued' LIMIT 1;
-- process the job
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

Two workers run the SELECT at the same moment and both get the same row. Both process it. A plain read takes no lock, so nothing stops this.

Attempt 2: lock it

BEGIN;
SELECT * FROM jobs WHERE status = 'queued' LIMIT 1 FOR UPDATE;
-- process the job
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

FOR UPDATE takes a row lock, so only one worker gets the row. Correct, but every other worker's scan lands on that same first row and waits for the lock. When the first worker commits, the waiters re-check the row, see it is no longer queued, and move on to the next one, where the same thing happens again. The queue is serialised: one job at a time, however many workers you run.

Attempt 3: skip what is locked

BEGIN;
SELECT * FROM jobs
WHERE  status = 'queued'
ORDER  BY run_at, id
LIMIT  1
FOR UPDATE SKIP LOCKED;
-- process the job
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

SKIP LOCKED tells the scan to behave as if locked rows do not exist. Worker A locks job 1. Worker B does not wait; it takes job 2. N workers claim N different jobs with no coordination outside the database. That is the whole trick, and it has been in Postgres since 9.5.

Two relatives worth knowing so you can rule them out:

FOR UPDATE NOWAIT raises an error instead of waiting. Useful when you would rather fail fast than queue up. Wrong for a worker loop, where you want the next row, not an exception to catch.
Advisory locks (pg_try_advisory_lock(id)) can build the same thing. The lock is on a number the application chooses rather than on a row, so the application has to agree with itself about what the number means and has to remember to release it. SKIP LOCKED uses the row lock Postgres already tracks, which is why it is the default choice.

The real worker loop

Collapse "claim" and "mark as running" into one statement:

UPDATE jobs
SET    status = 'running', locked_at = now(), attempts = attempts + 1
WHERE  id = (
  SELECT id FROM jobs
  WHERE  status = 'queued' AND run_at <= now()
  ORDER  BY run_at, id
  LIMIT  1
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

With a partial index so the subquery never looks at finished jobs:

CREATE INDEX jobs_queued_idx ON jobs (run_at, id) WHERE status = 'queued';

Then there is a choice about the transaction. Either hold it open while the job runs, or commit the claim immediately and process outside it.

Hold the transaction. If the worker dies, the lock dies with its connection and the job is queued again by itself. The cost is one connection per in-flight job for the whole job, and a long-running transaction that pins old row versions across the entire database, which is the start of the vacuum trouble below.
Commit the claim. The connection goes back to the pool at once and vacuum is unaffected. The cost is a crashed worker leaves its job stuck in running, so you need a reaper: a periodic UPDATE that puts rows back to queued when locked_at is older than a timeout.

Hold the transaction only for jobs that finish in milliseconds. For anything slower, commit the claim and run the reaper. The reaper makes delivery at-least-once, but it already was: a worker can always die after the side effect and before the done update. Make handlers idempotent, and when a handler writes to something outside Postgres, send attempts along as a fencing token so a stale worker that wakes up late cannot overwrite the work of the one that replaced it.

Where it breaks

Everything above works beautifully in dev and staging. The lifecycle is insert, lock-and-update, update-or-delete, repeated thousands of times a second, and at ten workers Postgres does not notice. At a few thousand workers it does. CPU climbs without throughput following it. Autovacuum stops keeping up with the jobs table. And pg_stat_activity fills with backends waiting on LWLock:MultiXactOffsetSLRU and LWLock:MultiXactMemberSLRU.

What a MultiXact is

A row lock is recorded in the row itself: the tuple's xmax field holds the id of the transaction that locked it. One field, one transaction id. When more than one transaction has to hold a lock on the same row at once, the id no longer fits, so Postgres mints a MultiXactId: a number that stands for a list of transaction ids, stored on disk under pg_multixact with a small in-memory cache called an SLRU ("simple least-recently-used").

A job row ends up with a multixact in xmax more easily than you would expect, even when one worker is supposed to own it:

Subtransactions. The framework wraps the handler in a savepoint (Rails requires_new, Django's nested atomic, SQLAlchemy's begin_nested, a JDBC retry wrapper). The outer transaction locked the row, the subtransaction updates it, and now two transaction ids belong on that row. Multixact.
Foreign keys. jobs.queue_id references queues. Every insert takes a KEY SHARE lock on the parent queues row so it cannot be deleted underneath it. A thousand concurrent inserts into the same queue are a thousand transactions sharing one row lock. Multixact, on the parent this time, and every worker that reads the parent has to resolve it.
FOR SHARE or FOR KEY SHARE from any reader that locks jobs without updating them.

Why that is expensive

Once a row's xmax is a multixact, every backend that touches the row and needs to know whether it is locked or visible has to look up the multixact's members. That lookup goes to the SLRU. Before Postgres 17 the cache was a few dozen fixed pages behind one lightweight lock. With thousands of pollers scanning the same hot table in a tight loop, that lock becomes the single most contended thing in the database. That is the wait event you are looking at.

Then vacuum. Multixact ids have their own wraparound counter, so heavy minting forces more frequent freezing. Meanwhile workers that hold transactions open while processing pin the oldest visible transaction, so vacuum cannot remove the dead versions of finished jobs. The jobs table bloats. The partial index points at dead tuples that every poll has to step over. Polling gets slower, so workers poll harder. The loop only goes one way.

Getting out of it

In the order to try them:

Commit the claim and process outside the transaction. One short write to claim, one to finish. No long-lived transactions, so vacuum runs, and no held locks for anything to pile onto. Add the reaper.
Remove subtransactions from the claim path. Find the savepoint your framework adds and turn it off for the worker. This alone removes the most common source of multixacts on job rows.
Drop the foreign key from jobs to hot parent rows, or stop touching those parents in the same transaction. A constraint is cheap to check once and expensive when a thousand inserts a second all pin the same parent.
Poll less, claim more. Claim a batch (LIMIT 50) instead of one row. Back off exponentially on an empty poll. Use LISTEN / NOTIFY so idle workers sleep until an insert wakes them.
Keep the table small. Delete finished jobs or move them to an archive table, so the partial index and vacuum have little to do. Give the table its own autovacuum settings (autovacuum_vacuum_scale_factor = 0 with a fixed threshold) so it is vacuumed on a schedule the rest of the database does not dictate.
Upgrade. Postgres 17 made the SLRU cache sizes configurable (multixact_member_buffers, multixact_offset_buffers) and split their locks into banks, which moves the single-lock cliff a long way out.

And the honest last step: if you are running tens of thousands of jobs a second across thousands of workers, that is a message broker's workload. The jobs table was still the right choice for the years it took to get there.