SELECT FOR UPDATE SKIP LOCKED is a queue, until it isn't

"Just use Postgres" is good advice for job queues far past the point where people assume it stops working. It is not unlimited advice. The useful thing is knowing which limit you hit first.

The pattern#

UPDATE jobs
SET state = 'running', locked_at = now(), locked_by = $1
WHERE id = (
  SELECT id FROM jobs
  WHERE state = 'pending' AND run_after <= now()
  ORDER BY priority DESC, run_after
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

SKIP LOCKED is what makes this work. Without it, every worker queues behind the same row and throughput collapses to one job at a time. With it, a worker that finds a row locked moves straight to the next candidate, so N workers pull N distinct jobs.

This gives you exactly-once delivery under transactional semantics, dead simple operations, and the ability to enqueue a job in the same transaction as the business write that caused it. That last property is worth a great deal — it is the whole outbox pattern for free, and it is the thing a separate broker cannot give you.

What breaks first#

Not throughput. Dead tuples.

Every state transition on a job row is an UPDATE, and every UPDATE in Postgres writes a new tuple version and marks the old one dead. A job that goes pending → running → succeeded leaves two dead tuples behind. At 500 jobs per second that is 1,000 dead tuples per second, roughly 86 million a day, in one table.

Autovacuum has to keep up with that, and by default it will not try hard enough. The trigger is proportional:

threshold = autovacuum_vacuum_threshold        (default 50)
          + autovacuum_vacuum_scale_factor     (default 0.2)
          * reltuples

On a table holding a million rows that means autovacuum waits for 200,000 dead tuples before starting. Meanwhile the index on (state, run_after) is accumulating pointers to dead tuples, and every dequeue scans more of them.

Tune it per table, aggressively:

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 1% instead of 20%
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_vacuum_cost_delay   = 0,      -- do not throttle on this table
  fillfactor                     = 70      -- leave room for HOT updates
);

fillfactor is the underrated one. Leaving 30% free space in each page lets Postgres do heap-only tuple updates — the new version goes in the same page and no index entry is written at all. For a table whose whole life is updates to non-indexed columns, that removes most of the index churn.

The second thing that breaks#

Long-running transactions, from anywhere in the database.

Vacuum can only remove tuples that are invisible to every open snapshot. One analytics query sitting open for forty minutes pins the horizon, and no vacuum anywhere can clean up anything newer — including on your jobs table, even though the query never touched it.

SELECT pid, age(backend_xid), age(backend_xmin), state,
       now() - xact_start AS duration, left(query, 60)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

If your queue mysteriously degrades every morning, look for the report that runs at 9am.

Where the actual ceiling is#

Rough numbers, single unpartitioned table on decent hardware:

Past a few thousand jobs a second the operational work of keeping the table healthy stops being cheaper than running NATS or Redis Streams, and the transactional-enqueue benefit no longer pays for it.

Getting the most out of it before then#

Partition by state. Completed jobs are the bulk of the table and are never queried by the dequeue path. Partitioning on state means dequeue only ever touches a small hot partition, and archival becomes DETACH PARTITION instead of a DELETE that generates yet more dead tuples.

Delete, do not accumulate. A completed_jobs table that grows forever will eventually make every autovacuum pass slower. Move rows out on a schedule.

Batch the dequeue. Pulling ten jobs per round trip instead of one cuts both query overhead and lock contention by an order of magnitude, at the cost of coarser redistribution when a worker dies.

Use a partial index. The dequeue only cares about pending rows:

CREATE INDEX CONCURRENTLY jobs_pending_idx
  ON jobs (priority DESC, run_after)
  WHERE state = 'pending';

On a table where 99% of rows are completed, this index is 1% of the size and stays in memory.

Done properly this carries a lot of systems a long way. The mistake is not choosing Postgres for the queue — it is choosing it and then running it on defaults until the morning it stops working.