On this path · Distributed Primitives 1. The Outbox Pattern, Deeply
The Outbox Pattern, Deeply
All six outbox incidents, the partitioned-outbox revamp, and why this is Trade Republic's flagship reliability pattern.
Learning outcomes
A broker moves money in a database and notifies the rest of the world through a message stream. Those two systems do not share a transaction, so a crash between them leaves the database updated but the outside world blind to it. The outbox pattern is the standard fix, and the interesting lesson is not the pattern itself but the six production incidents it took to make it run under real load.
After studying this page, you can:
- Explain the dual-write problem and why wrapping a database write and a Kafka publish in one call cannot work.
- State the outbox invariant: the business row and the event row commit in one transaction, so they succeed or fail together.
- Diagnose all six Trade Republic outbox incidents, from clock skew to visibility-map collapse, and name the fix for each.
- Justify the partitioned-outbox design: one partition for unpublished rows, one for published, partition pruning instead of a partial index.
- Reason about migration limits (no in-place conversion to partitioned), index bloat, and autovacuum tuning.
- Defend every design choice as an interviewer probes ordering, batch size, poison pills, and scale.
Before we dive in
Picture a sell order. A customer taps the button to sell one hundred shares. The trading service debits the holdings table, credits the cash balance, and then needs to tell a dozen downstream systems that this trade happened: the portfolio view, the tax ledger, the push notification service, the analytics warehouse. The safest place to record the financial truth is the relational database, because a row there is durable, transactional, and queryable. The safest way to fan the news out to many consumers at high throughput is Kafka, because it is an append-only log that many services can read independently.
The moment you need both, you hit a wall. The database transaction commits, or it does not: that is an atomic all-or-nothing promise that lives inside the database engine. The Kafka publish succeeds, or it fails: that is a separate network call to a separate system that knows nothing about your database connection. There is no transaction that spans both. If the application commits the database row and then crashes before publishing to Kafka, the database says the trade happened and nobody downstream knows. The customer’s balance moved, but the portfolio screen still shows the old holdings. If you publish to Kafka first and then crash before committing the database row, the opposite disaster happens: downstream systems act on a trade the database never recorded.
This is the dual-write problem, and it is the origin of the outbox pattern. The outbox is not a clever optimization. It is the answer to a concrete reliability question: how do you make two systems that cannot share a transaction behave as if they did?
The dual-write problem
A dual-write is any code path that writes to two independent systems inside one logical operation and needs both writes to agree. In a brokerage, the canonical dual-write is a balance update plus a Kafka event. The two writes cannot share a transaction because Kafka does not participate in PostgreSQL transactions. Wrapping them in one database transaction does not help: the Kafka call is a network side effect that happens regardless of whether the transaction later commits, which risks phantom events if the transaction rolls back after the publish, and it holds the database transaction open for the duration of a network call, which is a long-running transaction that blocks vacuum and degrades the whole database.
There are three naive attempts, and each fails for a specific reason. You could publish first then commit, which risks phantom events: a downstream consumer reacts to a trade that the database then rolls back. You could commit first then publish, which risks lost events: a crash after the commit means the trade is permanent but the event never fires. You could try to publish and commit in one transaction, but the publish is a side effect the database cannot roll back, so a transaction abort still leaves the event in Kafka. None of these is safe.
The outbox dissolves the problem by removing the second write from the application’s critical path entirely.
What an outbox is, plainly
The idea is almost embarrassingly simple once stated. Instead of writing to the database and then publishing to Kafka in two calls, you write only to the database: you insert the business row (the balance change) and an event row into an outbox table in the same transaction. Because both rows live in the same database, they share one transaction and commit atomically. Either both are durable, or neither is. There is no second call that can fail independently.
A separate process, the publisher or relay, then reads unpublished rows from the outbox table and publishes them to Kafka. Once a row is published, the publisher marks it so. The publisher’s job is to guarantee at-least-once delivery: it publishes, then marks. If it crashes after publishing but before marking, it will publish the same row again on restart, which means consumers must be idempotent. The business operation never talks to Kafka directly. The only thing the business transaction does is write two rows that commit together.
The tradeoff is now explicit and honest. You have traded a distributed consistency problem (make two systems agree) for a local consistency guarantee (two rows in one transaction) plus an operational problem (run a reliable publisher). Local consistency is something the database already knows how to do. The operational problem is bounded and observable. That is a good trade, which is why every serious event-driven system converges on some form of outbox.
You still have to deliver events reliably, but the delivery is now decoupled from the business transaction. The transaction is fast (two inserts), and the publisher retries at its own pace without holding a database connection open over a network call.
So far this is textbook. The interesting engineering begins when the outbox meets real load, which is exactly where Trade Republic published the incidents that fill the rest of this page.
The six incidents
Trade Republic ran a polling-based outbox in production and documented six distinct failure modes it hit before redesigning the table. Each incident teaches a separate lesson about ordering, data types, indexing, transaction length, error handling, and the physics of PostgreSQL’s visibility map. We take them in the order they hurt.
1. Clock skew breaks total ordering
The publisher reads unpublished rows in some order so it can publish them in sequence. The natural choice is to order by a timestamp column, because a timestamp feels like a universal ordering key. It is not.
The trading service runs on multiple pods, each with its own clock. Wall clocks drift. One pod’s clock might be a few milliseconds ahead of another’s. When two pods each insert an outbox row at nearly the same instant, the row with the earlier wall-clock timestamp may represent the logically later event. A customer who sold shares and then immediately bought more could see the buy published before the sell, because the sell row’s timestamp came from a pod whose clock lagged. Ordered by timestamp, the publisher sends the events in the wrong order, and a downstream portfolio service that consumes them in arrival order computes the wrong intermediate balance.
Wall-clock timestamps across machines are not a total order. NTP smooths drift but never eliminates it, and a pod can be milliseconds or more off its neighbors. Logical order and clock order are different things.
The fix is to order by a database-generated sequence identifier instead of a timestamp. A sequence is a single counter maintained by the database engine, incremented monotonically under its own lock. Every insert, from every pod, draws the next value from the same sequence, so the sequence identifier is a genuine total order of insertion. Two pods racing to insert will each get a distinct, strictly ordered value, and the publisher reading by sequence identifier recovers the true commit order.
Note the subtlety: a sequence does not reflect the order in which transactions commit, only the order in which they draw their identifier, which is close enough for event ordering because the gap between drawing and committing is tiny and the publisher only reads committed rows. The sequence gives a stable, monotonic ordering key that no skewed clock can corrupt.
2. Data types that quietly overflow
The sequence column needs a type large enough to never exhaust. The first instinct is often a 32-bit integer, which holds about 2.1 billion values. A busy brokerage inserts millions of outbox rows per day. Run that for a year or two, and a 32-bit sequence runs out of values. When it does, every insert fails, and because the outbox insert is inside the business transaction, every trade fails. The system does not degrade; it stops.
Two choices matter here. First, the identifier is int8, a 64-bit bigint, which holds roughly 9.2 times ten to the nineteenth power values. That is effectively unbounded for any realistic service lifetime. Second, the type is GENERATED ALWAYS AS IDENTITY, the modern PostgreSQL replacement for SERIAL. ALWAYS means the database refuses to let an insert supply its own identifier, which prevents a misbehaving client from injecting a value that breaks the monotonic ordering guarantee. The timestamp column uses timestamptz, not timestamp, so it records an absolute instant rather than a wall time whose meaning shifts with session timezone.
3. The wrong index
The publisher’s query is always some form of: find unpublished rows, in order, take a batch. The naive index is on the published_at column, on the theory that the publisher filters on it. That index is useless, and the reason is subtle and worth getting right because interviewers love it.
The publisher queries rows where published_at IS NULL. A standard B-tree index on published_at indexes the non-null values: all the published rows. An index lookup for IS NULL finds nothing useful there, because NULL values are the ones the publisher wants and they are exactly the rows a plain index does not efficiently serve. The planner falls back to a sequential scan of the whole table every poll, which at scale is catastrophic.
The correct index is a partial index: index the identifier, but only for rows where published_at IS NULL.
A partial index is small because it excludes everything already published, which over time is the vast majority of rows. It directly serves the query SELECT ... WHERE published_at IS NULL ORDER BY id, returning the publisher’s batch in order without touching published rows at all. This is the difference between an index that helps and one that sounds plausible but does nothing for the actual query. The lesson: index for the predicate your query actually uses, and use a partial index when the working set is a small, stable fraction of the table.
4. The batch that blocks autovacuum
A publisher that wants throughput will grab many rows per iteration. Why publish ten at a time when you could publish ten thousand? The instinct is reasonable, and the failure it causes is one of the most insidious in PostgreSQL operations.
Publishing a batch happens inside a transaction: the publisher opens a transaction, reads the batch, publishes to Kafka, marks the rows published, and commits. The larger the batch, the longer that transaction stays open. A long-running transaction is dangerous in PostgreSQL because of how vacuum works. PostgreSQL uses multiversion concurrency control: an update or delete does not change a row in place, it creates a new version and marks the old one dead. Autovacuum is the background process that eventually reclaims dead row versions so the table does not grow without bound.
The problem is that autovacuum cannot reclaim a dead row version if any open transaction might still need to see it. A long-running publisher transaction, holding the table open while it slowly publishes a huge batch, pins the database’s notion of the recent past. Every row that died after the transaction started cannot be vacuumed, because the transaction could theoretically still read it. Dead rows accumulate. The table bloats. Other queries slow down as they scan past dead versions. In the worst case, the database approaches transaction identifier wraparound, which forces an emergency, blocking autovacuum that can take the whole database offline.
Every long-running transaction delays vacuum for every row that died after it started. A publisher batch is a transaction, so a huge batch is a long transaction. The fix is small batches, on the order of one hundred per iteration, so each transaction is short and vacuum keeps up.
The documented Trade Republic guidance is to start around one hundred messages per batch. That keeps the publish transaction short, so it releases its hold on the table quickly and autovacuum can proceed. A smaller batch trades a little throughput for far better vacuum behavior, which protects the whole database, not just the outbox. The lesson is that batch size is not a throughput knob to maximize; it is a transaction-length knob that trades publisher speed against database health.
5. The poison pill
A poison pill is a message that the publisher cannot successfully deliver: the Kafka broker rejects it, a serialization fails, or a downstream schema rejects it, and the failure is permanent for that one message. The naive publisher treats the batch as one transaction: read a batch, publish all of them, and if any one fails, roll back the whole transaction. Now the poison pill sits at the front of the batch. The next iteration reads the same batch, hits the same poison pill, fails again, rolls back, and the batch never advances. One bad message stalls the entire outbox. Every event behind it, including events for totally unrelated accounts, is stuck.
The fix is to stop treating a batch as an atomic unit of delivery. Group messages by their key, typically the entity or account identifier, and publish and retry each group independently. A poison pill in one group fails only that group. The other groups advance. The stuck group can be retried, quarantined, or routed to a dead-letter topic while the rest of the outbox keeps flowing.
The deeper point is about failure isolation. A batch is an optimization, not a correctness boundary. Correctness requires that each event be delivered at least once and marked; it does not require that a group of events succeed or fail together. Designing the publisher so that the failure of one message cannot block unrelated messages is what keeps the outbox available under partial failure.
6. Visibility-map collapse and the revamp
The sixth incident is the one that triggered the full redesign. Even with a correct partial index, the outbox degrades catastrophically under sustained insert and publish load, and the cause is deep in PostgreSQL’s storage engine.
The publisher’s partial index points at unpublished rows. To check whether an index entry is still valid, PostgreSQL must visit the heap (the table’s main storage) to confirm the row’s visibility: is this row still visible to my transaction, or was it updated or deleted? In a high-churn table, where rows are constantly inserted and then marked published, the visibility map, which records which pages contain only visible rows and therefore allow index-only scans, cannot keep up. The index cannot answer visibility from itself, so every index entry the publisher considers forces a heap fetch.
At low volume this is fine. At high volume the partial index bloats, because it accumulates entries for rows that were unpublished when indexed but are now published, and the dead entries are not reclaimed fast enough. The number of heap fetches per publisher query explodes. Trade Republic documented query times growing by roughly five orders of magnitude as the table churned. The partial index, which was the right answer at small scale, becomes the bottleneck at large scale because it cannot prune itself fast enough and it cannot answer visibility without touching the heap.
This is the incident that prompted the partitioned-outbox revamp, which is the subject of the next section.
The partitioned-outbox revamp
The redesign changes the table’s structure so that the publisher never needs to check visibility at all. Instead of one table with a partial index, the outbox is partitioned by the published_at column into two partitions: one for unpublished rows, where published_at IS NULL, and one for published rows, the DEFAULT partition.
Why does this fix the visibility problem? Because every row in the unpublished partition is guaranteed to be unpublished by the partition definition itself. The publisher does not need a partial index, and it does not need to check visibility per row: it simply reads from the unpublished partition, and it knows every row there is work to do. There is no index to bloat, because there is no index on the working set at all; the partition itself is the index. The publisher scans the small unpublished partition directly.
When the publisher marks a row as published, it sets published_at. That changes the partition key, so PostgreSQL physically moves the row from the unpublished partition to the published partition. This row movement is the mechanism that keeps the unpublished partition small: published rows leave it. The critical operational rule, and an interviewer favorite, is that the UPDATE predicate must always include the partition key.
When marking rows published, the WHERE clause must say WHERE id = $1 AND published_at IS NULL, not just WHERE id = $1. The partition key in the predicate lets the planner prune to the unpublished partition alone. Without it, the planner may scan the published partition too, defeating the whole design.
Once rows are published, the published partition grows. Rather than delete published rows one by one, which generates more dead tuples and vacuum work, the revamp uses TRUNCATE on the published partition. Because every row in the published partition is, by construction, already published, truncating it loses no information: the events have all been delivered to Kafka. TRUNCATE is far cheaper than row-by-row deletion, because it simply removes the storage files rather than marking rows dead for vacuum to reclaim later.
The aha reframe is this: the redesign stops treating published as a flag you index, and starts treating it as a location. Unpublished rows live in one place, published rows live in another, and movement between them is the database’s partition-routing machinery, not an index lookup with visibility checks. That shift eliminates the entire class of visibility-map and index-bloat problems.
Migrating to a partitioned table
You cannot convert a regular table to a partitioned table in place. PostgreSQL does not support adding a partition scheme to an existing plain table with a single command. Migrating a live, high-throughput outbox from the old single-table design to the new partitioned design is its own online operation, and Trade Republic documented the care it takes.
The migration requires creating the new partitioned table alongside the old one, dual-writing to both during a transition window, backfilling the historical rows, and then cutting the publisher over to read from the new table. Because the outbox is in the write path of every trade, the migration cannot take the system down. It must be done with shadow writes and a controlled cutover, verifying at each step that no event is lost or duplicated.
The second operational lesson from the migration is about index bloat, which is distinct from the visibility-map problem. When rows are updated or deleted, PostgreSQL’s vacuum marks the dead index entries invalid, but it does not remove them from the index structure. Over time the index accumulates dead entries that take up space and slow scans, even though vacuum has logically reclaimed them. The remedy is periodic REINDEX on the affected indexes, or tuning autovacuum to run more aggressively on the outbox partitions so dead entries are reclaimed sooner.
Vacuum reclaims dead tuples for reuse and marks their index entries invalid, but it does not shrink the index. Periodic REINDEX, or building a fresh index concurrently, compacts the structure. For a high-churn table like the outbox, autovacuum tuning and scheduled REINDEX are part of routine care, not one-time fixes.
Tuning autovacuum for the outbox specifically means lowering the threshold at which autovacuum triggers for that table, so it runs more often and keeps dead tuple counts low. The outbox’s churn rate is higher than most tables, so the default autovacuum thresholds, which are tuned for typical workloads, are too permissive. Per-table autovacuum settings let you tell PostgreSQL to vacuum the outbox partitions far more aggressively than the rest of the database.
What breaks at scale, and the publisher throughput ceiling
The partitioned outbox solves the visibility-map collapse, but it does not eliminate every limit. As the insert rate keeps climbing, the next wall is the publisher itself. A single publisher instance reads the unpublished partition, publishes to Kafka, and marks rows, all sequentially. Its throughput is bounded by the round trip of one publish-and-mark cycle, and when the insert rate exceeds that throughput, the unpublished partition grows without bound, and latency from commit to delivery rises.
The response is horizontal scaling of the publisher by sharding the sequence range. Because rows are ordered by the sequence identifier, multiple publisher instances can each own a disjoint slice of the identifier space: one publishes rows with identifiers in one range, another the next range, and so on. Each instance reads only its slice, so they do not collide, and the combined throughput scales with the instance count. The cost is that ordering across slices is no longer guaranteed, which is acceptable because ordering matters per account key, not globally, and a single account’s events fall within one slice if the sharding respects the key.
The deeper lesson is that each scale wall has a specific structural response, not a bigger-machine response. The visibility-map wall was answered by partitioning (a structural change), not by a bigger index. The publisher-throughput wall is answered by sharding the work (a structural change), not by a faster instance. A senior engineer recognizes the wall as structural and reaches for the structural fix, rather than tuning parameters on a design that has hit its fundamental limit.
Common mistakes candidates make
A candidate asked to design an event pipeline for a brokerage makes a predictable set of errors. The most common is treating the dual-write as solvable by care: “I will publish to Kafka and then commit, and if the commit fails I will compensate.” Compensation requires knowing the publish succeeded, which you cannot know reliably after a crash, and it pushes a distributed-consistency problem into ad hoc application code that will have bugs. The outbox exists precisely so you do not have to write that code.
The second common mistake is ordering by timestamp. Candidates reach for created_at as the ordering column because it feels natural, and they do not realize that wall-clock order across pods is not a total order until an incident teaches them. The correct answer is a database sequence, and a strong candidate explains why a sequence’s monotonic guarantee is sound where a timestamp’s is not.
The third is the index mistake. Candidates index published_at because that is the column the query filters on, without noticing that a plain index serves the non-null values and the query needs the nulls. The fix is a partial index, and at the frontier, the partitioned design that removes the index entirely.
The fourth is batch-size arrogance. Candidates maximize the batch for throughput and do not see that the batch is a transaction whose length blocks vacuum. The right answer is a modest batch, around one hundred, chosen to keep transactions short.
The fifth is the poison-pill blind spot. Candidates assume every message will publish successfully and design a batch that fails atomically. The production answer is grouping by key and retrying groups independently, so one bad message cannot stall the pipeline.
The sixth, and the one that separates senior from mid-level, is missing the visibility-map collapse entirely. A candidate who has only read the textbook outbox stops at the partial index and never asks what happens to that index under heavy churn. The partitioned revamp is the answer to a question most candidates do not think to ask: what happens to my index when the working set churns as fast as the inserts?
Defending the design
An interviewer who has seen the outbox before will probe the design at each layer. Here is how to defend it.
On ordering, the defense is: we order by a database-generated sequence identifier, not a timestamp, because the sequence is a true total order drawn from a single counter, immune to clock skew. On durability, the defense is: the business row and the event row commit in one transaction, so the event is guaranteed to exist if and only if the business change happened. On delivery, the defense is: the publisher delivers at-least-once, marking rows after a successful publish, so a crash between publish and mark means a duplicate on restart, which is why every consumer must be idempotent. On scale, the defense is: we partition by the publication marker so the working set is physically separated from completed work, we keep batches short to protect vacuum, we isolate poison pills by key, and we truncate rather than delete to reclaim space cheaply.
The question an interviewer asks next, almost always, is what happens if the publisher is slow and the unpublished partition grows. The answer is that the unpublished partition is bounded by the publisher’s throughput, and if throughput cannot keep up, the publisher scales horizontally by sharding the sequence range: each publisher instance owns a disjoint slice of the id space. The question after that is about exactly-once delivery, and the honest answer is that exactly-once is achieved end to end through idempotent consumers and transactional sinks, not by pretending the publisher can publish exactly once. At-least-once plus idempotency is the achievable, defensible contract.
Mastery Questions
Question
Why can you not solve the dual-write problem by wrapping the database commit and the Kafka publish in one transaction?
Answer
Because Kafka does not participate in PostgreSQL transactions. The publish is a network side effect that happens regardless of whether the transaction commits, so a rollback after the publish leaves a phantom event in Kafka, and holding the transaction open for the network call creates a long-running transaction that blocks vacuum. The two writes are independent, so no local transaction can make them atomic.
Sources & evidence8 claims · 2 cited
All claims backed by Trade Republic outbox/debezium/partitioning sources; frontier 10x/100x reasoning and isolation-anomaly detail is internal-reasoning.
- Sorting outbox messages by created_at across pods breaks total ordering because pods have skewed clocks; a database sequence id is the correct ordering key, as a single counter incremented under the database lock gives a genuine monotonic total order.verified
- The outbox id column should use int8 (bigint) GENERATED ALWAYS AS IDENTITY rather than int4 or SERIAL, because int4 sequences exhaust at about 2.1 billion values under sustained insert load, and ALWAYS prevents clients from supplying a value that breaks ordering.verified
- The correct outbox index is a partial index on id WHERE published_at IS NULL; indexing published_at directly is useless because a plain B-tree serves the non-null published values while the publisher needs the null unpublished rows, forcing a sequential scan.verified
- Picking too many messages per publisher iteration opens a long-running transaction that blocks autovacuum from reclaiming dead tuples, causing table bloat and risk of transaction id wraparound; about 100 messages per batch is a good starting point.verified
- A poison-pill message that can never be delivered stalls the whole publish transaction when the batch fails atomically; grouping messages by key and retrying groups independently isolates the failure so unrelated events keep flowing.verified
- At high insert and publish churn the partial index bloats and visibility-map heap fetches explode query time by roughly five orders of magnitude; the revamp partitions the outbox by published_at into a NULL unpublished partition and a DEFAULT published partition so every row in the unpublished partition is guaranteed unpublished by construction.verified
- With a partitioned outbox, the UPDATE predicate must always include the partition key (published_at IS NULL) so the planner prunes to the unpublished partition alone, and the published partition is TRUNCATEd rather than deleted because every row there is already delivered.verified
- PostgreSQL cannot convert a regular table to a partitioned table in place, so migration requires an online procedure; index bloat accumulates because vacuum marks dead index entries invalid but does not remove them, requiring periodic REINDEX or autovacuum tuning.verified
Cited sources
- PostgreSQL + Outbox Pattern Revamped Part 1: Incidents and a new design pattern · Trade Republic Engineering (Sadeq Dousti)
- PostgreSQL + Outbox Pattern Revamped Part 2: converting to partitioned, index bloat, autovacuum · Trade Republic Engineering (Sadeq Dousti)