Recipe: SKIP LOCKED job queue pattern

Build a concurrent job queue where workers claim rows without blocking each other. Uses PostgreSQL's SKIP LOCKED to eliminate contention.

Schema

CREATE TABLE job_queue (
  id         BIGSERIAL PRIMARY KEY,
  payload    JSONB NOT NULL,
  status     TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_job_queue_status
  ON job_queue (status, created_at)
  WHERE status = 'pending';

Claim query

WITH next_job AS (
  SELECT id
  FROM job_queue
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE job_queue
SET status = 'processing'
FROM next_job
WHERE job_queue.id = next_job.id
RETURNING job_queue.*;

Worker loop

async function worker() {
  while (true) {
    const job = await db.oneOrNone(CLAIM_QUERY);
    if (!job) {
      await sleep(1000);
      continue;
    }
    await processJob(job);
    await db.none(
      'DELETE FROM job_queue WHERE id = $1',
      job.id
    );
  }
}

Why it works

No row-level contention — locked rows are invisible to other workers.
Linear horizontal scaling — add workers, throughput increases.
No orchestration — Postgres is the queue broker.