What is the inbox pattern, and why isn't it exactly once delivery?

The inbox pattern is an idempotent consumer technique. Before a consumer applies a message's side effects, it records the message's ID in a Postgres table. If that ID is already there, the consumer skips the message. Brokers make this necessary because they redeliver messages in normal operation, not only in rare failures.

There are three common causes of redelivery:

  • A crash between processing and acknowledgment. The consumer commits its database write and dies before it acks. The broker never learns the work was done, so it hands the message to the next consumer.
  • Rebalances. When partitions or queues move between consumer instances, in-flight messages and uncommitted offsets get replayed on the new owner.
  • Producer retries. A producer that times out waiting for confirmation sends the message again. The broker may then hold two copies that carry the same payload.

At-least-once delivery is therefore the baseline to design for. The inbox pattern does not change that. Duplicates still arrive. What the pattern gives you is exactly-once effects, not exactly-once delivery.

The mechanism is simple. The consumer inserts the message ID into an inbox table inside the same transaction as the business write. A unique constraint on that ID rejects the second insert, and the whole transaction rolls back with it. Postgres does the deduplication, so application code has no check-then-act race to get wrong.

Use this pattern if your consumers write to Postgres and cannot tolerate duplicate state changes. Typical cases are charging a payment, decrementing inventory, and incrementing a counter.

How does the inbox pattern work inside a Postgres transaction?

The consumer does all of its work inside one database transaction and acknowledges the broker only after that transaction commits:

receive ─ BEGIN ─ INSERT inbox ─ business write ─ COMMIT ─ ack
               │◄──── crash A ────────────────►│◄ crash B ►│

After receiving a message, the consumer opens a transaction and runs INSERT INTO inbox ... ON CONFLICT DO NOTHING using the message ID. If the affected row count is zero, the message was already processed. The consumer skips the business logic, commits, and acks. If the count is one, it applies the business change in the same transaction, commits, and then acks.

Crash windows

  • Crash A (before commit): Postgres rolls back the inbox row and the business change together. The broker never received an ack, so it redelivers the message, and the retry starts clean.
  • Crash B (after commit, before ack): The broker redelivers. The inbox insert now conflicts, the row count is zero, and the handler skips the message.

Both windows are safe because the dedup record and the state change commit atomically. If you reverse the order and ack before commit, a crash between the two loses the message. The broker considers it delivered, and the database never stored it.

Concurrent duplicates

Suppose a rebalance or visibility timeout hands the same message to two consumers. The second consumer's INSERT finds the first transaction's uncommitted entry in the unique index and blocks. If the first transaction commits, the second gets the conflict and skips. If the first rolls back, the second insert succeeds and processes the message. The unique index serializes duplicates, so you don't need SELECT ... FOR UPDATE or advisory locks. This behavior assumes the default READ COMMITTED isolation level. Under REPEATABLE READ or SERIALIZABLE, the second transaction gets a serialization error instead and has to retry.

 Single delivery: one transaction, ack only after COMMIT

 receive ─► BEGIN ─► INSERT inbox ─► rows=1? ─► business ─► COMMIT ─► ack
                     ON CONFLICT     rows=0? ─► skip ─────────┘
                     DO NOTHING
           │◄──────────────────── crash A ───────────────────────►│◄crash B►│

  crash A: Postgres rolls back inbox row and business write together;
           no ack was sent, broker redelivers, retry starts clean
  crash B: both rows committed, no ack; broker redelivers,
           INSERT conflicts, rows=0, handler skips
  ack before COMMIT + crash in between = message lost

 Concurrent duplicates (READ COMMITTED)

 Consumer 1 ─► BEGIN ─► INSERT msg_id ─► holds uncommitted index entry
 Consumer 2 ─► BEGIN ─► INSERT msg_id ─► blocks on that index entry
                                            │
                         ┌──────────────────┴──────────────────┐
                         ▼                                     ▼
               Consumer 1 COMMITs                    Consumer 1 ROLLBACKs
               conflict: rows=0, skip                insert ok, process

  The unique index serializes duplicates; no FOR UPDATE or advisory locks

The inbox insert and the business write commit atomically before the broker ack, which makes both crash windows safe, and the unique index makes a concurrent duplicate wait for the first transaction's outcome.

Implementing the inbox table and consumer handler

