Postgres as a queue: SKIP LOCKED instead of Redis or Kafka

You already have a webhook (or a job, or an email, or an event) committed to Postgres. Now you need a worker to pick it up, do the work, and mark it done — exactly once if you're lucky, at-least-once if you're honest. The reflexive answer is to reach for Redis or Kafka: push the row's id onto a queue and let consumers pop it.

The moment you do that, you've created a distributed-systems problem you didn't have a second ago. The row lives in Postgres; the work item lives in Redis. If the API writes the row and then crashes before the push, the job is silently lost. If it pushes and then the transaction rolls back, you have a job pointing at a row that doesn't exist. You now own a two-phase consistency problem between two systems that don't share a transaction, and you'll spend the next year writing reconciliation jobs to paper over the gap.

For a large class of workloads, you don't need any of that. Postgres can be the queue. The mechanism is one clause — FOR UPDATE SKIP LOCKED — and it has been production-grade since Postgres 9.5 (2016). This post walks through the claim query, lease-based crash recovery, the LISTEN/NOTIFY path for low latency, and the throughput ceiling you're trading into. The examples are the actual queries from Dropless, a self-hostable webhook gateway whose only runtime dependency is Postgres — it is both the source of truth and the queue, no Redis, no Kafka.

Why a Postgres queue beats a separate broker for transactional work

The single most valuable property is that the queue and the data share a transaction. In Dropless an inbound event and every fan-out delivery row it produces are written in one Postgres transaction, and the HTTP API returns 2xx only after that commit lands. We call it commit-then-2xx. A crash either commits everything or nothing; there is no window where we acknowledged an event to the caller but lost the work.

You cannot get this with Redis-as-queue without inventing an outbox pattern — and the outbox pattern is a Postgres queue, just with an extra hop bolted on. If your work items already originate as rows in Postgres, skip the broker and dequeue from the table directly. One source of truth, one fsync, zero reconciliation.

The claim query: FOR UPDATE SKIP LOCKED in Postgres

The naive way to hand rows to workers is a transaction that does SELECT ... WHERE status = 'pending' FOR UPDATE LIMIT 1. With more than one worker this serializes instantly: worker B blocks on the row worker A locked, waiting for A's transaction to finish before it can even look at the next row. Your concurrency collapses to one.

SKIP LOCKED changes the lock-conflict behavior: instead of waiting on a row another transaction holds, the scan steps over it and takes the next free one. N workers running the identical query each walk away with a disjoint batch — no coordination, no central dispatcher. Here is the actual claim query Dropless workers run (sqlx, runtime-checked):

UPDATE deliveries AS d
SET status = 'in_progress',
    locked_by = $1,
    locked_until = now() + ($2::int * interval '1 second'),
    updated_at = now()
WHERE d.id IN (
    SELECT id FROM deliveries
    WHERE (status IN ('pending', 'failed') AND next_attempt_at <= now())
       OR (status = 'in_progress' AND locked_until < now())
    ORDER BY next_attempt_at
    FOR UPDATE SKIP LOCKED
    LIMIT $3
)
RETURNING d.*;

A few things worth pointing out:

• The inner SELECT ... FOR UPDATE SKIP LOCKED does the claiming; the outer UPDATE flips state in the same statement. The SKIP LOCKED must sit on the subquery — you cannot put it directly on an UPDATE. The whole thing is one round trip, atomic, and returns the claimed rows via RETURNING so the worker has the payload immediately.
• The predicate matches two distinct sets: rows that are genuinely due (pending/failed with next_attempt_at <= now()), and rows stuck in_progress whose lease has lapsed. That second clause is the entire crash-recovery story, covered below.
• ORDER BY next_attempt_at gives roughly-FIFO, due-time ordering. SKIP LOCKED makes no global ordering guarantee under contention — a locked row is skipped — so treat this as best-effort priority, not a strict sequence.

Index the dequeue scan or it degrades

A queue table is mostly hot rows you're trying to drain plus cold rows you've finished. A full-table scan to find due work gets slower as history accumulates. Partial indexes keep the scan proportional to the work outstanding, not the table size. These are the indexes Dropless ships:

CREATE INDEX deliveries_due_idx
    ON deliveries (next_attempt_at)
    WHERE status IN ('pending', 'failed');

CREATE INDEX deliveries_locked_idx
    ON deliveries (locked_until)
    WHERE status = 'in_progress';

Once a row reaches a terminal state — succeeded, or dead once it lands in the dead-letter queue — it drops out of both indexes entirely, so they stay small. The first index serves the due-work scan; the second serves the reclaim scan.

Lease and visibility timeout: Postgres queue crash recovery without a coordinator

A broker like SQS gives you a visibility timeout: a claimed message is hidden for N seconds, and if the consumer doesn't ack in time it reappears. You can build the exact same thing in Postgres with two columns — locked_by and locked_until — which is what the claim query above writes.

When a worker claims a row it stamps locked_until = now() + lease. While that timestamp is in the future, the row is invisible to the due-work predicate. The worker does its HTTP POST, then writes the outcome guarded by its own lease:

UPDATE deliveries
SET status = 'succeeded', locked_by = NULL, locked_until = NULL, ...
WHERE id = $1 AND locked_by = $3 AND locked_until > now();

