The Transactional Outbox
Write the event as a row in the same transaction as the state change, then publish it from a background reader — at-least-once, by design.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
How do I guarantee that an event is published for every committed change, exactly as reliably as the change itself?
order.placed must reach the broker for every order that exists, and must never reach it for an order that does not. The publish may be late; it may not be missing.
Commit, then publish, and retry the publish a few times if it fails. That covers broker blips, which is the realistic failure.
It does not cover the process dying between the commit and the publish, which is the failure that actually loses events (The Dual Write Problem).
- It does not cover the process dying between the commit and the publish, which is the failure that actually loses events (The Dual Write Problem).
- Retrying in the request path means a broker outage becomes request latency and then pool exhaustion (Connection Pools).
- There is no record that the publish was owed, so nothing can ever repair the gap. The information that an event should exist lives only in a stack frame that is gone.
What is actually happening
- The event is written as a row in an `outbox` table in the same database, in the same transaction as the business change. One commit makes both durable, or neither.
- A separate reader — a poller or a change-data-capture consumer — selects unpublished rows, publishes them to the broker, and marks them published.
- If the reader crashes after publishing and before marking, it will publish that row again on restart. That is why the guarantee is at-least-once, and why every consumer must be idempotent (Job Idempotency).
- Multiple reader instances claim rows with a locking read that skips rows another reader already holds —
SELECT ... FOR UPDATE SKIP LOCKED— so scaling the reader does not duplicate work beyond the crash case. - Change-data-capture is the same idea with the polling removed: a connector reads the database's own replication stream and emits a message per committed row, so there is no query load and no marking step (Write-Ahead Logging).
- Ordering is per-partition at best. An outbox preserves insertion order within a single reader on a single key; it does not give global ordering, and neither does the broker (Webhook Retries and Ordering).
One commit, then a reader
The pattern has two halves and the split is the point. The write path is entirely synchronous and entirely inside one database: the order row and the outbox row commit together, and the handler returns. Nothing in the request path talks to the broker, so a broker outage cannot affect checkout at all.
The read path is a separate process whose only job is to turn rows into messages. It can be restarted, scaled, paused during a broker incident and resumed afterwards, and the events wait patiently in a table the whole time.
- 1Transaction writes both rows
Business change and outbox row commit atomically.
fails by Writing the outbox row in a separate transaction — the bug returns in full (The Dual Write Problem).
- 2Reader claims a batch
SELECT ... FOR UPDATE SKIP LOCKED LIMIT ninside its own short transaction.fails by Plain
SELECTthenUPDATE: two readers publish the same rows. - 3Publish to broker
Sends each message with the outbox id as deduplication key.
fails by Broker down — rows stay unpublished and the backlog grows visibly, which is the desired behaviour.
- 4Mark published
Sets published_at, commits the claim transaction.
fails by Crash here republishes on restart — the designed at-least-once window.
- 5Consumer processes
Applies the event, keyed by the event id.
fails by Non-idempotent consumer double-applies the duplicate (Job Idempotency).
- 6Prune
Deletes or archives published rows past retention.
fails by Never run — the outbox becomes the largest table and the claim query slows.
The only unbounded resource here is the outbox table. Everything else self-heals; the table needs a scheduled prune.
The claim query is the whole implementation
ANY($1). MySQL 8.0 has FOR UPDATE SKIP LOCKED but no partial indexes, so the equivalent is a status column with an index on it. On MySQL 5.7 and earlier there is no SKIP LOCKED at all and readers must claim with a conditional UPDATE and re-select.Most of the correctness of an outbox lives in six lines of SQL. The locking read is what lets several readers run without coordinating; the partial index is what keeps the poll cheap as the table grows; publishing before marking is what keeps the guarantee at-least-once rather than at-most-once.
1-- Keeps the claim query cheap no matter how large the history is2CREATE INDEX outbox_unpublished3 ON outbox (created_at)4 WHERE published_at IS NULL;5 6-- The reader, one short transaction per batch7BEGIN;8 9SELECT id, topic, payload10FROM outbox11WHERE published_at IS NULL12ORDER BY created_at13FOR UPDATE SKIP LOCKED -- other readers move past these rows14LIMIT 100;15 16-- ... publish each message to the broker, using id as the17-- ... deduplication key. If this fails, we ROLLBACK and the18-- ... rows are simply claimed again by someone later.19 20UPDATE outbox SET published_at = now() WHERE id = ANY($1);21 22COMMIT;Publishing happens between the claim and the mark, so a crash at any point replays rather than skips. SKIP LOCKED is what makes a second reader useful instead of a source of duplicates.
Be honest about at-least-once
The outbox eliminates lost events. It does not eliminate duplicates, and it cannot: between the broker acknowledging a message and the reader committing the mark, there is a window in which a crash means the row is still unpublished as far as the database is concerned. Making that window smaller does not make it zero, for exactly the reason the dual write could not be fixed by retrying harder.
So the guarantee is: every committed change produces at least one event, and no rolled-back change produces any. That is a strictly better failure mode than at-most-once, and it moves one obligation downstream — every consumer must be able to process the same event twice with the same result.
Say this out loud in design reviews. "Exactly-once" is where these systems go wrong, because someone builds a consumer that assumes it and the duplicate arrives six months later (Queue Semantics).
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Reader process stops | Downstream silently stops updating; the API is healthy | Nothing publishes; rows accumulate | Alert on oldest-unpublished age, not just on reader liveness (Health Checks: Startup, Readiness, Liveness) |
| One row fails to publish repeatedly | Backlog grows behind a single message | Poison payload blocking the batch | Attempt counter plus a dead-letter path (Dead-Letter Queues) |
| Mark before publish | Occasional missing events after restarts | At-most-once ordering reintroduced | Always publish first, mark second |
| Crash between publish and mark | Consumer sees the same event twice | The designed at-least-once window | Idempotent consumers keyed on the event id (Duplicate Detection) |
| Published rows never pruned | Claim query slows; table dominates the database | Unbounded growth | Scheduled delete or partition-and-drop; keep the partial index |
| Two readers, plain SELECT | Every event published twice, consistently | No locking claim | FOR UPDATE SKIP LOCKED or a conditional claim update |
| Consumer assumes commit ordering | Out-of-order updates, later state overwritten by earlier | Parallel readers and broker partitioning do not preserve global order | Version or sequence number in the payload; ignore stale versions (Webhook Retries and Ordering) |
How to build it
Most important first.
- Keep the outbox row small and stable: an id, a topic, a payload, a timestamp, a published marker. The payload should be the event contract, not a dump of the row (Naming Events).
- Generate the event id inside the transaction and use it as the broker's deduplication key, so a duplicate publish is recognisable downstream (Idempotency Keys).
- Claim rows with
FOR UPDATE SKIP LOCKEDand a bounded batch size, so several readers can run and one slow publish does not block the rest. - Publish first, mark second. The reverse order converts at-least-once into at-most-once and reintroduces the bug you are fixing.
- Delete or archive published rows on a schedule. An outbox table that is never pruned becomes the largest table in the database and slows the poller's own query.
- Monitor two numbers and alert on both: unpublished row count and age of the oldest unpublished row. A stalled reader is silent otherwise.
- Consider change-data-capture when polling load or latency becomes the constraint, and accept the operational component that comes with it.
What can go wrong
- The reader stops — crash-looped, deployed badly, stuck on a poison message — and nothing publishes. The database looks fine; downstream simply stops learning about anything.
- A poison row that fails to publish repeatedly and blocks the batch behind it. Needs an attempt counter and a dead-letter path (Dead-Letter Queues).
- Marking before publishing, which loses events on a crash — the exact failure the outbox exists to prevent, reintroduced by an ordering mistake.
- Unbounded table growth degrading both the poller query and the database's maintenance work.
- Duplicate publication treated as a defect and "fixed" with deduplication in the reader, which cannot work: the crash window is between the broker acknowledging and the row being marked.
- Payload built by re-reading the entity in the reader rather than captured at write time, so an event describes a later state than the one that caused it.
- Ordering assumed and not provided: consumers written as if events arrive in commit order break when the reader parallelises.
- Two readers claiming the same row — prevented by
FOR UPDATE SKIP LOCKED, not by a plainSELECTfollowed by anUPDATE. - Crash between publish and mark: the row is republished. This is the designed-in race, and it is why consumers must be idempotent (Duplicate Detection).
- Reader publishing an event whose consumer immediately reads back the entity from a replica that has not caught up — the consumer sees no such order (Replication Lag: Reads That Are Correct and Stale).
- Two events for the same aggregate published by different readers arriving out of order at the consumer; consumers that care need a version or sequence number in the payload.
- The outbox is a durable copy of event payloads sitting in your database. Whatever data classification applies to the event applies to the table, including retention (Sensitive Data Classification).
- Prune published rows on a schedule that matches your retention policy, not whenever someone remembers.
- Consumers must not treat an event as an authorization decision. An event says something happened; it does not say the recipient may act on it (Agent Authorization is the same mistake one domain over).
- Duplicate delivery must be safe for security-relevant consumers too — a replayed
permission.grantedmust not extend anything (Webhook Idempotency).
- "The outbox gives exactly-once." It gives at-least-once publication with no loss. Exactly-once *processing* is achieved by idempotent consumers, which are your responsibility, not the pattern's (At-Least-Once Delivery).
- "The outbox preserves ordering." Insertion order in one table is not delivery order after batching, retries, partitioning and parallel readers.
- "We can skip the outbox if we retry harder." Retries cannot survive the process that is doing the retrying.
- "It is just a queue table." A queue table is a fine thing; the outbox's defining property is that the enqueue happens in the same transaction as the state change. That is the whole point (Job Queues).
- "Once published, the row can stay." An unpruned outbox becomes your biggest table and eventually your slowest query.
Operating it
- Unpublished row count and oldest unpublished age. These are the two-number dashboard for the whole mechanism.
- Publish attempts, successes and failures per topic, plus per-row attempt counts to catch poison rows.
- End-to-end lag: commit timestamp to consumer processing timestamp, which is the number the product actually cares about (Depth Is Not an Emergency; Age Is).
- Reader liveness as its own health signal. A reader that is running but making no progress looks identical to one with nothing to do, unless you emit progress (Health Checks: Startup, Readiness, Liveness).
- Polling cost grows with table size unless the query is indexed on the unpublished predicate and published rows are pruned. A partial index on unpublished rows keeps the poll cheap regardless of history.
- At higher volume, several readers with
SKIP LOCKEDscale nearly linearly — until ordering matters, at which point you partition by aggregate id and accept per-partition ordering only. - At large scale change-data-capture replaces polling entirely, moving the load off the tables and onto the replication stream.
- Downstream consumers become the constraint before the outbox does, and their backlog is where the lag will actually show (Queue Backlog).
- You get "no event is ever lost" and you pay with "some events arrive twice". Every consumer now carries an idempotency obligation, forever.
- You add a background process with its own deployment, monitoring and failure modes. It is a small service, and it is a service.
- Publication is asynchronous, so downstream is eventually consistent with the database — a window the product must tolerate (Eventual Consistency in Practice).
- The outbox table is write amplification on the hot path: every business transaction now writes an extra row.
- Change-data-capture removes the polling cost and adds an infrastructure component coupled to your database's replication configuration.
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALThe pattern works with any transactional database and any broker; only the claim syntax and the CDC tooling change.
- DATABASE-SPECIFIC
SELECT ... FOR UPDATE SKIP LOCKEDis available in Postgres 9.5+ and MySQL 8.0+, and absent from earlier MySQL, where readers must be serialised or claim rows with a conditionalUPDATE ... WHERE status = 'new'instead. Change-data-capture reads logical replication on Postgres and the binlog on MySQL, which have different configuration, permissions and ordering guarantees. - SCALE-SPECIFICAt low volume, polling every second or two is entirely adequate and CDC is unjustified complexity. The crossover comes when poll frequency and table size make the claim query itself a load problem.
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.
- — Distributed Systems — delivery semantics, deduplication windows and why "exactly-once" is a property of processing rather than of transport.