Inbox Pattern in Microservices: Deduplicating Messages in the Database

What the inbox pattern solves: duplicates under at-least-once delivery

Most message brokers promise at-least-once delivery, and that promise is exactly what produces duplicates. A producer that times out waiting for an acknowledgment resends, even though the first send may have landed. A consumer charges a card, writes a row or calls an API, then crashes before it acks the message or commits its offset, so the broker hands the same message out again. A consumer group rebalance moves a partition to another instance before the old owner committed its progress. A queue's visibility timeout expires while a slow handler is still working, and a second worker picks the message up in parallel. None of these are bugs. Brokers redeliver on purpose because otherwise messages would be lost.

It helps to separate two guarantees. Exactly-once delivery across a network is not achievable end to end, because a sender can never tell a lost message apart from a lost acknowledgment. Exactly-once processing is achievable when every effect of handling a message lands in a single database that can commit atomically.

The inbox pattern relies on that atomicity. The consumer inserts the message ID into a processed-messages table inside the same transaction as the business change. The ID column has a unique constraint. If the message is delivered again, the insert fails on that constraint, the transaction rolls back or is skipped, and the redelivery does nothing.

You need this if your consumers do work that is not naturally idempotent, such as incrementing balances, appending ledger entries or creating orders.

Designing the processed messages table

The table needs only a few columns. The core is a composite primary key of message_id and consumer_name. Including the consumer name lets several handlers in the same service share one table. If an OrderPlaced event feeds both a billing handler and a notification handler, each records its own row and neither blocks the other. Add a received_at timestamp. Optional metadata, such as message type, a correlation ID or a payload hash, helps when debugging but plays no part in deduplication. A secondary index on received_at serves the time-based purge, so cleanup deletes old rows by range instead of scanning the whole table.

The dedup key must come from the producer. It can be a message ID assigned when the event was created, or a business idempotency key such as a payment request ID. Broker-side identifiers do not work. Delivery attempt counters, receipt handles and partition offsets describe a delivery, not the message. Some change on every redelivery, and offsets can shift when a message is republished or replayed into another topic. A key built from them treats each redelivery as new, and the duplicate gets processed.

This makes the consumer's guarantee depend on producer discipline. Deduplication fails silently in two cases:

• The producer mints a new ID on every retry. The same logical event arrives with two IDs, and both get processed.
• Two producers' IDs collide. A real message is discarded as a duplicate, which is worse because nothing errors.

Make producers generate the ID once, when the event is created. Use UUIDs or another scheme that is unique across all producers.

Copy-- PostgreSQL. One table shared by every handler in the service.
CREATE TABLE inbox_processed_messages (
    -- Producer-assigned ID (or business idempotency key), set once
    -- when the event is created. Never a broker offset, receipt
    -- handle or delivery attempt: those change on redelivery.
    message_id     text        NOT NULL,
    -- Which handler processed it, e.g. 'billing' or 'notifications'.
    -- Lets several consumers of the same event each record a row.
    consumer_name  text        NOT NULL,
    received_at    timestamptz NOT NULL DEFAULT now(),
    -- Optional metadata for debugging only; not part of dedup.
    message_type   text,
    correlation_id text,
    payload_hash   bytea,
    PRIMARY KEY (message_id, consumer_name)
);

-- Serves the time-based purge as a range delete, not a full scan.
CREATE INDEX inbox_processed_messages_received_at_idx
    ON inbox_processed_messages (received_at);

-- Inside the handler's transaction: claim the message first.
-- Zero rows inserted means this consumer already processed it.
INSERT INTO inbox_processed_messages
    (message_id, consumer_name, message_type)
VALUES ('3f6c2a9e-8b1d-4e7a-9c55-0d2f1b7e4a10', 'billing', 'OrderPlaced')
ON CONFLICT (message_id, consumer_name) DO NOTHING;

