Idempotent Consumer Pattern Explained

Quick answer

An idempotent consumer makes repeated delivery of the same message produce one committed business effect. Give every event a stable message ID, then let each consumer insert (consumer_name, message_id) into a PostgreSQL processed_message table protected by a composite primary key. Insert that marker and perform the business mutation in the same database transaction. A successful insert owns the work; a unique conflict means this consumer already committed it.

The database constraint, not an earlier read or an in-memory cache, is the final correctness guard. A consumer acknowledges the broker only after the transaction commits. If the business update fails, PostgreSQL rolls back both the update and marker, and the message remains eligible for redelivery.

That rule gives duplicate safety for one database transaction. It does not make an email, payment API call, or other remote side effect atomic with PostgreSQL. Those boundaries need their own idempotency key, an outbox, or another protocol.

Failure timeline

Use one order flow throughout: OrderPlaced reserves inventory, InventoryReserved authorizes payment, PaymentAuthorized triggers notification. The producer publishes stable event IDs through a broker-confirmed transactional outbox, and the broker provides at-least-once delivery.

Consider inventory receiving message evt-7:

Delivery A starts a transaction and claims evt-7.
It subtracts one unit from inventory.
PostgreSQL commits the marker and inventory change.
The process crashes before acknowledging the broker.
The broker redelivers evt-7 as delivery B.
B cannot insert the same marker, so it skips the subtraction and acknowledges.

Without the marker, inventory falls twice. If A wrote the marker before starting the business transaction, a crash between those operations would permanently skip the reservation. If A wrote the marker after the business transaction, a crash between commit and marker creation would apply it twice. The only safe local invariant is all-or-nothing commit of marker and business state.

This pattern accepts that redelivery is normal. It turns the broker’s uncertain acknowledge window into a deterministic database decision.

Why check-then-act is unsafe

A tempting implementation runs SELECT to ask whether evt-7 exists, performs the update when no row is found, and then inserts the marker. Two simultaneous deliveries can both observe “not processed” before either inserts. Both subtract inventory. One later loses the unique-constraint race, but the damage has already happened if the inventory update committed separately.

Application locks do not repair the general problem. Two pods have different memory, a process can restart, and a rebalance can move delivery to another host while the database still must commit the business state.

An in-memory cache is useful as a best-effort fast path for a hot duplicate storm, but it cannot be the final correctness guard. Cache eviction, restart, replication delay, and a cache/database split-brain can all admit a duplicate. It may reduce database traffic; it must never authorize the mutation.

Instead, attempt the insert. Let PostgreSQL arbitrate. INSERT ... ON CONFLICT DO NOTHING converts the uniqueness decision into a normal result that can be tested inside the transaction.

The database invariant

For a consumer named inventory-reservation-v1 and message evt-7, exactly one committed row may exist:

processed_message("inventory-reservation-v1", "evt-7")

If that row commits, the corresponding inventory mutation must also commit. If the mutation rolls back, the row must not exist. Therefore:

committed marker for consumer C and message M
    implies
committed local business effect for C and M

The reverse is enforced by transaction structure: the code never executes the business mutation unless its marker insert affected one row. This is a local atomicity guarantee, not a claim that the broker and database share a transaction.

consumer_name is part of the key because one event can legitimately drive several handlers. Inventory, payment, analytics, and notification must each process evt-7 once. A primary key on message_id alone would let the first consumer suppress all others. Use a stable logical name, not a pod ID. Version the name only when a deliberate replay should run a new consumer behavior.

PostgreSQL processed-message schema

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

CREATE TABLE inventory (
    sku       text   PRIMARY KEY,
    available bigint NOT NULL CHECK (available >= 0),
    reserved  bigint NOT NULL CHECK (reserved >= 0)
);

A PostgreSQL primary key creates the composite unique index that serializes competing inserts. Both key columns are non-null, so no message can bypass equality through null semantics.

The claim statement is:

INSERT INTO processed_message (consumer_name, message_id)
VALUES ($1, $2)
ON CONFLICT DO NOTHING
RETURNING message_id;

