The dual-write problem and the outbox pattern

A customer pays, the order row commits, and the warehouse never hears about it. The order service wrote to its database and then published an order.placed event to the broker — two systems, two writes, no transaction spanning them. The broker was mid-deploy, the publish timed out, and the event evaporated while the order sat in the database looking healthy. This is the dual-write problem: the usual source of "the data is right but the downstream system disagrees".

Four interleavings, two of them corrupt state

The naive handler does this:

tx = db.begin()
tx.insert(order)
tx.commit()                       # step 1: local state
broker.publish(order_placed)      # step 2: the rest of the world

Two independently failing steps give four outcomes:

Commit	Publish	Result
ok	ok	Correct: order exists, warehouse ships.
ok	fails	Event lost. The order is paid and stored; nothing downstream learns, and no error surfaces.
rolled back	ok	Phantom event. The warehouse ships an order that does not exist; billing references a row nobody can find.
rolled back	fails	Correct. Nothing happened.

Half the failure space corrupts state, and both bad cases are silent. Wrapping the pair in a try/catch does not fix it:

try:
    tx.commit()
    broker.publish(order_placed)
except PublishError:
    tx.rollback()   # the commit is already durable and visible

There is nothing to roll back. The compensating move is a second write (delete the order) that can itself fail — one level deeper in the same problem. Retrying in a loop is no better: a rolling deploy can kill the process between the two lines, and the retry state dies with it. No application code creates atomicity across two systems with no shared transaction log.

Size the damage before calling it theoretical. 1 million orders/day ≈ 1,000,000 ÷ 86,400 ≈ 12 orders/sec average, peak 2–5× ≈ 50/sec. If the publish fails 0.1% of the time — broker restarts, network blips, evictions — that is 1,000,000 × 0.001 = 1,000 broken orders/day, roughly 40 per hour. Even at 99.99% success, 100 a day.

The transactional outbox

Stop writing to two systems. Write the event as a row in an outbox table inside the transaction that changes state:

BEGIN;
INSERT INTO orders (id, user_id, total_cents, status)
     VALUES ('o_8123', 'u_44', 249900, 'placed');
INSERT INTO outbox (id, aggregate_id, type, payload, created_at)
     VALUES ('e_5591', 'o_8123', 'order.placed', '{"order_id":"o_8123"}', now());
COMMIT;

There is now one atomic write: order and event both exist, or neither does. The phantom case is gone by construction, the lost case degrades to "not published yet" — a delay, not corruption. A relay turns rows into messages:

The transactional outbox: state change and event committed together, relayed afterwards100%−+FitSVGEsc

Scroll to zoom · drag to pan · 0 fits · Esc closes

SELECT * FROM outbox WHERE sent_at IS NULL
  ORDER BY id LIMIT 500 FOR UPDATE SKIP LOCKED;
-- publish, then:
UPDATE outbox SET sent_at = now() WHERE id = ANY($1);

Derive the parameters, don't guess them. Poll every 200 ms with a batch of 500 and the ceiling is 500 ÷ 0.2 s = 2,500 events/sec, 50× the 50/sec peak. Added lag is about half the interval, ~100 ms median, plus a 0.5 ms datacenter round trip to the broker — invisible beside the ~200 ms India-to-US-East trip the client already pays. Little's Law says one worker suffices: 50 events/sec × 0.5 ms = 0.025 publishes in flight. Cost to the database is two extra statements per order: 50/sec × 2 = 100 QPS against a commodity Postgres budget of ~5,000 simple QPS, about 2%. Rows accumulate at 1 KB × 1 million/day = 1 GB/day, so archive sent ones on a schedule.

The relay produces duplicates, and that is the deal

The relay publishes, then marks the row sent, and can crash between the two. On restart the row still has sent_at IS NULL, so it publishes again — up to 500 duplicates per batch. Two relay instances without FOR UPDATE SKIP LOCKED or a lease do it continuously; see distributed locking for holding that lease safely.

