The outbox pattern and change data capture

Why did the kitchen never hear about a paid order?

Payments now live in their own service with their own database. The last lesson showed why a single transaction cannot cover both databases. So the order service records a payment in two steps: when the payment service confirms the money, it marks the order paid, then publishes an order.paid event to the order log that the kitchen screen reads (lesson 5.3).

The code is two lines: save the order as paid, then publish the event. If the publish fails, the code keeps the event in a list in memory and tries again two seconds later.

At 12:21 one Thursday a broker in the log cluster changed leaders, and for about three seconds publishes timed out. At the same moment a deploy restarted the order servers, one by one.

At 1:10 a customer called with the UPI receipt on her phone. Her lunch had never come. The kitchen had never had her order at all.

When the team went back through that day’s records, they found the pattern. Twelve orders were marked paid in the database, and none of them had reached the kitchen screen. Each line of code had done what it was written to do. The save worked, and the publish would have worked on its retry two seconds later, but the restart threw away the list in memory that held the retries.

The obvious repair is to swap the lines: publish first, then save. Try it on paper before reading on. If the database fails over between the two steps, the kitchen cooks an order the database never recorded: food with no paid order behind it. Swapping the lines moves the gap without closing it.

Whichever order the two writes go in, one of them can succeed while the other does not, because they go to two different systems and no transaction covers both.

Pause and predict The service has to do two things: mark the order paid in its database, and make sure the kitchen is told. How can it turn these into a single write that either happens completely or not at all?

Two writes, and a gap between them

Writing to your database and then to another system in the same piece of code is called a dual write. Each write can succeed or fail on its own, and the process can die between them. Whichever write you do first, one bad outcome is still possible.

Step through the two-line version, then switch to the failure path.

Retrying more often does not help. A retry only happens if something remembers that the publish is still owed, and here the only record of that was in the memory of the process that was restarted. Two-phase commit (lesson 6.4) could make both writes atomic, but it needs both systems to take part in the protocol, and it holds locks while it waits for a coordinator that may be gone.

A mental model

A paper depot solved this long before databases did.

Transactional outbox

A table in the service’s own database. Whenever the service changes data and needs to announce the change to other services, it inserts an event describing the change into this table, in the same transaction as the change. A separate relay publishes the rows to the message log.

For example: BEGIN; UPDATE orders SET status = 'paid' WHERE id = 8812; INSERT INTO outbox (id, aggregate_id, type, payload) VALUES (…, 8812, 'order.paid', …); COMMIT;

Two ways to carry the rows

• A polling publisher runs a query every few hundred milliseconds: select unsent rows in order, publish them, mark them sent. It works with any SQL database. Its costs are the queries, a delay of up to one polling interval, and some care to publish events in the order they were written.
• Log tailing, also called change data capture (CDC), reads the database’s own write-ahead log (lesson 2.2) and publishes each committed insert into the outbox. Nothing polls the table, changes arrive in commit order within tens of milliseconds, and the same machinery can stream changes from any table. In PostgreSQL this is logical decoding through a replication slot; Debezium is a widely used open-source tailer that runs on Kafka Connect.

Both give at-least-once delivery. A relay can publish a batch and crash before marking it; a tailer can publish changes and crash before saving its position. So the last piece is on the receiving side: an idempotent consumer, usually an inbox table.

-- the kitchen, for each event it receives BEGIN; INSERT INTO inbox (event_id) VALUES ($1) ON CONFLICT DO NOTHING; -- if a row was inserted: show the order on the screen (same transaction) COMMIT; -- then commit the consumer offset in the log

This is the idempotency key from lesson 4.1, used by the consumer. Each event has a unique id, and the inbox table records which ids the consumer has already acted on, so a second copy of the same event is recognised and skipped.

What the outbox costs

Assume the delivery service’s lunch peak: 15 events a second leaving the order service (paid, cooking, left the kitchen), 30,000 orders and 90,000 events a day, about 1 KB per event. These are planning figures, not measurements.

Three of these deserve a sentence each.

• Repeats are routine. Kafka Connect, which runs Debezium, saves its position in the log (its source offset) every 60 seconds by default (offset.flush.interval.ms). After a crash it restarts from the last saved position, so it can send up to a minute of changes a second time. Debezium’s own FAQ says that when things go wrong, consumers see every event “at least once”. Every consumer therefore needs an inbox table, even if duplicates seem rare.
• The outbox table must be cleaned. Delete sent rows in batches, or partition the table by day and drop old partitions. An outbox that only grows makes every poll slower.
• A stopped tailer fills the disk. A replication slot keeps every WAL segment that its consumer has not yet confirmed reading, even when no consumer is connected. PostgreSQL’s documentation warns that slots “will prevent removal of required resources even when there is no connection using them”. max_slot_wal_keep_size can cap this, but its default (-1) is unlimited. Set an alert on how far the slot has fallen behind, in the same way you alert on free disk space.

Poll the table, or tail the log?

The outbox table is in place. What remains is to choose how its rows reach the log. One option gives the lowest delay and can be reused for other tables later; the other has the fewest moving parts for the person who is paged at 3 a.m. to understand.

Log tailing is likely to be the right choice later: once several teams want change feeds from several tables, its extra machinery is worth running. Today one table feeds one log at 15 events a second, and at that volume a polling relay is fast enough and simpler to operate.

The decision has a review date: when a second team asks for a change feed, revisit log tailing.

What to remember

1. A dual write (save, then publish) always has a gap: whichever order you choose, a crash between the writes leaves a lost event or a ghost event.
2. The transactional outbox turns two writes into one local transaction: the change and the event row commit together, or neither does.
3. A polling relay works with any SQL database and costs queries and some delay; log tailing (CDC) reads the write-ahead log in commit order but needs a replication slot, which must be watched so it never falls far behind.
4. Both deliver at least once. Every consumer needs an inbox table or another idempotency check, written in the same transaction as its effect.

Write the change and the event in one transaction, and let a relay carry the event as many times as it takes.

In the real world

The lab has one service, one relay and a fixed timeline. Real deployments add what it leaves out: several relay instances, of which only one may publish at a time (a lease, as in lesson 3.4, or SKIP LOCKED batches); schemas and versions for the event payloads; and, with Debezium, a choice of decoding plugin (PostgreSQL’s built-in pgoutput, available since version 10, or Debezium’s own decoderbufs, which is the connector’s default setting). Debezium’s outbox router also ignores deletes on the outbox table, so a service can clean up sent rows without the deletes being published.

The outbox has made each service’s own changes reliable. It has not made a process that spans services reliable: if the kitchen cannot cook an order after the customer has paid, someone must refund her. That is the saga, in lesson 6.6.