Postgres SKIP LOCKED work queue

Why Naive SQL Work Queues Collapse

For small to medium workloads, standing up dedicated Kafka or RabbitMQ clusters introduces operational complexity. Engineers often attempt to use PostgreSQL as a work queue. Naive implementations collapse under load:

• SELECT * FROM jobs WHERE status = 'pending' LIMIT 1 FOR UPDATE; locks the first row. Every other concurrent worker blocks waiting on the row lock, turning parallel workers into a completely serialized bottleneck.
• Polling without row locks causes double-processing race conditions.

The Breakthrough: SKIP LOCKED

PostgreSQL 9.5 introduced FOR UPDATE SKIP LOCKED. When a transaction executes this query, the database engine checks if candidate rows are currently locked by another active transaction. Instead of blocking and waiting, it instantly skips past locked rows and locks the next available unlocked row.

SKIP LOCKED Worker Execution Pipeline

Production-Grade Implementation SQL

-- Atomically fetch and lock 1 pending job without blocking other workers
WITH next_job AS (
  SELECT id
  FROM job_queue
  WHERE status = 'pending' AND scheduled_at <= NOW()
  ORDER BY priority DESC, id ASC
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE job_queue
SET status = 'processing',
    locked_at = NOW(),
    worker_id = 'worker_pod_42'
WHERE id = (SELECT id FROM next_job)
RETURNING *;
      

Operational Caution: Table Bloat: Frequent inserts and deletes create dead tuples. Queue tables require aggressive PostgreSQL autovacuum settings (e.g. autovacuum_vacuum_scale_factor = 0.05) to prevent sequential scan performance collapse.

Production-grade claim query

CREATE TABLE jobs (
  id          bigserial PRIMARY KEY,
  queue       text        NOT NULL,
  payload     jsonb       NOT NULL,
  status      text        NOT NULL DEFAULT 'pending',
  run_at      timestamptz NOT NULL DEFAULT now(),
  attempts    int         NOT NULL DEFAULT 0,
  locked_until timestamptz
);
CREATE INDEX jobs_ready ON jobs (queue, run_at) WHERE status = 'pending';

-- claim up to 10 jobs, lease them for 5 minutes, commit immediately
WITH next AS (
  SELECT id FROM jobs
  WHERE queue = 'email' AND status = 'pending' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', attempts = attempts + 1,
    locked_until = now() + interval '5 minutes'
FROM next WHERE j.id = next.id
RETURNING j.id, j.payload;

A reaper periodically runs UPDATE jobs SET status='pending' WHERE status='running' AND locked_until < now(), so crashed workers' jobs are retried. On success, delete the row or mark it done. On failure, set run_at = now() + backoff.

Worked example: throughput budget

Each claim-and-complete cycle costs about 3 statements (claim, process, delete), roughly 1 to 2 ms of database time with batching of 10. A modest primary sustaining 5,000 simple writes per second supports on the order of 1,500 to 2,500 jobs per second before queue traffic competes with application traffic. Each processed job leaves at least one dead tuple, so at 2,000 jobs per second that is 170 million dead tuples per day for autovacuum to clean.

Database queue versus broker

Who uses it

• Oban (Elixir), Solid Queue (Rails), River (Go), Graphile Worker (Node) and pg-boss are all built on SKIP LOCKED.
• Solid Queue became the Rails 8 default, which shows how far "just use Postgres" goes for most apps.

Failure modes

• Long-running transactions elsewhere (analytics queries, idle-in-transaction sessions) stop vacuum from removing dead job tuples, and the queue table bloats. Set idle_in_transaction_session_timeout.
• ORDER BY with SKIP LOCKED is not strict FIFO: locked rows are skipped, so ordering is best effort.