-- Periodic cleanup. Keep the window longer than the broker's
-- maximum redelivery or replay window.
DELETE FROM inbox_processed_messages
WHERE received_at < now() - interval '14 days';

A PostgreSQL inbox table keyed on a producer-assigned message ID plus consumer name, with a received_at index for the time-based purge and the insert-or-skip statement a handler runs inside its transaction.

How it works: the idempotent consumer transaction and ack ordering

The handler runs one database transaction in a fixed order: begin, insert the message ID into the inbox table, apply the business change, commit, then ack the broker. Inserting first makes the duplicate check and the claim a single atomic step. If the insert raises a unique constraint violation, or INSERT ... ON CONFLICT DO NOTHING reports zero rows affected, the message was already handled. Roll back, skip the work and ack.

Do not check with a SELECT first. Two consumers that receive the same message at the same moment can both see no row, both apply the change and both commit. With a unique constraint, the second insert waits on the first transaction's uncommitted row. If the first commits, the second hits the conflict. If the first rolls back, the second proceeds. Only one transaction can commit. The losing transaction rolls back as a whole, and because the business writes live in that same transaction, no partial changes are left behind. Under stricter isolation levels the loser may get a serialization failure instead of a conflict. Handle it like any transient error: roll back and retry.

Each crash point ends in a retry or a safe no-op:

• Before commit: the transaction rolls back, nothing is recorded, the broker redelivers, and the retry does the work.
• After commit, before ack: the broker redelivers, the insert conflicts, and the handler acks without repeating the work.

Keep the transaction short. The uncommitted inbox row blocks concurrent duplicates for as long as the transaction stays open, so keep slow calls out of it.

Copyimport psycopg2
from psycopg2 import errors

# Assumed schema: CREATE TABLE inbox (message_id text PRIMARY KEY, received_at timestamptz NOT NULL);

def handle(conn, msg_id, account_id, amount):
    """Apply a message at most once. Returns True when it is safe to ack."""
    with conn:  # psycopg2: commit on normal exit, roll back on exception
        with conn.cursor() as cur:
            # 1. Claim first. A concurrent duplicate blocks here on our uncommitted row.
            cur.execute(
                "INSERT INTO inbox (message_id, received_at) VALUES (%s, now()) "
                "ON CONFLICT (message_id) DO NOTHING",
                (msg_id,),
            )
            if cur.rowcount == 0:
                # Already committed by an earlier delivery: skip the work, just ack.
                return True
            # 2. Business change in the same transaction, so it commits or rolls back with the claim.
            cur.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (amount, account_id),
            )
    # 3. Committed. Only now is it safe to ack.
    return True


def on_delivery(conn, delivery, ack, requeue):
    # ack/requeue stand in for your broker client's acknowledge/redeliver calls.
    try:
        handle(conn, delivery["id"], delivery["account_id"], delivery["amount"])
    except errors.SerializationFailure:
        requeue(delivery)  # transient under stricter isolation: rolled back, retry later
        return
    except Exception:
        requeue(delivery)  # crash before commit: nothing recorded, redelivery redoes the work
        raise
    # If the process dies here (after commit, before ack), redelivery hits the
    # conflict above and acks without repeating the work.
    ack(delivery)

An insert-first inbox handler using psycopg2 and PostgreSQL's ON CONFLICT DO NOTHING, where zero rows affected marks a duplicate, the business update shares the claim's transaction, and the ack happens only after commit.

Variant: the store-then-process inbox

In this variant the consumer does almost nothing on receipt. It inserts the raw payload into the inbox table with status = 'pending', relies on the unique key on message ID to drop duplicates, commits, and acks. A separate worker later picks up pending rows, runs the business logic, and marks each row done in the same transaction as the domain changes.

The extra parts are worth it in three cases. First, when handlers are slow enough to exceed the broker's visibility or ack timeout, because the ack no longer waits on processing. Second, when you need replay: since raw payloads are stored, you can reset rows to pending after fixing a bug. Third, when you need ordering per aggregate: the worker can process rows in arrival order per aggregate ID even if the broker delivers them out of order across partitions.

