Cruxly › System design › Databases by Name: Internals and Terms

PostgreSQL internals: MVCC cost, VACUUM, wraparound and WAL

every update leaves a corpse, and the vacuum that cleans them is the thing that pages you

This lesson is about the part of PostgreSQL that interview answers skip: what MVCC costs you in dead row versions, how autovacuum decides when to clean them, why a 32-bit transaction counter can stop a production database, and how the write-ahead log doubles as the replication contract. It builds on Isolation levels, in depth (snapshot visibility) and Storage engines: B-trees, LSM-trees, row vs column (WAL, fill factor), and goes into the defaults, the arithmetic and the real incidents.

Dead tuples: the real price of MVCC

An UPDATE in Postgres is an INSERT of a new row version plus a mark on the old one. The old version is a dead tuple once no snapshot can see it, and it stays in the heap until vacuum reclaims the space. The Postgres routine-vacuuming docs are blunt about why vacuum exists: without it, dead versions pile up, tables bloat, scans read more pages, and the transaction counter eventually runs out. Snapshot visibility itself is in Isolation levels, in depth; here the subject is the bill.

VACUUM versus VACUUM FULL. Plain VACUUM marks dead space reusable inside the table and does not normally hand it back to the operating system, so a table that ballooned stays big on disk. VACUUM FULL rewrites the whole table into a new file, takes an ACCESS EXCLUSIVE lock for the duration and needs room for a second copy of the table, according to the same docs. On a hot table that is an outage with a disk-space requirement. Online tools such as pg_repack (see Operating a database: backups, migrations, multi-region, cost) exist because of this.

The visibility map. Vacuum keeps a per-page bit meaning "every tuple on this page is visible to every transaction". Two things use it: vacuum skips those pages next time, and index-only scans can answer from the index without visiting the heap page. The docs note the map is far smaller than the heap, so it caches easily. The consequence people miss: an index-only scan stays cheap only while vacuum keeps the map fresh. A table with heavy churn and a lagging vacuum quietly turns index-only scans back into heap fetches.

HOT updates, the cheap path. A heap-only tuple (HOT) update happens when the update changes no indexed column (from Postgres 16, summarizing indexes such as BRIN are the exception) and the new version fits in the same page. Then no new index entries are written, and the old version can be pruned in normal operation without waiting for vacuum (Postgres HOT docs). The converse is the trap: adding one index on a frequently updated column turns every update on that table from HOT into a full write to every index.

fillfactor is the dial for HOT. For tables the CREATE TABLE docs give a range of 10 to 100 with a default of 100, meaning pages are packed completely on insert. Lowering it (the docs suggest smaller values for heavily updated tables) reserves room in each page for new versions, which raises the chance an update stays in the page. The trade is a bigger table for fewer index writes. Check the effect in pg_stat_all_tables, which counts HOT and non-HOT updates separately.

What Uber counted. In its 2016 post on moving from Postgres to MySQL, Uber explained that Postgres indexes point at the physical row location, so a non-HOT update must write every index. Its example was a users table with three indexes (the primary key plus two secondary indexes), where changing one birth year caused at least four physical updates: the new row version plus an entry in each of the three indexes. The post adds that a table with a dozen indexes would have to propagate the change into all twelve. Arithmetic, not Uber's number: with 12 indexes a non-HOT update writes 1 heap tuple plus 12 index entries, 13 writes for one logical change. That is the whole mechanism behind the debate in the last section.

:::key What experts say in the room "Updates are not free in Postgres." The argument is about index count and HOT ratio, not about whether MVCC is good. The failure is bloat plus slow vacuum. What a senior checks: the HOT-update ratio on the hottest tables, dead-tuple counts, the age of the longest open transaction (an old snapshot stops vacuum from removing anything newer), and whether fillfactor was a documented decision rather than a default. :::

Autovacuum: the knobs, the defaults, the arithmetic

The defaults assume small tables. Autovacuum triggers on a table when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x row count. The Postgres runtime-config docs (current) give 50 and 0.2. Arithmetic, not a measured number: on Postgres 17 and earlier a 1,000,000,000-row table reaches 50 + 0.2 x 10^9 = 200,000,050 dead tuples before default autovacuum starts. Postgres 18 added autovacuum_vacuum_max_threshold, a cap on that sum with a default of 100,000,000, so on 18 the same table triggers at 100,000,000. Either way the table is already 10 to 20 percent garbage, the vacuum that follows has hundreds of millions of rows to walk, and it runs long enough that new garbage accumulates behind it. A 100,000,000-row table triggers at 20,000,050.

