All entries
Chapter VIII
Journal · 18 Nov 2026 · 10 min read

Idempotent Consumer vs Inbox Pattern in Postgres

Idempotent consumer vs transactional inbox in Postgres, tested: broker dedupe windows, the REPEATABLE READ 40001 trap and Stripe's 24-hour key gap.

Stripe retries a failed webhook for up to three days, but it only promises to remember the idempotency key on your API call for 24 hours. Take a consumer that forwards the event ID as that key, calls Stripe, then crashes before it commits. If the retry lands on day two, the key may be gone, and Stripe can make the transfer a second time.

Most guides to the idempotent consumer pattern miss this. Of the seven top results I checked, none compares the dedupe windows brokers give you, only one notes that ON CONFLICT throws under REPEATABLE READ, and one Java example checks for the ID and then saves, the exact race the pattern exists to close. So I ran the failure modes against a throwaway Postgres 18.6 container. Timings and errors below are pasted from those runs.

What Is the Idempotent Consumer Pattern?

Every broker a payments team is likely to use delivers at least once. A consumer that processes a message, commits, and crashes before it acks will see the message again. The idempotent consumer records each message ID it has handled, in the same database transaction as the work, so a redelivery finds the ID and does nothing.

The minimum table:

CREATE TABLE inbox (
  consumer    text        NOT NULL,
  message_id  text        NOT NULL,
  received_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer, message_id)
);
consumer is in the key because two services reading the same topic must each process the event once, not once between them. The claim and the decision fit in one statement:
WITH claimed AS (
  INSERT INTO inbox (consumer, message_id) VALUES ('ledger', 'evt_new_1')
  ON CONFLICT DO NOTHING RETURNING 1)
SELECT count(*) AS first_delivery FROM claimed;

The first run returned 1; the second returned 0. Do the ledger work only on 1, commit, then ack.

Idempotent Consumer vs Transactional Inbox: What's the Difference?

People use the two names loosely, but the designs ack at different times.

Idempotent consumerTransactional inbox
What gets storedMessage ID onlyFull message (ID, type, payload)
When the broker is ackedAfter the work commitsAs soon as the row is inserted
Who retries a failureThe broker (redelivery)Your worker, from the table
Ordering controlWhatever the broker givesYours, per aggregate
Extra moving partsNoneA polling worker, FOR UPDATE SKIP LOCKED
FitsKafka or SQS consumers whose work is one DB transactionHTTP webhooks with short deadlines, slow handlers

Ack timing decides it. SQS hides a message for 30 seconds by default, 12 hours at most, and a handler that runs longer gets a second copy while it is still working. Adyen wants a 2xx inside 10 seconds. If the handler calls a downstream bank API, store the webhook, return 200 and work from the table.

My rule: an internal consumer whose whole job is one Postgres transaction uses the idempotent consumer. Anything arriving over HTTP from a PSP goes into a transactional inbox. I covered the producer half, and why ID pollers skip rows, in transactional outbox in Postgres.

Does SQS FIFO or Kafka Exactly-Once Remove the Need for Dedupe?

No. Each broker dedupes over a far narrower window than payments needs.