The inbox table needs four columns: consumer_name text NOT NULL, message_id text NOT NULL, received_at timestamptz NOT NULL DEFAULT now(), and a nullable processed_at timestamptz. Declare PRIMARY KEY (consumer_name, message_id). The primary key does the deduplication. You don't need a separate unique index, and the key doubles as the lookup path for later cleanup.

The key is scoped per consumer because the same broker message often fans out to several services. If billing and notifications both record message_id = 'abc' in a shared inbox without consumer_name, whichever commits second would treat a legitimate first delivery as a duplicate and silently skip its work. With the composite key, each consumer gets its own deduplication namespace.

The handler transaction runs in three steps:

  1. BEGIN.
  2. INSERT INTO inbox (consumer_name, message_id) VALUES ($1, $2) ON CONFLICT DO NOTHING.
  3. If the reported row count is 1, run the business writes and UPDATE inbox SET processed_at = now() for that row. If it is 0, skip the business writes.

In either case, COMMIT and only then ack the broker. Acking before commit loses the message if the commit fails. Committing before acking at worst produces a redelivery, which the inbox row absorbs.

At the top of the transaction, run SET LOCAL lock_timeout and SET LOCAL statement_timeout. A duplicate that arrives while the first copy's handler is still running will wait on the uncommitted key. Without these timeouts, it holds a connection the whole time, and a burst of redeliveries can exhaust the pool. With them, the duplicate fails fast, gets nacked, and is retried after the original commits.

-- Composite key gives each consumer its own deduplication namespace
CREATE TABLE inbox (
  consumer_name text        NOT NULL,
  message_id    text        NOT NULL,
  received_at   timestamptz NOT NULL DEFAULT now(),
  processed_at  timestamptz,
  PRIMARY KEY (consumer_name, message_id)
);

-- Handler transaction for one delivery
BEGIN;
-- Fail fast if a duplicate is waiting on the first copy's uncommitted key
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';

INSERT INTO inbox (consumer_name, message_id)
VALUES ('billing', 'msg-7f3a9c')
ON CONFLICT DO NOTHING;            -- driver reports row count: 1 or 0

-- Run only when the row count was 1 (first delivery for this consumer)
INSERT INTO payments (order_id, amount_cents) VALUES ('ord-1042', 2599);
UPDATE inbox SET processed_at = now()
 WHERE consumer_name = 'billing' AND message_id = 'msg-7f3a9c';

COMMIT;

-- Pseudocode for the consumer:
--   tx = begin()
--   tx.exec("SET LOCAL lock_timeout = '2s'")
--   tx.exec("SET LOCAL statement_timeout = '10s'")
--   n = tx.exec(INSERT ... ON CONFLICT DO NOTHING, consumer, msg.id)
--   if n == 1:
--       apply_business_writes(tx, msg)
--       tx.exec(UPDATE inbox SET processed_at = now() ...)
--   # n == 0: duplicate, skip business writes
--   tx.commit()        # on timeout or error: rollback, nack, let broker retry
--   broker.ack(msg)    # only after commit succeeds

The inbox DDL with a per-consumer composite primary key, plus a handler transaction that sets lock and statement timeouts, branches on the ON CONFLICT DO NOTHING row count, and acks the broker only after COMMIT.

How should you choose the message deduplication key?

The deduplication key has to identify the message as the producer meant it, not a particular delivery of it. That rules out anything the broker assigns. A RabbitMQ delivery tag is scoped to a channel and changes every time the message is redelivered. A Kafka offset is fixed for a record already written, but a producer retry without idempotence writes the duplicate at a new offset. Mirroring or re-producing into another topic assigns fresh offsets as well. Either way, the inbox sees two different keys and processes both.

Use an ID the producer assigns once and carries in the payload or headers. A UUID generated when the business event is created works, as does the outbox row's primary key. The ID must be generated before the send loop. A producer that builds a new UUID on each retry attempt defeats the pattern, and so does one that rebuilds the message from scratch after a timeout. Every retry then looks like a new message.

When upstream messages carry no ID, derive one deterministically. Hash a canonical serialisation of the fields that define the event's identity, such as order ID, event type and source timestamp. Include a field that distinguishes legitimate repeats. Without one, two genuine identical events collapse into one, for example a customer adding the same item twice in the same second. The second is silently dropped as a "duplicate," and nothing errors.

