Transactional Outbox

Pradeep P
3 days ago
3 min read

Series: Modern System Design · Layer 4 — Data

Layer 4 · Post 13 of 17

← Previous: BASE → Next: Dual Writes

Layer 4 — Data · Post 107 of 119

Put the "tell the rest of the world" record in an outbox table in the same commit as the source-of-truth write, then publish asynchronously.

What you'll learn

Why "INSERT order then produce to Kafka" in two steps is a lie
Outbox table + poller or CDC as the two usual implementations
At-least-once publication and what the consumer still must do

The idea in one minute

You need one database transaction to change business state and to record "please tell Kafka / email / search." You cannot atomically commit Postgres and a Kafka produce from the app (that is dual write, next post).

Transactional outbox: in the same SQL transaction,

INSERT the order (or UPDATE the row)
INSERT into outbox(id, topic, payload, created_at)

Commit. A publisher (poller, Debezium on the outbox table, transaction log tail) reads new outbox rows and produces to the broker, then marks them sent (or deletes).

App                 Postgres                    Kafka
- -- BEGIN: -- INSERT outbox
- -- BEGIN: -- COMMIT  -------------------------->
                              poller / CDC  -->  produce

The order cannot exist without the outbox row. The Kafka message might still be duplicated (publisher retries). Consumers stay idempotent.

Why it matters

This is the standard answer to "how do you emit an event when the DB write succeeds?" CDC of the business table (Post 50) is the cousin: the log is the outbox. A dedicated outbox table lets you shape the event (not leak internal columns) and emit only what other domains should see.

Interviews: they want this or CDC, not a try/catch around producer.send.

How it works

Poller. SELECT ... FROM outbox WHERE published_at IS NULL ORDER BY id LIMIT n FOR UPDATE SKIP LOCKED. Publish, set published_at. Simple. Lag is poll interval. Watch table bloat; delete or partition old rows.

CDC. Debezium watches outbox (or the orders table). You get ordering from the WAL. You still design event types so you do not publish raw row images forever.

Relay in-process. After commit, the app tries to publish immediately and a sweep catches crashes. Faster happy path; still an outbox as source of truth for "must publish."

Ids. Outbox primary key or a business event_id becomes the Kafka key / idempotency key downstream.

Ordering. Per aggregate (order_id as key) you can keep partition order. Global order across aggregates is a harder promise — usually you do not need it.

Failures. If publish succeeds and "mark sent" fails, you will publish twice. At-least-once. That is required. Exactly-once effect is the consumer's unique key (Post 95).

Do not write the outbox in a second transaction after commit. Then you are dual-writing again.

A simple example

Checkout: insert orders status paid, insert outbox OrderPaid. Worker emails the user. App crashes after commit, before Kafka. Poller finds the row, produces, email goes out. Without the outbox, Kafka never heard, customer paid, no email, support ticket.

If the poller publishes, crashes before published_at, it publishes again. Email worker keys on order_id. One email.

Common mistakes

Outbox in a different database than the business row. Two commits. Dual write with extra steps.

Huge JSON blobs forever. The outbox is not your data lake. Publish, then archive/delete.

No index on "unpublished." The poller table-scans; lag grows; you page yourself.

Using the outbox as a queue for heavy work. It is a reliable publish pattern. Big jobs belong in a real queue after the event exists.

Forgetting schema evolution. Version the payload; consumers must tolerate new fields.

How this shows up in real systems

Debezium "outbox event router" — the named connector pattern.
.NET / Java "transactional outbox" libraries polling SQL Server / Postgres.
DynamoDB streams + outbox item in the same transact-write.
Sagas: each local transaction can outbox the next command.

Recap

Same commit: state + outbox row. Then async publish. Consumers dedupe.
CDC of business tables is the same idea with the WAL as the outbox.
Next: the anti-pattern this exists to kill — dual writes.