The transactional outbox

A service takes an order. It writes a row to its database, then publishes an order.created event so that billing, inventory and notifications can each do their part. Two writes, two systems, and nothing that makes them one.

Most of the time both succeed. The interesting case is the gap between them, where the commit lands and the process is killed before the publish. At a million orders a day, a window that catches one write in ten thousand is a hundred orders a day that were paid for and never shipped.

The dual write

BEGIN;
  INSERT INTO orders (id, customer_id, total) VALUES ($1, $2, $3);
COMMIT;

publish("order.created", payload);   -- a second commit, to a second system

Both orderings are broken. Commit first, and a crash before the publish leaves an order that exists in your database and nowhere else. Publish first, and a rollback leaves billing charging for an order that never existed.

A retry does not help, because the failure that matters is the process not living long enough to run it. Nor does pushing the retry onto a durable queue, since writing to the queue is the same problem again. The distributed transaction that would genuinely solve it, XA, puts a coordinator on the critical path of every write and is not supported by Kafka at all.

One commit instead of two

Your database can already make several writes atomic. So write the event into a table in the same database, in the same transaction as the business data, and let a separate process publish it.

BEGIN;
  INSERT INTO orders (id, customer_id, total) VALUES ($1, $2, $3);
  INSERT INTO outbox (id, aggregate_id, type, payload)
  VALUES ($4, $1, 'order.created', $5);
COMMIT;

Now there is one commit. Either the order and its event both exist or neither does. The publish can still fail, but the event is durable before anyone attempts it, so failing means "not yet" rather than "never".

What actually changes

The outbox does not remove the crash window, it moves it. The window now sits between publishing and recording that you published, and anything caught in it is sent twice instead of lost.

That is the trade, and it is a good one. A lost event is unrecoverable without an audit process you have to build and then trust. A duplicate is a problem you solve once, in the consumer.

Getting the events out

Something has to read the table and publish. The simple version polls:

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

SKIP LOCKED is doing the real work there. It lets several relay workers run at once, each claiming a different batch instead of blocking behind the first. The alternative is to stop polling and tail the database's replication log with Debezium or similar: lower latency, no query load, events in commit order, and one more piece of infrastructure to operate. Start with polling.

The ordering bug

Sooner or later someone replaces the null scan with a high-water mark: remember the last id, then WHERE id > $last_seen. It looks obviously better, and it silently drops rows.

T1 inserts and takes id 100. T2 inserts, takes 101, and commits first. The relay wakes, sees 101, publishes it, and stores 101 as its mark. A moment later T1 commits. Row 100 is now visible, sits below the mark, and is never read again.

Staying with published_at IS NULL avoids it. Row 100 arrives late, but it does arrive, and you have traded loss for out-of-order delivery. If you need strict order, tail the log instead, which reads in commit order and cannot have the problem. And be clear about which order you actually need: global ordering is expensive and rarely the requirement, while per-entity ordering is what people usually mean and comes from partitioning on aggregate_id.

You are signing up for duplicates

None of this delivers exactly-once. Publishing and recording the publish are two writes to two systems, which is the original problem moved one step downstream, and it cannot be moved any further. The consumer is the only place it can be solved.

• Naturally idempotent work. UPDATE orders SET status = 'paid' WHERE id = $1 is the same run once or five times. Prefer this; it costs nothing.
• A dedupe table. Insert the event id under a unique constraint before doing the work, and treat a conflict as "already seen". Needs a retention policy bounded by your longest redelivery window.

Kafka's exactly-once semantics are exactly-once processing inside Kafka. They say nothing about what your consumer does to a payment provider.

Housekeeping, and when to skip it

An outbox that is never cleaned becomes the largest table in the database. Delete rows once published, and on PostgreSQL expect that churn to cause autovacuum and index bloat trouble well before disk does; partitioning by day and dropping whole partitions avoids it. Treat the table as a queue rather than a log, and keep any permanent record somewhere built for one.

Three cases where you do not want an outbox at all:

• The reader shares your database. Do not publish anything, read the table.
• The event is genuinely just "this row changed". Point change data capture at the real table. The outbox earns its place when the event is a domain fact with its own shape, so you can refactor columns without breaking three teams.
• There is no local transaction to ride on. If the second write is a call to a payment provider, no outbox helps: you need an idempotency key on the call and a job that reconciles both sides.

The same trick works at the other end, where it is called an inbox. The consumer records the event id and performs its side effect in one transaction, so a redelivery finds the id already there and does nothing.

The outbox does not make distributed writes atomic. It gives you one commit instead of two, and converts silent loss into visible duplication. The rest belongs to the consumer, which is the only place it was ever solvable.

Further reading

• Richardson, C., Transactional Outbox, microservices.io.
• Debezium, Outbox Event Router, for the log-tailing version.
• Kleppmann, M., Designing Data-Intensive Applications, O'Reilly, 2017. Chapter 11 on message ordering, chapter 12 on end-to-end idempotency.