PostgreSQL 原理图解 · 存储与 MVCC

堆表 + 独立索引：PG 里每一次索引查找都要回表

MySQL 的 InnoDB 是聚簇索引——主键索引的叶子节点就是整行数据，按主键查一次就拿到全部。PG 完全不是这样：表是一个无序的堆文件，所有索引（包括主键）都是独立的 B-tree，叶子节点只存 ctid。

这个结构带来三个直接后果

一、主键在 PG 里没有「聚集」的优待。InnoDB 的主键索引就是数据本身，按主键范围查是顺序 IO；PG 按主键查是先走一棵 B-tree 拿到 ctid，再去堆里按物理位置取行——两个随机 IO。行数一大，这个差距很明显。

二、没有「按主键聚簇存储」这回事。CLUSTER 命令能按某个索引重排一次物理顺序，但它是一次性的，后续的插入不会维护这个顺序，而且执行期间会锁表（8.0 之后可以 CLUSTER ... USING，但仍然要排他锁，通常配合 pg_repack 做）。

三、所有索引都平等。PG 里建 5 个索引就是 5 棵独立的树，没有「主键索引特殊、二级索引叶子存主键值」这一层。代价是二级索引的叶子直接存 ctid（6 字节），比存主键值更省——但如果行被更新导致换了物理位置，所有索引都要更新。

index-only scan：PG 版的「覆盖索引」，但有个陷阱

当查询要的列全在索引里，PG 可以做 index-only scan，不取堆。但——它仍然要判断这一行的可见性，而可见性信息不在索引里，在可见性映射表（visibility map）里。

EXPLAIN (ANALYZE, BUFFERS)
SELECT name FROM users WHERE name = 'zhangwei';

 Index Only Scan using idx_users_name on users
   Index Cond: (name = 'zhangwei'::text)
   Heap Fetches: 0          ← 0 才是真的没回堆
   Buffers: shared hit=4

MVCC：旧版本不在 undo log 里，就在表里

这是 PG 和 MySQL 最本质的分歧。InnoDB 把旧版本放在 undo log，回滚段里；PG 把旧版本直接留在表的数据页里，每一行都带自己的出生和死亡标记。

堆里真实存在的东西

三个会话各自看到什么

每一行头上的四个隐藏列

一条 UPDATE 到底做了什么

-- 表里原来有一行 balance = 100，由事务 1000 插入
UPDATE accounts SET balance = 200 WHERE id = 1;   -- 当前事务 1001

-- PG 内部做的是：
-- 1) 把旧行的 xmax 改成 1001（标记「被我删了」）
-- 2) 在同一个页（或新页）插入一条新行：xmin = 1001, xmax = 0, balance = 200
-- 3) 新行 ctid 变了 —— 所有索引都需要指向新 ctid

SELECT ctid, xmin, xmax, balance FROM accounts;
--  (0,1) | 1000 | 1001 | 100      ← 旧版本，还占着空间
--  (0,5) | 1001 |    0 | 200      ← 新版本

HOT 更新：PG 的一个优化，也是一道面试题

如果满足两个条件，PG 可以走 HOT（Heap-Only Tuple），不新建索引条目，只在新旧行之间加一条指针，索引还指向旧位置，顺着指针能找到新版本：

① 被更新的列没有被任何索引引用；② 新版本能放进同一个页的空闲空间（这时候 fillfactor 就很重要）。

三种隔离级别的实现

没有 undo log，代价是什么

InnoDB 的事务一回滚或提交，undo 里的旧版本就可以被 purge 掉，空间能复用。PG 的旧版本就躺在表里，谁都不能动——因为可能还有别的长事务的事务快照需要看到它。

所以：PG 的 UPDATE 越多、事务越长，表就越大。这个「越大」不是逻辑行数变多，是物理文件膨胀。这就引出了下一个考点：VACUUM。

VACUUM 与表膨胀：PG 最常见的线上问题

VACUUM 干两件事，很多人只知道第一件：① 把死元组占的空间标记为可复用；② 推进事务 ID 的冻结（防止回卷）。注意是「标记可复用」，不是「还给操作系统」。

autovacuum 什么时候才动手

不是「一有死元组就清」，而是有一个触发阈值：

触发条件：n_dead_tup > autovacuum_vacuum_threshold
                     + autovacuum_vacuum_scale_factor × 表的估算行数

默认值：threshold = 50，scale_factor = 0.2   （PG 默认）

为什么会出现「vacuum 跑了但表还是越来越大」

VACUUM 的四种形态，别搞混

PG 19 的两个改进（2026 年 11 月 GA）

当前生产稳定版是 18.6，19 计划 11 月 19 日 GA。19 里和这一块相关的有两个：autovacuum 支持并行 worker（autovacuum_max_parallel_workers），以及新增 REPACK 命令（带 CONCURRENTLY），算是把 pg_repack 的能力收进了内核。面试里提一句「19 把在线重表做进内核了」，是个很新的加分点。

事务 ID 回卷：PG 独有的停机事故

PG 的事务 ID 是 32 位无符号整数，约 42 亿个。写满之后会绕回 0——一旦绕回，新事务的 ID 比老事务还小，所有过去的数据看起来都「来自未来」，整个库就废了。所以 PG 必须定期把老行的 xid「冻结」成特殊值。

把数据库的年龄拖一拖

为什么「28 亿」和「20 亿」是两个不同的数字

三个必须会查的 SQL

-- 1. 每个库离回卷还有多远（最该进监控的一条）
SELECT datname, age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint
         - age(datfrozenxid) AS remaining
FROM pg_database ORDER BY xid_age DESC;

-- 2. 哪张表最老（找出那个没被 vacuum 的表）
SELECT relname, age(relfrozenxid) AS xid_age, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY age(relfrozenxid) DESC LIMIT 20;

-- 3. 谁在阻止 vacuum 推进（长事务 / 复制槽，必查）
SELECT pid, now() - xact_start AS dur, state, left(query, 60)
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY dur DESC LIMIT 5;

SELECT slot_name, active, age(xmin), age(catalog_xmin)
FROM pg_replication_slots;

常见的三种成因

① 有一张冷表从来没被访问过。autovacuum 是按「表的变更量」触发的，一张只读的冷表没有变更，就不会被 vacuum——但它的 relfrozenxid 一直停在那里，把整个库的 age 顶上去。这是最常见的原因。

② 长事务或失效的复制槽。它们的 xmin 卡在很早的位置，vacuum 无法冻结更晚的行。

③ autovacuum 被关掉过。有些「优化」教程会建议关掉 autovacuum，这在 PG 里是自杀行为。

和 MySQL 的对照