Step 2 of 14 · 1 min

Why you can't just do both

The code wants two things to happen together: change the database and send a message. There are only two orders to try them in, and both are broken:

Publish, then commit. If the commit fails, you've announced something that never happened. That's the welcome email to nobody.

Commit, then publish. If the publish fails, or the server dies between the two, something happened that you never announced. That's the missing users.

You might hope to wrap both in one transaction. Postgres transactions only cover Postgres; Kafka has its own transactions that only cover Kafka. There are protocols for transactions across two different systems (two-phase commit, or "XA"), but most message systems don't support them, and where they exist they're slow, complicated, and can leave both systems stuck waiting if the coordinator fails at the wrong moment. Almost nobody uses them for this.

There's a much simpler trick.

Step 3 of 14 · 1 min

The outbox

If the problem is that Postgres can't include Kafka in its transaction, then don't write to Kafka in the transaction. Write the message into Postgres, in a table set aside for it, and send it to Kafka afterwards.

CREATE TABLE outbox (
    id            bigserial PRIMARY KEY,
    event_id      uuid NOT NULL UNIQUE,
    topic         text NOT NULL,
    key           text NOT NULL,          -- Kafka partition key
    payload       jsonb NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now(),
    published_at  timestamptz
);

Sign-up now looks like this:

BEGIN;
INSERT INTO users (id, username, email, ...) VALUES (...);
INSERT INTO outbox (event_id, topic, key, payload)
VALUES (gen_random_uuid(), 'user_signed_up', '<user id>', '{"user_id": ..., "email": ...}');
COMMIT;

Both inserts are in one transaction in one database, so they happen together or not at all. If the user is saved, the message is saved too. If the transaction fails, neither exists. The welcome email to nobody and the missing users are both impossible now.

Then a separate process, the relay (also called a message relay or outbox publisher), reads unpublished rows from the outbox, sends each one to Kafka, and marks it as published. If the relay crashes, unpublished rows are still sitting in the table, and it picks them up again when it restarts.

That's the whole pattern: record the intent to publish in the same transaction as the change, and publish from the record. It's called the transactional outbox.

Step 4 of 14 · 2 min

Running the relay

The relay is simple to write and easy to get subtly wrong. A few details matter.

Polling

The simplest relay is a loop that queries for unpublished rows:

SELECT id, topic, key, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100;

then publishes them to Kafka, waits for Kafka to confirm, and marks them:

UPDATE outbox SET published_at = now() WHERE id = ANY(:ids);

An index on unpublished rows keeps the query cheap even when the table is large:

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

That's a partial index: it only includes rows matching the condition, so it stays tiny no matter how many published rows pile up.

Several relays at once

For speed and availability, you'll want more than one relay running. But if two relays run that same SELECT, they both get the same rows, and both publish them, so every event goes out twice.

Postgres has a feature made for exactly this:

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

FOR UPDATE locks the rows it returns, until the relay's transaction ends. SKIP LOCKED tells other relays to skip rows that someone else has locked, instead of waiting for them. So two relays running at the same moment get two different batches. When a relay finishes, it marks its batch published and commits, releasing the locks.

If a relay crashes while holding a batch, its transaction is rolled back, the locks are released, and another relay picks up those rows.

Order

Within one relay, rows are read in id order, so events go out in the order they were written. With several relays working in parallel, two batches can reach Kafka in either order. If order matters (it usually does for events about the same thing, like edits to one post), there are two common choices:

• Run one active relay, with a standby that takes over if it fails. Simple, and fast enough for most apps: a single relay publishing in batches of a few hundred can handle thousands of events a second.
• Split the work by key. Each relay handles a fixed share of keys (say, by a hash of the key), so all events for one post always go through the same relay, in order.

Chirp ran one active relay, plus a standby.

Cleaning up

