Taming "At-Least-Once" Delivery: Idempotent Webhook Ingestion in PostgreSQL

Webhooks are how payment processors, commerce platforms and card issuers keep your system in sync with theirs. Stripe tells you a payment succeeded, Shopify tells you an order was created, Marqeta tells you a card transaction happened.

Every one of those providers works under the same unspoken contract: at-least-once delivery. If a network timeout, a worker crash or a 5xx response leaves the sender unsure whether you received an event, it sends the event again. Sometimes the duplicate arrives milliseconds later, sometimes days later.

+----------------+            +-------------------+            +---------------+
| Webhook sender |            | Application node  |            | PostgreSQL    |
+----------------+            +-------------------+            +---------------+
        |                               |                              |
        |--- Event A (attempt 1) ------>|--- write order record ------>|
        |    (response times out)       |                              |
        |                               |                              |
        |--- Event A (attempt 2) ------>|--- duplicate write attempt! ->|

Naive ingestion turns that into charged-twice customers, inventory that goes negative, and reports that quietly drift away from reality.

This guide shows how to push deduplication down into PostgreSQL, where it can be enforced atomically: an inbox table with INSERT ... ON CONFLICT DO NOTHING, conditional upserts for out-of-order events, advisory locks and FOR UPDATE SKIP LOCKED for contention, a comparison with MERGE, isolation-level behaviour, and a transactional outbox for side effects. PostgreSQL 18 is the current major release. The SQL in this article was run against PostgreSQL 16.15; the few snippets that need PostgreSQL 18 or 19 are labelled and were not executed.

What providers actually promise

Before designing anything, read the delivery contract of the provider you integrate with. The details differ, and some of them change what you build. The table below reflects vendor documentation as of September 2026.

A few details that matter for the rest of this article:

• Stripe occasionally generates two separate Event objects for the same underlying change. In that case the event ID differs, and Stripe's own guidance is to combine the ID of the object in data.object with event.type to spot the duplicate. Stripe also says not to use the event's created timestamp to order events or detect duplicates, because it has one-second resolution and distinct events can share a value.
• Shopify has changed its numbers over time. Current documentation says failed deliveries are retried 8 times over 4 hours, and that a subscription created through the Admin API is deleted after 8 consecutive failures. Older blog posts still quote "19 retries over 48 hours". Its docs also distinguish two headers: X-Shopify-Webhook-Id identifies a delivery and is the one to deduplicate on, while X-Shopify-Event-Id is shared by deliveries to different subscriptions for the same underlying event. Shopify also recommends periodic reconciliation jobs, because delivery is not guaranteed.
• Marqeta batches up to 10 notifications of the same type into a single HTTP message, so "one request equals one event" is the wrong mental model. Deduplicate per notification, not per request.

Three design consequences follow:

1. Your deduplication memory has to outlive the longest window in which the provider can redeliver: 3 days of automatic retries plus up to 30 days of manual replay for Stripe, a little over 12 days for Marqeta.
2. You can never assume events arrive in order.
3. A duplicate can be an HTTP retry, a manual resend, or a second logically identical event, so keep uniqueness at more than one level (more on that in section 2).

1. The check-then-act anti-pattern

A handler written the obvious way looks like this:

# ANTI-PATTERN: do not use in production
def handle_webhook(payload):
    event_id = payload["id"]

    # 1. CHECK
    existing = db.query("SELECT id FROM processed_events WHERE id = %s", event_id)

    if not existing:
        # 2. ACT
        process_business_logic(payload)
        db.execute("INSERT INTO processed_events (id) VALUES (%s)", event_id)

Now suppose the same event is delivered twice at the same moment, for example because of a sender-side retry racing a slow response. Two workers pick them up:

