Article URL: https://boringsql.com/posts/mvcc-bad-bad/ Comments URL: https://news.ycombinator.com/item?id=49098417 Points: 22 # Comments: 9

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: 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. 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. 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: 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):