PostgreSQL's MVCC is bad. So is everyone else's

Comments

The first thing you will probably learn about Postgres, if you follow people who don't like Postgres, is that MVCC is bad. The 40‑year‑old design mistake. It’s signatures are everywhere. Bloated tables that double in size, 32‑bit transaction counter limit, the never ending struggle with VACUUM, dead tuples nightmares. It comes with credentials, too: Uber measured the write amplification in 2016 and left for MySQL over it; Andy Pavlo’s database group called MVCC the part of PostgreSQL they hate the most. It’s a real thing. Postgres is as bad as it gets.

While none of this is exaggerated, it comes down to a real design choice. The bloat, the amplified writes, the vacuum babysitting: every charge traces to a decision, not a defect, and we reproduce each one below on a live PostgreSQL 19 beta2 instance, so you can watch the damage happen yourself. But the verdict that spreads from community to community always stops one question early: compared to what? What does every other engine do instead, and what does that cost?

Because MVCC is not optional. Any database that wants readers to not block writers has to keep multiple versions of rows somewhere, and every engine that does so answers the same four questions:

1. Where do old versions live? In the table itself, or in a separate structure?
2. Which way do version chains point? From old to new, or new to old?
3. What do indexes point at? A physical row location, or a logical key?
4. Who cleans up, and when? A background process later, or the transaction itself?

PostgreSQL’s answers: in the table, old to new, physical location, background process later. Every cost the critics list follows from those four answers. And every alternative is a different set of answers with the bill sent to someone else - the writer, the reader of history, tempdb, the cache, the compactor. One of them spent years of engineering to buy the one property PostgreSQL’s design has had for free since day one. All of them fail, differently, when a transaction stays open over lunch.

The charges against PostgreSQL

If you want the full mechanism, PostgreSQL MVCC, Byte by Byte walks through it with pageinspect. The short version: an UPDATE in PostgreSQL never modifies a row. It writes a complete new copy of the row into the heap, stamps the old version’s t_xmax, and leaves both versions sitting on disk. Visibility is decided at read time, tuple by tuple. Cleanup is somebody else’s problem, specifically VACUUM’s.

Four charges follow, reproduced one at a time below.

Charge 1: write amplification

The heart of Uber’s complaint. Because every index on the table points at the row’s physical location (a page number plus a slot, the ctid), and an UPDATE creates a new physical row, every index needs a new entry pointing at the new location. Even indexes on columns you didn’t touch.

Set up two copies of the same 1M‑row table. One with just a primary key, one with four additional secondary indexes:

CREATE TABLE accounts (
    id bigint PRIMARY KEY,
    email text NOT NULL,
    status text NOT NULL,
    balance numeric NOT NULL,
    created_at timestamptz NOT NULL,
    last_seen timestamptz
);

INSERT INTO accounts
SELECT g,
       'user' || g || '@example.com',
       'active',
       100,
       now(),
       now()
FROM generate_series(1, 1000000) g;

CREATE INDEX ON accounts (email);
CREATE INDEX ON accounts (status);
CREATE INDEX ON accounts (balance);
CREATE INDEX ON accounts (created_at);

CREATE TABLE accounts_lean (LIKE accounts);
ALTER TABLE accounts_lean ADD PRIMARY KEY (id);
INSERT INTO accounts_lean SELECT * FROM accounts;

Now update 100,000 rows, touching only last_seen. Note that last_seen is not in any index, on either table. Measure the WAL generated (pg_stat_wal, after a CHECKPOINT and pg_stat_reset_shared('wal') to get a clean window):

UPDATE accounts_lean SET last_seen = now()
WHERE id > 100000 AND id <= 200000;

UPDATE accounts SET last_seen = now()
WHERE id > 100000 AND id <= 200000;

The lean table writes 7.1 WAL records per row; the indexed table writes about twice as much. Every secondary index must be updated even though no indexed column changed.

That is the default. There is a mitigation: heap‑only tuples (HOT). When a page has free space and none of the indexed columns change, an UPDATE can leave the new version on the same page and skip index maintenance. To enable HOT you set fillfactor below 100 (say 70), reserving space for in‑page updates. Repeat the same update with fillfactor=70:

ALTER TABLE accounts SET (fillfactor = 70);
-- (reload the table, then run the same UPDATE as above)

First batch (100,000 rows) with fillfactor=70:

wal_records | wal_bytes | n_tup_hot_upd (this batch)
------------+-----------+----------------------------
   455812   |  51 MB    | 41,996 of 100,000