BrokerDedupe mechanismWindowWhat it doesn't cover
SQS FIFOMessageDeduplicationId (max 128 chars) or SHA-256 of the body5 minutesA resend after 5 minutes; redelivery after a visibility timeout
SNS FIFODeduplication ID5 minutesAn SQS consumer that misses its visibility timeout
Azure Service BusMessageId duplicate detection10 min default, 20 s to 7 daysBasic tier; queues created without it (can't be enabled later)
NATS JetStreamNats-Msg-Id header2 min defaultRedelivery to a consumer that missed its ack
Google Pub/SubExactly-once deliveryUntil ackedPush subscriptions, cross-region subscribers, publish-side duplicates
Kafka EOSTransactions, read_committedPer transactionAny output that isn't a Kafka topic
SQS standard, RabbitMQNonen/aEverything

The broker windows stop a publisher retrying within minutes. None stops the consumer side: a handler that commits and dies before acking gets a fresh delivery with the same ID, and only your table catches it. Resend after the interval and, in AWS's words, SQS "can't detect duplicate messages". Azure accepts a duplicate send as successful and silently drops it, and you can't switch detection on for a queue that already exists.

Pub/Sub comes closest, but only for pull subscriptions in the same region, and Google warns publish-side duplicates arrive "even with exactly-once delivery enabled". Kafka's exactly-once covers topic-to-topic processing. For a Postgres output its docs say it "generally requires cooperation" from that system: an inbox row, or the offset stored in the same transaction. RabbitMQ calls its redelivered flag a hint, and EventBridge warns a target can be invoked more than once.

NServiceBus vs MassTransit vs Wolverine: Dedupe Defaults in 2026

If a framework owns your inbox, check its default, because most are far shorter than a PSP's retry window:

FrameworkDedupe storeDefault retention
NServiceBus, SQL persistenceOutbox records7 days, purged every minute
MassTransit, EF CoreInboxState by MessageId30 minutes (DuplicateDetectionWindow)
WolverineDurable inbox5 minutes (KeepAfterMessageHandling)
Eventuate Tramreceived_messages (consumer_id, message_id)No purge in the detector
Spring IntegrationMetadataStoreIn memory by default, lost on restart

A Wolverine service behind Stripe forgets an event five minutes after handling it, and Stripe may retry for three days. MassTransit's EF Core outbox also defaults its transaction to IsolationLevel.RepeatableRead in source, which matters for the next section.

What Happens When Two Consumers Get the Same Message at Once?

Two pods picking up the same event is normal after a rebalance or a visibility timeout. I opened two psql sessions. Session A inserted evt_1 and slept three seconds inside its transaction. Session B ran the same INSERT ... ON CONFLICT DO NOTHING one second later.

Under the default READ COMMITTED:

  • B blocked until A finished. When A committed, B returned INSERT 0 0 after 1,989 ms.
  • When A rolled back instead, B returned INSERT 0 1 after 1,997 ms, and took over the work.
That is correct behaviour, with a cost. The losing consumer holds a pooled connection for as long as the winner's transaction runs. Put an 8-second bank call inside that transaction and every duplicate delivery pins a connection for 8 seconds.

Under REPEATABLE READ, with B's snapshot taken before A committed, B's insert failed outright:

ERROR:  could not serialize access due to concurrent update

That is SQLSTATE 40001. The manual only hints at it, saying the quiet skip "is only the case in Read Committed mode". The reason is in nodeModifyTable.c: skipping on a row your snapshot can't see "would not be consistent with guarantees of the higher isolation levels". So a MassTransit consumer on EF Core defaults, or any consumer at REPEATABLE READ for a ledger posting, throws on an ordinary duplicate. Retry the complete transaction, as the Postgres docs advise; the new snapshot sees A's row and the insert returns 0.

The check-then-insert version fails differently. Both sessions ran SELECT count(*) and saw 0. Both did the work. A's insert succeeded; B's raised duplicate key value violates unique constraint "inbox_pkey" and rolled back. With the primary key, that is a noisy error. Without one, it is a double posting.

How Long Should You Keep Processed Message IDs?

At least as long as the longest window in which a copy can still arrive. For payment consumers that window is set upstream:

SourceHow late a copy can arrive
Stripe webhooks (live mode)Up to 3 days of retries
Adyen webhooksQueued for up to 30 days
PayPal webhooks25 attempts over 3 days
Checkout.com webhooks8 retries, about 30 hours in total
Kafka topic, default config7 days (log.retention.hours=168) after an offset reset
EventBridge target24 hours of retries
SQS FIFOBroker forgets after 5 minutes

Kafka has a trap here. A consumer group that sits empty for longer than offsets.retention.minutes (default 10,080, seven days) loses its committed offsets. When it comes back, auto.offset.reset decides: earliest rereads everything still on the topic, up to seven days by default, and the default latest silently skips it all. If you pick earliest, which a payments consumer should, the inbox must remember at least as far back as the topic does.

What does that cost? I inserted one million rows with Stripe-shaped IDs (evt_ plus 24 characters) and vacuumed:

message_id typeHeapPrimary keyTotal per million rows
text (28 chars)73 MB73 MB146 MB
uuid57 MB51 MB109 MB

At 50 events a second, 30 days of retention is 129.6 million rows: about 20 GB with text IDs, 15 GB with UUIDs. At 10 a second it is 4 GB.

Deleting old rows needs an index on received_at, and a B-tree on 1.1 million rows was 7.5 MB. A BRIN index on the same column was 24 kB, and a DELETE of 802,607 expired rows through it ran in 235 ms. BRIN relies on rows arriving in time order; heavy deletes let new rows refill old pages and blur that, so recheck the plan now and then.

Why Partitioning the Inbox Table by Date Breaks Deduplication

The obvious way to expire rows is to partition by received_at and drop old partitions. Postgres refuses the key you need:

ERROR:  0A000: unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "inbox_p" lacks column "received_at" which is part of the partition key.

Add received_at to the key and the table accepts it, but the key no longer dedupes. I delivered evt_9 at 23:59:58 and again at 00:00:03. Both inserts returned INSERT 0 1, one row in inbox_p_1117 and one in inbox_p_1118. A retry that crosses midnight is processed twice.

The fix is to partition on a date carried by the event, not by the clock at arrival. Stripe's Event object has a created timestamp that is the same on every redelivery. Key on (consumer, message_id, event_created) and partition on event_created: the same two deliveries went to one partition and the second returned INSERT 0 0. Drop a partition only once its events are older than the longest redelivery window.

Calling Stripe or Adyen From a Consumer: The 24-Hour Key Gap

The inbox makes database work exactly-once. It cannot do that for an HTTP call, because the call happens before the commit and a crash can land between them.

The usual advice is to forward the message ID as the PSP's idempotency key. That works only while the PSP still remembers the key. Stripe's keys can be pruned after 24 hours, against three days of webhook retries. Adyen keeps keys for "7 to 14 days" but queues webhooks for 30. I compared retention across providers in idempotency keys in payment APIs.

My position, and it is opinion: never let a PSP's idempotency store be your dedupe store. Before the call, commit a pending row with your own reference and pass that reference as the key. On redelivery the consumer finds the pending row and, instead of calling create again, asks the PSP what happened, by GET on the object or a list filtered by your reference, then finishes the row. The inbox handles redelivery, the pending row handles the crash window, and the PSP key covers only a retry inside one attempt.

The frameworks agree. NServiceBus's outbox guarantee excludes handler work outside its transaction, with email as its example, and Wolverine says such handlers "should account for that". A PSP call is that kind of work.

What This Means for Payments Engineers

1. Key on the producer's event ID plus the consumer name. Not a body hash, and not a delivery ID: GoCardless warns its meta.webhook_id identifies the attempt, not the event. For Stripe, also guard on object ID plus event type, since it sometimes sends two Event objects for one change. 2. Claim and work in one transaction, with INSERT ... ON CONFLICT DO NOTHING RETURNING. Never SELECT first. 3. At REPEATABLE READ or above, retry on 40001. The dedupe insert throws on an ordinary concurrent duplicate. 4. Keep transactions short. A duplicate waits for the winner's whole transaction. 5. Store webhooks, then ack. A transactional inbox meets Adyen's 10-second deadline; processing inline often doesn't. 6. Set retention from upstream: 3 days for Stripe-only, 30 for Adyen, at least topic retention for Kafka. Budget about 150 MB per million text IDs. 7. Don't partition by arrival time. Partition by the event's own date, or don't partition and delete through BRIN. 8. Write a pending row before any external call, and reconcile with a read on retry.

Signature checks come before all of this; I covered them in webhook signature verification.

I'm Tom Wang, and I write these guides from test output where I can get it. Run the two-session test against your own consumer, at the isolation level it really uses, before you trust its dedupe.