The fix is per-table overrides, which the routine-vacuuming docs support: autovacuum parameters can be set on a table with ALTER TABLE ... SET (...) while the global defaults stay in postgresql.conf. Setting a scale factor of 0.01 on the billion-row table moves the trigger to 10,000,050 (arithmetic). That per-table setting is the standard runbook item for any table past a few tens of millions of rows.

Insert-driven vacuums. autovacuum_vacuum_insert_scale_factor (0.2 in current docs) triggers vacuum on append-only tables too. Before insert triggers existed, such tables were vacuumed essentially only for wraparound protection, and the first vacuum was one giant freeze at the worst moment. Insert triggers spread that work out and set visibility-map bits so index-only scans work.

The throttle is where vacuum loses races. Autovacuum is deliberately slowed so it does not starve queries. Current defaults: autovacuum_vacuum_cost_delay 2 ms, and autovacuum_vacuum_cost_limit of -1, which means it falls back to vacuum_cost_limit (200). The worker does a small unit of work, then sleeps. On a write-heavy table this budget can be smaller than the rate at which garbage is produced.

More workers do not mean more speed. The cost accounting is shared among running workers, so raising autovacuum_max_workers alone from 3 to 6 without raising the cost limit makes each worker slower. If one giant table is the problem, workers are not the fix: per-table cost settings are. This is exactly the configuration trap in Sentry's story in the next section.

The oldest-transaction problem. Vacuum can only remove a dead tuple that no open snapshot could still see. One forgotten session that is "idle in transaction" holds the horizon back for the whole database, and every table bloats while autovacuum runs "successfully" and reclaims nothing. The server-side guard is idle_in_transaction_session_timeout, which is 0 (disabled) by default, so it only helps once you set it. This is why the most important autovacuum alert is often not about autovacuum at all, it is the age of the oldest open transaction.

:::warn Autovacuum can be cancelled, except one kind Normal autovacuum yields to conflicting lock requests such as DDL. An anti-wraparound vacuum, on a table already past autovacuum_freeze_max_age, is not cancelled the same way (Postgres routine-vacuuming docs). That is the one that arrives at peak hours. :::

:::key What experts say in the room "The defaults were written for tables that fit in memory." The number is 200,000,050 on Postgres 17 and earlier, and the 100,000,000 cap on 18; the failure is a vacuum that starts late, runs long and never catches up. What a senior checks: n_dead_tup and last_autovacuum for the top twenty tables, saturation of the three workers, the oldest xmin across sessions and replication slots, and per-table scale factors on anything over about 50 million rows (a rule of thumb, not a Postgres number). :::

Transaction ID wraparound: the 2-billion cliff

Postgres transaction IDs are 32 bits and are compared modulo 2^32. For any given ID, about 2 billion IDs are in its past and 2 billion in its future (2^31 is 2,147,483,648). The routine-vacuuming docs state the consequence: every table in every database must be vacuumed at least once every 2 billion transactions. If it were not, an old row's ID would eventually look like it is in the future and the row would become invisible, so the server protects itself by refusing to run.

Freezing is the work that makes old rows immune to the counter. Vacuum marks sufficiently old rows frozen. Since 9.4 this sets a flag bit and keeps the original xmin, so the value stays useful for debugging. A table that has not been frozen in a long time forces a vacuum that must read the whole table, which is why a huge, quiet table can become the emergency.

The numbers on every wraparound runbook (current Postgres docs, routine vacuuming and vacuum runtime config):

Arithmetic, not a Postgres number: 200 million is 200e6 / 2^31, about 9.3 percent of the horizon. At a steady 5,000 transactions per second, 200 million IDs are consumed in 11.1 hours and the full 2 billion in about 4.6 days. The window between "alert" and "outage" is days when the load is steady, but a vacuum that cannot finish a multi-terabyte table inside that window turns it into hours.

Multixact wraparound is a second clock. Multixact IDs, which record multiple row lockers (heavy SELECT ... FOR SHARE or foreign-key checks), are also 32 bits, with the same 40 million warning and 3 million refusal. The docs add that aggressive vacuums begin once multixact member storage passes about 10 GB (the wrap limit is about 20 GB). A database can look healthy by transaction age while multixacts run out.

:::key Verified October 2026: three million, not one million Sentry's July 2015 post says Postgres stops with "fewer than one million transactions left". The current routine-vacuuming docs say three million. Quote the current docs for the margin, and Sentry for the story. :::

