The dual-write problem & the transactional outbox

Every problem so far lived inside one database, where BEGIN … COMMIT could always save you. This chapter is about the moment that stops being true: there is no BEGIN that spans PostgreSQL and Kafka. The theory (why two writes to two systems can't be made atomic, and how an outbox shrinks the damage) is Concepts: dual writes & the outbox; this page proves it on PostgreSQL, crashes included.

The dual-write problem

Write to the database and publish to the broker, two writes, two systems, and a process that can die between them:

Attempt 1 — write, then publish. The order commits…

App> BEGIN;
BEGIN

App> INSERT INTO orders VALUES (1, 'alice', 90);
INSERT 0 1

App> COMMIT;
COMMIT

…and the process crashes before the publish step ever runs. The broker never hears about order 1.

App> SELECT (SELECT count(*)::int FROM orders) AS orders,
            (SELECT count(*)::int FROM broker) AS events;
 orders | events 
--------+--------
      1 |      0 
(1 row)

Attempt 2 — publish first, then write. The event goes out…

App> INSERT INTO broker VALUES ('order_placed: order 2');
INSERT 0 1

…and then the order INSERT fails — a constraint, a crash, a timeout, anything.

App> INSERT INTO orders VALUES (2, 'mallory', -5); -- check_violation
ERROR:  23514: new row for relation "orders" violates check constraint "orders_amount_check"

Downstream services now process an order that never existed.

App> SELECT (SELECT count(*)::int FROM orders WHERE id = 2) AS orders,
            (SELECT count(*)::int FROM broker WHERE event LIKE '%order 2%') AS events;
 orders | events 
--------+--------
      0 |      1 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Write-first loses events; publish-first invents them. Retries only change the odds.

The fix: only ever write to one system

The application never talks to the broker at all. The event is written to the same database, in the same transaction as the order, and atomicity, which PostgreSQL has guaranteed since chapter 1, does the rest:

The order and its event are written in ONE transaction — both land in the same database.

App> BEGIN;
BEGIN

App> INSERT INTO orders VALUES (1, 'alice', 90);
INSERT 0 1

App> INSERT INTO outbox (event) VALUES ('order_placed: order 1');
INSERT 0 1

App> COMMIT;
COMMIT

Atomicity covers the failure path too: no committed order, no event.

App> BEGIN;
BEGIN

App> INSERT INTO orders VALUES (2, 'bob', 75);
INSERT 0 1

App> INSERT INTO outbox (event) VALUES ('order_placed: order 2');
INSERT 0 1

App> ROLLBACK;
ROLLBACK

App> SELECT id, event FROM outbox ORDER BY id;
 id |         event         
----+-----------------------
  1 | order_placed: order 1 
(1 row)

A relay claims the event exactly like a chapter-5 job-queue worker…

Relay> BEGIN;
BEGIN

Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id |         event         
----+-----------------------
  1 | order_placed: order 1 
(1 row)

…publishes it to the broker (an HTTP call — outside any transaction), deletes it, and crashes before COMMIT.

Relay> DELETE FROM outbox WHERE id = 1;
DELETE 1

Relay> ROLLBACK;
ROLLBACK

The delete evaporated with the crash — the event is still in the outbox. The restarted relay publishes it AGAIN: at-least-once delivery, so consumers must be idempotent.

Relay> BEGIN;
BEGIN

Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id |         event         
----+-----------------------
  1 | order_placed: order 1 
(1 row)

Relay> DELETE FROM outbox WHERE id = 1;
DELETE 1

Relay> COMMIT;
COMMIT

Relay> SELECT count(*)::int AS pending FROM outbox;
 pending 
---------
       0 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

A separate relay process moves events from the outbox to the broker. It is exactly the SKIP LOCKED job-queue worker from chapter 5, pointed at the outbox table.

At-least-once, by construction

Look closely at what the crash proved. The relay published the event, then died before committing the DELETE, so the event is delivered twice. That is the deal you signed: at-least-once delivery, and repeats are exactly what chapter 5's idempotency keys already handle on the consumer side.

The shape to remember: the order and its event commit or vanish together, with no window where one exists without the other, and a relay that is nothing more than a SKIP LOCKED worker (crash-safe, parallelizable, five lines of SQL) carries them onward. The price of that simplicity is at-least-once delivery, so consumers have to be idempotent. Polling the outbox also adds latency, which the next lesson trades away with a transactional wake-up call.

The anomaly catalog

The dual-write problem & the transactional outbox