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.

Three orders and a broker blip
  • 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

Schema
  • outbox: id BIGINT PK, aggregate_type VARCHAR, aggregate_id VARCHAR, event_type VARCHAR, schema_version INTEGER, payload JSON, created_at TIMESTAMP, published_at TIMESTAMP
ColumnWhy it is there
idThe dedupe key consumers store, and the polling publisher's read order. Never reused.
aggregate_idThe broker partition key, so each order's events land in 1 partition, in order.
event_type, schema_versionConsumers route and parse by declared type and version; versions change by expand-contract.
payloadThe event as its data contract defines it, built inside the transaction. Internal columns stay out.
published_atPolling 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?

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 500

This 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

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.