Sentry, July 2015. On Monday 20 July 2015 (written up on 23 July), the transaction ID counter on Sentry's primary Postgres neared its limit and Postgres stopped accepting writes. Sentry's post says that, waiting on autovacuum, they had "invested nearly 3 hours" with nothing to show for it. The cause was configuration that was too conservative for their write volume: a high freeze max age, only the default three autovacuum workers and a vacuum cost delay. One autovacuum run had also failed without anyone noticing, because verbose logging was off. To get running again they truncated one very large table; the post says the system was fully restored five minutes later. Afterwards they moved to a machine with 256 GB of RAM and 24 cores and set autovacuum_freeze_max_age to 500,000,000, autovacuum_max_workers to 6, autovacuum_vacuum_cost_delay to 0 and vacuum_freeze_min_age to 10,000,000. Those numbers are Sentry's own post-incident choices for its own workload, not recommendations.

Notion and Figma. Scaling Postgres and MySQL before you leave them already covers Notion's first sharding trigger (a stalled VACUUM and wraparound risk) and Figma's note that multi-terabyte tables hurt vacuum. The same clock sits behind both stories.

:::key What experts say in the room "The counter is a shared clock, and a table nobody vacuumed is a time bomb." The numbers are 200 million, 1.6 billion, 3 million and the 2 billion cliff; the failure is Sentry's day of downtime. What a senior checks: age(datfrozenxid) per database and age(relfrozenxid) for the largest tables against those thresholds (alert at 50 percent of autovacuum_freeze_max_age), multixact age as a separate alert, vacuum logging turned on, and a planned freeze window for any table large enough that an emergency vacuum would take hours. :::

WAL, checkpoints and the replication slot trap

The WAL is both the durability log and the replication contract. Storage engines: B-trees, LSM-trees, row vs column walks a commit through WAL, flush and checkpoint. This section is the tuning side, from the Postgres WAL runtime-config docs (current).

If checkpoints are forced by max_wal_size more often than the timeout, you get a storm: each checkpoint resets the "first change" clock, so many pages emit full-page images again, WAL volume jumps, and I/O spikes. That is the signature of a max_wal_size set far too low for the write rate.

Replication slots keep WAL for a consumer. A slot is a named, crash-safe position that stops the server from removing WAL (and catalog rows) that the consumer still needs, even when that consumer is not connected (Postgres logical-decoding docs). That is the point: a replica or a CDC pipeline can fall behind and still resume. It is also the trap. A forgotten or stuck slot makes WAL pile up without bound; the docs say that in extreme cases this can cause a shutdown to prevent wraparound. max_slot_wal_keep_size defaults to -1, which is unlimited, so there is no cap unless you set one. Setting it trades a slot that can be invalidated (the consumer must re-sync) against a full disk. Choose deliberately.

Logical decoding. It extracts committed changes from the WAL into a stream through an output plugin. One slot streams one database in commit order. This is how change-data-capture pipelines of the Debezium type read Postgres, and each consumer adds a slot that needs an alert. Because it needs wal_level = logical, plan the restart ahead of the first consumer.

GitLab, 31 January 2017. GitLab's postmortem describes the opposite failure: the WAL was gone when it was needed. Replication to the secondary lagged under load and then broke because the primary had already deleted WAL segments the secondary still needed; GitLab.com was not archiving WAL, so the secondary had to be resynchronised by hand. While rebuilding the secondary an engineer removed about 300 GB of data on the primary by mistake. GitLab.com was down roughly 18 hours. Data created between 17:20 and 23:30 UTC was lost, including roughly 5,000 projects, 5,000 comments and 700 users, according to the postmortem (its introduction gives the end of the loss window as 00:00 UTC, the impact section as 23:30). The backup path did not save them: pg_dump backups failed because the 9.2 tools were being run against a 9.6 database, and the alert emails were rejected, so nobody knew. The published lessons are about WAL retention, tested restores and monitoring the backup jobs themselves. The postmortem counts four backup and replication techniques in place (pg_dump to S3, LVM snapshots, Azure disk snapshots and replication) and says none worked reliably.

Read the two incidents together. GitLab lost WAL that a replica needed; the slot trap keeps WAL nobody needs. Both are the same decision about how long the primary remembers.

:::key What experts say in the room "WAL is both durability and the contract with every replica and CDC consumer." The numbers are 1 GB, 5 minutes and an unlimited slot default; the failure is GitLab's missing segments on one side and a full disk on the other. What a senior checks: pg_replication_slots for inactive slots and the age of restart_lsn, the size of the WAL directory, how many checkpoints were requested versus timed, max_slot_wal_keep_size as a written decision, and the date of the last successful restore of a base backup plus WAL (not the date of the last backup). :::

TOAST, wide rows and what a column costs

