Skip to content
learn
  1. Learn
  2. High Level System Design
  3. Asynchronous Processing and Messaging

System Design HLD-09.08

Transactional outbox and change data capture

The dual-write problem, the transactional outbox pattern, change data capture from the database log, and reliable handoff to a message bus.

Advanced 14 min Last reviewed

The big idea

An order service saves a new order to its database and publishes OrderPlaced to Kafka so that billing, shipping and email can react. Two writes to two different systems. Now crash the service between them. If the database write happened and the publish did not, the order exists but nobody ships it. Publish first and crash before the database commit, and the warehouse ships an order that does not exist. Retrying does not fix it, because you cannot know which half succeeded. This is the dual-write problemWriting the same change to two systems (such as a database and a message broker) as separate steps, which can leave them inconsistent if a failure happens between the two writes..

There is no transaction that spans your database and your broker. The fix is to stop doing two writes. Write once, to the database, inside a transaction that you already trust, and derive the event from that write afterwards. The two ways to derive it are the transactional outboxA pattern where a service writes its business change and an event row to an outbox table in the same database transaction, and a separate relay publishes outbox rows to the message bus. and change data capture (CDC)Change data capture: reading a database's own change log (such as the PostgreSQL WAL or MySQL binlog) to stream every committed row change as an event..

Think of a shop that writes every sale into its ledger and, on the same line, ticks a box "notify the warehouse". A clerk walks the ledger and phones the warehouse for every unticked line. Whatever happens, the sale and the note to notify are recorded together, so nothing is forgotten.

How it works

Step 1: Why the obvious fixes fail

  • Publish after commit: a crash after commit loses the event.
  • Publish before commit: a failed commit leaves a published event for an order that never existed.
  • Distributed transactions (two-phase commit) across the database and broker: rarely supported by brokers, slow, and the coordinator becomes a single point of failure.
  • Retry the publish in a background thread: the retry state lives in memory and dies with the process.

Step 2: The transactional outbox

Transactional outbox with a CDC relay An order service writes the new order row and an OrderPlaced row into the outbox table in one database transaction. A relay, either a poller or a change data capture connector reading the database log, picks up committed outbox rows and publishes them to Kafka. Billing, shipping and email services consume the event. one transaction publish consume Order service orders table order 42 outbox table OrderPlaced 42 Relay / CDC reads committed rows Kafka orders topic Billing, shipping, email Figure 1. The order and its event are written in one transaction, so they commit or fail together. A relay (a poller, or CDC reading the database log) publishes committed outbox rows to Kafka. Delivery is at least once, so consumers dedupe on the event id.
  1. In the same local transaction as the business change, the service inserts a row into an outbox table: event id, type, aggregate id (the partition key), and payload. The database guarantees both rows commit or neither does.
  2. A relay reads committed outbox rows in order and publishes them to the broker.
  3. After the broker acknowledges, the relay marks the rows as sent (or deletes them, or simply advances its position).

If the relay crashes after publishing but before marking a row, it publishes that row again on restart. So the outbox gives at-least-once delivery, and consumers dedupe on the event id ( Delivery semantics ). The guarantee that matters holds absolutely: an event is published if and only if the business change committed.

outbox.pyruns with python3 (standard library, sqlite3)
import sqlite3, json

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER);
  CREATE TABLE outbox (id INTEGER PRIMARY KEY, topic TEXT, payload TEXT, sent INTEGER DEFAULT 0);
