PostgreSQL MVCC: tradeoffs compared to other engines
PostgreSQL MVCC: tradeoffs compared to other engines
PostgreSQL MVCC：与其他引擎的权衡对比
Table of Contents
目录
- The charges against PostgreSQL (针对 PostgreSQL 的指控)
- Charge 1: write amplification (指控 1：写放大)
- Charge 2: your table is an accident scene (指控 2：你的表就像事故现场)
- Charge 3: one idle transaction poisons the well (指控 3：一个空闲事务会污染整个系统)
- Charge 4: the 32-bit debt (指控 4：32 位债务)
- The undo camp: Oracle and InnoDB (Undo 阵营：Oracle 和 InnoDB)
- SQL Server: versioning as an optional extra (SQL Server：作为可选功能的版本控制)
- MongoDB: versions in the cache (MongoDB：缓存中的版本)
- The LSM generation: garbage collection as a lifestyle (LSM 世代：作为生活方式的垃圾回收)
- Outside the RDBMS entirely: etcd (完全脱离 RDBMS：etcd)
- Fixing PostgreSQL itself (修复 PostgreSQL 本身)
- 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.
如果你关注那些不喜欢 Postgres 的人，你首先会学到的就是：MVCC 很糟糕。这是一个 40 年前的设计错误，其特征随处可见：表膨胀导致体积翻倍、32 位事务计数器限制、与 VACUUM 的无休止斗争、死元组（dead tuples）的噩梦。它甚至还有“背书”：Uber 在 2016 年测量了写放大问题并因此转向 MySQL；Andy Pavlo 的数据库研究小组称 MVCC 是他们最讨厌的 PostgreSQL 特性。这确实存在，Postgres 在这方面表现得非常糟糕。
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?
虽然这些说法并不夸张，但归根结底这是一个真实的设计选择。膨胀、写放大、需要人工干预的 VACUUM：每一项指控都源于一个决策，而非缺陷。我们在下文中通过一个运行中的 PostgreSQL 19 beta2 实例重现了每一个问题，你可以亲眼观察这些损害是如何发生的。然而，在社区中流传的结论总是少问了一个问题：与什么相比？其他引擎是怎么做的，代价又是什么？
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?
因为 MVCC 是不可或缺的。任何希望实现“读不阻塞写”的数据库都必须在某处保留行的多个版本，而每个实现这一点的引擎都必须回答同样的四个问题：旧版本存放在哪里？是在表本身，还是在独立的结构中？版本链指向哪个方向？是从旧到新，还是从新到旧？索引指向什么？是物理行位置，还是逻辑键？谁来清理，何时清理？是稍后的后台进程，还是事务本身？
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.
PostgreSQL 的答案是：存放在表中、从旧指向新、物理位置、稍后的后台进程。批评者列出的每一项成本都源于这四个答案。而每一种替代方案都是另一套答案，只不过将代价转嫁给了其他人——写操作者、历史记录读取者、tempdb、缓存或压缩器。其中一些引擎花费了数年的工程努力，才换取了 PostgreSQL 从第一天起就免费拥有的特性。当一个事务在午餐时间保持开启时，它们都会以不同的方式失败。
The charges against PostgreSQL
针对 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.
如果你想了解完整机制，可以参考《PostgreSQL MVCC, Byte by Byte》中通过 pageinspect 进行的详细解析。简而言之：PostgreSQL 中的 UPDATE 操作从不修改行。它会将行的完整新副本写入堆（heap），标记旧版本的 t_xmax，并将两个版本都留在磁盘上。可见性是在读取时逐个元组决定的。清理工作是别人的问题，具体来说是 VACUUM 的问题。以下是四项指控，我们将逐一重现。
Charge 1: write amplification
指控 1：写放大
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.
这是 Uber 抱怨的核心。因为表上的每个索引都指向行的物理位置（页码加槽位，即 ctid），而 UPDATE 会创建一个新的物理行，所以每个索引都需要一个指向新位置的新条目。即使是那些你没有修改的列上的索引也是如此。
Set up two copies of the same 1M-row table. One with just a primary key, one with four additional secondary indexes: 设置两个相同的 100 万行表副本。一个仅有主键，另一个有四个额外的二级索引：
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):
现在更新 10 万行，仅修改 last_seen。注意，在两个表中，last_seen 都不在任何索引中。测量生成的 WAL（在 CHECKPOINT 和 pg_stat_reset_shared(‘wal’) 之后，以获得干净的统计窗口）：
UPDATE accounts_lean SET last_seen = now() WHERE id > 100000 AND id <= 200000;
-- wal_records | wal_fpi | wal_bytes | wal_pretty
-- -------------+---------+-----------+------------
-- 302510 | 1419 | 38218531 | 36 MB
UPDATE accounts SET last_seen = now() WHERE id > 100000 AND id <= 200000;
-- wal_records | wal_fpi | wal_bytes | wal_pretty
-- -------------+---------+-----------+------------
-- 709440 | 1981 | 72394573 | 69 MBSame 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 the row moved and they all point at physical locations.
同样的逻辑变更，10 万个时间戳。精简表每行产生约 3.0 条 WAL 记录。而索引表产生了 7.1 条：堆更新，加上主键中的一个新条目，再加上四个二级索引中各一个新条目——尽管这些索引都没有包含我们修改的列。它们都被重写了，因为行发生了移动，而它们都指向物理位置。
This is the amplification Uber measured, and it compounds: more WAL means more checkpoint work, more full-page writes, and more bytes shipped to every replica. A single-column update on a heavily indexed table is one of the most expensive things you can do per byte of useful change.
这就是 Uber 测量的放大效应，而且它是复合的：更多的 WAL 意味着更多的检查点工作、更多的全页写入，以及发送到每个副本的更多字节。在索引繁多的表上进行单列更新，是每字节有效变更中最昂贵的操作之一。
PostgreSQL’s mitigation is the HOT update (Heap-Only Tuples): if no indexed column changed and the new version fits on the same page, indexes are left alone. Both conditions failed above; the pages were packed full, so every new version landed on a different page. Give the table breathing room and try again:
PostgreSQL 的缓解措施是 HOT 更新（Heap-Only Tuples）：如果没有索引列发生变化，且新版本能放入同一页，则索引保持不变。上述测试中两个条件都未满足；页面已填满，因此每个新版本都落在了不同的页面上。给表留出空间再试一次：
ALTER TABLE accounts SET (fillfactor = 70);
VACUUM FULL accounts;
UPDATE accounts SET last_seen = now() WHERE id > 200000 AND id <= 300000;
-- wal_records | wal_bytes | n_tup_hot_upd (this batch)
-- -------------+-----------+----------------------------
-- 455812 | 51 MB | 41,996 of 100,000Better, 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, th…
情况好转了，但请注意这个数字：42% 的 HOT 更新，而不是 100%。每个页面有 30% 的空闲空间和大约 80 行数据；一旦页面上的前 30 次左右更新耗尽了预留空间，那么……