Lucas Lusardo

No. 004 · November 3, 2025

The Outbox Pattern in Practice

The failure mode that gets people is never the one they were warned about. Everyone who’s read about dual writes knows you shouldn’t write to a database and publish to a message broker in two separate operations. Fewer people have watched it actually happen: a payment gets recorded, the process dies before the SNS publish, and now the ledger says “settled” while nothing downstream ever hears about it. No exception, no retry, no alert. Just a payment that quietly stopped existing to the rest of the system.

The shape of the problem

Two systems, one business event, no shared transaction:

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = ?;
  INSERT INTO ledger (...) VALUES (...);
COMMIT
-- publish("payment.settled", event)  <- not transactional with the above

The database commit and the publish are two different failure domains. Either can succeed while the other fails, and you don’t get to choose which. Distributed transactions (two-phase commit, 2PC) fix this on paper and are miserable in practice — you’re now coordinating availability across a broker and a database, and brokers are not built to be transaction participants.

What we actually run

The outbox pattern sidesteps coordination by writing the event to the same database, in the same transaction, as the state change:

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = ?;
  INSERT INTO ledger (...) VALUES (...);
  INSERT INTO outbox (id, topic, payload, created_at)
  VALUES (?, 'payment.settled', ?, now());
COMMIT

A separate relay process reads the outbox table and publishes to SNS, marking rows as published (or deleting them) once the publish is acknowledged. The relay can crash, restart, double-publish — none of that matters, because consumers are built to be idempotent on the event’s id — the same discipline covered in the idempotency post. What matters is that the outbox row and the state change are atomic. That’s the entire guarantee, and it’s enough.

The relay itself is a polling worker, and the query is the part that actually matters once you’re running more than one instance of it:

SELECT * FROM outbox
WHERE published_at IS NULL
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT n;

Without SKIP LOCKED, two relay instances polling concurrently fight over the same rows — the second worker blocks behind the first one’s row locks instead of doing useful work. With it, each worker skips rows currently locked by another worker and grabs the next available ones instead of waiting. That lock only lasts for the duration of the transaction, though — it’s not a permanent claim, and it doesn’t give a worker exclusive ownership of a row. If a worker locks a batch and dies before marking it published, another worker eventually picks the same rows back up and publishes them again, and that’s fine. Nothing here promises exactly-once. The relay can double-publish, and consumer idempotency is what makes that safe. What SKIP LOCKED buys is concurrency, not correctness — it’s the difference between “run a second relay instance” being a contention problem and being horizontal scaling.

The parts that bite

Ordering. The relay reads the outbox table in created_at order, but that only gives you ordering at the write end. SNS and standard SQS give no ordering guarantee downstream of it, and once more than one instance of the writer service is producing outbox rows, even created_at order isn’t guaranteed to match the real sequence events happened in. If a consumer’s correctness depends on strict ordering, the outbox pattern doesn’t get you there by itself — that has to be solved on the consumer side, with idempotent, state-checked writes instead of an assumption that messages arrive in the order they happened.

Table growth. The outbox is a queue implemented as a table, and tables that get inserted into constantly and deleted from constantly will bloat and fragment under most MVCC (multi-version concurrency control) storage engines. We run an aggressive vacuum/cleanup job dedicated to the outbox table, separate from the general schedule for the rest of the database.

Publishing can keep failing. A crashed relay is one failure mode; a relay that’s up but can’t get a publish to succeed — SNS rejecting it, throttling, whatever — is another, and “the next poll will pick it up again” isn’t a policy on its own. What we run is exponential backoff on the publish attempt, up to a configurable limit; past that limit, the row stops retrying automatically, gets marked failed in place, and the failure gets surfaced — an alert, some way of pulling a person in. Retrying forever just delays the moment someone finds out a row is actually stuck, not stalled.

“At least once” is a promise you have to keep downstream. The outbox guarantees the event is persisted for publication, not that it gets published exactly once. The gap is concrete: the relay publishes a row to SNS, SNS acknowledges it, and the relay crashes before it marks that row as published. The row is still sitting there with published_at IS NULL, so the next poll — this instance after restart, or another one — picks it back up and publishes it again. That’s one source of duplicates; SNS itself is at-least-once on top of that, so the same published message can also arrive at a subscriber more than once with the relay doing nothing wrong at all — the same guarantee covered in the idempotency post. If your consumer isn’t idempotent on event id, the outbox will faithfully deliver that duplicate, and the bug will look like it’s in the outbox when it’s actually in the consumer.

The pattern isn’t clever. That’s the point — the alternative is coordinating two systems that were never designed to be coordinated, and every version of that we tried was harder to operate than a table and a relay.

← all posts