Idempotent upserts and exactly-once illusions

The webhook fired twice. Stripe retries on timeout; the handler had no idempotency key. Two subscription rows, two welcome emails, finance reconciliation off by one MRR. The engineer said the queue promised exactly-once. The queue lied — or rather, marketed. Postgres idempotent upserts and dedup tables are how production systems survive the truth: networks retry, consumers crash mid-handler, and at-least-once is the only delivery guarantee worth designing for.

Exactly-once processing requires exactly-once delivery plus idempotent handlers plus transactional outbox plus cooperative infrastructure — a stack most teams partially implement and fully assume. Idempotent upserts collapse the problem to: if this message arrived before, the database state is unchanged; if not, it applies once.

Delivery guarantees in practice

Design for at-least-once. Make effects idempotent. Measure duplicates in metrics — if zero forever, observability might be wrong.

Exactly-once is an outcome you build, not a checkbox on the broker.

Idempotency keys at the application layer

Client or message carries stable key:

Idempotency-Key: stripe_evt_1A2b3C...

Handler logic:

1. Begin transaction
2. INSERT INTO processed_events (idempotency_key) VALUES ($1) ON CONFLICT DO NOTHING RETURNING id
3. If no row returned → duplicate → commit empty, return 200
4. Else apply business logic + commit

Table schema:

CREATE TABLE processed_events (
  idempotency_key TEXT PRIMARY KEY,
  processed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload_hash    TEXT
);

Retention policy: keep keys long enough to cover retry window — Stripe retries for days; internal queues may need 7–30 days. Partition or TTL old keys.

Postgres upsert patterns for state application

INSERT ... ON CONFLICT DO UPDATE — apply latest state idempotently:

INSERT INTO subscription_status (stripe_subscription_id, status, updated_at)
VALUES ($1, $2, now())
ON CONFLICT (stripe_subscription_id) DO UPDATE
SET status = EXCLUDED.status,
    updated_at = EXCLUDED.updated_at
WHERE subscription_status.updated_at < EXCLUDED.updated_at;

The WHERE clause on update prevents stale retries from overwriting newer state — monotonic versioning or updated_at comparison is critical for out-of-order delivery.

ON CONFLICT DO NOTHING — first write wins; duplicates ignored — good for create-once semantics.

Unique constraints define idempotency scope

• One row per external event ID
• One row per (user_id, operation_type, business_key)
• Partial unique indexes for soft-delete patterns

Index design intersects composite index patterns — idempotency lookups are point queries on key columns; primary key or unique index required.

Webhooks, outbox, and double-publish

Inbound webhooks: idempotency key from provider event ID.

Outbound events: transactional outbox pattern — write business row and outbox row in same transaction; publisher reads outbox. Publisher duplicates possible — consumers still idempotent.

BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = $1;
  INSERT INTO outbox (event_id, type, payload) VALUES ($1, 'OrderPaid', ...);
COMMIT;

Consumer processing OrderPaid uses same event_id in processed_events dedup table.

Exactly-once illusions that cause incidents

"Our SDK handles idempotency." Verify what it deduplicates — often HTTP layer only, not your business row.

Upsert without monotonic guard. Second retry with older payload overwrites newer payment state.

Idempotency key collision. Reusing keys across different operations — keys must scope operation + entity.

Long transactions holding locks. Idempotency insert plus slow business logic in one transaction — keep dedup check fast, business logic optimized.

Deleting dedup rows too aggressively. Retry after TTL window creates duplicate effects.

Testing idempotency before production proves it

Idempotency bugs are invisible until duplicate delivery — test deliberately:

Double-submit integration test. Send same webhook payload twice; assert one row, one side effect, second response 200 with no duplicate email job enqueued.

Crash simulation. Kill consumer after business write but before ack — redeliver message; state unchanged, no duplicate.

Out-of-order delivery. Apply older event after newer; monotonic updated_at guard preserves latest state.

Key collision audit. Review idempotency key construction — ensure different operations on same entity use different key namespaces.

Load test dedup table insert rate — hot idempotency keys under concurrent delivery should not become lock contention bottleneck. Partition processed_events by week if insert rate exceeds comfortable single-table throughput.

Partial failure: when idempotency is not enough

Idempotency prevents duplicate successful application. Partial failures need complementary patterns:

• Two-phase effects. Reserve with idempotent pending row; confirm in second step — duplicate first step harmless.
• Compensating actions. Charge idempotent; refund also idempotent with separate key — rollback thinking applies to message-driven flows.
• Inbox pattern. Store raw message idempotently; process in separate transaction — replay processing without re-fetching from queue.

Handler crash after external API call but before dedup commit is the classic window — dedup insert must be in same transaction as business state change, or external calls must be idempotent at provider (Stripe idempotency keys on API, not only webhook).

How should teams implement idempotent Postgres handlers?

Implementation checklist for message consumers and webhooks.

Where should idempotency keys come from?

External provider event ID for webhooks; client-generated UUID for APIs; hash of (operation, entity, version) for internal jobs. Keys must be stable across retries of same logical operation.

Is one dedup table enough?

Often one processed_events plus domain-specific unique constraints. High-volume systems partition by date. Separate tables per consumer group if isolation needed.

How does this relate to saga and compensation?

Idempotency prevents duplicate forward effects. Sagas handle partial failure across services — complementary. Compensating transactions should also be idempotent — refund twice is same failure mode as charge twice.

A common argument runs the other way

The opposing view holds that idempotency overhead is unnecessary if consumers are careful and retries are rare.

Retries are not rare — they are guaranteed under at-least-once. "Careful" code without dedup fails on crash between side effect and ack — the classic duplicate window. Idempotency cost is one indexed insert per message; duplicate incident cost is customer trust and finance hours.

Key takeaways

• Design for at-least-once delivery; exactly-once is built via idempotent effects.
• Dedup table with idempotency key primary key — insert in same transaction as business write.
• Upserts need monotonic guards — do not let stale retries overwrite newer state.
• Test with double-submit, crash simulation, and out-of-order delivery before production.
• Partial failures need inbox pattern or compensating idempotent actions — dedup alone insufficient.
• Webhook handlers use provider event ID as key; retain keys through retry window.
• Transactional outbox for outbound events; consumers still dedupe.

Conclusion

Exactly-once delivery is a comforting story brokers tell and engineers retell until duplicate webhooks hit production. Postgres idempotent upserts make duplicates visible and harmless — one key, one effect, retries welcome.

Audit highest-volume webhook and queue handlers: is there a dedup key insert in the same transaction as the business write? Missing insert is missing idempotency — fix before the next retry storm.