Skip to content

Trade-offs · Production decisions

Outbox vs dual write vs change data capture

How to choose between a dual write, a polled outbox table and change data capture when a service must save data and tell others, with numbers.

Asked at: SDE-2Senior

The decision

After one business event, a service often saves data and sends a message so that other services can react. The database and the message broker cannot commit together. A dual write does both steps in code, one after the other, and hopes both work. An outbox saves the message in a table, in the same transaction as the data. A small program, the relay, reads that table and sends the messages. Change data capture (CDC) reads the database's own log of committed changes and turns them into messages, with no polling. The choice decides whether you can lose events, how late they arrive, and how much you must run.

Choose dual write when

  • Losing an event is harmless or fixable. Examples are an analytics ping and a cache refresh.
  • A later job compares both sides and repairs the difference.
  • Money, stock and orders are not involved. For those, do not choose this.

Choose an outbox with a polling relay when

  • The event must never be lost, and you want the simplest setup that guarantees it.
  • Volume is moderate, up to a few thousand events a second.
  • Your database or hosting does not let you run log readers.
  • A delay of a fraction of a second is fine.

Choose change data capture when

  • Volume is high, or polling load on the database matters.
  • You need low delay, in tens of milliseconds.
  • You want changes from code you cannot change, such as an old service.
  • You have people who can run connectors.

CDC often reads the outbox table, which gives the safety of the outbox and the speed of the log.

The numbers that change the answer

Worked example: a food-delivery app. Assumptions: 1,000,000 orders a day, an average order of Rs 400, and the kitchen must hear about every order.

Dual write loss (calculation). Assumption: 0.05% of sends fail between the save and the send, because of a crash, a deploy or a broker blip.

  • Lost events = 1,000,000 x 0.0005 = 500 a day.
  • Cost: assumption of Rs 450 per lost order (refund Rs 400 plus Rs 50 of support). 500 x 450 = Rs 225,000 a day.

Outbox cost (assumption plus calculation).

  • Each order adds one 300-byte insert: 1,000,000 x 300 = 300 MB a day, or 2.1 GB for 7 days before cleanup.
  • Polling every 200 ms is 5 queries a second. The average delay is half the interval = 100 ms.

Relay lag in a sale (calculation). Assumptions: the relay publishes 2,000 events a second. A sale sends 5,000 orders a second for 60 seconds. Normal traffic is 1,000,000 / 86,400 = 11.6, about 12 a second.

  • Backlog at the end of the sale = (5,000 - 2,000) x 60 = 180,000 events.
  • The newest order at the end of the sale waits 180,000 / 2,000 = 90 seconds for its event.
  • Drain rate afterwards = 2,000 - 12 = 1,988 a second. Drain time = 180,000 / 1,988 = 91 seconds.

If 90 seconds is too long for the kitchen, add parallel relays keyed by order, or move to CDC.

CDC disk risk (calculation). In PostgreSQL, a replication slot keeps log files until the reader has used them. Assumption: 2 MB of log a second, and the connector is down for 1 hour. Log kept = 2 x 3,600 = 7,200 MB = 7.2 GB. If the disk has less spare room, it fills, and the database can stop (see Sources).

Cited facts. Debezium says one transaction cannot span the service database and Kafka. It also describes the outbox pipeline as at-least-once, so consumers must detect duplicates (see Sources).

What breaks with each choice

Dual write.

  • Save, then send: a crash between them gives a lost event. Send, then save: a failed save gives a ghost event, a message for an order that does not exist. A retry does not help, because after a crash nothing remembers it. Fallback: a reconcile job that finds orders without events and events without orders.

Outbox with relay.

  • The relay crashes after it sends and before it marks the row, so it sends again. Fallback: consumers skip a repeated event ID.
  • "The last ID I read" is not safe, because a smaller ID can commit later and be skipped. Use a sent flag.
  • Backlog during bursts (the 91 seconds above). Fallback: more relays on different rows, with the order ID as key so one order stays in order.
  • The table grows. Fallback: delete sent rows in batches.

CDC.

  • The slot fills the disk if the connector stops (7.2 GB per hour in the example). Fallback: alert on slot lag and disk, and drop a slot you no longer need.
  • After a crash a slot can return to an earlier position, so duplicates happen. Fallback: idempotent consumers.
  • Reading business tables leaks your table design, and a renamed column breaks every consumer. Fallback: read an outbox table with a stable message.

What it costs

  • People. Assumption: outbox 1 to 2 engineer-weeks. A CDC platform 3 to 6 engineer-weeks, then cheap for each new service. CDC also needs spare disk for the log.
  • Changing your mind. Dual write to outbox: add the table and write both rows in one transaction. Run the old send and the relay side by side for a week while consumers skip repeated IDs, then remove the old send. Outbox to CDC is easy because the table stays: replace the relay with a connector. So start with a polled outbox, and move to CDC when lag or polling load hurts.

How each level answers

SDE-1: A database and a broker cannot commit together, so a dual write can lose or invent events. An outbox saves the message with the data.

SDE-2: I use an outbox. The order and the message are saved in one transaction. A relay sends the message later, so a crash cannot lose it. A message may repeat, so consumers skip repeated IDs.

Senior: A dual write at 0.05% loses 500 events a day, about Rs 225,000. An outbox adds 2.1 GB and 5 polls a second. In a sale, a relay at 2,000 a second falls behind by 180,000 events and needs 91 seconds to catch up. So I would add relays keyed by order or move to CDC. With CDC I alert on slot lag, because 1 hour of outage keeps 7.2 GB of log.

Staff: I frame it as the cost of a lost event against the cost of running a platform. For orders I do not accept a plain dual write. I ship the polled outbox first, in one shared library, and plan CDC later on the same table. I measure event delay at the 99th percentile, events found missing by reconciliation, and slot lag. The platform team owns connectors, and product teams own message formats.

Say it in the interview

I would write the order and an outbox row in one transaction, and let a relay publish the row to the broker. A crash cannot lose the event, and consumers skip repeated IDs, since delivery is at-least-once. If sales bursts make the relay late, I would add relays keyed by order ID, or read the same table with change data capture.

Common mistakes

  • Saying "we retry the send". The retry lives in a process that crashed. The memory of "still to send" must be in the database.
  • Publishing inside the transaction, just before commit. The send can work and the commit can fail, which gives a ghost event.
  • Expecting exactly-once from an outbox. It gives at-least-once. The consumer must skip repeats.

Sources

Understand the trade-off. Practise explaining your choice.

In an AI mock interview, the interviewer can push on a choice like this one: it changes a number and asks if your answer still holds.

See the mock interviewsAll trade-offs