FOR UPDATE SKIP LOCKED: A Postgres Queue for Webhook Delivery

A webhook delivery queue can live in an ordinary PostgreSQL table. Store one row per pending delivery, and let workers claim rows with SELECT ... FOR UPDATE SKIP LOCKED: each worker locks the rows it takes, and any row already locked by another worker is skipped rather than waited on. Many workers claim disjoint batches concurrently, without a broker, and the event and its deliveries are written in the same transaction.

Most guides to Postgres as a job queue assume the job is local work that finishes in milliseconds. A webhook delivery is different: the job is an HTTP POST to a server you do not control, which can hang for thirty seconds, fail for an hour, or succeed without telling you. That changes the lock timeout, how connections are held, and what the claim query has to filter out. Below is the design for a deliveries table, the schema behind it, the HTTP-specific details that show up under production traffic, and the point where a Postgres queue stops being the right answer.

What a webhook delivery queue has to guarantee

Strip a webhook gateway down to its delivery layer and the obligations are short. Accept an inbound event and answer the sender fast. Persist it before acknowledging, so a crash one millisecond later cannot lose it. Hand each pending delivery to exactly one worker at a time, so two workers never POST the same payload to the same destination at once. Retry failures on a schedule, and never drop anything without a record of why.

Two things are missing from that list: exactly-once semantics, and millions of messages per second. Webhook delivery is I/O-bound work measured in hundreds of milliseconds per attempt, mostly spent waiting on somebody else’s HTTP server, so the destination sets the pace long before the queue does. What the queue needs is durability, visibility and safe concurrent claiming. A distributed log is more than that.

Table: how a webhook delivery differs from a typical background job, and what each difference changes in the queue.

Why a table is a legitimate queue

The usual objection is that queues belong in brokers and tables hold data. A table still offers properties a separate broker cannot match.

It is transactional with the rest of your data. When the service accepts an inbound webhook, the event row and the delivery rows it fans out to are written in one transaction. There is no window where the event exists but its deliveries do not, and no two-phase dance between a database and a broker. The event is committed first, answered with 200 OK, then delivered out of band by workers.

It is queryable. “Show me every pending delivery for destination 41” is a WHERE clause, and debugging a backlog at 02:00 with plain SQL beats any broker’s tooling.

And it is backed up with everything else: your existing backup, restore, replication and monitoring cover the queue at no extra cost, which leaves one fewer system to operate. Bringing in Kafka to move ten thousand webhooks a day adds a cluster to size, patch and monitor for a load a single table handles.

The naive version and its race

Before SKIP LOCKED, the obvious implementation looks like this:

BEGIN;
SELECT id, payload
  FROM deliveries
 WHERE status = 'pending'
   AND next_attempt_at <= now()
 ORDER BY next_attempt_at
 LIMIT 1;

UPDATE deliveries SET status = 'in_flight' WHERE id = $1;
COMMIT;

Run one worker and this is fine. Run four and it breaks in a way that will not show up in your tests.

The SELECT takes no lock on the rows it reads. Under Postgres MVCC, four workers can run it at the same instant and all four see the same row as pending, because none has committed yet. All four then UPDATE it. Three block on the row lock, then proceed once the first commits, and their update succeeds because their WHERE clause only matched on id. The same webhook is now delivered four times, at the same moment, to the same destination.

Tightening the update to WHERE id = $1 AND status = 'pending' fixes correctness: three of the four updates affect zero rows, and those workers know they lost. But it trades a correctness bug for a throughput bug. Every worker keeps grabbing the same head-of-queue row and losing, so adding workers stops adding throughput.

Plain FOR UPDATE is correct too, and worse: workers queue up behind each other on the same row. NOWAIT avoids the wait but turns a contended claim into an error the worker must catch. Neither makes a busy worker’s rows invisible to everyone else, and that is the behavior a queue needs.

FOR UPDATE SKIP LOCKED