Now consider a worker that gets kill -9'd mid-delivery — no graceful shutdown, no chance to release anything. Its row sits in_progress with a locked_until that will simply pass. The instant the lease lapses, the second clause of the claim predicate (status = 'in_progress' AND locked_until < now()) makes the row eligible again, and any surviving worker re-claims it. No leader election, no heartbeat table, no external coordinator. The dead worker's lease expiring is the recovery signal.

This is also why delivery is at-least-once, not exactly-once. A worker can complete the POST and die before writing succeeded; the lease lapses; another worker delivers the same payload again. We accept that and design for it: every request carries an immutable Idempotency-Key so receivers can dedupe. Exactly-once across an HTTP boundary is a fairy tale; at-least-once plus idempotency is the real contract. Dropless also signs every request with Svix-compatible HMAC-SHA256 headers (webhook-id, webhook-timestamp, webhook-signature over {id}.{timestamp}.{body}), so the receiver can both verify authenticity and key its dedupe on the stable webhook-id.

The property is enforced, not asserted. Dropless has a CI test that sends a batch of messages, kill -9s the dispatcher mid-delivery, restarts it, waits for the leases to lapse, and asserts 0 dropped (duplicates allowed — subscribers dedupe on webhook-id). The lease-guarded write closes the obvious race: if your lease already expired and another worker re-claimed the row, your UPDATE matches zero rows, so you abandon the result instead of clobbering a delivery you no longer own.

LISTEN/NOTIFY for low latency without busy-polling

Polling alone forces a latency-vs-load tradeoff: poll every 50ms for snappy delivery and you hammer the database with empty scans; poll every 5s and an idle queue adds seconds of lag to the first message. LISTEN/NOTIFY dissolves the tradeoff. Idle workers LISTEN on a channel; the producer fires NOTIFY after committing an enqueue; a waiting worker wakes immediately and claims.

The trap is calling pg_notify per enqueue. Notifications serialize on an internal queue lock, so under a burst NOTIFY contention throttles your ingest throughput — the opposite of what you wanted. Dropless coalesces: at most one NOTIFY per time window, fired off the producer's response path. One wake is enough; the woken worker drains continuously via SKIP LOCKED and keeps pulling until the table is empty.

Crucially, NOTIFY is an optimization, never the correctness mechanism. It is fire-and-forget and lossy — a notification delivered while no one is listening just evaporates. The poll loop is the backstop, and it does double duty: it catches the wakeups NOTIFY dropped and it's the only thing that can surface a timed retry, since a row scheduled for now() + 30s (exponential backoff plus jitter, after a failed attempt) has no enqueue event to notify on. NOTIFY for latency on the happy path; polling for correctness on everything else.

Postgres vs Redis queue: the honest tradeoffs

This is not free, and pretending otherwise is how people end up surprised at 3am. The real cost is a throughput ceiling. Every claim and every completion is a row update, which means WAL writes and, on commit, an fsync. A single Postgres instance comfortably does thousands of jobs per second; with batched claims and coalesced completions, tens of thousands. It will not do the millions per second a partitioned Kafka cluster delivers, because you're paying for durability and transactional consistency on every item.

A rough decision guide:

• Use a Postgres queue when the work items already live in Postgres, you need transactional enqueue (commit-then-act), throughput is in the thousands/sec, and operational simplicity — one system to back up, monitor, and reason about — is worth more than raw ceiling. This is most webhook, billing, notification, and back-office workloads.
• Reach for Kafka when you genuinely need a high-throughput ordered log, replay across consumer groups, or fan-out to many independent subscribers at firehose scale.
• Reach for Redis/SQS when the jobs are ephemeral and not already in your database, and you can tolerate the dual-write consistency gap (or you build a proper outbox — at which point you're running a Postgres queue anyway).

One more operational note: completed rows accumulate. Set autovacuum aggressively on the queue table, or archive and delete terminal rows, so bloat doesn't slowly degrade the index scans. A queue table is high-churn by nature; treat vacuum as part of the design, not an afterthought.

The full Postgres queue pattern, end to end

A durable, crash-safe job queue on Postgres needs surprisingly little: a status column, a (locked_by, locked_until) lease, two partial indexes, a FOR UPDATE SKIP LOCKED claim that also reclaims expired leases, a poll loop for correctness, and an optional coalesced LISTEN/NOTIFY for latency. Layer on exponential backoff with jitter, a per-endpoint circuit breaker, a dead-letter queue, and one-click replay, and you have a complete delivery engine. No Redis, no Kafka, no second source of truth, no reconciliation job. The cost is a throughput ceiling you can measure in advance and will likely never hit.

Dropless implements exactly this pattern — Postgres as both source of truth and queue, with the lease-based recovery and coalesced NOTIFY described here — as its dispatch engine, and it builds with sqlx's runtime-checked API so cargo build needs no database at all. It's open source under AGPL-3.0 if you want to read the real code or self-host it: github.com/r4nd0mENRYU/dropless (Docker image at ghcr.io/r4nd0menryu/dropless). There's also a hosted option at dropless.dev if you'd rather not run it yourself.