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