How do you build a job queue on PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED?

Summary

Workers select queued rows with FOR UPDATE SKIP LOCKED, which locks the rows they take and skips rows other workers have locked, so many workers can claim jobs concurrently without blocking or double-processing.

Longer Answer

WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next WHERE jobs.id = next.id
RETURNING jobs.*;

Add a partial index on (run_at) WHERE status = 'queued', handle crashed workers by timing out stale running jobs, and keep the table small (archive finished jobs) because high churn means much vacuuming. Libraries such as Oban, river, graphile-worker and pg-boss implement this pattern. Other lock strengths: FOR NO KEY UPDATE (does not block foreign-key checks), FOR SHARE, FOR KEY SHARE, and NOWAIT.

Key Points

• SKIP LOCKED lets workers claim different rows.
• Claim and mark in one statement.
• Partial index on queued jobs.
• Recover stale running jobs; archive done ones.