Storing webhook idempotency keys in PostgreSQL

Once you have decided that a durable key is the right mechanism — the choice laid out in idempotency keys vs deduplication windows — the remaining work is a schema and four SQL statements. This page is the PostgreSQL half of the pattern described in idempotency in webhooks: what the table looks like, why the UNIQUE constraint does the locking for you, and how to stop the table growing forever.

Postgres is a good fit here for one specific reason. A unique index is already a serialization point: when two workers insert the same key concurrently, one blocks on the other’s uncommitted tuple and then discovers the conflict at commit. You get mutual exclusion without a distributed lock, a lease timeout, or a second datastore to keep alive during an incident. The handler shape around it — key extraction, response caching, transactional boundaries — is covered in how to design idempotent webhook consumers; here the focus stays on the store itself.

Prerequisites

• PostgreSQL 12 or newer; the examples use plain INSERT ... ON CONFLICT, which has been available since 9.5.
• Python 3.11+ with psycopg 3 and a connection pool; the handler must control transaction boundaries explicitly, so autocommit-per-statement ORMs need care.
• A stable event identifier on every delivery — the provider’s event id, or a hash of retry-stable fields, as discussed in at-least-once vs exactly-once delivery trade-offs.
• A documented provider retry horizon, which sets the floor for how long a key must be kept.
• A scheduler — cron, a Kubernetes CronJob, or an in-process timer — to run the retention sweep every few minutes.

Step 1: Design the table and its UNIQUE constraint

The table stores one row per logical operation, not one row per HTTP request. Its natural key is the pair (source, event_id): source because two providers can and will issue the same identifier, event_id because that is what stays constant across the provider’s retries. Making that pair the primary key means the constraint is not a validation afterthought — it is the concurrency control.

Every other column exists to answer a question you will have during an incident: what was the payload, what did we reply, and when.

CREATE TABLE webhook_idempotency (
  source           TEXT        NOT NULL,
  event_id         TEXT        NOT NULL,
  payload_digest   BYTEA       NOT NULL,
  response_status  SMALLINT,
  response_body    JSONB,
  claimed_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at     TIMESTAMPTZ,
  PRIMARY KEY (source, event_id)
);

-- Retention sweeps read this; keep it BRIN-cheap on an append-ordered column.
CREATE INDEX webhook_idempotency_claimed_idx
  ON webhook_idempotency USING BRIN (claimed_at);

-- Find rows that were claimed but never completed (crashed mid-flight).
CREATE INDEX webhook_idempotency_stuck_idx
  ON webhook_idempotency (claimed_at)
  WHERE completed_at IS NULL;

Resist the urge to reach for declarative partitioning here on day one. Postgres can only enforce a unique constraint across partitions when the partition key is part of that constraint, so partitioning by claimed_at would push claimed_at into the primary key — and since it defaults to now(), every retry would get a fresh timestamp and the conflict you depend on would never fire. Global uniqueness on (source, event_id) is the property the whole design rests on; Step 5 covers how to keep the table bounded without giving it up.

The response_body column is JSONB rather than TEXT so you can query it during an incident, and payload_digest is a BYTEA SHA-256 rather than the whole body: you want to detect a provider reusing an id with different content without storing a second copy of every payload.

Step 2: Claim the key with INSERT ON CONFLICT DO NOTHING

The claim is a single statement. INSERT ... ON CONFLICT DO NOTHING RETURNING event_id inserts the row if nobody has it and returns one row; if the key is taken it returns zero rows without raising. Zero rows means “somebody else owns this event” — no SELECT first, no read-then-write window for a concurrent delivery to slip through.

That distinction is the whole point. A SELECT-then-INSERT pair has a gap between the check and the write, and under a provider’s retry storm two workers will both see “not present” and both run the side effect. The single-statement claim has no gap: the second insert blocks on the first transaction’s uncommitted row and, when that transaction commits, finds the conflict.

# store.py
import hashlib
from dataclasses import dataclass
from typing import Any

import psycopg
from psycopg.rows import dict_row

CLAIM_SQL = """
INSERT INTO webhook_idempotency (source, event_id, payload_digest)
VALUES (%(source)s, %(event_id)s, %(digest)s)
ON CONFLICT (source, event_id) DO NOTHING
RETURNING event_id
"""

LOOKUP_SQL = """
SELECT payload_digest, response_status, response_body, completed_at
FROM webhook_idempotency
WHERE source = %(source)s AND event_id = %(event_id)s
"""


@dataclass(frozen=True)
class Claim:
    won: bool
    cached_status: int | None = None
    cached_body: Any = None
    in_flight: bool = False


def digest(raw_body: bytes) -> bytes:
    return hashlib.sha256(raw_body).digest()