That is the guarantee, not a defect to engineer away. Outbox publication is at-least-once, at-least-once means duplicates, and duplicates mean consumers must be idempotent — a processed_event_id table that drops repeats, or effects that are naturally idempotent (UPDATE shipments SET status = 'ready' is safe twice; amount = amount - 100 is not). See message queues for the delivery guarantees.

CDC: tail the log instead of polling

Change data capture replaces the polling loop: Debezium reads the write-ahead log — the same replication stream a follower consumes — and emits every committed outbox row without querying the table.

Prefer CDC when polling load or lag bites. Cutting the interval to 10 ms costs 100 queries/sec per relay; across 20 services that is 2,000 QPS, roughly 40% of a 5,000 QPS box spent on mostly empty polls. CDC removes them and drops median lag to tens of milliseconds. Prefer polling with one or two services, no Debezium in production, and 100 ms of lag irrelevant. CDC also pins WAL segments behind an unconsumed replication slot until the disk fills.

Either way, capture the outbox, not orders: the payload is a designed contract, your schema is not, and CDC on it leaks every column rename to consumers — the coupling microservices exist to avoid.

When not to use it

If the event and the write do not need to be consistent, this is over-engineering. An outbox costs a table, a relay to deploy and monitor, and a dedupe path in every consumer. Apply the test: if this event were dropped once per 1,000 writes, who notices? A thousand lost page-view pings out of a million is a 0.1% error in a dashboard nobody reads that finely; a thousand unshipped paid orders is an incident with a refund queue attached. And if only the same service reads it from the same database, skip the broker — a job table read in the same transaction is the pattern without the moving parts. Whichever you build, alert on the age of the oldest unsent row and page above 60 seconds: that threshold, not relay uptime, is the real service level.

In an interview

The interviewer is testing whether you know a database and a broker share no transaction, and whether you reach for a compensating hack or the pattern. It comes up the moment you draw a service writing to Postgres with an arrow to Kafka.

Say it in this order: "Two systems, no shared transaction, so two of the four interleavings corrupt state — a lost event and a phantom event. I'd write the event to an outbox table in the same transaction as the state change and let a relay publish from it. That is at-least-once, so consumers dedupe on event id, and CDC replaces polling if lag matters." Then draw the boundary: "If the event isn't consistency-critical, fire and forget."

The mistake that loses points is proposing two-phase commit across the database and the broker, or claiming exactly-once delivery — both signal you think atomicity can be pushed into infrastructure. Close behind is "wrap it in a try/catch and retry", which invites "what if the process dies between the commit and the retry?" and has no answer: the intent to publish was never durable. Naming the relay's duplicate window before the interviewer finds it separates a memorised pattern from an understood one.

Check yourself

1. A service takes 2 million writes/day and its publish call fails 0.05% of the time. How many events break per day, and does that justify an outbox?

2,000,000 × 0.0005 = 1,000 broken events/day, about 42/hour. It depends on the consumer: if the event triggers fulfilment or billing, that is 1,000 customer-visible failures a day and the outbox is mandatory. If it feeds a dashboard, it is a 0.05% metric error — take the loss.

2. Your relay polls every 500 ms with a batch of 200 rows. Peak production is 300 events/sec. Does it keep up, and what do you change?

Ceiling is 200 ÷ 0.5 s = 400 events/sec against a 300/sec peak: 75% utilised, so any spike builds a backlog that never drains. Raise the batch to 1,000 for 2,000/sec (15% utilised) rather than shortening the interval, which multiplies empty queries against the database.

3. The relay publishes a batch, then crashes before the sent_at update lands. What breaks, and where is the fix?

Nothing is lost — the rows are still unsent, so the relay republishes on restart. The fix belongs at the consumer, as a processed-event-id table or effects written as idempotent assignments rather than increments. Making the relay exactly-once only reintroduces the dual-write problem, now between the broker and the sent_at column.