""")
bus = []                                             # stands in for Kafka

def place_order(order_id, total, fail_after_db=False):
    with db:                                         # ONE transaction: both rows or neither
        db.execute("INSERT INTO orders VALUES (?, ?)", (order_id, total))
        db.execute("INSERT INTO outbox (topic, payload) VALUES ('orders', ?)",
                   (json.dumps({"event": "OrderPlaced", "order": order_id, "total": total}),))
        if fail_after_db:
            raise RuntimeError("crash before commit")

def relay(crash_before_mark=False):
    """Polls the outbox, publishes in order, then marks rows sent (at-least-once)."""
    for row_id, topic, payload in db.execute("SELECT id, topic, payload FROM outbox WHERE sent = 0 ORDER BY id").fetchall():
        bus.append((row_id, payload))                # publish, keyed by the outbox row id
        if crash_before_mark:
            return                                   # crash: the row stays unsent
        with db:
            db.execute("UPDATE outbox SET sent = 1 WHERE id = ?", (row_id,))

place_order(1, 40)
try:
    place_order(2, 99, fail_after_db=True)           # rolled back: no order AND no event
except RuntimeError:
    pass
relay(crash_before_mark=True)                        # publishes order 1, crashes before marking
relay()                                              # restart: publishes order 1 again
print("orders in DB :", db.execute("SELECT id FROM orders").fetchall())
print("bus messages :", [json.loads(p)["order"] for _, p in bus])
print("unique events:", sorted({row_id for row_id, _ in bus}), "(consumers dedupe on the event id)")

Output

orders in DB : [(1,)]
bus messages : [1, 1]
unique events: [1] (consumers dedupe on the event id)

Order 2's transaction fails, and because the outbox row was in the same transaction, no event exists for it. Order 1 is published twice because the relay crashed before marking it sent, yet there is only one unique event id, so deduplicating consumers apply it once.

Step 3: Two ways to run the relay

Polling publisher: a loop runs SELECT ... FROM outbox WHERE sent = false ORDER BY id LIMIT 100 every few hundred milliseconds. Simple, works with any database, but adds query load and some latency, and ordering needs care when several relay instances run at once.

Log-based CDC: every database already writes a durable, ordered log of committed changes for its own recovery and replication: the write-ahead log in PostgreSQL, the binlog in MySQL ( Transactions and ACID ). A CDC connector reads that log like a replica would and turns each committed insert into an event. No polling queries, low latency, and commit order comes for free. Debezium is the best-known open source CDC platform, and it ships an outbox event router that turns outbox inserts into properly keyed Kafka messages.

Step 4: CDC without an outbox

CDC can also stream changes from your ordinary tables directly, with no outbox at all: every insert, update and delete on orders becomes an event. This is how teams feed search indexes, caches, data warehouses and analytics from an operational database without touching application code.

The trade-off is coupling. Raw table changes expose your internal schema: rename a column and every consumer breaks, and a row change says "status went from 2 to 3", not "order shipped". The outbox lets you publish deliberate, stable domain events while still using CDC as the transport. A common rule: raw CDC for data pipelines inside your team; outbox events for contracts with other teams.

Going deeper

Ordering and partitioning

Use the aggregate id (such as the order id) as the message key so all events for one order stay in one partition, in commit order ( Ordering, partition keys and parallelism ). A log-based CDC relay preserves commit order naturally. A polling relay ordered by an auto-increment id can be subtly wrong: two transactions can obtain ids 10 and 11 but commit in the order 11, 10, and a poller that read past 11 may skip 10. Log-based CDC avoids this.

Housekeeping

The outbox table grows with every event. Delete published rows on a schedule, or with CDC, delete each row right after inserting it in the same transaction: the insert still appears in the log, so the connector sees the event, and the table stays empty.

The inbox pattern

The mirror image on the consumer side: write each incoming event id into an inbox (dedupe) table in the same transaction as the side effects of handling it. Outbox on the producer plus inbox on the consumer gives effectively-once processing between services.

Trade-offs

OptionWins whenLoses when
Dual write (DB, then publish) Never for events you rely on Consistency: crashes lose events or publish phantom ones
Outbox + polling relay Any database, simple to build, explicit domain events Polling load and latency; ordering pitfalls with several pollers
Outbox + log-based CDC Low latency, commit-ordered, no polling, domain events Running a CDC platform; needs access to the database log
Raw CDC on business tables Feeding search, caches and warehouses without app changes Consumers coupled to your internal schema; changes, not intent

Common mistakes

  • Publishing to the broker inside the database transaction and assuming a rollback will unpublish it.
  • Writing the outbox row in a separate transaction from the business change.
  • Forgetting that the relay is at-least-once and building consumers that cannot handle duplicates.
  • Exposing raw table CDC as a public contract, so a column rename breaks other teams.
  • Letting the outbox table grow forever.

In the wild

Debezium

Debezium documentation (opens in a new tab)

The Debezium documentation describes it as a set of distributed services that capture changes in your databases so applications can see and respond to them. Debezium records every row-level change in each table as a change event stream, and applications read those streams to see the changes in the same order they occurred. That ordered stream of committed changes is exactly what an outbox relay needs: commit the event row once, and let CDC carry it to the bus.

Check yourself

  1. A service commits an order, then crashes before publishing OrderPlaced. With a plain dual write, what happens?

    Show answer

    B. The order exists but the event is lost; downstream services never hear about it The two writes are independent; nothing records that a publish was still owed.

  2. Why does the outbox row go in the same transaction as the business change?

    Show answer

    B. So the event exists if and only if the business change committed Atomicity of the local transaction is the whole trick; separate transactions recreate the dual write.

  3. The relay publishes an outbox row and crashes before marking it sent. What do consumers see?

    Show answer

    B. The event twice, so they must dedupe on the event id The outbox relay is at-least-once; the event id makes the duplicate harmless.

  4. What is the main risk of exposing raw CDC events from business tables to other teams?

    Show answer

    B. Consumers become coupled to your internal schema and see row changes, not business intent Internal refactors become breaking changes; an outbox lets you publish stable domain events.

Recap

  • Writing to a database and a broker as two steps can leave them inconsistent: the dual-write problem.
  • The outbox writes the business change and the event in one local transaction.
  • A relay, polling or log-based CDC, publishes committed outbox rows at least once.
  • CDC reads the database's own commit log, giving low latency and commit order.
  • Consumers dedupe on event ids; outbox plus inbox gives effectively-once between services.

Get new lessons by email

Get new posts on backend and AI internals. No spam, unsubscribe anytime.

Your email is only used to send new posts. Privacy

Next up