SKIP LOCKED provides it. Added in PostgreSQL 9.5 and documented in the SELECT reference, it modifies a locking clause so rows that cannot be locked immediately are omitted from the result instead of blocking the query. The docs call this an inconsistent view of the data, and then point out that this property makes the clause suitable for queue-like tables. Four workers running the same claim query concurrently each get a different, non-overlapping batch, and none of them waits on a lock held by another.

The canonical form combines the claim and the state change in one statement:

UPDATE deliveries AS d
   SET status       = 'in_flight',
       attempt      = d.attempt + 1,
       locked_by    = $1,
       locked_until = now() + interval '60 seconds'
 WHERE d.id IN (
         SELECT id
           FROM deliveries
          WHERE status = 'pending'
            AND next_attempt_at <= now()
          ORDER BY next_attempt_at
          LIMIT 20
          FOR UPDATE SKIP LOCKED
       )
RETURNING d.id, d.event_id, d.destination_id, d.attempt;

The subquery does the locking and the skipping. The outer UPDATE flips the rows out of pending, so a later transaction will not see them even after this one commits. And RETURNING hands the worker its batch in the same round trip, so there is no second SELECT and no window between claiming and reading.

Batch size is a tuning knob: a large batch amortizes the query cost, but one worker crash strands the whole batch until its lock expires.

One caveat: SKIP LOCKED weakens ordering. ORDER BY next_attempt_at sorts the candidates, but locked rows are skipped, so delivery order is approximate. For most webhook traffic that is acceptable. It also explains why strict ordering is usually an opt-in: it means partitioning the claim by ordering key and refusing to skip ahead within a partition, which costs throughput. We took that trade apart separately in when webhook ordering is worth the head-of-line blocking.

Schema that holds up

The columns that matter are few:

