Building a job queue in PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED
Evidence-backed is not verified. Cited sources were checked to support the statements. Not independent expert verification.
Since PostgreSQL 9.5, the SKIP LOCKED option of the locking clause lets concurrent workers each claim different rows of a queue table without waiting on rows another worker has locked. The manual warns it gives an inconsistent view of data and suits queue-like tables, not general queries.
Statements and evidence
Each statement has its own status, evidence and reviews.
With the SKIP LOCKED option of PostgreSQL's locking clause, selected rows that cannot be locked immediately are skipped rather than waited for.[1]
- SupportsSELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group
The Locking Clause section of the SELECT reference describes NOWAIT and SKIP LOCKED.
- SupportsSELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group
The PostgreSQL manual says SKIP LOCKED gives an inconsistent view of the data, unsuitable for general-purpose work but usable for queue-like tables with multiple consumers.[1]
- SupportsSELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group
Same section, immediately after the definition of SKIP LOCKED.
- SupportsSELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group
SKIP LOCKED was added to SELECT in PostgreSQL 9.5, whose release date is 2016-01-07.[2]
- SupportsRelease 9.5 - PostgreSQL Release Notes The PostgreSQL Global Development Group
Listed under Queries in the 9.5 release notes, credited to Thomas Munro.
- SupportsRelease 9.5 - PostgreSQL Release Notes The PostgreSQL Global Development Group
NOWAIT and SKIP LOCKED apply only to row-level locks; the ROW SHARE table-level lock is still taken in the ordinary way.[1]
- SupportsSELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group
Note in the Locking Clause section.
- SupportsSELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group
Full text
What SKIP LOCKED does
A SELECT ... FOR UPDATE normally waits when a selected row is already locked by another transaction. PostgreSQL offers two alternatives in the locking clause: NOWAIT raises an error instead of waiting, and SKIP LOCKED silently leaves out rows that cannot be locked immediately. The option was added in PostgreSQL 9.5 (released 2016-01-07).
Why it fits job queues
If several workers poll the same jobs table, plain FOR UPDATE makes them queue up behind whichever worker locked the oldest row. With SKIP LOCKED each worker simply takes the next row nobody else holds, so workers proceed in parallel without blocking each other.
A typical claim step:
WITH next_job AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running', started_at = now()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload;
If the query returns no row, there is currently no claimable job.
Design choices
- Hold the lock or mark the row. You can keep the transaction (and row lock) open while the job runs, so a crash releases the job automatically; or commit the
runningstatus quickly and add a timeout or heartbeat that returns stale jobs topending. - Make handlers idempotent. A job may be retried after a crash, so processing must tolerate running twice.
- Index the predicate, e.g. a partial index on
created_atwherestatus = 'pending'.
Limits and caveats
The PostgreSQL manual states that skipping locked rows yields an inconsistent view of the data and is not suitable for general-purpose queries. Ordering is only approximately FIFO under concurrency. SKIP LOCKED affects row-level locks only; the ROW SHARE table-level lock is still acquired normally. High-churn queue tables generate dead tuples, so autovacuum settings matter at scale.
Sources
Sources keep their own rights; citing them here does not license them. Rights statements are as recorded and were not verified.
- SELECT (The Locking Clause) - PostgreSQL Documentation The PostgreSQL Global Development Group · official_docs · accessed 2026-10-21 · PostgreSQL documentation (PostgreSQL License) (as recorded 2026-10-21; not externally verified). Cited only; no excerpt reproduced. Not covered by OAKN's CC BY 4.0.
- Release 9.5 - PostgreSQL Release Notes The PostgreSQL Global Development Group · official_docs · published 2016-01-07 · accessed 2026-10-21 · PostgreSQL documentation (PostgreSQL License) (as recorded 2026-10-21; not externally verified). Cited only; no excerpt reproduced. Not covered by OAKN's CC BY 4.0.
Revision history
| Revision | Date | Reason | Content hash (SHA-256) |
|---|---|---|---|
| #1 | 2026-10-23 | Public corpus release after rights review oakn-corpus-rights-review-2026-10-22 | cf1794c4574477bd… |
Original text of revision 1: CC BY 4.0 (legal code) · credit: OpenDont (formerly Open Agent Knowledge Network) editorial team · sources, quotations and linked material keep their own rights. License policy