def claim(cur: psycopg.Cursor, source: str, event_id: str, raw_body: bytes) -> Claim:
    """Atomically claim an event. Must run inside an open transaction."""
    cur.execute(CLAIM_SQL, {"source": source, "event_id": event_id, "digest": digest(raw_body)})
    if cur.fetchone() is not None:
        return Claim(won=True)

    # Lost the race, or this is a plain retry of an event we already finished.
    cur.row_factory = dict_row
    cur.execute(LOOKUP_SQL, {"source": source, "event_id": event_id})
    row = cur.fetchone()
    if row is None:
        # The winner rolled back between our INSERT and this SELECT: treat as retryable.
        return Claim(won=False, in_flight=True)
    if row["completed_at"] is None:
        return Claim(won=False, in_flight=True)
    if row["payload_digest"] != digest(raw_body):
        raise ValueError(f"event id {event_id} reused with a different body")
    return Claim(won=False, cached_status=row["response_status"], cached_body=row["response_body"])

in_flight matters more than it looks. If the winner is still working, you cannot answer with a cached response that does not exist yet, and you must not run the side effect yourself. Return 409 Conflict or 503 with a short Retry-After and let the provider redeliver — by then the winner has committed and the duplicate gets the cached answer.

Step 3: Commit the claim and the side effect together

The claim and the business write must land in the same transaction. Claim, commit, then crash before the side effect, and the retry finds a completed-looking row and skips work that never happened — the event is silently lost despite at-least-once transport. Do the side effect first and commit the claim afterwards and a crash in between re-runs the effect on retry.

One transaction removes both windows. Either the row and the write are visible together, or neither is, and the provider’s next retry starts from a clean slate.

# handler.py
import json
from typing import Any

import psycopg

from store import Claim, claim

COMPLETE_SQL = """
UPDATE webhook_idempotency
SET response_status = %(status)s,
    response_body = %(body)s,
    completed_at = now()
WHERE source = %(source)s AND event_id = %(event_id)s AND completed_at IS NULL
"""


def apply_side_effect(cur: psycopg.Cursor, payload: dict[str, Any]) -> dict[str, Any]:
    """The real work — must only touch this same transaction's cursor."""
    cur.execute(
        "INSERT INTO orders (order_id, total_minor) VALUES (%s, %s)"
        " ON CONFLICT (order_id) DO NOTHING",
        (payload["order_id"], payload["total_minor"]),
    )
    return {"order_id": payload["order_id"], "status": "recorded"}


def process(conn: psycopg.Connection, source: str, event_id: str, raw_body: bytes) -> tuple[int, Any]:
    payload = json.loads(raw_body)
    with conn.transaction():          # BEGIN ... COMMIT around everything below
        with conn.cursor() as cur:
            outcome: Claim = claim(cur, source, event_id, raw_body)
            if outcome.in_flight:
                return 409, {"status": "in_flight"}
            if not outcome.won:
                return outcome.cached_status or 200, outcome.cached_body

            body = apply_side_effect(cur, payload)
            cur.execute(
                COMPLETE_SQL,
                {"status": 200, "body": json.dumps(body), "source": source, "event_id": event_id},
            )
            return 200, body

Two constraints hold this together. The side effect must use the same cursor, because a write on another connection is not covered by this transaction and will survive a rollback. And nothing that cannot be rolled back — sending an email, charging a card through a third-party API — belongs inside the block; those go through a transactional outbox row written here and dispatched after commit.

Step 4: Store and replay the cached response

The cached response is what makes the duplicate path correct rather than merely quiet. A duplicate that gets a bare 200 OK while the original got 200 {"order_id": ...} will break any provider that reconciles response bodies, and it destroys your ability to answer “what did we tell them?” during an audit.

Write the status and body onto the claimed row inside the same transaction, as the UPDATE above does, and serve it back verbatim on every later delivery. The completed_at IS NULL predicate in that statement is a cheap assertion: it means a second completion attempt updates zero rows instead of overwriting the recorded answer.

Cache only successful outcomes. If the side effect raises, the transaction rolls back and the claim row disappears with it, which is precisely right — the provider retries, a fresh claim is made, and the work is attempted again. Persisting a failure would convert a transient database blip into a permanently poisoned event.

Step 5: Expire keys with a bounded sweep

The table grows at exactly your event rate, so it needs a floor. What it must not get is DELETE FROM webhook_idempotency WHERE claimed_at < now() - interval '30 days' in one shot: on a busy table that rewrites millions of tuples in a single transaction, generates WAL proportional to the deletion, holds a snapshot open for minutes, and leaves autovacuum chasing dead rows while your claim latency doubles.

Sweep in bounded batches instead, on a short interval, so the delete never becomes an event. The retention horizon is the one number that carries real risk: it must comfortably exceed the provider’s documented retry window, because the moment a key is gone a late redelivery claims it again and re-runs the side effect.

# retention.py
import logging
import time

import psycopg

logger = logging.getLogger(__name__)

RETENTION_DAYS = 30
BATCH_SIZE = 5_000

SWEEP_SQL = """
DELETE FROM webhook_idempotency
WHERE ctid IN (
    SELECT ctid FROM webhook_idempotency
    WHERE claimed_at < now() - %(retention)s::interval
    LIMIT %(batch)s
)
"""


