Effectively-once: idempotent consumers and the inbox pattern

An idempotent consumer makes a message’s effect happen once even though the broker may deliver the message many times. It records a stable key, the producer’s event ID or a business key, in the same database transaction as the effect, so a redelivery finds the key and changes nothing. The inbox pattern goes one step further: store every incoming message in a table first, acknowledge the broker at once, and process from the table on your own terms.

Picture it first: the society treasurer’s WhatsApp

Your housing society collects maintenance by UPI, and residents send the treasurer a payment screenshot on WhatsApp. Flat B-204 pays ₹3,000, sees one grey tick and forwards the screenshot again; later the spouse forwards it too. Three messages, one payment.

Add ₹3,000 per screenshot and B-204 has paid ₹9,000. So the treasurer writes the 12-digit UPI reference (the UTR) and the amount on one line, in one go, and before writing he looks for the reference. He doesn’t key on the WhatsApp message: every forward is a new message, but the UTR inside never changes.

When the secretary helps, the rule is: whoever writes a UTR first owns it. If both glanced at the register, saw nothing and then wrote, B-204 would be credited twice.

On the first of the month 300 screenshots arrive in an hour. So he replies “received” at once, drops each one into a folder, and works through the folder in the evening, retrying tomorrow the ones the bank app couldn’t confirm.

Map it back: screenshots are messages, forwards are redeliveries, the UTR is the dedupe key, the WhatsApp message is a broker delivery ID, the one-line entry is one database transaction, the write-first rule is a unique constraint, and the folder is the inbox.

Idempotent by nature, idempotent by design

Brokers promise at-least-once: a consumer that crashes before its ack, or outlives its lease, gets the message again (delivery guarantees traces every source). You can’t prevent the second delivery, so you make it harmless. That is effectively-once: delivered one or more times, applied once. The consumer must be idempotent: handling a message twice leaves the same state as handling it once. Some operations already are.

Kafka’s design notes say the same: when messages carry a primary key, a repeat overwrites a record with a copy of itself.

Increments are the trouble. Turn the delta into a row: “credit ₹24 for payment pay_42” becomes an insert into cashback_credits with UNIQUE (payment_id), and the balance changes in the same transaction. Chris Richardson’s idempotent consumer pattern lists this option: keep the message’s key in the business row itself.

Idempotent is not order-safe. SET status = 'PAID' is harmless twice, but a copy redelivered after REFUNDED moves the payment back to PAID. Versions fix that (below); message ordering covers the rest.

Pick a dedupe key that survives redelivery

A dedupe key must be identical on every copy of one logical message and different for two genuinely different ones. The default is a producer-assigned event ID: a UUID created with the event, ideally the outbox row’s ID, carried in the body or a header. Relay resends, redeliveries and DLQ redrives don’t rewrite the payload, so it survives all three. A business key such as pay_42:CAPTURED also catches duplicates an upstream created with fresh IDs, such as a webhook sender retrying under a new event ID, but only where the domain has one.

AWS’s docs are explicit: a receipt handle belongs to one receive, and a redriven message gets a new ID. A RabbitMQ delivery tag identifies a delivery on one channel. Offsets stop re-reads, but a relay that published twice put its copy at a different offset.

Scope the key by consumer. Richardson’s table keys on (subscriberId, messageId). Two handlers sharing a table keyed by message ID alone would make the second skip work it never did.

The idempotent consumer: a dedupe row in the same transaction

One table remembers which messages each consumer has applied:

CREATE TABLE processed_messages (
  consumer     text        NOT NULL,   -- 'wallet-debit': one handler
  message_id   uuid        NOT NULL,   -- the producer's event id
  processed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer, message_id)
);

One rule uses it: insert the key and apply the effect in one transaction, then ack.

BEGIN;
INSERT INTO processed_messages (consumer, message_id)
VALUES ('wallet-debit', '0b7f6c2e-3d41-4a8e-9f0d-5c2a7e19b4d3')
ON CONFLICT DO NOTHING;
-- command tag INSERT 0 1: new message, carry on
-- command tag INSERT 0 0: duplicate, so ROLLBACK and ack

UPDATE wallets
SET    balance_paise = balance_paise - 49900
WHERE  user_id = 'u_17';
COMMIT;
-- ack the broker only after COMMIT returns