TOAST is how a row bigger than a page stays storable. Postgres pages are 8 KB, so values cannot sit inline forever. Once a row exceeds about 2 kB (the TOAST_TUPLE_THRESHOLD), Postgres compresses and/or moves large field values out of line into a companion TOAST table; the maximum logical size of one value is 1 GB (Postgres TOAST docs). An on-disk pointer to an out-of-line value is 18 bytes, and out-of-line values are cut into chunks of about 2,000 bytes, four per page. Arithmetic: a 1 MB value (1,048,576 bytes) takes about 525 chunks of 2,000 bytes.

Storage strategies are per column: PLAIN (no compression, no out-of-line), EXTENDED (the default: try compression, then move out of line), EXTERNAL (out of line without compression) and MAIN (compress, avoid out of line). The docs point out that EXTERNAL makes substring operations on wide text or bytea columns faster, because Postgres can fetch only the needed chunks rather than decompress the whole value. toast_tuple_target overrides the 2 kB target per table; you rarely need it, but know it exists.

What this means for query cost. A "text" column holding 100 KB is not in the heap page at all. A query that selects it pays extra index and heap lookups in the TOAST table and decompression; a query that does not select it never touches those chunks. Two practical consequences follow:

• SELECT * is a performance bug on wide tables. The main table stays narrow and cache-friendly only if you do not fetch the big columns you do not need.
• Updating a row without touching the TOASTed field is cheap, because the pointer is unchanged and the out-of-line data is not rewritten. Updating the large value itself writes a new version of it.

JSONB documents of tens of kilobytes are the usual way this hurts: each read of the document is several chunk fetches plus decompression, and a small change in the document rewrites the whole value, creating a full-size dead version for vacuum.

:::key What experts say in the room "Wide columns are not free, and SELECT * hides the cost." The numbers are the 2 kB threshold, the 18-byte pointer and the 1 GB cap. What a senior checks: pg_total_relation_size against pg_relation_size for the table (the gap includes TOAST and indexes), whether the TOAST table is bloating from rewritten documents, and whether hot paths select only the columns they need. :::

The Uber argument and sharding with IDs

Both sides were right about different versions and workloads. This is the architects' debate most often quoted in interviews, and the way to answer it is to separate Uber's claims from the replies and to date them.

Uber's claims (company's own, 2016 post). Uber's engineering post explained its move from Postgres to MySQL, and said its analysis was based mainly on "the somewhat old Postgres 9.2 release series". Four claims carry the argument:

• Index design. Postgres indexes point at the physical row location, so a non-HOT update touches every index (the three-index, four-physical-updates example above). InnoDB secondary indexes point at the primary key instead.
• Physical replication. Postgres streams low-level WAL changes. Uber said this inflated cross-datacenter bandwidth and caused WAL archival to fall behind at peak. MySQL's row or statement replication is logical.
• Replica conflicts. Uber said replicas could routinely lag seconds behind the primary.
• Connections. Postgres forks a process per connection. Uber described trouble scaling past a few hundred active connections, against about 10,000 for MySQL.

It also reported that upgrading from 9.1 to 9.2 took many hours, and that its largest replicas had 768 GB of memory.

The reply from the Postgres side. Robert Haas, a Postgres committer then at EDB, answered on his own blog on 2 August 2016 (this is his argument, not a project statement; EDB later republished it). He agreed with Uber's diagnosis on two points: that in-core logical replication would address the change-stream size, cross-version replication, corruption propagation and replica-cancellation concerns, and that secondary-index write amplification is the "thorniest" problem; he argued Postgres should allow alternative on-disk formats, including index-organised ones. He raised questions on the rest, framed as things he wanted Uber to clarify: Uber did not say whether it tried out-of-core logical replication tools; query cancellation on replicas can be avoided with hot_standby_feedback = on; WAL can be compressed over a wide-area link; multi-hour pg_upgrade runs are not supposed to happen and later releases (9.4 or 9.5) fix many-object cases; and he questioned whether all of Uber's secondary indexes were needed and whether HOT could be extended. The Postgres hot-standby docs confirm the mechanics he cites: a long standby query can conflict with WAL replay (for example with vacuum cleanup records), the standby cancels it after the configured delay, and hot_standby_feedback stops vacuum on the primary from removing rows the standby still needs, at the cost of delayed cleanup and possible bloat on the primary.

