Transactional Outbox Pattern.

🧪The myth

"I INSERT the order into Postgres, then publish OrderCreated to Kafka. Both succeed 99.9% of the time. Good enough."

Actually: those are two systems that cannot commit together. That 0.1% isn't noise — it's silent, permanent data loss, and nothing in your logs will tell you.

⚠️The dual-write problem

db.commit()                   # ✅ order row exists
kafka.publish(OrderCreated)   # 💥 broker timeout / SIGKILL / GC pause

Order exists. No email, no invoice, no warehouse pick. Nobody retries — the request already returned 200.

Flip the order and it's worse:

kafka.publish(...)   # ✅
db.commit()          # 💥 deadlock → rollback

Now downstream ships an order that doesn't exist.

A retry loop does not fix this: the process can die between the two lines. XA/2PC technically fixes it, and nobody wants it — Kafka doesn't speak XA, and a blocked coordinator holds your locks hostage.

🧰The fix: one transaction, one database

Write the event as a ROW, in the SAME transaction as the business data. A separate process ships it later.

BEGIN;
  INSERT INTO orders(id, user_id, total) VALUES (...);
  INSERT INTO outbox(id, topic, payload)
  VALUES (gen_random_uuid(), 'orders',
          '{"type":"OrderCreated","order_id":"..."}'::jsonb);
COMMIT;

Atomic: both rows land or neither. Bonus — no broker on the request path, so your p99 drops too.

🚚The relay

SELECT id, topic, payload FROM outbox
WHERE published_at IS NULL
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 100;

→ publish → UPDATE outbox SET published_at = now() WHERE id = ANY($1);

SKIP LOCKED lets you run 5 relay workers with zero double-claiming. Upgrade path: drop the poller and tail the WAL with Debezium/CDC — same table, no polling lag.

🎯The nuance people get wrong

• You bought at-LEAST-once, not exactly-once. Crash between publish and UPDATE → the event resends. Consumers MUST dedupe on event id. Outbox fixes atomicity, never duplication.
• Ordering: ORDER BY a monotonic id, and use aggregate_id as the partition key so per-order events stay in sequence even with parallel relays.
• Purge it. An outbox with 40M published rows turns "WHERE published_at IS NULL" into a table scan. Delete on a schedule and keep a partial index:

CREATE INDEX ON outbox (id) WHERE published_at IS NULL;

• Don't store just the ID and let consumers re-read the row — by then it reflects state from three updates later. Snapshot the payload at write time.

🏋️Your exercise

Find one endpoint doing db.save() then queue.publish(). Add an outbox table + that partial index, move the publish into the transaction as an INSERT, and write a 20-line relay loop. Then kill -9 the relay mid-batch and verify two things: every event still arrives, and your consumer survives the one that arrives twice.