ON CONFLICT DO NOTHING turns a unique-key violation into “insert nothing”, and the command tag reports the rows inserted, so the driver returns 1 or 0. Each detail closes a gap:

• One transaction. If the debit fails, the key rolls back too and the redelivery retries cleanly. Commit the key alone and a crash before the debit loses the payment; commit the debit alone and a crash before the key debits twice. Amazon’s Builders’ Library states the same rule for APIs: record the token and every mutation in one ACID operation.
• Key first. A duplicate then costs one index lookup, and a concurrent copy waits at the first statement (next section).
• Ack after commit. A crash before the ack still redelivers, but the insert now returns 0 rows.
• Ack the duplicate. Treat 0 rows as success; an error makes the broker redeliver until the message is dead-lettered.

If every message creates exactly one business row, the key can live on that row instead; the side table earns its place when a message touches several rows, or none.

Two consumers, one message

Duplicates can also run at the same time. If consumer A outlives its visibility timeout (30 seconds by default on SQS), the queue hands m7 to B while A still works. If a Kafka consumer exceeds max.poll.interval.ms (5 minutes by default), the group rebalances and another member re-reads from the last committed offset.

The natural-looking code is check-then-act: SELECT the key, and if it is missing, do the work and INSERT the key. Both consumers can SELECT before either commits, both see nothing, both debit.

Insert-first under a unique key closes the race. When B inserts a key A has inserted but not committed, PostgreSQL makes B wait to see whether A commits. If A commits, B inserts 0 rows and skips; if A rolls back, B does the work. Exactly one debit commits. (At REPEATABLE READ or SERIALIZABLE, B may get a serialization error instead; retry it.)

Even check-then-act is rescued if the table has the unique key and the insert shares the debit’s transaction: B’s insert fails and its debit rolls back. Insert-first is still better, because B never does work it must throw away, or reaches a side effect it can’t undo.

The inbox pattern: store first, process later

With a processed-messages table you still ack after the work, so a slow handler holds its message, and its lease, throughout. The inbox pattern splits receiving from processing: a thin receiver inserts each message into an inbox table, deduplicating on insert, and acks at once; a processor works through the table separately.

CREATE TABLE inbox (
  consumer        text        NOT NULL,
  message_id      uuid        NOT NULL,
  ordering_key    text        NOT NULL,             -- e.g. the order id
  payload         jsonb       NOT NULL,
  status          text        NOT NULL DEFAULT 'PENDING',  -- PENDING, DONE, FAILED
  attempts        int         NOT NULL DEFAULT 0,
  next_attempt_at timestamptz NOT NULL DEFAULT now(),
  received_at     timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer, message_id)
);

-- Receiver: one statement, commit, then ack whether it inserted 1 row or 0.
INSERT INTO inbox (consumer, message_id, ordering_key, payload)
VALUES ($1, $2, $3, $4)
ON CONFLICT DO NOTHING;

-- Processor: claim due work without blocking other processors.
SELECT consumer, message_id, payload
FROM   inbox
WHERE  status = 'PENDING' AND next_attempt_at <= now()
ORDER  BY received_at
LIMIT  50
FOR UPDATE SKIP LOCKED;

For each claimed row, the processor applies the effect and sets status = 'DONE' in one transaction: the idempotent consumer again, with the inbox row as the dedupe record.

1. The broker delivers m7.
2. The receiver runs the deduplicating insert and commits; a duplicate inserts 0 rows.
3. The receiver acks either way, milliseconds after delivery.
4. A processor claims due rows with SKIP LOCKED, so processors never share a row.
5. In one transaction it applies the effect and marks the row DONE.
6. On an error it counts the attempt and backs off; past the limit the row is parked as FAILED, with an alert.

What you gain:

• A fast ack. A 40-second handler no longer races a 30-second visibility timeout, so fewer duplicates are born.
• Retries on your terms. The backoff is a column, the same on every broker, and a poison message becomes a FAILED row instead of a blocked Kafka partition.
• Per-key order you control. Process only the oldest pending row per ordering_key, or give each processor a hash range of keys; plain SKIP LOCKED can reorder one order’s events, as with the outbox relay.
• Audit, replay and burst absorption. Replay is an UPDATE back to PENDING, and a spike lands at insert speed. Wolverine, a .NET framework, calls this store-and-forward: messages persisted at once and tracked until processed or dead-lettered.

What you pay:

• More writes. Insert, status update and delete: three row writes where the processed-messages table needs one. At 2,000 messages a second, 6,000 row writes a second, plus WAL.
• Latency and a worker. Polling every 200 ms adds about 100 ms on average.
• Your own dead-letter queue. The broker thinks everything was delivered, so alarm on FAILED rows and on the oldest PENDING row’s age.
• Durability. After the ack, the inbox row is the only copy; a failover that loses recent commits loses messages the broker already forgot.
• Stuck claims. Claim with an IN_PROGRESS status and a lease instead of row locks, and a too-short lease replays running work, a trap Wolverine’s docs warn about.

Careful: in MassTransit’s consumer outbox and NServiceBus, “inbox” means the dedupe ledger used while processing, with the ack after the work. Say which you mean.

Reach for it when handlers are slow or bursty, when you need per-key order the broker can’t give, or when one bad message must not stall a partition. Otherwise the processed-messages table is enough.

Other places to keep dedupe state

Conditional and versioned writes

If every event carries the aggregate version it produced, the business row can dedupe for you:

UPDATE order_status_view
SET    status = 'DELIVERED', version = 8
WHERE  order_id = 'ord_9f3' AND version = 7;
-- 1 row: applied. 0 rows: read the stored version to see why.

On 0 rows, read the stored version. 8 or higher: a duplicate or stale copy, so ack and skip. Below 7: version 7 is missing, so retry later. If events carry the full new state, accepting any version newer than the stored one is enough; if they are deltas (“add ₹100”), you need exactly 7, and gaps must wait. This is optimistic locking with the producer supplying the version: one check gives dedupe and stale-update protection, but the producer must number each aggregate’s events, and messages that change no versioned row get no help.

DynamoDB: the condition inside a transaction

In DynamoDB, TransactWriteItems is all-or-nothing, so the dedupe item and the effect become two actions in one call:

{
  "TransactItems": [
    { "Put": { "TableName": "processed",
               "Item": { "pk": { "S": "rewards#e-91" } },
               "ConditionExpression": "attribute_not_exists(pk)" } },
    { "Update": { "TableName": "wallets",
                  "Key": { "pk": { "S": "u_17" } },
                  "UpdateExpression": "ADD cashback_paise :c",
                  "ExpressionAttributeValues": { ":c": { "N": "2400" } } } }
  ]
}

A duplicate fails the condition, the whole transaction is cancelled with a ConditionalCheckFailed reason, and the handler acks. DynamoDB performs two underlying writes per item (prepare and commit), charged even on cancellation, so two small items cost 2 × 2 = 4 write units per message instead of 1. The call’s ClientRequestToken is different: it makes a retry of the same call idempotent for 10 minutes, not a redelivery days later.

Redis: a pre-filter, never the authority

SET seen:e-91 1 NX EX 86400 is tempting: one fast round trip, self-expiring. But Redis and your database share no transaction. Write Redis first and a crash before the database commit means the redelivery finds the key and skips: the effect never happens. Write the database first and a crash before the SET lets the duplicate through to apply twice, unless something else checks. Redis also forgets: keys are evicted under memory pressure, and a failover to an asynchronous replica can drop recent writes.

The safe shape is a cache of “done” in front of the database check: GET first and skip on a hit; on a miss, run the transaction with its unique key, and SET only after the commit. Every failure falls through to the database.

It pays off only when duplicates are common, say a day-long replay, and the database is the bottleneck; see Redis.

How long to remember a message

A dedupe record protects you only while it exists. NServiceBus puts the rule well: after retention lapses, a retried message looks new, so retention must outlast the longest time a message can still be retried, manual replays included.

• SQS with a DLQ. Queues keep messages up to 14 days; a dead letter redriven on its last day arrives two weeks after the first copy, with a new message ID. Keep 15 days.
• Kafka. Topics keep data 7 days by default (log.retention.hours=168), and resetting a group to the earliest offset replays all of it: keep a week plus a margin. For compacted or forever-retained topics no window is enough; use versions or stored offsets.

Then size it with NServiceBus’s formula, records = rate × period. At 500 messages a second, 500 × 86,400 = 43.2 million rows a day, × 15 days = 648 million rows. A PostgreSQL row spends about 28 bytes on header and line pointer before any data; add a 16-byte UUID, a timestamp, a consumer name and a primary-key index entry, and call it 100 bytes: 648 million × 100 bytes ≈ 65 GB. Fine for one table, if old rows go cheaply: batched deletes leave dead rows for vacuum, while dropping a daily partition costs almost nothing (table partitioning).