CREATE TABLE deliveries (
    id              bigserial   PRIMARY KEY,
    event_id        bigint      NOT NULL REFERENCES events (id),
    destination_id  bigint      NOT NULL REFERENCES destinations (id),
    status          text        NOT NULL DEFAULT 'pending',
    attempt         smallint    NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    locked_by       text,
    locked_until    timestamptz,
    completed_at    timestamptz,
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX deliveries_claimable_idx
    ON deliveries (next_attempt_at)
    WHERE status = 'pending';

The partial index is the most important line in the file. Without the WHERE clause it covers every row the table has ever held, including millions of delivered ones, and grows forever. With it, the index contains only rows the claim query can match: the pending set, which in a healthy system stays small and roughly constant even as the table reaches tens of millions of rows.

Per-attempt history lives in a separate delivery_attempts table, one row per HTTP attempt with status code, response body and timing. With history kept out of deliveries, the hot queue table stays narrow and the claim path never touches the append-only history table.

Operational details

The claim query is the easy part. The problems that wake someone up at night come from the table around it.

Dead workers. A worker that claims 20 rows and then loses its VM leaves them in_flight forever. The locked_until column acts as a visibility timeout, and a periodic reaper returns expired claims to the pool.

UPDATE deliveries
   SET status = 'pending', locked_by = NULL, locked_until = NULL
 WHERE status = 'in_flight'
   AND locked_until < now();

Set the timeout comfortably above your HTTP timeout. Too short and you re-deliver work that is still in flight; too long and a crash strands deliveries for minutes. This is a direct consequence of the at-least-once contract this design implements: a reaped delivery may be a duplicate, and your consumer must be idempotent.

One dead destination. A generic job queue has no notion of where a job goes. A webhook queue does, and it matters: if one customer’s endpoint starts timing out, every claim that picks up its deliveries ties a worker up for the full HTTP timeout. With enough of that destination’s rows at the head of the queue, all workers end up waiting on one dead server while healthy destinations back up behind it. The fix belongs in the claim query: keep a pause marker on the destination, set by a circuit breaker after repeated failures, and skip its deliveries while it is set.

UPDATE deliveries AS d
   SET status       = 'in_flight',
       attempt      = d.attempt + 1,
       locked_by    = $1,
       locked_until = now() + interval '60 seconds'
 WHERE d.id IN (
         SELECT dl.id
           FROM deliveries dl
           JOIN destinations dest ON dest.id = dl.destination_id
          WHERE dl.status = 'pending'
            AND dl.next_attempt_at <= now()
            AND (dest.paused_until IS NULL OR dest.paused_until <= now())
          ORDER BY dl.next_attempt_at
          LIMIT 20
          FOR UPDATE OF dl SKIP LOCKED
       )
RETURNING d.id, d.event_id, d.destination_id, d.attempt;

The OF dl is not decoration. Without it, FOR UPDATE locks the matching rows of every table in the FROM clause, destinations included. The first worker to claim a delivery for destination 41 then holds a lock on that destination’s row, and SKIP LOCKED makes every other worker skip all of destination 41’s pending deliveries until the first one commits. Throughput quietly collapses to one worker per destination, and nothing in the logs says why. Locking only the delivery rows keeps destinations shared.

Bloat and autovacuum. Every UPDATE in Postgres writes a new row version and leaves the old one dead. A queue table is updated on every claim, attempt and completion, so it produces dead tuples far faster than a typical table. If autovacuum cannot keep up, the table and its indexes bloat and the claim query slows even though the live row count has not moved. The remedy is per-table tuning: lower autovacuum_vacuum_scale_factor here so vacuum triggers on a small absolute number of dead rows rather than a percentage of a growing table. Read the routine vacuuming chapter before tuning.

Keep the hot set small. Delivered rows have no business in the queue table. Move them out on a schedule, in batches, so the delete never takes a long lock:

WITH batch AS (
    SELECT id
      FROM deliveries
     WHERE status = 'delivered'
       AND completed_at < now() - interval '7 days'
     LIMIT 5000
),
moved AS (
    DELETE FROM deliveries
     WHERE id IN (SELECT id FROM batch)
  RETURNING *
)
INSERT INTO deliveries_archive
SELECT * FROM moved;

Retention works the same way. A 14, 30 or 90 day limit is just this job with a different interval and no archive table.

Connection pool sizing. The instinct is to run many workers for throughput. Each worker holds a connection while it POSTs to a destination, which can take seconds, so a hundred workers means a hundred connections sitting idle-in-transaction. Hold the connection only for the claim and the result write, never for the HTTP call: claim, release, deliver, re-acquire, record. A modest pool then serves a large worker count.

When Postgres stops being enough

A single database has a write ceiling, and a queue table eventually reaches it.

Table: where a Postgres queue and a dedicated broker each make sense for webhook delivery.

The signals that you have outgrown a table are specific. Autovacuum is permanently behind no matter how you tune it. The claim query sits at the top of pg_stat_statements, competing with user-facing queries for the same buffers. One inbound webhook fans out to hundreds of destinations, and so writes hundreds of rows. Or you need the queue to survive independently of the database, in another region.

Until one of those signals appears, a broker mainly adds a second system to keep running, and the rest of the delivery pipeline you’d build around the queue costs far more engineering time than the queue itself.

If you would rather not run it

The queue is the smallest part of a delivery pipeline. Around it you still need retries on an exponential-backoff schedule, a per-destination circuit breaker so one dead endpoint doesn’t tie up the worker pool, per-attempt history, and a dead-letter queue with replay for deliveries that ran out of attempts. Each of those is another table, index or background job with its own failure mode. A breaker needs a half-open state so a recovered endpoint gets traffic again, and a replayed delivery should go back through the same claim query as a first attempt instead of a side channel.

Webhooker runs this pipeline as a managed service: inbound webhooks get 200 OK in single-digit milliseconds and delivery happens off the request path.

None of the pieces is exotic. A table, a partial index and a FOR UPDATE SKIP LOCKED claim query carry most of the load; the rest is careful handling of timeouts, vacuum and connections.

Frequently asked questions