One returned row means this transaction owns processing. Zero rows means another transaction committed the same key. Do not use ON CONFLICT DO UPDATE merely to obtain a row: that creates needless writes and makes “inserted versus duplicate” harder to read.

For stronger business protection, add an operation record such as inventory_reservation(order_id, sku, quantity) with UNIQUE (order_id, sku). The message marker identifies a delivery; the reservation key identifies the business fact. They address different duplicate classes.

Java Spring Boot implementation

Keep the transactional method in a Spring-managed service. JdbcTemplate participates in the transaction associated with its configured DataSource.

public record OrderPlaced(
    String id,
    String orderId,
    String sku,
    long quantity
) {}

public enum ApplyResult { APPLIED, DUPLICATE }

@Service
public class InventoryTransaction {
    private static final String CONSUMER = "inventory-reservation-v1";
    private final JdbcTemplate jdbc;

    public InventoryTransaction(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional(
        propagation = Propagation.REQUIRES_NEW,
        rollbackFor = Exception.class
    )
    public ApplyResult apply(OrderPlaced event) {
        int claimed = jdbc.update("""
            INSERT INTO processed_message (consumer_name, message_id)
            VALUES (?, ?)
            ON CONFLICT DO NOTHING
            """, CONSUMER, event.id());

        if (claimed == 0) {
            return ApplyResult.DUPLICATE;
        }

        int changed = jdbc.update("""
            UPDATE inventory
               SET available = available - ?,
                   reserved = reserved + ?
             WHERE sku = ?
               AND available >= ?
            """,
            event.quantity(),
            event.quantity(),
            event.sku(),
            event.quantity());

        if (changed != 1) {
            throw new IllegalStateException(
                "Unknown SKU or insufficient inventory for " + event.orderId()
            );
        }

        return ApplyResult.APPLIED;
    }
}

The listener calls the separate service through its Spring proxy and only then acknowledges:

@Component
public class OrderPlacedListener {
    private final InventoryTransaction transaction;

    public OrderPlacedListener(InventoryTransaction transaction) {
        this.transaction = transaction;
    }