Key source Stable across retries Stable across replays Failure mode
Broker delivery tag No No Every redelivery processed again
Broker offset Only for idempotent producers No, if re-produced or mirrored Duplicates slip through
Producer UUID, generated per attempt No Yes Producer retries become new messages
Producer UUID or outbox row ID, generated once Yes Yes None inherent; depends on producer discipline
Content hash of business fields Yes Yes Legitimate repeats wrongly dropped
import hashlib
import json
import uuid


def publish_with_retry(send, event: dict, attempts: int = 3) -> None:
    # Assign the ID ONCE, before the retry loop, so every attempt carries
    # the same key and the consumer's inbox can recognise the duplicates.
    message = {"message_id": str(uuid.uuid4()), "payload": event}
    for attempt in range(attempts):
        try:
            send(message)  # same message object, same ID, on every attempt
            return
        except TimeoutError:
            # Wrong: message["message_id"] = str(uuid.uuid4()) here would
            # make each retry look like a brand-new message to the inbox.
            if attempt == attempts - 1:
                raise


def derived_message_id(event: dict) -> str:
    # Fallback when upstream messages carry no ID: hash only the fields
    # that define the event's identity, in a canonical serialisation.
    identity = {
        "order_id": event["order_id"],
        "event_type": event["event_type"],
        "source_ts": event["source_ts"],
        # Distinguishes legitimate repeats (e.g. the same item added twice
        # in the same second). Without it, the second event is silently
        # dropped as a "duplicate".
        "line_seq": event["line_seq"],
    }
    canonical = json.dumps(identity, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()

A producer that generates its message ID once before retrying, plus a deterministic SHA-256 key derived from canonical identity fields, including a sequence field so that legitimate repeats are not collapsed.

When should you use a store-then-process inbox instead?

In the store-then-process variant, the consumer handler does almost nothing: it inserts the raw message into the inbox (with ON CONFLICT DO NOTHING on the dedup key), commits, and acks the broker. A separate worker processes the rows later. Inside a transaction, it claims a batch with SELECT id, payload FROM inbox WHERE processed_at IS NULL ORDER BY received_at LIMIT 50 FOR UPDATE SKIP LOCKED, runs the business logic, and sets processed_at = now() before committing. SKIP LOCKED lets several workers poll the same table without blocking each other or claiming the same row twice.

When the decoupling helps

  • Slow handlers. If business logic takes seconds, it no longer holds a broker delivery open.
  • Broker ack deadlines. If a message is not acked within the visibility timeout, the broker redelivers it. Acking right after a cheap insert avoids redelivery storms.
  • Bursts. Postgres absorbs spikes, and workers drain the backlog at whatever rate downstream systems can handle.

What it costs

  • Polling latency. Messages wait up to one poll interval before processing starts.
  • A second failure point. The worker needs its own deployment, monitoring, and alerting on backlog age.
  • Poison messages. A row that always fails will be reclaimed forever unless you track an attempts counter and move it to a dead-letter state once it passes a threshold.
  • Long transactions. A row stays locked for as long as its transaction is open. Keep claim transactions short, or use a lease column (claimed_until) so a crashed worker's rows become claimable again.

Neither variant guarantees ordering. Messages that arrive or commit out of order still need version checks or per-key sequencing.

CREATE TABLE inbox (
  id            bigserial PRIMARY KEY,
  message_id    text NOT NULL UNIQUE,   -- broker-assigned dedup key
  payload       jsonb NOT NULL,
  received_at   timestamptz NOT NULL DEFAULT now(),
  attempts      int NOT NULL DEFAULT 0,
  claimed_until timestamptz,            -- lease: expires if a worker crashes
  processed_at  timestamptz,
  dead_at       timestamptz             -- set once attempts pass the threshold
);
-- Keeps the claim scan small as processed rows accumulate
CREATE INDEX inbox_pending ON inbox (received_at)
  WHERE processed_at IS NULL AND dead_at IS NULL;

-- Handler: cheap insert, commit, then ack the broker
INSERT INTO inbox (message_id, payload) VALUES ($1, $2)
ON CONFLICT (message_id) DO NOTHING;

-- Worker claim: its own short transaction, committed immediately.
-- Bumping attempts here means the count survives a crash or rollback.
UPDATE inbox
SET attempts = attempts + 1,
    claimed_until = now() + interval '5 minutes'
WHERE id IN (
  SELECT id FROM inbox
  WHERE processed_at IS NULL AND dead_at IS NULL
    AND (claimed_until IS NULL OR claimed_until < now())
  ORDER BY received_at
  LIMIT 50
  FOR UPDATE SKIP LOCKED   -- concurrent workers skip rows already being claimed
)
RETURNING id, payload, attempts;

-- Success: run in the same transaction as the business writes
UPDATE inbox SET processed_at = now() WHERE id = $1;

-- Failure: release the lease, or dead-letter after 5 attempts
UPDATE inbox
SET claimed_until = NULL,
    dead_at = CASE WHEN attempts >= 5 THEN now() END
WHERE id = $1;

A store-then-process inbox in Postgres. The handler only inserts and acks. Workers claim batches with FOR UPDATE SKIP LOCKED under a lease, count attempts, and dead-letter poison messages.

What does the inbox pattern cost, and when should you avoid it?

Every consumed message adds an insert, a unique-index entry, and WAL traffic to the business database. That extra work raises commit latency on the hot path. In high-throughput consumers, the steady churn of inserts and deletes leaves dead tuples behind, and autovacuum has to keep up with them. If it falls behind, the table and its index bloat, and dedup lookups slow down.

Retention is a correctness parameter

A pruned key cannot catch a duplicate. Inbox rows must outlive the broker's maximum redelivery window and any replay from an old offset.

Use this rule: retention ≥ max(redelivery window, oldest offset you might replay from) + safety margin.

If someone resets a consumer group to an offset older than your retention, the inbox will reprocess those messages without complaint.

Pruning

Avoid a single large DELETE. It holds locks for a long time, produces a burst of WAL, and leaves a large block of dead tuples for vacuum. There are two better approaches:

  • Batched deletes. Run a loop like DELETE FROM inbox WHERE id IN (SELECT id FROM inbox WHERE processed_at < now() - interval '14 days' LIMIT 5000) until it removes zero rows.
  • Time-based partitions. Range-partition on processed_at and drop expired partitions. Dropping a partition creates no dead tuples.

Side effects outside the transaction

HTTP calls, emails, and writes to other databases do not roll back with the transaction. You have two options:

  • Pass the message ID downstream as an idempotency key, so the receiving system can dedupe on its side.
  • Write the side effect to an outbox table in the same transaction and let a relay deliver it.

When to avoid the inbox pattern

  • The business write is already naturally idempotent, such as an upsert keyed on the entity.
  • The consumer writes to several stores, so no single transaction covers both the inbox row and the business write.
  • Coupling message consumption to the service database's availability is unacceptable.
  • At your throughput, the per-message write costs too much.

What are the alternatives to the inbox pattern, and how do you test it?

If one of those conditions applies, a lighter mechanism may cover the cases you need.

Alternative Guarantee strength Operational cost Where it breaks
Upsert keyed on business identity Strong, if every effect is a single keyed write Low: no extra table Side effects such as emails, counters, or calls to other services
Version or conditional update Strong for state transitions Low, but producers must carry versions Out-of-order delivery leads to rejected updates that need handling
Dedup cache with TTL Weak: best effort Extra infrastructure to run Cache eviction, restarts, and redeliveries after the TTL expires
Broker-level deduplication Depends on the broker and its configuration Low to adopt, but the semantics differ between brokers Usually bounded by a window, and it cannot see failures in your handler

Testing the inbox

Most production failures happen at the boundaries, so test those directly.

  • Inject duplicates on purpose. Publish the same message several times, both in a row and spread across batches. Assert that there is one inbox row and that the business change happened once.
  • Kill the consumer between commit and ack. Terminate the process after the transaction commits but before it acknowledges the message. The broker will redeliver. The redelivered message should hit the unique constraint and get acknowledged with no side effects.
  • Race parallel consumers. Have several consumers process the same message at the same moment. Exactly one should commit and the others should fail on the constraint. Check the business tables as well as the inbox, because a passing constraint does not prove the handler wrote nothing outside the transaction.

When the database constraint does the deduplication, you can delete the custom check-then-insert logic and the race conditions that come with it.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to inbox pattern, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.