The costs are real. Every message takes an extra hop, so latency goes up. The worker is another process to deploy, scale and alert on. Rows can get stuck in processing after a crash, so you need a claimed-at timestamp and a reaper that returns stale claims to pending. Several workers will also contend for the same rows unless claiming is lock-aware.

The claim query (shown separately) selects a small batch WHERE status = 'pending' ORDER BY received_at with FOR UPDATE SKIP LOCKED, then sets those rows to processing. SKIP LOCKED lets each worker move past rows that another worker has already locked instead of blocking on them. This ordering is global across the batch. Per-aggregate ordering needs an extra rule, such as skipping any aggregate that still has an earlier row in processing.

Copy-- PostgreSQL. Raw messages land here; the unique key drops duplicate deliveries.
CREATE TABLE inbox (
  id           bigserial PRIMARY KEY,
  message_id   text        NOT NULL UNIQUE,
  aggregate_id text        NOT NULL,
  payload      jsonb       NOT NULL,
  status       text        NOT NULL DEFAULT 'pending',  -- pending | processing | done
  received_at  timestamptz NOT NULL DEFAULT now(),
  claimed_at   timestamptz,
  claimed_by   text
);

-- Claim a batch in its own short transaction. SKIP LOCKED lets concurrent
-- workers pass over rows another worker has locked instead of waiting.
WITH claimed AS (
  SELECT id
  FROM inbox
  WHERE status = 'pending'
  ORDER BY received_at
  LIMIT 50
  FOR UPDATE SKIP LOCKED
)
UPDATE inbox i
SET status = 'processing', claimed_at = now(), claimed_by = 'worker-7'
FROM claimed c
WHERE i.id = c.id
-- RETURNING order is not guaranteed; sort by received_at in the worker.
RETURNING i.id, i.message_id, i.aggregate_id, i.payload, i.received_at;

-- Reaper: return claims abandoned by a crashed worker to the queue.
UPDATE inbox
SET status = 'pending', claimed_at = NULL, claimed_by = NULL
WHERE status = 'processing'
  AND claimed_at < now() - interval '5 minutes';

A PostgreSQL inbox table, a batch claim query using FOR UPDATE SKIP LOCKED to move pending rows to processing, and a reaper that releases stale claims.

When to avoid it and what to use instead

The inbox only holds when the business state and the processed messages table share one transactional store. If the handler writes to Postgres and also to a search index or cache, the dedup row covers only the Postgres side. A redelivery after a crash can still double-write the other store.

It also does nothing for side effects outside the database. An HTTP call, an email, or a published event happens before the commit. If the transaction rolls back or the process dies, the redelivered message repeats that side effect even though the inbox row was never written.

Poison messages are the other trap. A message that always throws rolls back the dedup insert along with everything else, so it is redelivered indefinitely. Count attempts, and after a limit route the message to a dead-letter path. Keep that path separate from dedup: "failed permanently" and "processed" are different states, and merging them hides failures.

Alternatives and complements

Retention, cleanup and performance cost

Every message that reaches the consumer adds a row to the processed messages table, and nothing removes it. Retention is therefore a correctness setting, not a housekeeping choice. The dedup window has to be longer than the longest time a duplicate can arrive. That includes the broker's maximum redelivery delay, any dead-letter replay process, and consumer offset resets or topic replays that operators might run. If an ID is purged before its last possible redelivery, that duplicate passes the check and is processed again, and nothing reports the error. Set the window from the longest replay you are willing to support, then add a margin.

Purging without hurting the hot path

Index the table on (message_id) for the lookup, plus an index on processed_at for the purge. Alternatively, make the clustering or primary key lead with the time column if your database supports that. Delete in small batches, for example a few thousand rows per transaction ordered by processed_at. This keeps lock duration, replication lag and undo/WAL volume bounded. At high volume, partition the table by time (daily or hourly) and drop whole partitions that are older than the window. Dropping a partition is cheap and leaves no dead rows behind for vacuum or compaction to process.

