The table and the index the dequeue needs
A Postgres job queue is a table with a status, a priority and a time before which the job must not run. The index matters as much as the columns, because the dequeue runs constantly and must not sort the whole backlog every time.
CREATE TABLE job (
id bigserial PRIMARY KEY,
priority smallint NOT NULL DEFAULT 0,
run_at timestamptz NOT NULL DEFAULT now(),
status text NOT NULL DEFAULT 'ready',
attempts int NOT NULL DEFAULT 0,
locked_until timestamptz,
payload jsonb NOT NULL
);
CREATE INDEX job_ready ON job (priority DESC, run_at, id) WHERE status = 'ready';
The partial index keeps only ready rows, so it stays small however many finished rows accumulate. Its column order matches the ORDER BY below, which lets the planner walk the index from the top and stop at the first row it can lock.
The dequeue statement
Three things happen in one statement. The subquery finds the best candidate and takes a row lock on it. The outer UPDATE marks that row as claimed while the lock is still held. RETURNING hands the payload back without a second round trip. If the subquery finds nothing, it yields NULL, the UPDATE touches zero rows, and the worker knows the queue is empty.
SKIP LOCKED has been in PostgreSQL since 9.5. The documentation is candid that it gives an inconsistent view of the data. That is wrong for a report and exactly right for a queue, where the only question is "which row can I have?"
Ten workers, three versions of the statement
Run the naive version first: SELECT id ... LIMIT 1, then UPDATE job SET status = 'running' WHERE id = ?. Under READ COMMITTED all ten workers can read the same head row in the gap between those statements. Without a status check in the UPDATE, all ten process it. With one, nine block on the first worker's row lock, then update zero rows once it commits, and go round again. You have correctness, but nine-tenths of the effort is wasted on every claim.
Add FOR UPDATE without SKIP LOCKED and the double claim disappears, but the queue becomes single-file. Worker two's locking read reaches the head row, finds it locked, and waits for worker one's transaction to end. Workers three to ten wait behind it. When worker one commits, the waiters recheck that row under READ COMMITTED, find it no longer ready, and their LIMIT 1 subquery can come back empty rather than handing them the next job, so they claim nothing and must loop. Throughput is one claim per commit no matter how many workers you add.
Add SKIP LOCKED and the picture changes. Worker two reaches the head row, sees the lock, and moves on to the second row in index order. Worker three skips two rows and takes the third. Nobody waits, and the ten claims proceed in parallel.
What another session can see
Visibility is the part worth spelling out, because it explains why the lock is necessary at all. Until worker one commits, its status = 'running' is invisible to everyone else under MVCC. A plain SELECT from an eleventh session still sees the row as ready. The only thing other sessions can observe immediately is the row lock, and only locking reads look for it. So the queue's safety lives entirely in the lock, not in the status column, until the claim commits.
The same rule hides new work. A job inserted by a transaction that has not committed is invisible to every worker, so enqueue and its surrounding business write should commit together. That is a feature: a worker never picks up a job whose order row was rolled back.
The lock lasts only as long as the transaction
This is where a correct-looking answer quietly breaks. A candidate writes SELECT ... FOR UPDATE SKIP LOCKED, gets an id back, runs the job, then updates the status. Under a driver in autocommit mode, that SELECT was its own transaction. The lock was taken and released before the result reached the application, so it protected nothing, and two workers can run the job.
The claim must therefore do one of two things, and you should say which you chose.
Hold the transaction for the whole job. The open row lock is the claim. If the worker crashes, its connection drops, the transaction aborts, and the row is ready again with no clean-up code. The cost is one database connection per job in flight. Long transactions also hold back vacuum's view of which dead rows are safe to remove, across the whole database, not just this table. That is fine for ten workers running sub-second jobs and painful for long ones.
Commit the claim as a lease. The statement above does this: it writes running and locked_until, then commits, and the connection goes back to the pool. A crash now leaves the row stuck as running, so a reaper must return expired leases to ready. Delivery becomes at least once, because a slow worker can outlive its lease while another claims the job. Make jobs idempotent, and make completion conditional on still owning the claim, for example UPDATE job SET status = 'done' WHERE id = $1 AND attempts = $2.
What SKIP LOCKED gives up
Priority becomes approximately ordered rather than strictly ordered. If a priority-nine job is locked by a worker, others take priority-five jobs meanwhile. That is acceptable, since the priority-nine job is already being worked. Strict priority also starves low-priority work under steady load, which is a scheduling decision to raise, not a locking one.
The table itself is high churn: every claim and completion writes a new row version. Delete or archive finished rows, and make sure autovacuum keeps up with this table, or the dequeue slows as it steps over dead entries. For contention that turns into lock cycles elsewhere in the schema, the deadlock diagnosis page covers the general case. For deciding what to drop once a job queue backs up, see the backlog triage question.
A Postgres queue is safe only while the row lock is held, and that lock ends with the transaction. Claim, mark and return in one UPDATE ... FOR UPDATE SKIP LOCKED, then decide out loud whether the open transaction or a committed lease is your claim.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).