A job queue on PostgreSQL with SKIP LOCKED

Why a queue in your database

A broker such as Kafka or SQS is the right tool for many systems. But if you already run PostgreSQL and your volume is modest (hundreds to a few thousand jobs per second), a jobs table can be simpler and safer:

• You can enqueue in the same transaction as the business change. The job exists if and only if the change committed, so there is no gap between "saved the order" and "published the message".
• You can inspect, retry and delete jobs with plain SQL.
• One less system to run.

The claim problem

Many workers poll the same table. If each does SELECT ... LIMIT 1 FOR UPDATE, they all try to lock the same top row and wait for each other. FOR UPDATE SKIP LOCKED solves this: rows already locked by another worker are skipped, so each worker gets different rows.

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

100%

Figure SDA-SQR-03-1 · Workers claiming jobs without blocking each other · © forcsguy.com

Making it reliable

• Leases. The claim sets locked_until. If a worker dies, a reclaim query (or the claim itself, by also accepting running rows whose lease has expired) makes the job available again. Jobs must therefore be idempotent: they can run twice.
• Retries with backoff. On failure set run_at = now() + backoff(attempts), for example doubling each time with jitter.
• Dead letters. After a maximum number of attempts, mark the job dead and alert, so one poison job cannot loop forever.
• Priorities and scheduling. Order by priority, run_at; scheduled jobs are just a future run_at.
• Index. A partial index on (run_at) WHERE status = 'queued' keeps the claim query fast.

100%

Figure SDA-SQR-03-2 · Job life cycle · © forcsguy.com

Operating it

• Table bloat. Frequently updated rows create dead tuples. Delete or archive finished jobs, tune autovacuum, and keep the hot table small (a separate history table).
• Polling vs notify. Poll every few hundred milliseconds, or use LISTEN / NOTIFY to wake workers sooner.
• Visibility. Track queue depth, age of the oldest job, and failure counts. Alert on age, not just depth.
• Ordering. SKIP LOCKED does not give strict ordering across workers; if ordering per entity matters, route by key or use a broker with partitions.

When to move on

Switch to a dedicated broker when throughput outgrows one database, many independent consumers need the same events (fan-out), you need long retention and replay, or the queue load hurts your main database. You can keep the outbox idea: write events in the same transaction, and a relay publishes them to the broker.

Real-world reading