Side effects outside your database

The dedupe row protects only what commits with it. An email, SMS or payment-gateway call can’t join your transaction, and both orders fail: call, then commit, and a crash in between makes the redelivery call again; commit, then call, and the redelivery finds the key and never calls.

Fix 1: pass an idempotency key downstream, derived from the message (say refund: plus the event ID) so every redelivery sends the same one. Stripe is the reference design: send an Idempotency-Key header (up to 255 characters) on a POST, and Stripe saves the status code and body of the first request once it starts executing and replays them for every retry, 500 errors included. The same key with different parameters is an error, and a 409 means a request with that key is still running. Call first, then commit your dedupe row with the provider’s result; a crash anywhere repeats the call, and the provider replays its answer. (Idempotency covers the API side.)

Fix 2: record the intent, then send from a state machine. In the message’s transaction, insert notifications (event_id, channel, status = 'PENDING') under UNIQUE (event_id, channel), so the intent exists exactly once. A sender moves it to SENDING, calls the provider and stores SENT with the provider’s ID. A crash in SENDING forces a choice: reconcile if the provider can look up your reference; otherwise risk a duplicate (resend) or a gap (skip). Resend a one-time password; a duplicate “₹5,000 debited” alert is merely annoying. Without provider help, an external effect stays at-least-once or at-most-once: the state machine shrinks the window to one call, but can’t close it.

Choosing a strategy

In practice you combine them: a processed-messages table or inbox as the backbone, versions where order matters, a key on every external call, Redis only if profiling demands it.

Kafka transactions versus an idempotent consumer. Kafka transactions commit output records and input offsets atomically, but only Kafka to Kafka, for read_committed readers (delivery guarantees has the details). When the output is a database row, store the (partition, offset) in the same transaction and seek to it on restart; that stops re-reads but not upstream duplicates at new offsets. An idempotent consumer works on every broker and catches both, at the cost of a table to size and clean.

End to end: outbox in, dedupe out

Effectively-once is two promises kept at opposite ends. Nothing is lost: the producer’s transactional outbox makes the state change and the event one commit, and every later hop retries until acked. Nothing is applied twice: the consumer’s dedupe row turns every extra copy into a no-op. Drop the outbox and events go missing; drop the dedupe and the retries that saved them become double effects.

Walk one payment through it. A customer pays ₹1,200 by UPI; the payments service commits pay_42 as SUCCESS plus an outbox row PaymentSucceeded, event ID e-91, in one transaction. Rewards gives 2% cashback: ₹1,200 × 0.02 = ₹24.

1. The relay resends. It publishes e-91, crashes before marking the row, and publishes again. On copy 1, Rewards inserts (rewards, e-91), a cashback_credits row under UNIQUE (payment_id), the ₹24 wallet credit and its own outbox row CashbackCredited, all in one transaction. Copy 2 inserts 0 rows and is acked.
2. A redrive repeats it. A deploy bug dead-letters a batch holding a third copy; ops redrives on day 3. SQS gives it a new message ID, but the body still says e-91 and the 15-day window still holds the key: 0 rows, ack.
3. The SMS goes out once, if the provider helps. Notifications records the intent (e-91, sms) once and sends with a key derived from it.

Three deliveries, one ₹24 credit. The UNIQUE (payment_id) is belt and braces: it stops a second credit even if a buggy upstream emits the payment twice under two event IDs.

Going deeper: what idempotency does not fix

Ordering. The dedupe table knows m7 was applied, not that m9 should wait for m8. Add versions, and see message ordering.

Non-deterministic handlers. A handler that reads the clock, a random number or another service’s current state can decide differently on a retry. If the first attempt texted “₹24 cashback” and failed before committing, and the rate then moved to 3%, the retry commits ₹36. Put decisions in the event, or record them before acting.

Partial effects across two stores. A handler that writes Postgres and a search index is only half protected: crash between them and the retry either skips the index (the key exists) or writes it twice. Make the extra write an upsert keyed by entity ID and run it before the commit, or feed secondary stores from an outbox or change data capture.

Failures marked as done. A handler that catches an exception and still commits the dedupe row has turned a failed message into a forgotten one.