    public void onMessage(OrderPlaced event, Acknowledgment ack) {
        ApplyResult result = transaction.apply(event);
        // REQUIRES_NEW committed before apply() returned.
        ack.acknowledge();
    }
}

REQUIRES_NEW, unlike default REQUIRED, always opens an independent physical transaction and suspends any ambient transaction. Spring commits that inner transaction as the interceptor exits, before apply() returns. Therefore the acknowledgment call cannot run before this database commit. If the update or commit fails, the method throws and the listener leaves the delivery unacknowledged. rollbackFor = Exception.class also covers checked processing failures.

Configure manual acknowledgment and avoid self-invocation, which can bypass proxy interception. If an outer transaction already holds a connection, REQUIRES_NEW needs another; size the pool above concurrent listener demand.

Node.js TypeScript implementation

The official node-postgres transaction guide requires every statement in a transaction to use the same checked-out client. pool.query() calls may use different connections, so reserve one client from BEGIN through COMMIT or ROLLBACK.

import { Pool } from "pg";

const pool = new Pool();
const CONSUMER = "inventory-reservation-v1";

type OrderPlaced = {
  id: string;
  orderId: string;
  sku: string;
  quantity: number;
};

type ApplyResult = "applied" | "duplicate";

export async function applyOrderPlaced(
  event: OrderPlaced
): Promise<ApplyResult> {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const marker = await client.query<{ message_id: string }>(
      `INSERT INTO processed_message (consumer_name, message_id)
       VALUES ($1, $2)
       ON CONFLICT DO NOTHING
       RETURNING message_id`,
      [CONSUMER, event.id]
    );

    if (marker.rowCount === 0) {
      await client.query("COMMIT");
      return "duplicate";
    }

    const mutation = await client.query(
      `UPDATE inventory
          SET available = available - $1,
              reserved = reserved + $1
        WHERE sku = $2
          AND available >= $1`,
      [event.quantity, event.sku]
    );

    if (mutation.rowCount !== 1) {
      throw new Error(
        `Unknown SKU or insufficient inventory for ${event.orderId}`
      );
    }

    await client.query("COMMIT");
    return "applied";
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

The broker adapter awaits applyOrderPlaced(event), then acknowledges for either "applied" or "duplicate". If the promise rejects, it must not acknowledge. The retry or dead-letter policy owns the next step. Parameter placeholders keep message data separate from SQL text.

Do not acknowledge from inside the transaction function before COMMIT succeeds. Commit itself can fail because of a lost connection, serialization error, or deferred constraint. Only a successfully resolved commit justifies acknowledgment.

Business idempotency versus message deduplication

Message deduplication asks, “Has consumer C committed message ID M?” It protects a redelivery whose ID is unchanged. Business idempotency asks, “Has this logical operation already happened?” Those questions are not equivalent.

Suppose a producer bug emits two different IDs for the same OrderPlaced fact, or an operator reconstructs an event during recovery with a new ID. Both pass the processed_message key. A business key such as (order_id, sku) on inventory_reservation can still prevent the second reservation. Likewise, a payment provider should receive an idempotency key derived from the payment attempt, not merely trust the broker message ID forever.

Conversely, do not define the message key as order_id without analysis. One order may have valid events for placement, amendment, cancellation, and retry of a new payment attempt. Collapsing them can suppress real work. Preserve the producer’s stable event ID for message deduplication and model business invariants explicitly in business tables.

Concurrency, rollback, and ordering

The following winner behavior assumes PostgreSQL’s default READ COMMITTED isolation. When two concurrent transactions insert the same composite key, the unique index is the arbiter. One insert wins; the other may wait for its outcome.

If the winner commits, the waiting ON CONFLICT DO NOTHING returns zero rows. The loser skips the inventory update and commits an empty duplicate path. If the winner rolls back, its marker disappears; the waiting insert can succeed and safely perform the mutation. There is no window in which both transactions own the key.

If the winner inserts the marker and its inventory update throws, rollback removes both changes. The broker must not receive an acknowledgment. A later delivery can claim the same ID and try again. This rollback-before-ack behavior prevents a failed attempt from becoming a permanent false duplicate.

At REPEATABLE READ or SERIALIZABLE, a concurrent change can instead abort the attempt with serialization failure SQLSTATE 40001. Catch that code outside the transaction and perform a bounded retry of the whole transaction, from marker insert through business mutation, using the same message ID. Apply backoff and jitter. Never retry only the statement that failed, because the transaction is aborted. After the limit, leave the broker delivery unacknowledged so normal retry or dead-letter policy can take over.

Idempotency does not provide ordering. OrderCancelled arriving before OrderPlaced, or version 4 arriving before version 3, needs aggregate versions, state-transition constraints, partitioning, or buffering. Deduplicating each message once can still apply valid messages in the wrong sequence.

Retention and replay

The marker table grows continuously, so retention needs an explicit contract. Deleting markers after 30 days asserts that the broker, backups, replay tooling, and operators will never redeliver an older message to that consumer. If a six-month replay occurs after marker deletion, old effects can run again.

Choose retention from the longest plausible retry and replay horizon, not only the broker’s normal message retention. Archive or partition rows when audit needs exceed the hot-table window. Before deletion, document which event streams can be replayed, from what timestamp, under which consumer name, and what business constraints remain.

A new consumer version sometimes needs to reprocess history. Giving it a new consumer_name deliberately bypasses old markers, so treat renaming as a migration with impact analysis. Reusing the old name skips previously processed events. Changing names accidentally repeats all retained history.

DLQ replay must preserve the original stable message ID. Generating a new ID defeats message deduplication. If replay is intended to repeat business work, use an explicit repair command with its own audited business key rather than silently rewriting identity.

Testing strategy

Test the database invariant against real PostgreSQL:

Deliver one event twice sequentially. Assert one marker and one inventory decrement.
Start two transactions for the same (consumer_name, message_id) using separate connections and release them together. Assert exactly one reports "applied".
Force the business update to fail after marker insertion. Assert both marker and mutation are absent after rollback.
At READ COMMITTED, pause the first transaction after inserting, then commit it. Assert the second waits and returns "duplicate".
At READ COMMITTED, repeat but roll back the first. Assert the second acquires the key and applies once.
Process one message ID with two consumer names. Assert both local handlers can commit.
Simulate a crash after commit but before acknowledgment, redeliver, and assert no second effect.
At REPEATABLE READ and SERIALIZABLE, force SQLSTATE 40001. Assert bounded whole-transaction retry keeps the same message ID and never acknowledges a failed attempt.

Unit tests can cover adapter decisions, but a mock cannot reproduce PostgreSQL unique-index waiting and rollback visibility. Keep at least the concurrency cases as integration tests.

Also test semantic duplicates with different message IDs against business constraints, and test out-of-order events separately. Those failures are outside message deduplication.

Monitoring and operations

Record outcomes as applied, duplicate, retryable_failure, and terminal_failure, labeled by consumer and event type. Track duplicate rate, transaction latency, unique-conflict waits, rollback count, oldest unacknowledged delivery, and marker-table growth.

A duplicate-rate spike can mean normal recovery after an outage, an acknowledgment failure, a producer resending events, or a consumer crash loop. It is a signal, not automatically an error. Correlate it with broker redelivery headers and database latency.

Log message ID, consumer name, event type, and trace ID, but avoid full sensitive payloads. Provide an operator query that proves whether a marker exists and a business query that proves the corresponding effect. A marker without its local effect indicates code or manual data manipulation violated the invariant and deserves immediate investigation.

Common mistakes

Checking with SELECT and then acting instead of letting a unique constraint arbitrate.
Storing markers in Redis or process memory while the authoritative mutation is in PostgreSQL.
Committing the marker and business update in different transactions.
Acknowledging before the database commit returns successfully.
Using only message_id as the key and suppressing other legitimate consumers.
Using pod names as consumer_name, which makes every restart look like a new consumer.
Swallowing exceptions inside a transactional method so a partial attempt commits.
Assuming deduplication makes remote payment or email calls atomic.
Treating message IDs as business keys or business keys as universal message IDs.
Deleting markers without accounting for old DLQ or disaster-recovery replay.
Assigning new message IDs during ordinary replay.
Assuming duplicate safety also solves event ordering.

Practical checklist

Require a stable, non-null event ID at the producer boundary.
Choose a stable logical consumer_name and document version changes.
Create processed_message with PRIMARY KEY (consumer_name, message_id).
Attempt INSERT ... ON CONFLICT DO NOTHING; do not check first.
Run marker insert and local business mutation in the same transaction.
Treat zero inserted rows as a successful duplicate no-op.
Roll back marker and business changes on every processing failure.
Acknowledge only after commit; leave failures unacknowledged.
Add domain-specific unique constraints for business idempotency.
Pass idempotency keys to remote systems that support them.
Test simultaneous delivery with separate real database connections.
Test both winner-commit and winner-rollback races.
Retry SQLSTATE 40001 from the transaction boundary with the same message ID.
Keep replay IDs unchanged and audit intentional reprocessing.
Align marker retention with the maximum retry and replay horizon.
Monitor duplicate outcomes, rollbacks, contention, and table growth.

Frequently asked questions

Does a unique constraint alone make the consumer idempotent?

Only when code uses its result correctly and the marker shares the business transaction. A separate marker commit can lose work; a marker after the mutation can repeat work.

Should a duplicate delivery be acknowledged?

Yes, after the transaction confirms the marker already exists. The desired effect is already committed, so retrying the same delivery adds no value.

Can Redis replace the PostgreSQL marker table?

Not as the correctness guard for a PostgreSQL mutation. Without a shared atomic commit, cache state and business state can diverge. Redis may be a non-authoritative optimization.

What if the handler calls a payment provider?

The database transaction cannot atomically commit the provider call. Use the provider’s idempotency key and usually write an outgoing command to a transactional outbox. Model the provider result as another event.

Sources

Continue through the System Design learning path and browse topic clusters.
Start with durable publication in Transactional Outbox Pattern Explained.
Compare acknowledgment guarantees in Message Delivery Semantics Explained.
Apply the invariant during recovery in Dead-Letter Queue Explained.
Coordinate longer workflows with Saga Pattern and Compensating Transactions Explained.