Building a job queue in PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED

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 running status quickly and add a timeout or heartbeat that returns stale jobs to pending.
• 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_at where status = '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.