Transactional Outbox for Cross-Shard Event Publishing

When a write on one shard must cause an effect somewhere else — another shard, a search index, a webhook — the outbox pattern makes that effect inevitable without a distributed transaction. This guide builds one: the table, the relay, ordering guarantees, and the retention that stops it becoming the largest table in the schema. It implements the durability-of-intent mechanism from Distributed Transactions & Cross-Shard Consistency, part of Cross-Partition Querying & Aggregation Strategies.

Prerequisites

Step 1 — Write the event in the same transaction as the data

This single property is the whole pattern:

CREATE TABLE outbox (
    id             bigint GENERATED ALWAYS AS IDENTITY,
    aggregate_type text        NOT NULL,
    aggregate_id   text        NOT NULL,
    event_type     text        NOT NULL,
    payload        jsonb       NOT NULL,
    created_at     timestamptz NOT NULL DEFAULT now(),
    delivered_at   timestamptz,
    attempts       int         NOT NULL DEFAULT 0,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE INDEX outbox_undelivered_idx
    ON outbox (aggregate_id, id) WHERE delivered_at IS NULL;
BEGIN;
  UPDATE accounts SET balance = balance - 2500 WHERE id = 8842;

  INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
  VALUES ('account', '8842', 'funds_reserved',
          jsonb_build_object('amount', 2500, 'dst_account', 1190,
                             'idempotency_key', '9f1c2d64-0a1e-4f7c-9d2b-3d8e5c1a77f0'));
COMMIT;

If the transaction commits, the event exists. If it rolls back, neither the balance change nor the event happened. There is no third state, which is exactly what a publish-after-commit cannot promise.

Operational note: The partial index on undelivered rows keeps the relay’s claim query fast no matter how many delivered rows are waiting to be pruned. Without it, the relay slows down as the table grows.

DBA tip: Include the idempotency key of the originating operation in the payload. It is what lets the consumer deduplicate, and it links the event back to the request that caused it during an investigation.

The window that the outbox closes Publishing after commit leaves a gap between the database commit and the broker acknowledgement. A process crash inside that gap loses the event permanently, with no record that it was owed. Writing the event into the same transaction removes the gap: the event is durable the instant the data change is, and a relay delivers it afterwards with unlimited retries. Publish after commit — a gap with no record COMMIT — balance changed the gap broker ack a crash here loses the event with nothing recording that it was owed Transactional outbox — no gap exists one transaction: balance change + outbox row relay claims SKIP LOCKED, retries forever broker at-least-once A crash anywhere after the commit is harmless: the row is still there, undelivered, and the next relay tick picks it up. The cost is that delivery is at-least-once rather than exactly-once, which is why consumers must be idempotent. The outbox does not replace a broker — it guarantees the handoff to one. Fan-out and routing stay downstream.

Step 2 — Build a relay that can run in parallel

The relay claims rows without blocking other instances:

# relay.py — run two or more instances; SKIP LOCKED keeps them from colliding
BATCH = 200

def tick(shard, broker):
    with shard.transaction() as tx:
        rows = tx.query("""
            SELECT id, created_at, aggregate_id, event_type, payload
            FROM   outbox
            WHERE  delivered_at IS NULL
            ORDER  BY id
            FOR UPDATE SKIP LOCKED
            LIMIT  %s
        """, (BATCH,))

        for row in rows:
            try:
                broker.publish(topic=row.event_type,
                               key=row.aggregate_id,          # ordering key
                               value=row.payload)
                tx.execute("""UPDATE outbox SET delivered_at = now()
                               WHERE id = %s AND created_at = %s""",
                           (row.id, row.created_at))
            except BrokerError:
                tx.execute("""UPDATE outbox SET attempts = attempts + 1
                               WHERE id = %s AND created_at = %s""",
                           (row.id, row.created_at))
                break     # stop the batch; the next tick retries from here

Operational note: Marking delivery inside the same transaction that claimed the row means a relay crash after publishing but before the update re-publishes the event. That is the at-least-once guarantee, and it is why the consumer must deduplicate.

DBA tip: Break out of the batch on the first failure rather than continuing. Continuing past a failing broker publishes later events before earlier ones for the same aggregate, which quietly violates the ordering the key was meant to provide.

Step 3 — Preserve per-aggregate ordering while scaling out

Global ordering is unnecessary and expensive; per-entity ordering is usually required:

-- claim by hash bucket so one aggregate is only ever handled by one relay instance
SELECT id, created_at, aggregate_id, event_type, payload
FROM   outbox
WHERE  delivered_at IS NULL
  AND  hashtext(aggregate_id) % %(relay_count)s = %(relay_index)s
ORDER  BY id
FOR UPDATE SKIP LOCKED
LIMIT  200;
Hash-bucketed claiming keeps per-aggregate order while scaling out Outbox rows are assigned to relay instances by a hash of the aggregate id, so all events for account 8842 go to relay zero and all events for account 1190 go to relay one. Within each relay, rows are processed in id order, so an entity's events are always published in the order they were written, while total throughput scales with the number of relays. outbox rows id 101 · acct 8842 · reserved id 102 · acct 1190 · credited id 103 · acct 8842 · released id 104 · acct 1190 · settled a single ordered sequence per shard relay 0 — bucket 0 101 then 103, in order relay 1 — bucket 1 102 then 104, in order broker per-key order preserved Events for different accounts may interleave arbitrarily, which no consumer cares about. Events for the same account never do, which every consumer depends on — and the guarantee costs one modulo in the claim query.

Operational note: Changing the relay count re-assigns buckets, so drain the outbox before scaling relays up or down, or accept a brief window where two instances could handle the same aggregate.

SRE tip: Use the same hash the broker uses for partition assignment where possible, so an event’s path from outbox to consumer stays on one ordered lane end to end.

Step 4 — Monitor age, and prune delivered rows

CREATE OR REPLACE VIEW v_outbox_health AS
SELECT count(*) FILTER (WHERE delivered_at IS NULL)                            AS undelivered,
       coalesce(extract(epoch FROM now() - min(created_at))
                FILTER (WHERE delivered_at IS NULL), 0)                        AS oldest_seconds,
       count(*) FILTER (WHERE delivered_at IS NULL AND attempts > 5)           AS poisoned
FROM   outbox;
Oldest undelivered age is the only outbox metric that matters During normal operation the oldest undelivered row is under a second old. A broker outage makes the age climb linearly, crossing the five-minute alert threshold after five minutes and peaking at forty minutes. When the broker returns, the relay drains the backlog and the age falls back to normal within two minutes even though the row count was very large. 40 m25 m10 m0 alert — oldest undelivered > 5 min broker unavailable broker returns; backlog drains in ~2 min Row count peaked at 1.4 million during the outage and never triggered anything, because a large backlog delivered quickly is healthy. Age is the signal; count is context. Nothing was lost — the outbox rows were durable throughout, which is the property the pattern exists to provide. 
# the alert that matters: age, not count
outbox_oldest_seconds > 300
outbox_poisoned > 0

Delivered rows are pruned by dropping whole partitions, exactly as in partition lifecycle and retention management — a DELETE FROM outbox WHERE delivered_at IS NOT NULL on a high-throughput table is a bloat generator.

Operational note: A row with many attempts is poison — usually a payload the consumer rejects. Move it to a dead-letter table rather than letting it block its aggregate’s ordering forever.

SRE tip: Chart oldest_seconds next to broker lag. A rise in the first with a flat second means the relay is the bottleneck; both rising together means the broker or the consumer is.

Verification

Kill the relay mid-flight and confirm nothing is lost:

# generate 1,000 events, kill the relay after ~200 are delivered, restart it
psql -c "SELECT count(*) FROM outbox WHERE delivered_at IS NULL"
 count
-------
   812

After restarting the relay, the undelivered count must reach zero and the consumer must have received at least 1,000 events with exactly 1,000 distinct idempotency keys — the duplicates being the at-least-once behaviour working as designed.

Failure mode table

Failure mode Root cause SRE mitigation
Outbox grows without bound the relay stopped and nothing alerted, or delivered rows are never pruned alert on oldest_seconds, not on row count; drop delivered partitions on a schedule
Events for one entity arrive out of order two relay instances handled the same aggregate, or the batch continued past a failed publish claim by hash bucket so an aggregate maps to one relay; break the batch on the first failure
A poison event blocks its aggregate forever the consumer rejects a payload permanently and the relay retries it indefinitely move rows past an attempt threshold to a dead-letter table and alert; never silently drop them

FAQ

Why not just publish to the broker after committing?

Because the process can die between the commit and the publish, and nothing records that the publish is owed. The outbox writes the intent inside the same transaction as the data change, so the two are atomic by construction. A relay then delivers it, and can retry forever because the intent is durable.

Does the outbox preserve event ordering?

Per aggregate, yes, if the relay processes rows for the same key in id order and does not run two workers on the same key. Globally, no — and it should not try. Claim rows with SKIP LOCKED partitioned by a hash of the aggregate id so parallel relays never interleave events for the same entity while still scaling out.

How large should the outbox table get?

It should be nearly empty in steady state. A growing table means the relay is slower than the write rate or has stopped. Alert on the age of the oldest undelivered row rather than on row count, because a backlog of a million rows delivered within a second is healthy and a single row stuck for an hour is not.

Back to Distributed Transactions Cross Shard Consistency