Postgres as a Message Queue with SKIP LOCKED

A Postgres table with FOR UPDATE SKIP LOCKED is a production-grade job queue, and it has been since the clause landed in Postgres 9.5 (January 2016). The PostgreSQL docs name the use case themselves: skipping locked rows exists "to avoid lock contention with multiple consumers accessing a queue-like table". One measured implementation put the overhead at about 1.5 ms of extra database time per task — in exchange, you get something Redis, RabbitMQ, and SQS structurally cannot offer: the job is enqueued in the same transaction as the business change that caused it, so a rolled-back order never sends its confirmation email. Rails 8 leaned into exactly this trade-off by shipping Solid Queue, a database-backed backend that "replaces the need for not just Redis, but also a separate job-running framework, like Resque, Delayed Job, or Sidekiq, for most people".

Why use Postgres as a job queue?

Because the hardest bug in queue-based systems is not throughput — it is the dual write. With an external broker, "save the order, then enqueue the email job" is two systems that can disagree: the database commits and the enqueue fails, or the enqueue succeeds and the transaction rolls back. The textbook fix is the transactional-outbox pattern, which means… writing the job to a database table first. A Postgres queue simply skips the middleman. River, the Go job library, is built on this observation: keeping jobs in the application database avoids "whole classes of distributed systems problems" — jobs enqueued inside a transaction become visible to workers only if that transaction commits.

It is also one less system to run, which is the core of the Just Use Postgres argument: every datastore you add brings its own backups, monitoring, upgrade cadence, and failure modes. A queue that lives in the database you already operate costs none of that.

How does FOR UPDATE SKIP LOCKED actually work?

FOR UPDATE locks the selected rows; SKIP LOCKED tells Postgres to silently skip any row another transaction has already locked instead of waiting. Run the same query from fifty workers and each gets a different job, with no coordination code:

CREATE TABLE jobs (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind        text NOT NULL,
  payload     jsonb NOT NULL DEFAULT '{}',
  status      text NOT NULL DEFAULT 'pending',   -- pending | running | done | failed
  attempts    int  NOT NULL DEFAULT 0,
  run_at      timestamptz NOT NULL DEFAULT now(),
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX jobs_claim_idx ON jobs (run_at) WHERE status = 'pending';

-- each worker runs this in a transaction
UPDATE jobs SET status = 'running', attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending' AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, kind, payload;

The partial index keeps the claim query fast no matter how many completed jobs accumulate. Retries are a run_at = now() + interval '1 minute' * attempts update on failure; enqueueing is a plain INSERT, which is why it composes with whatever transaction your application already has open. Delete or archive finished rows on a schedule — the PostgreSQL cheat sheet has the partitioning and bulk-delete patterns.

How do workers pick up jobs instantly without hammering the database?

Poll on a coarse interval as the reliable baseline, and layer LISTEN/NOTIFY on top for latency. Workers LISTEN new_job; enqueuers fire NOTIFY — and because notifications are delivered only when the sending transaction commits, a worker can never be woken for a job that later rolled back:

-- in the enqueueing transaction
INSERT INTO jobs (kind, payload) VALUES ('send_email', '{"user_id": 42}');
SELECT pg_notify('new_job', 'send_email');
COMMIT;  -- notification is delivered here, not before

-- worker connection
LISTEN new_job;  -- then claim with the SKIP LOCKED query on wake-up

Two limits to respect: payloads are capped at 8000 bytes (send a job id, not the job), and the notification queue is a finite 8 GB — a worker that goes to lunch inside an open transaction stops the queue from draining. Treat NOTIFY as a doorbell, not a delivery mechanism; the jobs table remains the source of truth.

Should you use pgmq or a worker library instead of raw SQL?

Usually, yes — the raw pattern is worth understanding, but retries, backoff, visibility timeouts, and metrics are solved problems. pgmq (5,000+ GitHub stars) is a Postgres extension exposing an SQS-shaped API in SQL: pgmq.send(), pgmq.read() with a visibility timeout that makes crashed-worker messages reappear automatically, and pgmq.archive() for replayable history. pg_cron adds scheduled jobs with ordinary cron syntax — SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM') — validate expressions with the cron parser.

What are the gotchas of a Postgres queue?

• Don't hold the claim transaction open while the job runs. A ten-minute PDF render inside the locking transaction blocks vacuum and pins the connection. Claim with a status update, commit, do the work, then mark done — and add a reaper that re-queues running jobs whose worker stopped heartbeating.
• Dead-tuple churn. Every state transition is an update, and at high throughput the jobs table becomes autovacuum's hardest customer. Keep the table small (archive finished jobs), and monitor n_dead_tup. This is the honest scaling ceiling of the pattern.
• SKIP LOCKED reads are deliberately inconsistent. The docs are explicit that skipping locked rows "provides an inconsistent view of the data" — fine for claiming work, wrong for anything else. Don't reuse the clause in ordinary queries.
• Strict FIFO is an illusion under concurrency. ORDER BY run_at plus SKIP LOCKED gives approximate ordering; if two workers claim simultaneously, completion order is theirs to decide. If exact ordering matters, process that queue with a single worker.

When is a real message broker the right call?

A jobs table is a work queue, not an event streaming platform. Reach for Kafka (or Redpanda, or a managed equivalent) when many independent services consume the same ordered stream and need to replay it from an offset — that is a different data structure, not a bigger queue. Reach for SQS when producers and consumers live in different trust domains, or when volume is so bursty that you want someone else's infinite buffer. And if your jobs are pure cache invalidations with no transactional tie to your data, a Redis-based queue loses little. The threshold to watch in Postgres is sustained thousands of jobs per second with autovacuum falling behind — below that, the simpler system wins.

References

• PostgreSQL docs — SELECT, The Locking Clause — SKIP LOCKED semantics and the queue-table use case, verbatim
• PostgreSQL docs — NOTIFY — commit-time delivery, 8000-byte payload limit, queue sizing
• leontrolski — Postgres as queue — the measured ~1.5 ms per-task overhead and a minimal implementation
• pgmq — SQS-like queue extension with visibility timeouts and archiving
• pg_cron — cron-syntax job scheduler running inside Postgres
• River — Go job queue built on transactional enqueue
• Graphile Worker — Node.js worker with LISTEN/NOTIFY wake-ups
• Rails 8.0 release — Solid Queue — the framework-level bet on database-backed jobs