The transactional outbox

Article.

A service commits an order and publishes OrderPlaced. Two systems, two writes, no shared transaction — and no combination of try/except makes them atomic. The outbox is the standard answer, and the interesting parts are the two things it does not give you: exactly-once delivery, and ordering.

The dual-write problem

def place_order(order):
    db.commit_order(order)                 # step 1
    kafka.publish("OrderPlaced", order)    # step 2

Three ways this is wrong, and they are not symmetrical:

• Step 1 commits, step 2 fails — the order exists and nobody downstream knows. Silent, permanent divergence.
• Step 2 succeeds, step 1 rolls back on a later constraint — a phantom event for an order that does not exist.
• Step 2 gets a broker timeout — it may have been accepted or not, and the client cannot tell.

There is no two-phase commit across Postgres and Kafka, and even where XA exists it is not something you want in a request path.

The pattern

Write the event into an outbox table in the same transaction as the business change. A separate relay publishes it.

BEGIN;
  INSERT INTO orders (id, user_id, total) VALUES (...);
  INSERT INTO outbox (aggregate_id, event_type, payload)
       VALUES (:order_id, 'OrderPlaced', :json);
COMMIT;

Both inserts or neither. The database transaction is the atomicity unit, which is the whole idea: you have replaced a distributed atomicity problem with a local one.

with session.begin():
    order = Order(user_id=user.id, total=total)
    session.add(order)
    session.flush()                       # populates order.id

    session.add(Outbox(
        aggregate_id=str(order.id),
        event_type="OrderPlaced",
        payload={"order_id": order.id, "total": str(order.total)},
    ))

The relay, and where its transaction ends

The obvious relay reads a batch, publishes, and marks the rows — all in one transaction:

with session.begin():
    rows = session.execute(
        select(Outbox).where(Outbox.published_at.is_(None))
        .order_by(Outbox.id).limit(100)
        .with_for_update(skip_locked=True)
    ).scalars().all()

    for row in rows:
        kafka.send(row.event_type, value=row.payload).get()   # network I/O
        row.published_at = utcnow()

That works, and it holds a database transaction open across up to a hundred network round trips. If the broker is slow, the transaction runs for minutes — holding row locks, pinning the oldest transaction id so VACUUM cannot reclaim anywhere in the database, and accumulating WAL.

Prefer short transactions and accept the duplicates, because you are already at-least-once:

rows = fetch_unpublished(limit=100)        # transaction 1: read and claim
acks = publish_batch(rows)                 # no transaction held
mark_published(acks)                       # transaction 2: short write

The trade is explicit. Holding the transaction across the publish means fewer duplicate events and a long-running transaction; releasing it means short transactions and slightly more duplicates. Since consumers must be idempotent either way, the duplicates cost you nothing you were not already paying for — and the long transaction costs the whole database.

Waiting for the ack is not optional

kafka.send(...) returns a future. Marking the row published without resolving it means the row is marked before the broker has the message, so a broker failure loses the event permanently — with the outbox row saying it was delivered. That single missing .get() defeats the entire pattern.

Ordering: the claim that is usually wrong

The common statement is that the outbox’s auto-increment id gives you global event order. It does not, and the reason is worth understanding because it generalises to every sequence in Postgres.

Sequences are non-transactional. nextval runs during INSERT, not at COMMIT, so ids are allocated in insertion order and rows become visible in commit order. Those differ:

t0  T1 INSERT outbox -> id 5      (uncommitted)
t1  T2 INSERT outbox -> id 6      (uncommitted)
t2  T2 COMMIT                     -> id 6 now visible
t3  relay polls: sees 6, publishes 6
t4  T1 COMMIT                     -> id 5 now visible
t5  relay polls: sees 5, publishes 5

Event 6 was published before event 5. The relay did nothing wrong.

This has a much worse cousin. If the relay tracks a high-water mark — WHERE id > :last_seen — then after publishing 6 it records 6, and row 5 is never published at all. Silent, permanent event loss, and it only happens under concurrency, so it survives every test you write.

Using WHERE published_at IS NULL as a claim flag, as above, is immune to that loss — the row stays unpublished until someone publishes it. It is still not ordered.

So how do you get ordering

Only where you actually need it, which is per aggregate, not globally:

• Key by aggregate id so all events for one order land on one Kafka partition. Necessary, not sufficient.
• Ensure one relay handles one aggregate at a time. SKIP LOCKED across several relay replicas explicitly does not do this — replica A takes rows 1-100, replica B skips them and takes 101-200, and B may publish first.

If order matters, shard the relay by aggregate rather than by row:

SELECT * FROM outbox
WHERE published_at IS NULL
  AND abs(hashtext(aggregate_id)) % :total_shards = :shard
ORDER BY id
FOR UPDATE SKIP LOCKED;

Now one shard owns an aggregate, relays scale horizontally, and events for the same entity are published by one process in id order.

The alternative is a single relay holding an advisory lock — simple, ordered, and a throughput ceiling plus a failover story.

Where it breaks in production

The relay dies and nothing alarms. This is the outbox’s characteristic outage: the application is healthy, requests succeed, the database is fine, and no events are flowing. Nobody notices until a downstream team asks why their data is a day stale.

Alert on outbox lag, not on relay liveness:

SELECT count(*) AS backlog,
       max(now() - created_at) AS oldest
FROM outbox WHERE published_at IS NULL;

Age of the oldest unpublished row is the metric. A backlog count alone looks fine at a steady 200 rows whether they are two seconds or two hours old.

No retention. The table grows forever, and every relay poll scans a larger index. Delete published rows past a debugging window:

DELETE FROM outbox WHERE published_at < now() - interval '7 days';

A poison event. One row whose payload the broker rejects — too large, bad encoding — and a relay that retries the batch from the start will retry it forever, blocking every event behind it. Track an attempt count per row and move repeat failures to a dead-letter state so the queue drains.

The CDC variant

Instead of polling, let Debezium read the Postgres WAL and turn every outbox insert into a Kafka message.

Postgres WAL -> Debezium -> Kafka topic

Lower latency than polling, no relay to write or operate, and it survives outages well. It costs you Kafka Connect and Debezium as infrastructure, and wal_level = logical on the database.

Keep the outbox table even with CDC. Debezium can read your business tables directly, and then your event schema is your database schema: every column rename becomes a breaking change for consumers, and internal structure leaks across a service boundary. The outbox table is a published contract you control.

The trade not taken

The outbox is machinery, and there are cases where it is the wrong amount.

Skip it when no events cross a service boundary, when the events are advisory and losing one rarely is genuinely acceptable, or when a workflow engine already owns the state — Temporal’s event history is a durable log, and adding an outbox beside it duplicates the guarantee.

Commit hooks — publishing in an after_commit listener — are the tempting middle ground. They are strictly worse than they look: if the broker is down when the hook fires, the event is gone, with no record that it should have existed. The outbox’s real value is not atomicity alone, it is that the event is durable and replayable because it is a row in your database.

Related

• Idempotency keys - the consumer side of at-least-once
• Sagas and compensation - coordinating across services once events flow
• Replication and Logical Decoding - the WAL decoding Debezium uses