記録 · Entry

Transactional outbox pattern: events that never go missing

Write to the database, then publish to Kafka, and one of them will eventually fail. How an outbox table makes the write and the event a single commit.

9 min read

Distributed SystemsPostgreSQLReliabilityBackendMessaging

Here is the code that every event-driven system starts with, and that most of them are still running years later:

await db.transaction(async (tx) => {
  await tx.insertOrder(order)
})
await kafka.send('orders', { type: 'order.created', orderId: order.id })

Save the order, then tell the world. Two lines, and they read as one step. They are not one step, and the space between them is where events go to disappear.

#Two writes, and every order they can fail in

The database and the broker are two systems. There is no transaction that spans both, so whichever order you put the writes in, there is a failure that leaves them disagreeing.

Publish after commit, as above, and the process can die after the commit and before the publish. The order exists. No event was ever sent. Nothing downstream will ever know, and nothing in either system records that something is missing. This is the quiet one, and it is the one that happens in production, because the moment a process is most likely to be killed is during a deploy, and deploys happen every day.

Publish before commit, and the event describes an order that may then fail to commit. Consumers act on a row that does not exist. If the broker is down, you also now cannot save the order at all, and you have coupled the availability of your write path to the availability of Kafka.

Publish inside the transaction, and you get the worst of both. A publish that succeeds followed by a rollback has already leaked the event. A slow broker holds the database transaction open for the duration of the publish. And if the process dies between the broker acknowledging and the database committing, the event is out and the row is not.

Retrying the publish does not help, because the failure you cannot see is the one where the process is gone. Wrapping it in a try-catch does not help, because there is nothing to catch when the process is gone. The problem is structural. You are trying to make two systems agree without a transaction, and the fix is to stop trying and use the transaction you already have.

#Write the event where the transaction can see it

The outbox is a table in the same database as the data:

CREATE TABLE outbox (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  aggregate_type text        NOT NULL,
  aggregate_id   text        NOT NULL,
  event_type     text        NOT NULL,
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz
);

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

The application never talks to the broker. It writes the event as a row, in the same transaction as the business change:

await db.transaction(async (tx) => {
  await tx.insertOrder(order)
  await tx.query(
    `INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
     VALUES ('order', $1, 'order.created', $2)`,
    [order.id, { orderId: order.id, total: order.total }]
  )
})

Now there is exactly one write, and it is atomic. If the order commits, the event row commits with it. If anything fails, neither exists. The process can die at any instruction and the two tables cannot disagree, because they are one database honouring one commit.

The event is not on the broker yet. Getting it there is a separate job, and separating it is the entire point.

#The relay moves rows to the broker

A relay process polls the outbox for unpublished rows, publishes them, and marks them published:

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

For each row it sends the event to the broker, waits for the acknowledgement, and then:

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

The relay can crash at any point. If it dies before publishing, the rows are still unpublished and the next run picks them up. If it dies after publishing but before marking, the rows are still unpublished, the next run picks them up, and publishes them again.

That second case is the guarantee you are actually getting, so it is worth stating precisely. The outbox gives you at-least-once delivery. Every event that was committed will reach the broker. Some events will reach it more than once. Exactly-once is not on offer, from this pattern or from any other, and a design that assumes it will fail the first time a relay restarts.

#Consumers have to tolerate the duplicate

Since the same event can arrive twice, every consumer needs to recognise the second copy. The outbox row's id travels in the message as the event id, and the consumer keeps an inbox:

CREATE TABLE inbox (
  event_id   bigint PRIMARY KEY,
  handled_at timestamptz NOT NULL DEFAULT now()
);

Handling an event means inserting its id into the inbox and doing the work in the same transaction. The second copy fails the primary key on insert, the transaction rolls back, and the work is not done twice. This is the same mechanism as an idempotency key on a write endpoint, applied to a message instead of a request, and it needs the same discipline: the dedupe record and the effect commit together, or the dedupe is decorative.

If the consumer's effect lives outside its database, the inbox cannot protect it, and the effect itself has to be idempotent. Send the email, but key it on the event id so the mail provider refuses the repeat.

#Ordering is where SKIP LOCKED bites back

SKIP LOCKED lets several relays share the outbox without contention. It also lets them publish out of order. Relay A locks rows 1 to 100. Relay B skips them and takes 101 to 200, and finishes first. Event 150 is on the broker before event 50.

For a lot of events that is fine. For order.created followed by order.cancelled on the same order, it is not, and a consumer that sees the cancel first will process it against an order it has never heard of.

The honest answers are, in order of preference: run one relay, which removes the problem and is fast enough for most systems since publishing a batch of a hundred is a single round trip; or partition the outbox by aggregate_id so that every event for one order is always handled by the same relay, and only order across aggregates is lost, which nobody needed. Multiple relays racing over one unpartitioned table is the configuration that looks like a scaling win and is a correctness bug.

#The watermark that skips events

There is a subtler ordering trap that has nothing to do with multiple relays.

An obvious optimisation is to remember the highest id published so far and query WHERE id > $last instead of WHERE published_at IS NULL. It avoids the update, it avoids the partial index, and it is wrong.

Identity values are handed out when the row is inserted, not when it commits. Transaction A gets id 10 and is slow to commit. Transaction B gets id 11 and commits at once. The relay runs, sees 11, records 11 as its watermark. Then A commits. Row 10 is now visible, and the relay will never look below 11 again. The event is committed, durable, and permanently unpublished.

The published_at IS NULL query does not have this problem, because it does not care about position. Row 10 becomes visible, the next poll sees it, it goes out late but it goes out. Late is a delay. Skipped is data loss. Keep the update.

#Change data capture does the same job from the log

Polling adds latency of up to one poll interval and a small steady query load. If either matters, the alternative is to stop polling the table and read the write-ahead log instead.

Postgres logical decoding exposes every committed change as a stream, in commit order, with no polling. Debezium ships a connector that subscribes to that stream, and an outbox event router transform that takes changes to the outbox table specifically, turns each inserted row into a message, and routes it to a topic by aggregate_type. The application code is identical: write the row in the transaction. What changes is that no relay process exists, no published_at column is needed, and the latency is that of the replication stream, which is milliseconds.

The cost is an extra moving part with its own failure modes, a replication slot that will hold WAL on disk forever if the connector falls behind, and a much larger operational surface than a loop with a SELECT in it. Start with the polling relay. Move to CDC when you can name the number the poll interval is costing you.

#Keep the table small

Every published row is history. Delete it:

DELETE FROM outbox
WHERE published_at < now() - interval '1 day';

Run that on a schedule, in batches if the table is large. An outbox that is never pruned becomes the biggest table in the database within months, the partial index keeps the relay fast right up until vacuum falls behind, and then the relay is slow for reasons that took a long time to build and will take a long time to undo. If you need an event archive, the broker's retention or a warehouse is where it belongs, not the table whose job is to be empty.

#When you do not need any of this

A single service that reacts to its own writes needs no outbox. Call the function. A side effect that can be lost without anyone caring, such as a metrics increment, needs no outbox. Fire it and forget it. The pattern earns its keep exactly when a second system must learn about a change, and must not be allowed to miss it.

That describes most order, payment, and account events in most products, which is why the two-line version at the top of this post is so often the thing that is quietly losing them.

#The one-line summary

You cannot make a database and a broker agree with two writes. Make the event a row in the same transaction as the data, and let a separate process move rows to the broker, at least once, with consumers that can take the repeat.

#References

Related entries