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

Table of Contents

The charges against PostgreSQL

Charge 1: write amplification

Charge 2: your table is an accident scene

Charge 3: one idle transaction poisons the well

Charge 4: the 32-bit debt

The undo camp: Oracle and InnoDB

SQL Server: versioning as an optional extra

MongoDB: versions in the cache

The LSM generation: garbage collection as a lifestyle

Outside the RDBMS entirely: etcd

Fixing PostgreSQL itself

The scorecard

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 VACCUM, 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:

Where do old versions live? In the table itself, or in a separate structure?

Which way do version chains point? From old to new, or new to old?

What do indexes point at? A physical row location, or a logical key?

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 (<br>id bigint PRIMARY KEY,<br>email text NOT NULL,<br>status text NOT NULL,<br>balance numeric NOT NULL,<br>created_at timestamptz NOT NULL,<br>last_seen timestamptz<br>);

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

CREATE INDEX ON accounts (email);<br>CREATE INDEX ON accounts (status);<br>CREATE INDEX ON accounts (balance);<br>CREATE INDEX ON accounts (created_at);

CREATE TABLE accounts_lean (LIKE accounts);<br>ALTER TABLE accounts_lean ADD PRIMARY KEY (id);<br>INSERT INTO accounts_lean SELECT * FROM accounts;<br>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()<br>WHERE id > 100000 AND id 200000; wal_records | wal_fpi | wal_bytes | wal_pretty<br>-------------+---------+-----------+------------<br>302510 | 1419 | 38218531 | 36 MBUPDATE accounts SET last_seen = now()<br>WHERE id > 100000 AND id 200000; wal_records | wal_fpi | wal_bytes | wal_pretty<br>-------------+---------+-----------+------------<br>709440 | 1981 | 72394573 | 69 MB<br>Same logical change, 100,000 timestamps. The lean table produced about 3.0 WAL records per row. The indexed table produced 7.1: the heap update, plus one new entry in the primary key, plus one new entry in each of the four secondary indexes, none of which index the column we changed. They all got rewritten anyway, because...