Better, but note the number: 42% HOT, not 100%. Each page has 30% free space and roughly 80 rows; once the first ~30 updates on a page consume the reserve, the rest of that page’s rows spill elsewhere, indexes and all.

Run the same batch a second time, after opportunistic pruning has recycled the dead versions in‑page:

wal_records | wal_bytes | n_tup_hot_upd (this batch)
------------+-----------+----------------------------
   304990   |  22 MB    | 66,314 of 100,000

66% HOT and 3.0 WAL records per row, nearly back to lean‑table cost. That’s the honest shape of the mitigation: HOT is opportunistic, arrives in steady state rather than on demand, and quietly costs you 30% of your table size in reserved space. It works, and OLTP tables in the wild routinely see 90%+ HOT ratios with well‑chosen fillfactor. But the default behavior is the 7.1 records per row, and the critics are describing the default.

Charge 2: your table is an accident scene

Every UPDATE is an INSERT plus a deferred DELETE. Do nothing about it and the dead versions accumulate:

VACUUM (FULL, ANALYZE) accounts_lean;  -- reset: 89 MB, 0% dead
\timing on
BEGIN;
UPDATE accounts_lean SET balance = balance + 1;  -- all 1M rows
ROLLBACK;

SELECT pg_size_pretty(pg_relation_size('accounts_lean'));
SELECT tuple_count, dead_tuple_count, round(dead_tuple_percent)
FROM pgstattuple('accounts_lean');

BEGIN                 Time: 0.032 ms
UPDATE 1000000        Time: 1590.112 ms (00:01.590)
ROLLBACK              Time: 0.124 ms

 pg_size_pretty
----------------
 178 MB

 tuple_count | dead_tuple_count | dead_pct
-------------+------------------+----------
   1000000   |    1000000       |   47

One statement, and a transaction that didn’t even commit, doubled the table. A million dead tuples wait for vacuum, and as VACUUM Is a Lie covers, the indexes that grew along the way will never shrink back on their own.

This is the bloat treadmill: the storage design guarantees garbage is created at write speed and collected at vacuum speed, and keeping those two rates matched is an operations job that PostgreSQL outsources to you, with autovacuum as a default‑configured intern.

Charge 3: one idle transaction poisons the well

Keep the experiment going. In a second session, open a transaction and just… hold a snapshot:

-- session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM accounts_lean;
-- go to lunch

Back in session A, update 100,000 rows and vacuum:

INFO:  finished vacuuming "mvcc_critics.public.accounts_lean": tuples: 14 removed, 1099945 remain, 100000 are dead but not yet removable
       removable cutoff: 718, which was 1 XIDs old when operation ended

“Dead but not yet removable.” Session B’s snapshot might still need those versions, so vacuum must preserve them, and not just in this table: the xmin horizon is held back for every table in the database, whether session B ever reads them or not. PostgreSQL will never cancel the reader and never make it fail. (Strictly true at the MVCC level, but production setups routinely add that cancellation back in from outside: idle_in_transaction_session_timeout and statement_timeout exist specifically to kill the lunch‑break transaction before it holds back the xmin horizon.)

It just silently stops collecting garbage, database‑wide, for as long as the transaction idles. Every Postgres operator eventually learns to hunt long‑running transactions and stale replication slots, usually the week after learning what n_dead_tup means.

Charge 4: the 32‑bit debt

Every tuple header stores its creator transaction ID in 32 bits. Four billion transactions and the counter wraps, so PostgreSQL must periodically freeze old tuples, rewriting pages that haven’t been touched in months, purely to reclaim XID space. Fall too far behind and the database stops accepting writes to protect itself. Ask Sentry, who wrote up their 2015 wraparound outage, or the several companies that have re‑learned it since. But only PostgreSQL makes you periodically rewrite untouched pages to keep the clock from running out.

So: charges stick. Amplified writes by default, garbage created at write speed and collected by a process you have to tune, cleanup held hostage by any idle snapshot, and a 32‑bit clock that must be wound forever. PostgreSQL’s MVCC is bad, in four specific and demonstrable ways.

The undo camp: Oracle and InnoDB

Oracle and MySQL’s InnoDB answer the four questions the opposite way: versions live outside the table, chains point new to old, cleanup is partly the writer’s problem. This is the design most “Postgres MVCC is bad” articles implicitly hold up as the fix, so it deserves the closest look.