Published rows are no longer needed. Delete them after a day or so (they're handy for debugging until then). Lesson 3.2's trick applies: with a busy outbox, it's much cheaper to split the table by day and drop old partitions than to delete millions of rows.

Or let change data capture do it

Instead of polling, a relay can read the outbox from the database's change log, exactly like the CDC pipeline in lesson 3.4. Every insert into outbox appears in the log; a CDC tool like Debezium turns each one into a Kafka message, and some CDC tools have built-in support for this outbox shape. There's no polling delay, no extra queries on the database, and ordering follows the log.

The trade-off is another moving part, with lesson 3.4's warning about replication slots attached. Polling is easier to start with; CDC is better at higher volume. Chirp started with polling and switched to CDC a year later.

Step 5 of 14 · 1 min

What consumers have to do

The outbox guarantees that every committed change produces an event. It doesn't guarantee only one. If the relay sends a batch to Kafka and then crashes before marking it published, the next relay sends the same batch again. That's lesson 4.1's at-least-once delivery, in a new place.

So consumers have to be ready for duplicates. Each event carries its event_id, which is unique and set when the outbox row was written. A consumer records which event IDs it has handled, in the same transaction as whatever the event causes it to do:

CREATE TABLE processed_events (
    event_id     uuid PRIMARY KEY,
    processed_at timestamptz NOT NULL DEFAULT now()
);

BEGIN;
INSERT INTO processed_events (event_id) VALUES (:event_id)
ON CONFLICT DO NOTHING;
-- if that inserted 0 rows, we've seen this event before: COMMIT and stop
-- otherwise, do the work:
INSERT INTO search_queue ... / UPDATE ... / etc.
COMMIT;

The primary key on event_id makes the check reliable even when two copies of the event arrive at two consumers at the same moment: only one insert succeeds. This is sometimes called the inbox pattern, the mirror image of the outbox.

When the consumer's work isn't in a database (sending an email, say), record the event ID before or after the send, and accept the small window that leaves, as lesson 4.1 described. Or, better, pass the event ID to the email provider as an idempotency key if it supports one.

Step 6 of 14 · 1 min

Is it worth it?

The outbox adds a table, a relay process, and a little extra work on every write. It's worth it when an event must not be lost and must never describe something that didn't happen. Sign-ups, orders, payments, posts, follows: anything where other systems act on the event.

It's overkill for events nobody depends on, like a "user viewed a profile" analytics event, where losing a few in a crash is fine and the simplest publish after commit is good enough.

Step 7 of 14 · 1 min

Other ways people solve this

You'll come across a few alternatives:

• Change data capture on the real tables. Skip the outbox and capture changes to users directly (lesson 3.4). This works well for keeping copies in sync. The difference is that it publishes row changes ("row in users inserted"), while an outbox publishes events you designed ("user signed up," with exactly the fields consumers need). Events are a cleaner contract between teams, because you can change your tables without changing what other systems receive.
• Make the log the source of truth. Write the event first, and have the database itself be one of the consumers that applies it. This is the idea behind event sourcing. It's powerful, and it changes how your whole system works, so it's a much bigger decision than adding an outbox.
• Listen and notify. Postgres's LISTEN/NOTIFY can wake up the relay as soon as a row is inserted, instead of it polling every second. Use it as a speed-up alongside polling, not instead of it, because notifications aren't stored and can be missed.

Step 8 of 14 · 1 min

From the real world: the race nobody can retry away

The first section of this lesson showed dual writes failing when one write fails. There's a nastier version where both writes succeed, and it's the reason the outbox exists. Martin Kleppmann drew it in a well-known 2015 talk and essay on why dual writes are a bad idea.

Two requests update the same post's title at almost the same moment, on two different app servers. Each one writes the database, then the search index.

Request 1 writes "A" to the database. Request 2 writes "B" to the database, after it. Request 2 then updates the index to "B". Then request 1, which paused for a moment (a GC pause, a slow network hop), writes "A" to the index. The database says B. The index says A. Neither request failed, so nothing retries, and nothing will ever notice.

The root problem: the database decided an order for the two writes, and the index got the writes in a different order. With two independent writes, there's no way to make them agree. The fix is to have one ordered source that everything else follows. That's what the outbox gives you (the outbox rows are written in the same transaction, in the database's commit order), and what change data capture gives you even more directly (the WAL is the commit order). Combined with the version checks in lesson 3.3, the index can never go backwards.

LinkedIn's engineers reached the same conclusion at larger scale. Jay Kreps' essay "The Log" (2013) explains how LinkedIn moved from many systems writing to many other systems to a single log that every system consumes, which is how Kafka came to exist.

Step 9 of 14 · 1 min

Going deeper: the gap in "id > last_seen"

The polling relay in this lesson needs to know which outbox rows it has already sent. The obvious design is a cursor: remember the highest ID published, then ask for WHERE id > last_seen ORDER BY id. It's simple, fast with an index, and it loses messages under load.

IDs from a sequence are handed out when a row is inserted, but the row only becomes visible to other connections when its transaction commits. Those happen in different orders.

Transaction 1 inserts an outbox row and gets ID 41, then does something slow before committing. Transaction 2 inserts and gets ID 42, and commits straight away. The relay polls: it can see 42 but not 41 (not committed yet), publishes 42, and moves its cursor to 42. Then transaction 1 commits. Row 41 now exists, but it's behind the cursor, and the relay will never ask for it.

It only happens when transactions overlap, which is to say, only under real load, which is why it survives testing.

Ways to avoid it:

• Poll for unpublished rows, not "rows after the cursor": a published_at IS NULL column with a partial index, and mark rows as published after sending. The order isn't strictly by ID any more, so consumers need per-key ordering or version checks (they should have them anyway).
• Leave a safety window: only consider rows older than a few seconds, and re-scan a window behind the cursor. Simpler, but it's a guess about how long transactions take.
• Read the database's log instead (change data capture, as in "Running the relay"). The WAL is written in commit order, so there's no gap.
• In Postgres, track transaction IDs (pg_snapshot_xmin) so the cursor never moves past a transaction that's still in flight. This is how some well-tested queue libraries built on Postgres do it.

Step 10 of 14 · 1 min

What Chirp changed

• Every place that published an event after (or during) a transaction now writes to the outbox table in the same transaction instead.
• One relay publishes from the outbox, using FOR UPDATE SKIP LOCKED, in id order, with a standby ready to take over. The outbox is partitioned by day.
• Every consumer records event_ids in a processed_events table, in the same transaction as its work.
• An alert fires if the oldest unpublished outbox row is more than 30 seconds old.
• A daily check compares sign-ups in users with user_signed_up events. It has matched every day since.

The welcome-email team, as a precaution, also changed their service to look the user up before sending. If the user doesn't exist, no email.

Step 11 of 14 · 1 min

In the simulation

In Stage 3, you choose how fan-out events are published when a post is created: directly after commit, or through an outbox. The simulation crashes servers at random moments. With direct publishing, a small fraction of posts never reach anyone's feed, and nothing reports an error. Lesson 4.4 picks this up.

Open the simulation →

Step 12 of 14 · 1 min

If you're here for an interview

Interviewers love this problem because it hides inside an innocent sentence: "save the order, then publish an OrderPlaced event." When you say something like that, add:

• "Those are two writes to two systems, so I'd use a transactional outbox": write the event into an outbox table in the same transaction, and have a relay publish it.
• "Consumers dedupe by event ID", because the relay delivers at least once.
• If they push further: SKIP LOCKED for parallel relays, or CDC instead of polling.

Mentioning that two-phase commit exists, and why you'd avoid it, is a good extra.

Step 13 of 14 · 2 min

Try these yourself

Step 14 of 14 · 1 min

Further reading

• Chris Richardson, "Pattern: Transactional outbox" and "Pattern: Idempotent consumer," on microservices.io.
• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 11, on keeping systems in sync, and Chapter 9, on why distributed transactions are hard.
• PostgreSQL documentation on SELECT ... FOR UPDATE SKIP LOCKED and partial indexes.
• Debezium documentation on its outbox support.
• Martin Kleppmann, "Using logs to build a solid data infrastructure (or: why dual writes are a bad idea)," 2015.
• Jay Kreps, "The Log: What every software engineer should know about real-time data's unifying abstraction," LinkedIn Engineering, 2013.