def sweep_once(conn: psycopg.Connection) -> int:
    """Delete at most BATCH_SIZE expired keys in its own short transaction."""
    with conn.transaction():
        with conn.cursor() as cur:
            cur.execute(
                SWEEP_SQL,
                {"retention": f"{RETENTION_DAYS} days", "batch": BATCH_SIZE},
            )
            return cur.rowcount


def run_sweeper(conn: psycopg.Connection, max_batches: int = 200) -> int:
    """Called every few minutes from cron. Bounded, so it can never run away."""
    removed = 0
    for _ in range(max_batches):
        deleted = sweep_once(conn)
        removed += deleted
        if deleted < BATCH_SIZE:
            break          # caught up; nothing left older than the horizon
        time.sleep(0.2)    # yield to the claim path between batches
    logger.info("idempotency sweep removed %d expired key(s)", removed)
    return removed

Each batch commits on its own, so the sweeper never holds a long snapshot and autovacuum can reclaim behind it. If the table eventually outgrows this — sustained tens of millions of keys inside the window — that is the point to consider declarative partitioning, and the price is explicit: the partition key must join the unique constraint, so you would be trading global suppression for per-partition suppression and must confirm your retry horizon never crosses a partition boundary.

Size the window off the provider’s documented retry horizon plus a wide margin — 30 days against a 72-hour horizon costs almost nothing and covers the manual replay you will eventually run out of a support ticket.

Verification and testing

The test that matters is the concurrency one, and it needs two real connections: a single-connection test can never reproduce the race because both statements run in the same transaction.

# test_claim.py
import psycopg
import pytest

from store import claim

DSN = "postgresql://localhost/webhooks_test"


def test_only_one_of_two_concurrent_claims_wins():
    with psycopg.connect(DSN) as a, psycopg.connect(DSN) as b:
        a.autocommit = False
        b.autocommit = False
        with a.cursor() as ca, b.cursor() as cb:
            first = claim(ca, "acme", "evt_91f2", b'{"order_id":"o1"}')
            assert first.won is True

            # B's INSERT blocks on A's uncommitted tuple, so commit A first,
            # exactly as the two workers would interleave in production.
            a.commit()
            second = claim(cb, "acme", "evt_91f2", b'{"order_id":"o1"}')
            assert second.won is False
            b.rollback()


def test_reused_event_id_with_a_different_body_is_rejected():
    with psycopg.connect(DSN) as conn:
        with conn.cursor() as cur:
            assert claim(cur, "acme", "evt_dup", b'{"order_id":"o1"}').won is True
            cur.execute(
                "UPDATE webhook_idempotency SET completed_at = now(), response_status = 200"
                " WHERE event_id = 'evt_dup'"
            )
            with pytest.raises(ValueError):
                claim(cur, "acme", "evt_dup", b'{"order_id":"o2"}')
        conn.rollback()

Two SQL assertions are worth running against production on a schedule. The first finds claims that never completed, which should be a handful at most and always recent:

SELECT source, event_id, claimed_at
FROM webhook_idempotency
WHERE completed_at IS NULL AND claimed_at < now() - interval '5 minutes'
ORDER BY claimed_at
LIMIT 50;

The second checks the retention horizon from both ends at once — the oldest surviving key must be older than the provider’s retry window (or you are deleting too aggressively) and younger than your retention setting (or the sweeper has stalled):

SELECT min(claimed_at) AS oldest_key,
       now() - min(claimed_at) AS oldest_age,
       count(*) FILTER (WHERE claimed_at < now() - interval '30 days') AS overdue_rows
FROM webhook_idempotency;

Failure modes and gotchas

• Deleting keys faster than the provider retries. A retention window shorter than the documented retry horizon means a late redelivery finds no row, wins a fresh claim, and re-runs the side effect — the exact double-charge the table exists to prevent, and it only shows up on the deliveries that took longest to arrive. Alert when the oldest surviving key is younger than the horizon.
• A SELECT before the INSERT. Reading first to decide whether to claim reintroduces the read-then-write race the constraint exists to remove, and it fails only under the concurrent-retry load you cannot reproduce locally. Always insert first and use the returned row count as the decision.
• Long-running claim transactions. The transaction stays open for the whole side effect, so an HTTP call to a slow third party inside the block holds the tuple lock, blocks every duplicate, and pins autovacuum’s horizon. Keep external calls out of the transaction — write an outbox row and dispatch after commit.
• Treating a digest mismatch as a duplicate. If the same event_id arrives with a different body, that is a provider bug or a spoofed replay, not a retry. Raising is correct: silently returning the cached response hides a real data-integrity problem behind a 200.

Frequently Asked Questions

Related

• How to design idempotent webhook consumers — the handler shape this store plugs into.
• Idempotency keys vs deduplication windows — choosing a durable key over a TTL window in the first place.
• At-least-once vs exactly-once delivery trade-offs — why the transport guarantees duplicates that this table absorbs.
• Idempotency in webhooks — the deduplication patterns this implementation belongs to.