Idempotent consumer: receiving duplicate messages without writing twice (Kafka, RabbitMQ) Intermediate

Estimated reading time: 7 min read

Read first

ACID in PostgreSQL: what is guaranteed and what is not?

The problem

A consumer receives OrderPaid, adds 12,500 to the merchant balance and commits the database. The process dies before telling the broker (Kafka: commit offset; RabbitMQ: basic.ack). The broker redelivers the same event, the handler adds it again, and the balance becomes 25,000.

No message was lost and there was no SQL error. This is at-least-once, exactly as designed: the database write and the broker acknowledgment are two places that store progress, with no shared transaction. The fix is to make the side effect idempotent.

Choosing a dedupe approach

Approach	How it works	When it fits
Inbox table (this article)	Insert a unique `(consumer, message_id)` in the same transaction as the side effect	Default: every side effect lives in one database
Naturally idempotent	`SET status = 'paid'`, upsert by business key	“Set a value” operations, not “add to”
Versioned update	`UPDATE ... WHERE version = $expected`	Events ordered per aggregate
Idempotency key downstream	Send the message ID to the external API (payment gateway)	The side effect is not in your DB

If the operation is already naturally idempotent, no inbox is needed. “Add 12,500” is not.

Inbox table

The event ID must be stable across every delivery: the producer generates it when publishing (a UUID or an ID from the outbox); the consumer never generates it. Put the primary key on (consumer_name, message_id) so each consumer group has its own ledger.

CREATE TABLE inbox (
  consumer_name text        NOT NULL,
  message_id    text        NOT NULL,
  processed_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer_name, message_id)
);

One transaction: insert into the inbox and update the business data only if the insert actually added a row.

BEGIN;
WITH ins AS (
  INSERT INTO inbox (consumer_name, message_id)
  VALUES ('accounting', 'event-42')
  ON CONFLICT DO NOTHING
  RETURNING 1
)
UPDATE ledger SET balance = balance + 12500
 WHERE merchant_id = 1 AND EXISTS (SELECT 1 FROM ins);
COMMIT;

Run on PostgreSQL 17 (scratch database): the first run gives UPDATE 1, redelivering the same event-42 gives UPDATE 0, and the final balance is 12500. The application reads the affected row count (or checks the insert result) to log “duplicate skipped”, then acks as usual.

The unique constraint is what blocks the race between two processes. A SELECT check followed by INSERT is not enough: both workers see “not there yet”.

The marker insert and the business write must be in the same transaction. Split them, and a half rollback makes a redelivery skip work never done, or redo work already done.

Second delivery hits a conflict in the inbox, the ledger does not change

Broker → Consumerevent-42

Step 1/4

event-42 is delivered for the first time (redelivered = false on RabbitMQ; Kafka has not committed the offset).

Kafka and RabbitMQ perspectives

	Kafka	RabbitMQ
The gap	DB committed, offset not yet committed	DB committed, `basic.ack` not yet sent (or the channel closed)
Redelivery signal	No flag; you see an already processed offset again	`redelivered = true` flag on the delivery
Dedupe key	Event ID in the payload/header (not the offset, because of replays or topic changes)	Message ID or event ID in the payload
See also	Offset commit	Ack and prefetch

RabbitMQ’s redelivered flag only says “this message was delivered before and requeued”. Use it as an optimization: when false, an expensive check can be skipped; when true, the inbox check is mandatory.

Do not use it to replace the inbox: a job published twice is two different messages, both with redelivered = false.

Kafka exactly-once only covers the read-from-Kafka, write-to-Kafka flow. Writing to PostgreSQL, sending email or calling REST still needs idempotency at the destination. See Kafka transactions.

Dedupe table retention

The inbox keeps growing, and deleting too early lets duplicates through. Rule: keep rows at least as long as the longest period during which a message can be redelivered or replayed.

Source of duplicates	Window to cover
Crash, rebalance, redelivery	A few minutes to a few hours
Outbox relay publishing again	How long the relay can be down and recover
Replay from a dead queue or a Kafka offset reset	How far back you allow replays, usually the topic retention

Cleanup:

-- simple: delete in batches
DELETE FROM inbox WHERE processed_at < now() - interval '7 days';

For large tables, partition by processed_at and DROP PARTITION; it is much cheaper than DELETE and leaves no dead tuples. The 7 days above is an example; choose based on your replay window.

After deletion, an event older than the window that gets replayed will be applied again, so periodic reconciliation is needed.

Demo that was run

npx --prefix examples ts-node examples/src/idempotent-consumer/consumer-demo.ts

Calling unsafeHandle twice with the same message gives a balance of 25000. After a reset, handleInOneTransaction returns applied the first time and duplicate the next, and the balance stays 12500.

The demo uses an in-memory Map and Set, so it has no real atomicity or unique constraint; the SQL above is the real protection.

To verify the real design: have two processes handle the same eventId and assert only one applies; kill the process after the DB commit, before the ack, then check the balance and the inbox.

Real-world example

When to use / not use

Use an inbox: add, subtract or create-record side effects, where messages can be redelivered (almost always true).
No inbox needed: the operation is already idempotent (upsert by business key, setting a status).
Not enough: side effects outside the database (email, HTTP). Use an idempotency key at the destination or an outbox for that step.
Choosing the key: orderId is too coarse if one order has both a payment and a refund; use the event ID.

Production checklist

message_id is generated by the producer and stable across redeliveries; do not use offsets or consumer-generated UUIDs.
Primary key or unique constraint on (consumer_name, message_id).
Marker and business write in the same transaction.
Ack (or commit the offset) after the transaction commits.
Duplicates are acked as successes, with a log line and a metric.
Inbox retention exceeds the redelivery and replay window; a job cleans up or drops partitions.
Retry transient errors; send poison messages to a dead-letter, never commit blindly to make lag look good.
Integration test a crash after commit, before ack.

Common mistakes

Summary

Self-check: (1) What happens on a crash after the DB commit, before the ack? (2) Why is an in-memory Set not enough? (3) One order has a payment and a refund: is deduping by orderId correct?

References

#messaging #kafka #rabbitmq #consumer #idempotency #inbox #postgresql