What changed since. Both sides' points are from 2016. Uber's numbers describe Postgres 9.2. Logical replication tooling such as pglogical (mentioned in Uber's own post) already existed in 2016 and Postgres has kept evolving since; check the release notes of your version for what HOT, vacuum and replication now do. The honest senior position: the secondary-index design difference is real and still structural (Haas called it the thorniest), the replication and connection issues have standard mitigations (PgBouncer, hot standby feedback, logical replication), and quoting a 2016 verdict as 2026 fact is the mistake.

Process per connection. The connection claim is why every large Postgres shop runs a pooler; the mechanics of PgBouncer are in Operating a database: backups, migrations, multi-region, cost. The design question for you is where the pool sits and how many server connections each shard really needs, not whether Postgres can be made to scale connections at all.

Sharding design question (no company claim here). Postgres has no built-in sharding. Whether to shard in the application, with an extension such as Citus, or by moving to a proxy tier such as Vitess is covered in the MySQL lesson of this track. Take the Postgres-specific lesson: HOT, vacuum and wraparound are per-database, so a shard that is too large is a vacuum that is too long. That is the reason Notion cited.

:::key What experts say in the room "Uber was right about 9.2 and the community was right about what could be fixed." The numbers are four physical updates for a three-index table, and version 9.2; the failure is treating a 2016 post as a current verdict. What a senior checks: how many indexes the hottest updated tables have and whether each is used, the HOT ratio, replica lag under real queries, whether hot_standby_feedback is on and what it costs the primary, and what the current Postgres release changed since the post. :::

Running Postgres at scale: the senior's list

Put the monitoring where the failures start. The previous sections each ended in a signal. This one organises them into what you would actually alert on, in an order that follows how incidents unfold. All thresholds below are Postgres defaults from the current docs unless marked as a rule of thumb.

Why the order matters. A dead-tuple alert is a symptom, a slot alert is a countdown, and a wraparound alert is the last hour of one. If your pager only fires on the last one, you have lost the days of warning.

Version-sensitive facts to re-check on upgrade. The numbers in this lesson are from the current Postgres docs read in October 2026; older clusters may have different defaults. Check your own version's page before writing a runbook.

Answering the staff-level follow-up. If an interviewer asks "how would you keep a 5 TB Postgres healthy", the structure is: per-table autovacuum settings on the biggest tables; a separate alert for the oldest transaction; a freeze plan so no emergency vacuum is the first one; replication slots with a documented cap; a pooler; and a restore drill. Then name the point at which you would stop scaling vertically and shard, with the vacuum duration of your largest table as a number.

:::warn Do not claim what you cannot defend "Postgres can't do X" claims about a version you have not tested age badly, and the Uber post is the standing example. Say what you measured and which version. :::

:::key What experts say in the room "Show me the alert that fires three days before the outage." The numbers are 200 million, 1.6 billion, 3 million and 2 billion. The failure is a team that discovers wraparound by being down. What a senior checks: that each of the signals above exists, has an owner and has been tested by actually exceeding it in a staging copy. :::

How to say it in the interview

"In Postgres an UPDATE writes a new row version and leaves the old one as a dead tuple, so bloat and vacuum are the price of MVCC. Autovacuum fires at 50 plus 20 percent of the table's rows, which for a billion-row table means 200 million dead tuples before it even starts, so I set per-table scale factors on big tables. I watch age(datfrozenxid) against the 200 million freeze trigger, because at about 2 billion transactions the server stops accepting writes, as Sentry found in 2015. And I monitor replication slots, since a stuck slot keeps WAL forever and fills the disk."

Checklist

• Explained that an UPDATE writes a new row version and that vacuum pays for the old one
• Checked the HOT-update ratio and fillfactor on the hottest update tables, and counted indexes on them
• Set per-table autovacuum scale factors on tables over about 50 million rows
• Alerting on the oldest open transaction and idle-in-transaction sessions
• Alerting on age(datfrozenxid) at half of autovacuum_freeze_max_age, plus multixact age
• Turned on autovacuum logging and reviewed failed or cancelled runs
• Decided max_slot_wal_keep_size deliberately and alert on inactive replication slots
• Checked forced versus timed checkpoints and sized max_wal_size for the write rate
• Avoided SELECT * on tables with large TOASTed columns
• Ran a restore drill from base backup plus WAL, and monitored the backup job itself

Related lessons

• MySQL and InnoDB: locks, buffer pool, replication and Vitess
• Redis in depth: cluster slots, eviction, persistence and the lock debate
• Cassandra and ScyllaDB: the ring, repair, compaction and Discord's migration
• DynamoDB under the hood: partitions, adaptive capacity, quotas and the 2015 outage
• MongoDB internals: oplog, WiredTiger, shard keys, the balancer and Stripe's DocDB
• Kafka as a log store: ISR, acks, compaction, transactions and LinkedIn scale