FOR UPDATE SKIP LOCKED: Postgres as a Job Queue

FOR UPDATE SKIP LOCKED turns an ordinary PostgreSQL table into a working job queue. A worker selects the rows it wants to process and locks them; any row already locked by another worker is skipped rather than waited on. Many workers can therefore claim disjoint batches from the same table concurrently, without a broker, without polling collisions, and inside the same transaction as the rest of your data.

This is the queue that runs Webhooker: a deliveries table in PostgreSQL, claimed by workers with SELECT ... FOR UPDATE SKIP LOCKED. Below is how that design works, what its schema looks like, and the operational details that bite once real traffic hits it — including where it stops being the right answer.

The requirement

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 silently drop anything.

Notice what is absent: 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. The queue is not the bottleneck; the destination is. So it needs durability, visibility and safe concurrent claiming, not a distributed log.

Why a table is a legitimate queue

The usual objection is reflexive: queues are for brokers, tables are for data. But a table gets you three things a broker cannot.

It is transactional with the rest of your data. When Webhooker 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. That is how events are persisted before delivery: committed to Postgres 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 for free. That is one fewer system to operate. Most teams who reach for Kafka to move ten thousand webhooks a day end up operating Kafka instead of shipping product.

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, which is what you actually want.

FOR UPDATE SKIP LOCKED

That is exactly what SKIP LOCKED does. 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, then note it is exactly what makes the clause suitable for queue-like tables. Four workers running the same claim query concurrently each get a different, non-overlapping batch: no waiting, no lost races, no coordination.

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;

Three details carry the weight. 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 fine. It is also the honest reason strict ordering is a Team-plan feature at Webhooker rather than a default — 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. Keeping it out of deliveries matters: the hot queue table stays narrow, and the append-only history table is never touched by the claim path.

Operational details

The claim query is the easy part. These are the things that page you.

Dead workers. A worker that claims 20 rows and then loses its VM leaves them in_flight forever. The locked_until column is the fix — 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.

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 and archiving are the same mechanism: Webhooker’s limits (14, 30 or 90 days by plan) are enforced by this class of job, entirely inside the EU.

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

There is a ceiling, and pretending otherwise would be dishonest.

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 then, a broker mostly buys you a second on-call rotation, and what else you’d have to build around the queue costs far more than the queue itself.

How Webhooker uses it

An inbound webhook arrives at https://webhooker.eu/in/{token}. We verify its signature (HMAC-SHA256 or SHA1, per source), write the event and its delivery rows in one transaction, and return 200 OK — single-digit milliseconds, p99 target under 10 ms. Delivery never happens on the request path.

Workers claim from deliveries with exactly the pattern above, and each attempt is recorded with its response code and body. Failures reschedule by setting next_attempt_at to an exponential-backoff time, and a per-destination circuit breaker stops a dead endpoint from consuming the pool. When attempts run out, the row moves to the dead-letter queue, which is where exhausted deliveries end up with full history and one-click or bulk replay.

None of this is exotic: a table, an index and a query, run carefully. If you would rather not run it yourself, you can use the queue without operating it — the Free plan covers 10,000 events a month.

Frequently asked questions