Throughput cost

Each message now costs an extra insert, an index entry, and a uniqueness check inside the business transaction. Under monotonic or time-ordered keys, a single high-volume topic can concentrate those inserts on one index page or partition and create a hotspot.

Illustrative estimate (assumed numbers, not a benchmark): rows retained = messages/sec × window seconds. At an assumed 2,000 msg/s and a 7-day window: 2,000 × 604,800 ≈ 1.2 billion rows. At an assumed 100 bytes per row including index overhead, that is roughly 120 GB.

Benchmark with your own message rates, key format and database before treating the cost as negligible.

Copy-- PostgreSQL. Dedup table: the primary key serves the lookup,
-- the processed_at index serves the purge.
CREATE TABLE processed_messages (
  consumer_name text        NOT NULL,
  message_id    text        NOT NULL,
  processed_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer_name, message_id)
);
CREATE INDEX processed_messages_processed_at_idx
  ON processed_messages (processed_at);

-- Hot path, inside the business transaction:
-- 0 rows inserted means the message is a duplicate, so skip the handler.
INSERT INTO processed_messages (consumer_name, message_id)
VALUES ('billing-consumer', 'msg-7f3a9c')
ON CONFLICT (consumer_name, message_id) DO NOTHING;

-- Purge job: delete in small batches and repeat until 0 rows are affected.
-- The 14-day window is a placeholder. It must exceed the longest
-- redelivery, DLQ replay or offset reset you support, plus a margin.
DELETE FROM processed_messages
WHERE (consumer_name, message_id) IN (
  SELECT consumer_name, message_id
  FROM processed_messages
  WHERE processed_at < now() - interval '14 days'
  ORDER BY processed_at
  LIMIT 5000            -- bounds lock time, WAL volume and replica lag
);

-- Partitioning caveat: on a table partitioned by processed_at,
-- PostgreSQL requires every unique constraint to include the partition key.
-- A constraint on message_id alone cannot then be enforced across partitions.
-- Before you replace these batched deletes with DROP of old partitions,
-- confirm that your dedup check still holds.

A PostgreSQL inbox table indexed for both the dedup lookup and the time-based purge, an ON CONFLICT insert that detects duplicates, and a batched delete bounded by the retention window.

Testing the guarantee and the impact on engineering time

Unit tests that mock the database cannot verify this guarantee, because the guarantee depends on the unique constraint and transaction isolation. Run these tests against the same database engine you use in production. Each one should assert on business state, such as the number of charge rows or the stock level, and not only on whether the handler returned successfully.

Three tests worth writing

• Sequential duplicate. Deliver the same message ID twice. Assert that the business effect happened once and that the second delivery was acknowledged without running the handler. This test catches a key derived from something unstable, such as a broker offset or a timestamp, instead of the producer's message ID.
• Concurrent duplicate. Start two workers and block both at a barrier just before the inbox insert, then release them together. Exactly one should commit. The other should hit the unique violation, roll back and acknowledge. This test exposes "check then insert" logic that passes the sequential test but races under real load.
• Crash between commit and ack. Inject a fault that kills the process after the transaction commits but before the ack is sent. Restart the consumer and let the broker redeliver. Assert that the effect is still applied once. This is the failure mode the pattern exists to handle, so test it directly.

Cost and payoff

The upfront cost includes the inbox table, a cleanup job, alerts on table growth and dedup hit rate, and making producers emit stable message IDs. The last item usually takes the longest because it spans teams.

In return, you get one tested dedup path in place of ad-hoc idempotency checks written separately in every handler. You also avoid incident work such as refunding duplicate charges or reconciling double-counted inventory. That payoff applies mainly to non-idempotent operations. If a handler already performs an idempotent upsert, the inbox adds cost without adding much safety.

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 microservices, 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.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

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.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• 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.