Duplicates with different IDs. A customer who taps Pay twice creates two events with two UUIDs. Stop that at the API with a client-supplied key, or with a business key.

Copies older than the window. Compare the event’s occurredAt with the clock, and refuse and alert on anything older than retention.

Cheat sheet

How to say it in the interview

“The broker gives me at-least-once, so I make the effect idempotent instead of fighting duplicates. Each event carries a UUID from the producer’s outbox. In one Postgres transaction the consumer inserts its name and the event ID into a processed-messages table with a primary key, applies the effect, commits, then acks. A redelivery inserts zero rows and is just acked, and two consumers racing on one message serialise on the key. I keep keys longer than any replay, about 15 days with SQS and a DLQ, in partitions by event date. External calls get a key derived from the event ID, plus my own record, because the provider’s window is shorter.”

Why not just use Kafka’s exactly-once? It covers Kafka to Kafka. My effect is a database row and an SMS: the row needs the dedupe key or offset in its transaction, the SMS an idempotency key.

Two consumers get the same message at once. What happens? Both insert the key first. PostgreSQL makes the second wait for the first transaction; if it commits, the second inserts zero rows and skips; if it rolls back, the second does the work. One effect either way.

How long do you keep dedupe records, and how big does that get? Longer than any redelivery, DLQ redrives and offset resets included. At 500 messages a second for 15 days: 648 million rows, about 65 GB. I partition by event date and drop old partitions.

Why not Redis SET NX? It isn’t in my transaction. Set it first and a crash loses the message; set it after and a crash lets the duplicate through. At most it caches committed keys in front of the database check.

When would you add an inbox table? For slow or bursty handlers, per-key order or my own retry schedule, or so one poison message can’t stall a partition. I pay extra writes, a poller and my own dead-letter handling.

Where to go next

• Delivery guarantees: where duplicates come from, Kafka transactions and DLQs.
• Transactional outbox: the producer half of effectively-once.
• Idempotency: idempotency keys for HTTP APIs.
• Message ordering: what dedupe can’t give you.
• Message queues: leases, visibility timeouts and competing consumers.
• Optimistic vs pessimistic locking: version checks in depth.
• Database constraints: the unique keys that do the real work.
• Design a payment system: all of this under real money.

References and further reading

1. Pattern: Idempotent Consumer (microservices.io, Chris Richardson)microservices.ioThe pattern: record processed message IDs in the database, keyed by (subscriber, message ID), or keep them in the business entities themselves.
2. Making retries safe with idempotent APIs (Amazon Builders’ Library)aws.amazon.comCaller-provided request identifiers, recording the token and the mutation in one ACID step, and returning an equivalent response to a repeat.
3. Stripe API reference: Idempotent requestsdocs.stripe.comThe Idempotency-Key header, keys up to 255 characters, saved responses replayed including 500 errors, and v1 keys pruned after at least 24 hours.
4. Amazon SQS Developer Guide: Configure a dead-letter queue redrivedocs.aws.amazon.comRedriven messages are treated as new: a new message ID and enqueue time, with retention reset.
5. Amazon SQS Developer Guide: Queue and message identifiersdocs.aws.amazon.comA receipt handle belongs to one receive, not to the message, and changes every time the message is received.
6. Apache Kafka 4.2 documentation: Design (message delivery semantics)kafka.apache.orgAt-least-once on consumer failure, updates keyed by a primary key being idempotent, and storing the consumer’s offset in the same place as its output.
7. PostgreSQL documentation: INSERT (the ON CONFLICT clause)postgresql.orgON CONFLICT DO NOTHING and DO UPDATE, unique-index arbiters, and the INSERT command tag that reports how many rows were inserted.
8. NServiceBus documentation: Outboxdocs.particular.netDeduplication by message ID in the business transaction, why native broker IDs are not safe keys, retention longer than any retry, and storage = throughput × period.
9. Wolverine documentation: Durable inbox and outbox messagingwolverinefx.netA store-and-forward inbox: incoming messages persisted immediately and tracked until processed, dead-lettered or expired, and the stale-claim timeout trap.
10. Amazon DynamoDB Developer Guide: Transactions, how it worksdocs.aws.amazon.comAll-or-nothing TransactWriteItems, cancellation on a failed condition, two underlying writes per item, and the 10-minute client token.