An UPDATE in InnoDB modifies the row in place, inside the clustered index (in InnoDB, the table is a B‑tree ordered by primary key). Before overwriting, it copies the old values into an undo log, a separate storage area, and stamps the row with a pointer to that undo record. Every row carries two hidden system columns for this: DB_TRX_ID, the last transaction to touch it, and DB_ROLL_PTR, the pointer into undo. Oracle’s structure differs in detail (undo segments, block‑level consistent reads) but the shape is identical: the table holds only the newest version, and history hangs off it in a chain of deltas.

A reader with an older snapshot finds the current row, notices DB_TRX_ID is too new, and walks the undo chain backwards, applying deltas until it reconstructs the version it’s entitled to see. Oracle does this at block granularity, rebuilding a whole consistent image of the block in memory.

Look at what this buys, point by point against the four charges:

• The table stays compact. One version per row, ever. No bloat treadmill, no pgstattuple showing 47% dead. Undo is recycled in a fixed‑size ring (Oracle) or purged continuously (InnoDB).
• Secondary indexes are spared. They point at the primary key, a logical reference, not a physical location. Update last_seen and the four secondary indexes from our experiment are untouched, because the row never moved and its key never changed. This single decision is most of the write‑amplification gap Uber measured.
• No vacuum. Cleanup is woven into normal operation: InnoDB’s purge threads trim undo history continuously, Oracle recycles undo segments.
• No wraparound rituals. InnoDB’s transaction IDs are 48‑bit and Oracle’s SCN is 64‑bit since 12.2 (the 48‑bit era ended with the headroom scare from charge 4), wide enough that neither engine ever has to freeze a tuple.

Four for four. Case closed?

Here is the bill. Rollback costs what the transaction cost. Remember our experiment: PostgreSQL rolled back a one‑million‑row UPDATE in 0.124 milliseconds, because abort in a heap‑versioning system means “do nothing, the old versions are already in place.” (It took 1.59 seconds to make the mess and 0.124 milliseconds to disown it. The mess remains, for vacuum, but your transaction is done.)

In an undo‑based engine, rollback means walking the undo chain and reversing every change, one row at a time, roughly as much work as the forward direction, sometimes more. Every MySQL operator eventually kills a long‑running UPDATE and then watches the database spend longer rolling it back than it spent running, unkillably, because the undo must be applied. Crash mid‑transaction and recovery inherits the same job before the system is fully healthy. It is precisely to escape this that SQL Server later grew a Postgres‑style version store, but we’re getting ahead of ourselves.

Readers pay for that history at read time, too. In PostgreSQL, an old row version is just sitting in the heap; reading it costs the same as reading anything else. The first reader of a recently committed tuple may take a detour through pg_xact to set its hint bits, a one‑time tax per tuple, and then it’s plain heap forever. In the undo camp, every version except the newest must be reconstructed, per read, by chasing pointers into a different part of storage and applying deltas. A long‑running report against a hot table gets progressively slower as the gap between its snapshot and current state widens, and it pressures the buffer pool with undo pages while it’s at it.

The idle‑snapshot problem didn’t go away. It changed its failure mode. Undo space is finite. When a long‑running query needs a version whose undo has already been recycled, Oracle doesn’t pause cleanup like PostgreSQL does. It kills the query: ORA-01555: snapshot too old, one of the most famous error codes in databases, decades of consulting hours in four digits. InnoDB makes the opposite choice within the same design: purge waits for the oldest read view, history piles up (watch the history list length climb in SHOW ENGINE INNODB STATUS), and undo tablespaces balloon. MySQL 8’s automatic undo truncation does shrink tablespaces whose logs have gone inactive, but the long read that caused the growth is exactly what keeps the logs active, so the garbage stays put for as long as the reader does. That is bloat, relocated to a place pgstattuple can’t see. Same fundamental tension, choose your poison: cancel the reader, or keep the garbage.

InnoDB’s purge threads are, functionally, vacuum with better marketing: they scan undo history, remove delete‑marked index records, and can fall behind under write bursts exactly the way autovacuum can. The war‑story threads on the MySQL side (history list length in the millions, purge lag stalls) read like Postgres bloat threads with the nouns changed.

So the undo camp didn’t eliminate MVCC’s costs. It moved them: away from the future (background cleanup, table bloat) and onto the writer’s critical path (rollback, in‑place update bookkeeping) and the reader of old data (reconstruction). If your workload commits nearly always, updates heavily indexed rows, and rarely runs long reads against hot tables, that trade is genuinely better - that is, roughly, the workload Uber had. It’s a real trade‑off favoring a specific workload shape, not a universal upgrade.

SQL Server: versioning as an optional extra

SQL Server is the remind