On this path · Data Infrastructure 1. PostgreSQL at Brokerage Scale
PostgreSQL at Brokerage Scale
MVCC, vacuum and autovacuum, index bloat, partial indexes, partition pruning, and long-running-transaction hazards.
Learning outcomes
A brokerage is not a typical web app. It must record every balance change, every order, and every legally mandated event durably and in order, while also fanning those events out to a streaming backbone in near real time. PostgreSQL sits at the center of that tension: it is the source of truth for money, and it is the feeding ground for the event stream. Running it at Trade Republic scale is less about writing SQL and more about understanding what the engine does silently when you update one row a billion times.
After studying this page, you can:
- Explain why MVCC creates dead tuples, why vacuum cannot remove them from indexes, and why that causes bloat on a high-churn table.
- Justify a partial index on
id WHERE published_at IS NULLover an index on a status column, and predict when index-only scans degrade into heap fetches. - Choose bigint identities over int4 sequences, and reason about primary key exhaustion as a design constraint.
- Decide between hash and list partitioning using measured tradeoffs, and explain why every predicate must carry the partition key.
- Describe how planner statistics let Postgres estimate cardinalities without scanning, and why stale statistics are dangerous.
- Connect the WAL and logical replication to change data capture and the outbox, and name why dual writes are forbidden.
- Defend the whole design under skeptical interview questioning and name the mistakes that expose a junior model.
Before we dive in
Imagine an order is filled. In the same breath the system must deduct cash from the customer balance, mark the order executed, and tell the rest of the company what happened: the notification service, the portfolio analytics, the tax engine, the ledger. The obvious first instinct is to update the database, then call Kafka. That instinct is the single most expensive bug in event driven systems, and the reason an entire class of patterns exists around it.
The trouble is that a database transaction and a Kafka produce are two separate commitments. Kafka does not participate in a PostgreSQL transaction. If you commit the balance change and the Kafka call then fails, the money moved but nobody learned about it: a phantom balance, invisible to downstream systems. If you publish first and the commit fails, downstream systems react to an order that never settled. Wrapping both in one transaction does not help, because there is no transaction that spans Postgres and Kafka. The deeper problem is that the two systems disagree on what committed means, and you cannot paper over that disagreement with a try catch.
The response forced into existence is the transactional outbox. Instead of publishing directly, the application writes the business change and an event row into the same database transaction. Either both land or neither does; there is no phantom state. A separate process then reads the unpublished rows and publishes them to Kafka. The database, not Kafka, is the source of truth, and the stream becomes a projection of it. Trade Republic runs exactly this pattern, and the engineering blog documents the production incidents and the redesign that followed. Understanding that redesign is understanding PostgreSQL at brokerage scale, because every choice in it is a choice about what the database does under write pressure.
The outbox: where Postgres meets the stream
The outbox table holds one row per unpublished event. A publisher process repeatedly selects a batch of rows where the event has not yet been sent, publishes them to Kafka, and marks them sent by setting a published timestamp. The whole loop looks innocuous until you measure it at the insert and publish rates a brokerage generates, where millions of rows turn over per day.
The naive design indexes the status column itself, so a publisher can find unpublished rows quickly. That design fails on a table with this churn profile, and the reason is the subject of the rest of this concept. First, though, one correctness detail that has nothing to do with performance: the ordering key.
The publisher selects ORDER BY id within the unpublished set, never by created_at. Sorting by creation timestamp across application pods is a trap: each pod has a slightly skewed clock, so the global order you observe does not reflect the order events were actually generated. The database sequence is a single monotonic source, so it gives a defensible total order. A publisher that grabs too large a batch in one transaction opens a long running transaction that blocks cleanup, which we will see is catastrophic. About a hundred rows per iteration is a sound starting point.
Why a partial index, not an index on the status column
The candidate instinct is to add a boolean or timestamp column and index it directly. Consider what that index contains: an entry for every row in the table, published or not. On a table that retains history, the published rows vastly outnumber the unpublished ones, so the index is dominated by rows the publisher never wants. The query WHERE published_at IS NULL still walks an index bloated with irrelevant entries.
A partial index restricted by WHERE published_at IS NULL physically contains only the unpublished rows. The moment a row is published it is removed from the index. The index is therefore proportional to the backlog, not to the lifetime row count, and it stays small as long as the publisher keeps up. This is the first lesson of brokerage scale: an index is not just an access path, it is a data structure whose size and shape determine whether a query is cheap or catastrophic.
The int4 trap and the bigint identity
A sequence that fits in a 32 bit integer tops out near 2.1 billion. A brokerage generating millions of outbox rows a day exhausts that space in months, and an overflow is a hard stop that requires an unpleasant migration under load. The lesson recorded in the engineering blog is blunt: use int8, a bigint, generated always as identity. The identity column is preferred over the older serial sequence because it is bound to the table and cannot be dropped independently, and the GENERATED ALWAYS flavor prevents applications from injecting arbitrary ids that would corrupt monotonicity. The same exhaustion logic applies to any surrogate key on a high volume table: instrument growth early and pick a width that survives years, not weeks.
MVCC: why updates never touch the original row
To understand why the outbox degrades, you have to understand what an UPDATE actually does in PostgreSQL. The engine uses Multi Version Concurrency Control, or MVCC. The core idea is that a reader never blocks a writer and a writer never blocks a reader, and Postgres achieves that by never overwriting a row in place.
When you UPDATE a row, Postgres writes an entirely new version of that row, a new tuple, into the table, and marks the old version as dead by setting a hint bit on it. The old tuple is not reclaimed at that moment; it stays on disk, occupying space, until a later cleanup. A DELETE is the same minus the insert: it marks the target tuple dead and leaves it in place. To a transaction that started before the update, the old version is still visible, which is how readers get a consistent snapshot without locks.
This is the property that gives Postgres its concurrency, and it is also the property that creates the operational disease you must diagnose. Every update is an insert plus a deferred delete. On a table where a publisher marks rows published as fast as they arrive, the heap fills with dead tuples proportional to the write rate. The cleanup mechanism that eventually removes them is vacuum.
Dead tuples, the visibility map, and heap-fetch explosion
The outbox publisher’s read path is SELECT id ... WHERE published_at IS NULL ORDER BY id. The plan the optimizer prefers is an index only scan on the partial index: read only the index, never touch the heap. An index only scan is fast because it skips the random heap fetch per row. But index only scans are only valid when the database can prove, for every index entry it reads, that the corresponding heap tuple is visible to all current transactions. If it cannot prove visibility, it must fetch the heap tuple to check, and the scan silently degrades from a pure index read into an index plus heap access.
The structure that records visibility is the visibility map, a small per table bitmap with one bit per heap page. A page’s bit is set when every tuple on that page is known visible to all transactions. The bit is cleared when any update writes to the page, and it is restored when vacuum confirms the page is fully visible. The consequence is subtle and severe. On a high insert, high publish table, the heap pages are in constant churn: new unpublished rows land, get published, become dead, and get vacuumed. Pages flip between visible and not visible continuously, so their visibility map bits are frequently clear. An index only scan that hopes to skip the heap instead fetches the heap tuple for a large fraction of entries to confirm visibility. These heap fetches are random reads, and on a hot table they blow up query time by orders of magnitude.
This is the aha moment of the whole incident. The outbox looked well indexed, and in isolation the index was correct. What killed it was the interaction between the partial index, the index only scan optimization, and a visibility map that could never settle because the table never stopped churning. The fix was not to tune the index. It was to stop churning the pages the publisher reads from, by partitioning.
The mechanics of visibility deserve a concrete trace. Suppose transaction one reads the outbox at noon, taking a snapshot of the database at that instant. At one second past noon, transaction two publishes a row, which writes a new tuple version and marks the old one dead. Transaction one continues to see the old version, because its snapshot predates the change, which is the whole point of MVCC: readers get a consistent view without locking writers. But from the engine’s perspective, that old dead tuple cannot be reclaimed yet, because transaction one might still read it. Only when transaction one commits, releasing its snapshot, can vacuum reclaim the dead tuple. If transaction one had stayed open for an hour, every dead tuple created during that hour would pile up unreclaimed, on every table, because the global horizon was frozen at noon. This is why a single long running read transaction is so damaging at scale: it does not touch those other tables, but it forbids their cleanup.
Why VACUUM cannot fix index bloat
Vacuum has two jobs: reclaim dead heap tuples so the space can be reused, and clean up dead entries in indexes. But here is the asymmetry that traps every engineer the first time. Vacuum removes dead heap tuples and marks their space free for reuse, so the heap does not grow unbounded. For indexes, vacuum marks the dead index entries invalid but does not physically remove them. The index still carries the dead entry, taking up space, until a reindex or an index rebuild compacts it.
So on a table that updates constantly, two kinds of bloat accumulate. Heap bloat is bounded because vacuum reclaims it. Index bloat is not bounded by vacuum, because the dead entries stay. Over weeks the partial index, the status index, every index on the churned table grows swollen with dead entries, which makes every scan touch more pages than it should. The remedy is periodic reindexing, or careful autovacuum tuning that runs aggressively enough to keep the dead entry fraction low, or the use of features that control index cleanup directly.
The point an interviewer is probing is whether you know that vacuum and reindex solve different problems. Vacuum keeps the heap healthy. Reindex keeps the indexes healthy. A candidate who says just run autovacuum harder has misunderstood which layer is bloated.
Tuning autovacuum for a high-churn table
Autovacuum is not a single knob; it is a set of thresholds that decide when a table is dirty enough to deserve cleanup. Two thresholds matter. The scale factor is the fraction of the table that must change before autovacuum considers it. The threshold is a flat number of row changes added on top. When inserted plus deleted plus updated rows exceed scale factor times reltuples plus the base threshold, autovacuum wakes for that table. The defaults, a scale factor of roughly two tenths and a base threshold of fifty, are calibrated for a typical web workload, not for an outbox that turns over its own size many times per day.
On a high churn table the defaults are too sleepy. By the time two tenths of the outbox has changed, the dead tuple pile and the visibility map churn are already degrading the publisher’s read path. The discipline is to lower the scale factor and threshold for the specific table, so autovacuum wakes far more often and reclaims dead tuples before they accumulate. Per table overrides let you tune the outbox aggressively without making autovacuum hammer every quiet table in the database. The cost limit and cost delay control how hard autovacuum works when it runs: a higher cost limit lets it do more work per cycle, and a lower delay lets it run more continuously, at the price of more IO contention with foreground traffic.
A worked example makes the tradeoff concrete. Suppose the outbox receives one hundred thousand inserts and one hundred thousand publishes per hour, and the table holds ten million published rows. With the default scale factor, autovacuum wakes only after two million changes accumulate, by which point the visibility map on the unpublished pages has churned enough to push the publisher’s index only scan into heavy heap fetching. Lowering the scale factor to one hundredth on the outbox alone makes autovacuum wake after one hundred thousand changes, roughly once per hour, which reclaims dead tuples and refreshes the visibility map before the publisher feels them. The foreground cost is a small, steady vacuum load on one table; the benefit is a publisher read path that stays in the index.
The mistake to avoid is tuning autovacuum globally to be aggressive. That floods the whole database with vacuum IO and starves foreground traffic. The right move is per table tuning: aggressive on the few high churn tables, default elsewhere. Knowing that autovacuum thresholds are per table overrides is the difference between a senior diagnosis and a blunt instrument.
Long-running transactions: the silent killer
Vacuum can only reclaim a dead tuple when no transaction still needs to see it. Postgres tracks the oldest active transaction id, and any dead tuple newer than that horizon cannot be removed, because some running transaction might still read it. A transaction that stays open for a long time, by holding a connection idle in transaction, freezes that horizon in place. Every dead tuple created while it runs accumulates, because vacuum is not allowed to reclaim any of them.
The outbox publisher is a prime offender precisely because the pattern encourages it. The lazy implementation opens a transaction, selects a thousand rows, publishes them one by one to Kafka, and commits at the end. While that transaction is open, autovacuum cannot reclaim the dead tuples from any updates to the outbox or, worse, to the busy tables around it. The whole database begins to bloat, the visibility map degrades, and index only scans across the system degrade with it. The discipline is to keep publisher transactions short: small batches, publish outside the database transaction, and commit the status update immediately. If a poison pill message cannot be serialized, it stalls the entire publish transaction, which is why messages are grouped by key so independent groups can be retried in isolation rather than blocking the whole batch.
A long running transaction blocks cleanup for the entire database, not just the table it reads. One idle connection in transaction can bloat every high churn table in the cluster, because the vacuum horizon is a single global cutoff. Monitoring for idle in transaction connections is not optional at brokerage scale; it is money safety.
Partitioning as the cure for the high-churn table
The redesign that resolved the outbox incidents partitions the table by the published flag itself. One partition holds unpublished rows, where published_at IS NULL. Another holds published rows, with a default. The partition boundary is the exact predicate the publisher reads, so the publisher’s query scans only the unpublished partition, and within that partition every row is guaranteed unpublished.
This dissolves the visibility map problem entirely. The publisher no longer reads a churned set of pages where half the tuples are dead. It reads a partition that contains only live, unpublished rows, so the index over it stays tight and the heap it touches stays clean. Published rows physically leave that partition when they are published, which means the heavy write churn happens in the published partition, away from the read path the publisher depends on.
PostgreSQL cannot convert a regular table into a partitioned table in place, so migrating a live outbox requires a careful online procedure: create the partitioned table, dual write to both, backfill, and cut over. The migration is its own engineering exercise, but the payoff is that the read path stops fighting the write path.
Hash versus list partitioning, measured
A different table at Trade Republic, a UUID based dedup table, raised the hash versus list question directly. List partitioning on a computed partition key sounds flexible, but it lost the ability to put a primary key on the parent table, because partition keys cannot include arbitrary expressions. Without a parent primary key, logical replication, the mechanism that feeds change data capture, becomes fragile. Hash partitioning distributes rows by a C implemented hash function, keeps primary keys intact, and measured faster on inserts: higher transactions per second and lower latency. The lesson the engineering blog records is that the boring optimized built in beat the hand rolled fancy approach, and that benchmark intuition is often wrong until you measure.
The general principle for a senior engineer is to choose partitioning by what the workload needs. Partition by a flag, like the published predicate, when you want to isolate a hot read set from churn. Partition by range, like a date, when you want time based retention and pruning. Partition by hash when you want even distribution and primary key integrity for replication. Each choice buys a different property, and the wrong choice can quietly disable a feature you depend on.
Always carry the partition key in your predicate
Partition pruning is the optimization that skips partitions the query cannot possibly match. It only fires when the planner can prove, from the predicate, that a partition is irrelevant. For the outbox, the unpublished partition is pruned correctly only if the query carries published_at IS NULL. A query that updates the status without that predicate forces the planner to consider every partition, which defeats the entire design.
This is the discipline that makes partitioning pay off: the partition key must appear in every query that touches the table, or you have built an expensive layout that the optimizer cannot use.
How the planner counts without scanning
A brokerage dashboard often asks how many customers hold a given instrument, or how many orders are open. A naive COUNT(*) scans the relevant rows, which is unacceptable on a table of hundreds of millions of rows. PostgreSQL never maintains a live row count, because doing so would serialise every insert and delete. Instead it keeps statistics, refreshed by ANALYZE, that let the planner estimate cardinalities without touching the data.
The statistics include reltuples, an estimate of the rows in a relation; most common values, the frequent values of a column; and histograms, a bucketed distribution of the rest. With these the planner estimates selectivity for a predicate, which is how it estimates how many rows a filter returns and therefore which join order and access path is cheapest. The same statistics are what an approximate count uses: estimate the fraction of rows matching a predicate from the histogram, multiply by reltuples, and return an answer in milliseconds instead of minutes.
The catch is freshness. Statistics are sampled, not exact, and they go stale when the data distribution shifts faster than ANALYZE runs. A query plan that was correct at night can be wrong by morning after a burst of inserts. Autovacuum triggers ANALYZE when a threshold of changes accumulates, but on a volatile table you may tune the analyze threshold lower or run ANALYZE explicitly after large loads. Stale statistics produce bad plans, which look like random slowness until you realize the planner is estimating with yesterday’s data.
The WAL as the bridge to Kafka
The outbox is the correctness layer. The mechanism that actually moves events from Postgres to Kafka is the write ahead log. Every change to a Postgres table is first written to the WAL before it is applied to the heap, which is what makes Postgres crash safe: replay the WAL and the data is restored to the last flushed change. For change data capture the relevant setting is wal_level = logical, which writes enough extra information into the WAL that an external process can reconstruct the logical change, the before and after row, not just the physical page edit.
A tool like Debezium connects to Postgres as a logical replication client, creates a replication slot so it remembers its position in the WAL, and emits a change event for every committed insert, update, and delete. Because it reads the WAL rather than polling the table, it adds near zero query load and captures changes with low latency. This is the decisive advantage over a polling based publisher, which adds redundant database load, a latency versus load tradeoff, and operational complexity. The outbox row written in the business transaction appears in the WAL the instant the transaction commits, and Debezium picks it up from there.
A replication slot is a promise: Postgres will retain WAL segments until the consumer confirms it has read them. If the consumer, Debezium or the Kafka connect pipeline downstream, stalls, the slot grows, and Postgres cannot recycle WAL, which eventually fills the disk and halts the database. Monitoring replication slot lag is therefore a first class operational concern. The connection between the outbox design and the WAL is the whole point: the database is the source of truth, and the stream is a tail of its log.
What breaks at scale: an operational runbook
The database that records money does not fail loudly. It degrades quietly, and the degradation shows up first as latency, then as bloat, then as an outage. A senior engineer reads the early signals before they become incidents.
The first signal is index-only scan degradation. The publisher’s read query, which used to return in a millisecond, starts taking tens of milliseconds, with most of the time in heap fetches. The diagnosis is the visibility map: a large fraction of the unpublished partition’s pages have their bits clear because of write churn, so the index-only scan degrades. The fix is to confirm the unpublished partition is actually isolated, lower the autovacuum threshold so visibility bits are restored faster, and check that no long-running transaction is freezing the horizon.
The second signal is unbounded table or index growth. The heap stops growing because vacuum reclaims it, but an index keeps swelling with dead entries. The diagnosis is index bloat, measured by comparing the index size to the number of live entries it should hold. The fix is a concurrent reindex on the bloated index, scheduled out of peak hours, plus tighter autovacuum so dead entries are marked faster and the bloat accrues slower.
The third signal is replication slot lag. The WAL directory grows, and the slot’s restart lsn falls further behind the current WAL position. The diagnosis is a stalled or slow downstream consumer, often the Debezium connector or a Kafka Connect task that has errored. The fix is to unblock the consumer, and in the worst case to drop and recreate the slot after catching up, accepting a brief gap that the outbox’s idempotent consumers will absorb.
A money database rarely crashes outright. It slows first, because MVCC bloat, visibility churn, and WAL retention all manifest as latency before they manifest as errors. The senior instinct is to instrument the causes, the dead tuple fraction, the visibility map coverage, the slot lag, and the idle in transaction count, and to alert on the causes rather than on the response time they eventually produce.
A recurring operational discipline is connection hygiene. Every connection holds backend state, and a connection left idle in transaction freezes the vacuum horizon. A pooler that enforces a statement timeout and an idle in transaction timeout protects the database from applications that forget to close their transactions. At brokerage scale the rule is simple: no transaction may outlive the request that opened it, and the publisher’s batch transactions must be the shortest of all, because they sit on the hottest table.
Defending the design in an interview
An interviewer who asks about Postgres at brokerage scale is rarely looking for syntax. They are probing whether you understand the engine as a system with internal invariants, and whether you can reason about what happens when those invariants are stressed. Expect to be pushed on each layer.
Why not dual write to Kafka and the database? Because there is no transaction spanning both, so any failure produces phantom events or lost events, and money systems cannot tolerate either. Why a partial index and not an index on a status flag? Because the index size tracks the backlog, not the lifetime row count, and because a status index is dominated by rows you never query. Why does an index only scan suddenly get slow? Because the visibility map churns on a hot table, and each unclear visibility bit costs a random heap fetch. Why does the partial index bloat if vacuum runs? Because vacuum marks dead index entries invalid but does not remove them; only reindex compacts. Why partition by the published flag? Because it isolates the publisher’s read path from the write churn and dissolves the visibility map problem. Why does a long running transaction hurt the whole database? Because the vacuum horizon is global, so one open transaction freezes cleanup everywhere.
Common mistakes candidates make
- Indexing the status column instead of using a partial index. The candidate understands indexes but not that an index over a skew column is mostly useless entries. The fix is to index only the rows you query.
- Believing autovacuum solves bloat. It solves heap bloat, not index bloat. A candidate who stops at autovacuum has not learned that vacuum cannot remove dead index entries.
- Forgetting the visibility map. The index only scan that worked in test degrades in production because visibility bits clear under write churn, and the candidate has no model for why a query that should not touch the heap now does.
- Running the publisher in a long transaction. Selecting a huge batch and publishing inside the transaction blocks the global vacuum horizon and bloats the whole cluster.
- Omitting the partition key from predicates. Partitioning is bought but never used because the queries do not carry the key, so the planner cannot prune.
- Picking int4 sequences. The table runs out of ids in months and forces a painful migration, a failure mode the candidate should anticipate from growth math.
- Treating a COUNT as free. Forgetting that Postgres has no live row count and that an exact count is a scan, while an approximate count comes from statistics that can be stale.
- Ignoring replication slot lag. A stalled consumer fills the disk via the retained WAL, which is a database wide outage, not a consumer problem.
Mastery Questions
Question
Why does an index only scan on a hot outbox table degrade into heap fetches in production, even though the index is correct?
Answer
An index only scan is valid only when the visibility map proves every index entry's heap tuple is visible to all transactions. On a high insert, high publish table the heap pages churn constantly, so their visibility bits are frequently clear. Each unclear bit forces a random heap fetch to confirm visibility, and those random reads dominate the cost. The fix is not the index but isolating the read path via partitioning.
Sources & evidence12 claims · 5 cited
Twelve claims backed by Trade Republic engineering sources (outbox parts 1 and 2, statistics, partitioning, Debezium). All design and operational claims are sourced; the planner-statistics and WAL-to-Debezium bridge are verified against src_tr_statistics and src_tr_debezium.
- The outbox uses a partial index on id where published_at IS NULL, which stays small because published rows fall out of it; indexing published_at directly is useless.verified
- With wal_level=logical the WAL carries enough to reconstruct logical changes, and Debezium tails it via logical replication and a replication slot with near-zero extra table-query overhead, far more scalable than polling.verified
- Poison-pill messages stall the whole publish transaction, so messages are grouped by key to let independent groups be retried in isolation.verified
- PostgreSQL cannot convert a regular table to a partitioned table in place, so migrating an outbox requires a careful online procedure.verified
- MVCC updates write a new tuple and leave the old one dead on disk, so every update is an insert plus a deferred delete that vacuum later reclaims.verified
- Vacuum marks dead index entries invalid but does not remove them, so index bloat accumulates on a high-churn table and periodic REINDEX or autovacuum tuning is required.verified
- On a high-insert high-publish outbox, index-only scans degrade because the visibility map churns and forces heap fetches; partitioning the outbox by published_at IS NULL fixes it because every row in the unpublished partition is guaranteed unpublished.verified
- Picking too many messages per publisher iteration opens a long-running transaction that blocks autovacuum and degrades the whole database; about 100 per batch is a good start.verified
- int4 id sequences run out at brokerage scale; use int8 GENERATED ALWAYS AS IDENTITY, and order the publisher by the database sequence id because pod clocks skew and break created_at ordering.verified
- With a partitioned outbox the partition key (published_at IS NULL) must be in UPDATE predicates so only the unpublished partition is scanned.verified
- PostgreSQL maintains planner statistics (reltuples, most-common-values, histograms) that let it estimate counts and cardinalities without scanning, which is how approximate counts return quickly.verified
- For a UUID dedup table, hash partitioning outperformed list partitioning on inserts (higher TPS, lower latency), keeps primary keys for logical replication, and the boring optimized built-in beat the hand-rolled approach.verified
Cited sources
- PostgreSQL + Outbox Pattern Revamped Part 1: Incidents and a new design pattern · Trade Republic Engineering (Sadeq Dousti)
- Streaming Outbox Events from Postgres to Kafka with Debezium · Trade Republic Engineering (Andrei Sukharev)
- PostgreSQL + Outbox Pattern Revamped Part 2: converting to partitioned, index bloat, autovacuum · Trade Republic Engineering (Sadeq Dousti)
- Statistics: how PostgreSQL counts without counting · Trade Republic Engineering (Sadeq Dousti)
- Postgres partitioning performance: Hash vs. List · Trade Republic Engineering (Sadeq Dousti)