The Transactional Outbox Pattern
When an order commits, checkout has 2 jobs: save the order and tell shipping. The database and the broker are separate systems, and no transaction spans both. If checkout commits first and then publishes, a crash or broker outage between the 2 steps loses the event. Publishing first fails the other way: a rollback leaves shipping with an event for an order that doesn't exist.
The transactional outbox moves the second write into the first system. Checkout inserts the event as a row in an outbox table inside the order's own transaction, so both rows commit or neither does. A separate publisher reads committed outbox rows and sends them to the broker, retrying until it accepts. The animation runs 3 orders through both designs during a 2-second broker outage.
- checkout (transform, service)
- orders (storage, Postgres)
- order_events (queue, Kafka)
- shipping (consumer, shipping)
- shipments (storage, shipping db)
- checkout -> orders
- checkout -> order_events
- order_events -> shipping
- shipping -> shipments
- Dual write: Result: 1002 is in orders and never shipped. No event was ever published for it
- Transactional outbox: Result: 3 orders shipped once each. The republished copy was skipped
In the dual-write variant, the order at second 3 arrives while the broker is down: its row commits but its publish fails, so shipping never hears of it. In the outbox variant, rows written during the outage wait in the table. Then the publisher crashes after sending 103 and before marking it, so its next run sends 103 again, and shipping dedupes on the outbox id to create 1 shipment. The outbox turns maybe-never into at-least-once; consumer dedupe makes it effectively-once.
Designing the outbox table
- outbox: id BIGINT PK, aggregate_type VARCHAR, aggregate_id VARCHAR, event_type VARCHAR, schema_version INTEGER, payload JSON, created_at TIMESTAMP, published_at TIMESTAMP
| Column | Why it is there |
|---|---|
| id | The dedupe key consumers store, and the polling publisher's read order. Never reused. |
| aggregate_id | The broker partition key, so each order's events land in 1 partition, in order. |
| event_type, schema_version | Consumers route and parse by declared type and version; versions change by expand-contract. |
| payload | The event as its data contract defines it, built inside the transaction. Internal columns stay out. |
| published_at | Polling publishers only: NULL until the broker acknowledges the row. |
Polling publisher or CDC publisher
- Polling publisher
- Queries the outbox every few seconds; delay is about the poll interval
- Adds query load on the primary at every poll, even an empty one
- Writes published_at per acknowledgement: 1 extra write per event
- Needs only SQL access
- CDC publisher
- Reads outbox inserts from the log; delay under a second
- Adds no query load: the log is written anyway
- Lets outbox rows be deleted soon after insert
- Needs a running log reader whose retention and failover you own
At peak, a payments service writes 3,000 outbox rows per second on a primary near 70% CPU. Fraud scoring needs each event within 1 second, and log-based CDC already runs for 2 other services. Which publisher fits?
- A CDC publisher: no query load on a busy primary, sub-second delay, and the log reader is already operated here.
- Polling every 200 ms: frequent polls meet the 1-second target, and SQL access avoids operating a log reader.
- Polling every 5 seconds with 500 rows per batch: fewer queries keep the load on the primary low.
- CDC on the payments table itself, skipping the outbox, since the log already holds every change.
The gap a bookmark poller falls into
Sequence ids are assigned at insert, but rows become visible at commit. Transaction A takes id 207, transaction B takes 208, and B commits first. A poller that tracks id > last_id sends 208, moves its bookmark to 208, and never sees 207 when A commits a moment later. Polling on published_at IS NULL keeps a late-committing row visible until it is sent:
SELECT
id,
aggregate_id,
event_type,
schema_version,
payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 500This poller runs every 2 seconds, then stores the highest id it sent as the next bookmark. Every order has an outbox row, yet a few orders a month never reach consumers. Why?
SELECT
id,
aggregate_id,
payload
FROM outbox
WHERE id > 48211
ORDER BY id
LIMIT 500- LIMIT 500 cuts each batch short, and rows past the limit are discarded instead of waiting for the next poll.
- A 2-second interval is shorter than some transactions, so the poller reads rows before they commit and loses them.
- A transaction holding a lower id committed after a higher id was sent, so the bookmark had already passed it.
- ORDER BY id ignores created_at, so rows inserted in the same millisecond come back shuffled and get skipped.
About this lesson
This section is part of the Ingestion Patterns: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.