Transactional outbox - publish without dual-write lies

Worked first (Chen’s checkout): Chen’s service must INSERT an order and tell inventory + email that it exists. He tried COMMIT then kafka.publish. Under a 15% “die after commit” fault injector in staging, about one in seven orders never produced an event — support saw paid rows with no fulfillment. Flipping to publish-then-commit created the opposite bug: inventory reserved stock for orders that rolled back. The fix was not “try harder.” It was one transaction that wrote both the order and an outbox row, plus a boring relay.

The dual-write trap

You need two successes that no single product API can make atomic:

1. Durable business state in the database
2. A message on a broker for other services

BEGIN;
INSERT INTO orders …;     -- succeeds
COMMIT;

broker.publish("OrderPlaced", …);  // crash here → DB has order, no event

Reverse the order (publish, then commit) and consumers may act on a row that never lands. Distributed 2PC across DB + broker is often unsupported or couples availability of both systems into every write path. Richardson’s framing: without 2PC, “send mid-transaction” and “send after commit” are both unreliable — you need a third place that is in the DB transaction: the outbox.

The outbox move

BEGIN;
INSERT INTO orders (id, …) VALUES (…);
INSERT INTO outbox (id, topic, payload, created_at)
VALUES (…, 'OrderPlaced', '{"orderId":…}', now());
COMMIT;
-- separate relay:
--   read unpublished outbox rows (ORDER BY id)
--   publish to broker
--   mark published_at (or delete)

The business row and the intent to publish commit together or not at all. The broker becomes eventually consistent with that intent — not with a second, independent network write from the request path.

Lab numbers (30-run median)

Pure JS fault simulation (not a claim about your Kafka cluster’s uptime). 200 orders per run, 30 runs, dual-write crash probability 0.15 after DB commit before broker publish. Outbox relay models a 5% “published to broker, crashed before mark” → duplicate on restart.

Mode	median lost / 200	median published	Notes
Dual-write (DB then broker)	30	170	~15% loss ≈ crash probability 0.15
Outbox + relay	0	210	median 10 duplicate publishes (at-least-once relay)

The recorded benchmark harness and standalone results are not included in this repository. Outbox does not invent exactly-once effects — Day 30 still owns side effects on the consumer.

Diagram: one txn, later publish

Worked failure timeline

t	Dual-write	Outbox
T0	INSERT order · COMMIT	INSERT order + outbox · COMMIT
T1	Process crashes	Process crashes
T2	No publish — event lost unless healed manually	Outbox row still durable — relay publishes after restart
T3	Consumers never see OrderPlaced	Consumers see event (maybe twice) — Day 30

Relay options

Relay	How	Tradeoff
Polling publisher	SELECT unpublished rows on an interval; optional Day 48 NOTIFY wake	Simple ops; lag ≈ poll interval; easy to reason about
Transaction log tailing (CDC)	WAL / Debezium-style reader emits outbox inserts	Lower lag; more moving parts and failure modes

Both still deliver at least once to the broker if the relay crashes after publish and before marking. That is a feature of the recovery story, not a bug — as long as consumers are idempotent.

Minimal schema + claim

CREATE TABLE outbox (
id              bigserial PRIMARY KEY,
aggregate_type  text NOT NULL,
aggregate_id    text NOT NULL,
aggregate_version bigint NOT NULL,
event_type      text NOT NULL,
payload         jsonb NOT NULL,
created_at      timestamptz NOT NULL DEFAULT now(),
published_at    timestamptz,
UNIQUE (aggregate_type, aggregate_id, aggregate_version)
);
-- Allocate aggregate_version while locking/versioning the business aggregate
-- in this same transaction; version N+1 must not commit before version N.
-- Day 22 partial: only the unpublished slice
CREATE INDEX ON outbox (id) WHERE published_at IS NULL;

Multi-relay: FOR UPDATE SKIP LOCKED (Day 38) prevents two workers from claiming the same row concurrently. It does not preserve publication order: relay A can lock version 10 while relay B skips it and publishes version 11 first. ORDER BY id only chooses among rows that are not currently locked, and bigserial allocation is not transaction commit order.

Ordering and multi-writer services