Worker A                                   Worker B
  |--- SELECT ... FROM processed_events       |
  |    (0 rows)                               |--- SELECT ... FROM processed_events
  |                                           |    (0 rows)
  |--- process_business_logic()               |--- process_business_logic()
  |    (creates order #1001)                  |    (creates order #1001 AGAIN)
  |--- INSERT INTO processed_events           |--- INSERT INTO processed_events
  |    (succeeds)                             |    (unique violation, too late)

PostgreSQL's default isolation level is READ COMMITTED, so Worker B cannot see Worker A's uncommitted insert. Both workers read "nothing there", both run the business logic, and only then does the unique constraint fire. Wrapping the check and the act in a single transaction does not change this: at READ COMMITTED each statement sees only data committed before that statement began, so both transactions can read the pre-commit state. This is a classic time-of-check to time-of-use (TOCTOU) race.

Notice that the unique constraint did its job, since only one marker row exists. The bug is one of ordering: the constraint was consulted after the side effects had already happened. The fix is to make the constraint the gate that runs first, and to make the claim and the work commit or roll back together.

2. The event inbox: claim first, work second

The Event Inbox pattern lands every incoming event in a dedicated table before anything else happens.

CREATE TABLE webhook_inbox (
    provider     VARCHAR(64)  NOT NULL,
    event_id     VARCHAR(255) NOT NULL,
    event_type   VARCHAR(128) NOT NULL,
    payload      JSONB        NOT NULL,
    status       VARCHAR(16)  NOT NULL DEFAULT 'pending',  -- pending | done | failed
    received_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    processed_at TIMESTAMPTZ,
    PRIMARY KEY (provider, event_id)
);

CREATE INDEX webhook_inbox_pending_idx
    ON webhook_inbox (received_at) WHERE status = 'pending';
CREATE INDEX webhook_inbox_received_at_idx
    ON webhook_inbox (received_at);   -- used by the retention job in section 9

Claiming an event is a single atomic statement:

INSERT INTO webhook_inbox (provider, event_id, event_type, payload)
VALUES ('shopify', 'a1b2c3', 'orders/create', '{"order_id": 1001, "total": "49.99"}'::jsonb)
ON CONFLICT (provider, event_id) DO NOTHING
RETURNING event_id;

If the key is new, the row is inserted and event_id comes back. If the key already exists, the conflict is swallowed and the statement returns zero rows. That empty result is your "duplicate" signal.

def process_incoming_webhook(provider: str, event_id: str, event_type: str, payload: dict):
    with db.transaction() as tx:
        claimed = tx.execute("""
            INSERT INTO webhook_inbox (provider, event_id, event_type, payload)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (provider, event_id) DO NOTHING
            RETURNING event_id;
        """, (provider, event_id, event_type, json.dumps(payload))).fetchone()

        if claimed is None:
            log.info("duplicate webhook %s:%s, skipping", provider, event_id)
            return {"status": "ignored", "reason": "duplicate"}

        apply_business_logic(tx, payload)   # same transaction as the claim
        return {"status": "processed"}

Because the claim and the business writes share one transaction, a crash before COMMIT rolls back both. The sender sees a failed delivery, retries, and the retry claims the event afresh.

What happens when two workers race

When two transactions try to claim the same key at the same moment, the second one does not fail and does not proceed. It waits for the first transaction to finish. If the first commits, the second statement returns zero rows. If the first rolls back, the second inserts the row and carries on. Try it with two psql sessions: hold the first transaction open with pg_sleep(3) and the second INSERT blocks for about that long, then returns (0 rows).

That is the correct behaviour, but it has a practical consequence: anything slow inside that transaction makes every duplicate wait too. Keep the claim transaction short, or use the receive-then-process variant below.

Receive fast, process later

Providers want a quick response. Shopify times out after 5 seconds, and Stripe tells you to return a 2xx before running any complex logic. A robust shape is to split ingestion into two steps:

def receive(provider, event_id, event_type, payload):
    verify_signature(request)          # before touching the database
    with db.transaction() as tx:
        tx.execute("""
            INSERT INTO webhook_inbox (provider, event_id, event_type, payload)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (provider, event_id) DO NOTHING;
        """, (provider, event_id, event_type, json.dumps(payload)))
    return 200                          # durable and deduplicated; work happens later

A background worker then claims rows from the inbox (see SKIP LOCKED in section 5) and does the real work. The HTTP handler stays fast and the deduplication guarantee is unchanged.

Batched deliveries

For providers that batch, such as Marqeta, insert the whole batch in one statement and process only what actually got inserted:

INSERT INTO webhook_inbox (provider, event_id, event_type, payload)
SELECT * FROM unnest($1::text[], $2::text[], $3::text[], $4::jsonb[])
ON CONFLICT (provider, event_id) DO NOTHING
RETURNING event_id, payload;

Rows that were already in the table, and rows repeated inside the same batch, are silently skipped, and RETURNING gives you only the new ones.

Two layers of idempotency

Event-level deduplication answers "have I seen this delivery?". It does not answer "has this business effect already happened?". Because a provider can emit two distinct events for one change, back the inbox with a business-level constraint as well, for example a unique key on the external payment ID in your payments table. If you ever bypass or purge the inbox, the domain tables still refuse to double-apply.

"Exactly once" is really "effectively once"

Inside one database transaction you get effectively-once behaviour. Anything outside the database (an email, a call to a third-party API, a Kafka publish) cannot be rolled back with it, so those side effects remain at-least-once. Section 8 shows how to hand them off safely.

3. Atomic domain upserts: ON CONFLICT DO UPDATE

Sometimes the webhook is a state snapshot rather than a discrete event, for example a customer profile change, an inventory level or an order status. There, INSERT ... ON CONFLICT DO UPDATE is the tool:

CREATE TABLE orders (
    order_id     VARCHAR(128) PRIMARY KEY,
    customer_id  VARCHAR(128)   NOT NULL,
    status       VARCHAR(64)    NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    updated_at   TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

INSERT INTO orders (order_id, customer_id, status, total_amount, updated_at)
VALUES ('ord_555', 'cust_999', 'processing', 120.00, NOW())
ON CONFLICT (order_id) DO UPDATE
SET status       = EXCLUDED.status,
    total_amount = EXCLUDED.total_amount,
    updated_at   = EXCLUDED.updated_at;

EXCLUDED is a pseudo-table holding the row you proposed for insertion, so you never need to bind the same values twice. The PostgreSQL documentation guarantees that, barring an unrelated error, DO UPDATE yields either an insert or an update, even under high concurrency.

Four gotchas worth knowing:

• Avoid no-op writes. Every DO UPDATE that fires writes a new row version, even if nothing changed. Duplicate webhooks therefore create dead tuples and WAL. Add a guard so identical replays do nothing:

  Code exampleCopy code

  ... DO UPDATE SET status = EXCLUDED.status, total_amount = EXCLUDED.total_amount
  WHERE (orders.status, orders.total_amount)
        IS DISTINCT FROM (EXCLUDED.status, EXCLUDED.total_amount);
  

• A key can appear only once per statement. If a single multi-row INSERT ... DO UPDATE proposes the same key twice, PostgreSQL raises ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate the batch first, keeping the newest row per key.

• Conflicts consume sequence values. A DO NOTHING that skips a row still advanced any serial or identity column, so you will see gaps. Harmless, but do not use such IDs for gapless numbering.

• PostgreSQL 19 adds DO SELECT. Version 19 was in beta at the time of writing (beta 3 shipped on 13 August 2026, with the final release expected in autumn 2026). ON CONFLICT DO SELECT ... RETURNING returns the existing row on conflict without modifying it, which removes the need for the no-op DO UPDATE workaround. Check that 19 has shipped, and that your managed provider supports it, before relying on it.

4. Out-of-order delivery: version guards

Duplicates are only half the story. Suppose a shipped update at 12:00:00 and a delivered update at 12:00:05 arrive in reverse order. A blind SET status = EXCLUDED.status lets the old event overwrite the newer one.

Event timeline:                     Arrival at the database:
  12:00:00  status = shipped          1. delivered arrives -> row = delivered
  12:00:05  status = delivered        2. shipped arrives   -> row = shipped   (state regression)

Attach a monotonic version to the row and let the upsert refuse to go backwards:

CREATE TABLE orders (
    order_id          VARCHAR(128) PRIMARY KEY,
    customer_id       VARCHAR(128)   NOT NULL,
    status            VARCHAR(64)    NOT NULL,
    total_amount      NUMERIC(12, 2) NOT NULL,
    source_updated_at TIMESTAMPTZ    NOT NULL
);

INSERT INTO orders AS o (order_id, customer_id, status, total_amount, source_updated_at)
VALUES ('ord_555', 'cust_999', 'shipped', 120.00, '2026-09-20T12:00:00Z')
ON CONFLICT (order_id) DO UPDATE
SET customer_id       = EXCLUDED.customer_id,
    status            = EXCLUDED.status,
    total_amount      = EXCLUDED.total_amount,
    source_updated_at = EXCLUDED.source_updated_at
WHERE o.source_updated_at < EXCLUDED.source_updated_at
RETURNING order_id;

The PostgreSQL docs are explicit that RETURNING only reports rows that were actually inserted or updated. If the row was locked but the WHERE failed, nothing is returned. An empty result therefore means "this event was stale, ignore it".

Telling an insert from an update

Sometimes you want to know which branch ran.

PostgreSQL 18 and later can do this with documented syntax. RETURNING accepts OLD and NEW, and for a plain insert every old value is NULL:

... RETURNING order_id, (old.order_id IS NULL) AS was_inserted;   -- PostgreSQL 18+

Earlier versions rely on a well-known implementation detail: a freshly inserted row has xmax = 0:

... RETURNING order_id, (xmax = 0) AS was_inserted;               -- PostgreSQL 15-17

This is not a documented interface, so treat it as a convenience and cover it with a test. Note that the xmin = 0 variant that circulates in some tutorials is wrong: xmin is the inserting transaction's ID and is never zero for a real row, so the expression is always false. On PostgreSQL 16 an inserted row returns xmax = 0 as true and xmin = 0 as false.

Where does the version come from?

A guard is only as good as its version, and not every provider gives you a trustworthy one:

• Stripe does not guarantee delivery order and tells you not to order by created. Its recommended approach is to treat the event as a hint and retrieve the current object from the API, for example fetching the invoice or subscription when invoice.paid shows up before the events you expected.
• Shopify does not guarantee order and suggests using the X-Shopify-Triggered-At header or the payload's updated_at to sequence events.
• If the payload carries a real revision counter, prefer it over a timestamp. Timestamps can tie, and with < a tie is treated as stale. That is the right default for identical replays, but decide deliberately.

Snapshots versus deltas

Version guards suit snapshot payloads, where the newest event fully replaces the state. They do not suit delta payloads such as "add 5 to inventory" or "deposit 10". You cannot skip a delta because a later one arrived first. For deltas, record every event once in the inbox (or a ledger table) and derive state from the set of events, which is what section 8 does.

Finally, no amount of clever SQL replaces a periodic reconciliation job that re-fetches recent objects from the provider's API. Shopify explicitly recommends this because webhook delivery is not guaranteed.

5. Locking strategies under load

During a flash sale or a provider replay after an outage, hundreds of duplicates can land at once. Three mechanisms cover the common cases.

                    Inbound webhook execution modes
                                  |
          +-----------------------+------------------------+
          |                                                |
  Heavy work per event                              Queue of pending events
  Advisory lock (try, don't wait)                   FOR UPDATE SKIP LOCKED

5.1 The unique index does the heavy lifting

With ON CONFLICT, contention on one key is simply serialized: the first transaction wins, the rest wait and then take the conflict path. This is correct, and cheap for short transactions. The cost of many workers stacking up on the same duplicate is time and held connections, not wrong results. It becomes a problem only when the transaction holding the key is slow.

5.2 Advisory locks for expensive work

If handling an event involves heavy computation or external lookups before the first write, you would rather have duplicate deliveries bail out immediately than queue up behind the first. Advisory locks are application-defined locks on a 64-bit key (or two 32-bit keys); PostgreSQL does not tie them to any table row.

pg_try_advisory_xact_lock attempts a transaction-scoped lock without waiting and returns false if someone else holds it. The lock is released automatically at commit or rollback.

import hashlib

def advisory_key(provider: str, event_id: str) -> int:
    # 64-bit signed integer derived from SHA-256, stable across processes and versions
    digest = hashlib.sha256(f"{provider}:{event_id}".encode()).digest()
    return int.from_bytes(digest[:8], "big", signed=True)

def process_heavy_webhook(provider, event_id, payload):
    with db.transaction() as tx:
        got_lock = tx.execute(
            "SELECT pg_try_advisory_xact_lock(%s)", (advisory_key(provider, event_id),)
        ).scalar()

        if not got_lock:
            # Another worker is on this exact event right now.
            return {"status": "busy"}

        claimed = tx.execute("""
            INSERT INTO webhook_inbox (provider, event_id, event_type, payload)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (provider, event_id) DO NOTHING
            RETURNING event_id
        """, (provider, event_id, payload["type"], json.dumps(payload))).fetchone()

        if claimed is None:
            return {"status": "ignored"}

        apply_business_logic(tx, payload)
        return {"status": "processed"}

Points to get right:

• Key size. hashtext() returns only 32 bits, so collisions are realistic at scale. A collision makes two different events contend for one lock, which costs a spurious "busy" but never a double-process. hashtextextended(text, seed) gives 64 bits in SQL (it exists since PostgreSQL 11, but it is an internal function rather than a documented one). Hashing in the application, as above, avoids depending on it.
• Connection poolers. Transaction-level advisory locks are safe with PgBouncer in transaction pooling mode, because the lock lives exactly as long as the transaction. Session-level advisory locks (pg_advisory_lock) are not: the lock stays on a server connection that PgBouncer may hand to someone else.
• Do not hold a transaction open across slow external calls. It pins a connection and can hold back vacuum. If the heavy work is a third-party API call, do it outside the transaction, guarded by an idempotency key, and keep the database transaction for the claim and the state change.
• Choose the "busy" response carefully. Returning a non-2xx status invites a retry, which is what you want if the other worker might crash. But repeated failures count against you: Shopify deletes an Admin-API subscription after 8 consecutive failed deliveries. The receive-then-process design in section 2 sidesteps this.

5.3 Queue workers with FOR UPDATE SKIP LOCKED

If you buffer events in a table and let several workers drain it, plain SELECT ... FOR UPDATE makes them line up behind whichever row is locked first. SKIP LOCKED lets each worker take rows nobody else has locked:

CREATE TABLE webhook_queue (
    id         BIGSERIAL PRIMARY KEY,
    status     VARCHAR(16) NOT NULL DEFAULT 'pending',
    locked_at  TIMESTAMPTZ,
    attempts   INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

WITH next AS (
    SELECT id
    FROM webhook_queue
    WHERE status = 'pending'
    ORDER BY created_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE webhook_queue q
SET status = 'processing', locked_at = NOW(), attempts = attempts + 1
FROM next
WHERE q.id = next.id
RETURNING q.*;

In a two-session test, a worker holding rows 1 and 2 in an open transaction leaves them alone, and a second worker immediately receives rows 3 and 4.

Two caveats:

• The PostgreSQL documentation warns that skipping locked rows gives an inconsistent view of the data, so it is not suitable for general-purpose queries. It is explicitly intended for queue-like tables with multiple consumers.
• Flipping a row to processing means a crashed worker leaves it stuck there. Run a reaper that returns rows to pending when locked_at is too old, and cap attempts so a poison message ends up in a failed state rather than looping forever.

For short jobs there is a simpler variant that needs no reaper: keep the row lock for the duration of the work. SELECT ... FROM webhook_inbox WHERE status = 'pending' ORDER BY received_at FOR UPDATE SKIP LOCKED LIMIT 1, do the work, UPDATE ... SET status = 'done', processed_at = NOW(), commit. If the worker dies, the lock disappears with the connection and the row is simply picked up again. The trade-off is one open transaction per in-flight event.

6. ON CONFLICT versus MERGE

PostgreSQL 15 added the standard SQL MERGE command, and people reasonably ask whether it should replace ON CONFLICT for idempotent ingestion. For webhooks, mostly no.

You can watch the difference yourself. Open two sessions on PostgreSQL 16, and in each run a MERGE that inserts the same absent key WHEN NOT MATCHED. Keep the first transaction open for a few seconds. When it commits, the second MERGE fails with duplicate key value violates unique constraint. Replace both statements with INSERT ... ON CONFLICT DO NOTHING and the second one returns zero rows instead. On PostgreSQL 16, adding RETURNING to a MERGE is a syntax error.

MERGE is a fine choice for batch synchronization from a staging table, or where you need conditional deletes. If you use it for ingestion, be ready to catch unique_violation and retry, or serialize writers yourself. For single-key idempotent writes, ON CONFLICT remains the better fit.

7. Transaction isolation

Everything above assumes PostgreSQL's default, READ COMMITTED. It is worth knowing what changes if your connection or framework uses a stricter level.

Take the same race as before, but let the second transaction start (and take its snapshot) before the first one inserts and commits:

• Under READ COMMITTED, the second INSERT ... ON CONFLICT DO NOTHING sees the committed conflict and returns zero rows.
• Under REPEATABLE READ (and SERIALIZABLE), the same statement fails with could not serialize access due to concurrent update (SQLSTATE 40001), because the conflicting row is not visible to the transaction's snapshot.

Higher isolation levels are legitimate, but the PostgreSQL docs are clear that applications using them must be prepared to retry transactions that hit serialization failures. For the inbox claim, READ COMMITTED plus a unique key is the simplest correct choice. If you run stricter, wrap the whole handler in a retry loop on SQLSTATE 40001, and remember that the retry re-reads the world and will now see the duplicate.

8. The complete pattern: inbox plus transactional outbox

Idempotent database writes cover everything that lives in PostgreSQL. To trigger external side effects (publishing to Kafka, notifying Slack, calling another service) without losing or duplicating them, combine the inbox with the transactional outbox: write the "please publish this" record in the same transaction as the state change, and let a separate relay deliver it.

-- 1. Inbox: see section 2 (webhook_inbox)

-- 2. Business entity
CREATE TABLE accounts (
    account_id VARCHAR(128) PRIMARY KEY,
    balance    NUMERIC(14, 2) NOT NULL DEFAULT 0.00,
    updated_at TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- 3. Outbox
CREATE TABLE transactional_outbox (
    id             BIGSERIAL PRIMARY KEY,
    aggregate_type VARCHAR(64)  NOT NULL,
    aggregate_id   VARCHAR(128) NOT NULL,
    payload        JSONB        NOT NULL,
    created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    processed_at   TIMESTAMPTZ
);

CREATE INDEX outbox_unprocessed_idx
    ON transactional_outbox (id) WHERE processed_at IS NULL;

CREATE OR REPLACE FUNCTION process_deposit_webhook(
    p_provider   TEXT,
    p_event_id   TEXT,
    p_account_id TEXT,
    p_amount     NUMERIC
) RETURNS JSONB LANGUAGE plpgsql AS $$
BEGIN
    -- Step 1: claim the event
    INSERT INTO webhook_inbox (provider, event_id, event_type, payload, status, processed_at)
    VALUES (
        p_provider, p_event_id, 'account.deposit',
        jsonb_build_object('account_id', p_account_id, 'amount', p_amount),
        'done', NOW()
    )
    ON CONFLICT (provider, event_id) DO NOTHING;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('status', 'duplicate');   -- no side effects
    END IF;

    -- Step 2: apply the state change
    UPDATE accounts
    SET balance = balance + p_amount, updated_at = NOW()
    WHERE account_id = p_account_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Account % not found', p_account_id;   -- rolls back the claim too
    END IF;

    -- Step 3: record the external side effect
    INSERT INTO transactional_outbox (aggregate_type, aggregate_id, payload)
    VALUES ('account', p_account_id,
            jsonb_build_object('event', 'balance_updated',
                               'account_id', p_account_id,
                               'deposit_amount', p_amount));

    RETURN jsonb_build_object('status', 'processed');
END;
$$;

Running it twice with the same event ID returns processed once and duplicate the second time, and the balance changes only once. Calling it for a missing account raises, and because the claim is in the same transaction it leaves no inbox row behind, so a later retry can succeed once the account exists.

A deposit is a delta, not a snapshot, which is why this function relies on the inbox for correctness instead of a version guard.

A relay process then drains the outbox. It uses the same SKIP LOCKED technique so several relays can run in parallel:

WITH batch AS (
    SELECT id FROM transactional_outbox
    WHERE processed_at IS NULL
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
SELECT o.* FROM transactional_outbox o JOIN batch USING (id);
-- publish each message, then:
-- UPDATE transactional_outbox SET processed_at = NOW() WHERE id = ANY($1);

The relay itself is at-least-once: if it crashes after publishing but before marking a row done, the message goes out again. So consumers must be idempotent too, for instance by deduplicating on the outbox id or a stable message key. The pattern moves the guarantee from "never lose, never duplicate" to "never lose, tolerate duplicates cheaply", which is the achievable one.

Do not turn permanent errors into endless retries

The function above raises for an unknown account. That is fine for a transient condition (the account row may simply not exist yet), but for a permanently bad event it means the provider keeps retrying until it gives up, and some providers penalize endpoints that fail repeatedly (Shopify deletes the subscription after 8 consecutive failures). For errors that will never succeed, store the event with status = 'failed' and the reason, return 2xx, and alert a human.

9. Retention and pruning

The inbox grows forever unless you trim it. Pick a retention period longer than the longest window in which your provider can redeliver: with Stripe's manual resend reaching back 30 days, 45 to 90 days is a defensible choice. Deleting keys removes protection against replays older than the retention window.

Be careful with time-based partitioning as the way to drop old data. PostgreSQL requires that a primary key or unique constraint on a partitioned table include all the partition key columns, because each partition's index can only enforce uniqueness within itself. If you partition the inbox by received_at, the primary key becomes (provider, event_id, received_at), and the database can no longer stop the same event ID being inserted into two different partitions. A duplicate that arrives after a month boundary would slip through.

Two safer options:

• Keep the inbox unpartitioned and delete in small batches using the index on received_at, for example DELETE ... WHERE ctid IN (SELECT ctid FROM webhook_inbox WHERE received_at < NOW() - INTERVAL '60 days' LIMIT 5000) in a loop.
• If you need partition-drop speed, hash-partition on (provider, event_id) instead, which keeps uniqueness enforceable, and accept batch deletes for expiry.

Production checklist

• Verify the provider's signature before writing anything.
• Deduplicate on the provider's documented ID (Stripe event id plus data.object ID and type for the twin-event case; Shopify X-Shopify-Webhook-Id), per notification if batches are possible.
• Enforce uniqueness with a primary key or unique index, and make it the first thing that runs.
• Put the claim and the state change in one transaction.
• Add a business-level unique constraint behind the inbox.
• Guard snapshot upserts with a version; model deltas as events. Never rely on delivery order.
• Acknowledge quickly, process asynchronously, and use FOR UPDATE SKIP LOCKED for workers.
• Use pg_try_advisory_xact_lock with a 64-bit key for expensive per-event work; avoid session-level advisory locks behind transaction-mode poolers.
• Send external side effects through a transactional outbox, and make consumers idempotent.
• Return 2xx for permanently bad events after recording them; alert instead of failing forever.
• Set retention longer than the provider's replay window, and think twice before partitioning by time.
• Run a reconciliation job against the provider's API.

Summary

Idempotency is a database concern, not an application-layer convention. A unique key turns "have we seen this?" into an atomic operation, a single transaction ties the claim to the work, version guards stop stale snapshots from winning, and an outbox keeps external effects honest. PostgreSQL gives you all the building blocks, but the guarantees are narrower than the marketing phrase "exactly once" suggests, and the provider's own delivery contract is part of your design. Read it, and re-read it, because the numbers change.

Sources

• Stripe, Receive Stripe events in your webhook endpoint: retries, event ordering, duplicate events, manual resend windows.
• Shopify, Verify webhook deliveries, Ignore duplicate webhooks, Troubleshoot webhooks and Best practices for webhooks: headers, retry policy, ordering, reconciliation.
• Marqeta, About webhooks: retry schedule and batching.
• PostgreSQL documentation: INSERT (ON CONFLICT, RETURNING, OLD/NEW), MERGE support functions, Transaction isolation, Explicit locking (advisory locks), SELECT locking clause, Table partitioning limitations.
• pganalyze, Postgres 15 MERGE vs. INSERT ON CONFLICT.
• Amazon RDS, PostgreSQL release calendar: PostgreSQL 18 minor versions.
• Neon, PostgreSQL 19: ON CONFLICT DO SELECT and VictoriaMetrics, PostgreSQL 19 tour: PostgreSQL 19 beta status.