Outbox insert order inside each transaction should match business causality. Across instances, two concurrent checkouts for different orders may interleave — that is fine. Two events for the same aggregate usually must not reverse. Assign the aggregate-local version while locking or version-checking that business aggregate in the same transaction, so version 11 cannot commit before version 10. Consumers can then detect gaps; do not infer causality from the global outbox id.

Practical strict-order patterns:

1. Single active relay — dequeue each aggregate by aggregate_version; simplest ownership story, lower parallelism.
2. Exclusive aggregate shards — hash aggregate_id so one active relay owns each shard and publishes each aggregate in version order.
3. Durable publisher cursor — store the last confirmed version per aggregate; version 11 is ineligible until cursor 10 is published. This remains safe even while a lower producer transaction is uncommitted and invisible.
4. Ordered WAL / CDC stream — preserve database commit order, provided same-aggregate business transactions commit in causal/version order, then partition by aggregate at the broker.

A query that merely checks “no lower visible unpublished row exists” is safe only if the producer commit-order invariant above already holds. Otherwise an uncommitted version 10 is invisible and version 11 can look eligible. A durable expected-next cursor makes that prerequisite executable.

A broker partition key of aggregate_id preserves the order in which the broker receives events for that key. It cannot repair version 11 arriving before version 10, so pair the key with an ordered publisher pattern above.

What outbox is not

Claim people make	Reality
“Outbox = 2PC with Kafka”	No — broker can be down; rows wait in outbox
“Exactly-once everywhere”	No — relay duplicates; Day 30 owns effects
“Replace the queue”	No — outbox is the publisher’s durable inbox; broker still fans out (Day 14)
“NOTIFY is enough” (Day 48)	Wake only — without an outbox row, a missed listen loses the signal

What breaks? — Anti-patterns

• Dual-write in the request path and “we’ll alert on lag” — lag metrics do not resurrect lost events.
• Infinite outbox growth — prune or archive published_at IS NOT NULL; huge JSON forever is a disk bomb.
• Relay without idempotent mark protocol — crash after publish, before mark, without dedupe → storm.
• Global SKIP LOCKED relays on saga-sensitive aggregates — claim safety is not ordering; two workers can publish the same key out of sequence.
• Blocking the user request on broker ACK after outbox commit — you reintroduced dual-system latency; relay is async on purpose.
• Ignoring outbox depth — if the broker is slow, depth is backpressure (Day 44) on your publish pipeline; page before the table is huge.

How it connects

• Day 10: the only atomic boundary you fully control is the local transaction.
• Day 14: broker still does fan-out, buffering, and consumer groups.
• Day 30: OrderPlaced may arrive twice — message id / business key as idempotency key.
• Day 22 / 38: partial index + SKIP LOCKED make claims cheap and prevent duplicate concurrent claims; ordering needs a separate design.
• Day 48: NOTIFY can wake the poller; the outbox row remains truth if nobody listened.
• Day 44: outbox depth is a backpressure signal when the relay cannot keep up.

Transfer questions

1. Chen commits an order + outbox row, the relay publishes, then crashes before published_at. On restart it publishes again. Who must make that safe, and with what key?
2. You already use Day 48 LISTEN/NOTIFY to wake workers on new jobs. Why is that still not a substitute for an outbox when the “message” must reach another service’s Kafka topic?
3. A saga needs OrderPlaced then PaymentTaken for the same order id. How do you stop two relay instances from publishing those two outbox rows in reverse order?

What you should be able to do

• Draw dual-write vs outbox and name the crash window on each side.
• Quote the lab shape: ~0.15 dual-write loss rate vs 0 outbox loss with possible duplicates.
• Choose poll vs CDC relay with one tradeoff each.
• Explain why Day 30 is mandatory even after “perfect” outbox adoption.

Interview version (60s)

“I never dual-write to Postgres and Kafka in two steps. I insert the business row and an outbox row in one transaction, then a relay publishes and marks. Lab: with a 0.15 crash window, dual-write loses ~15% of events; outbox loses zero and may duplicate — consumers stay idempotent. Polling or CDC are relay mechanics, not the consistency model.”

Quiz

Questions? Ask the agent — continue with Day 50 (Optimistic Concurrency). You live from Day 24 (MVCC).