learnNote

主题

第 8 章 MVCC 与 VACUUM ​

「读不阻塞写、写不阻塞读」——这是 PostgreSQL（以及所有现代关系数据库）最迷人的承诺。但 PG 实现这个承诺的方式独树一帜：多版本数据直接堆在表里，让旁边的 InnoDB 都觉得不可思议。

⚠️ 本章是 PostgreSQL 内核中面试最爱考、生产最爱炸的部分。读完这一章，你会理解：为什么一张「业务正常的」表会突然膨胀到 100GB？为什么有人说「PG 千万不要长事务」？为什么 GitLab、Sentry、Mailchimp 都因为 PG 同一个原因炸过？

8.1 导读：先看一个让所有 PG 新手震惊的实验 ​

sql
CREATE TABLE t (id INT PRIMARY KEY, val TEXT);
INSERT INTO t VALUES (1, 'hello');

-- 看看物理大小
SELECT pg_size_pretty(pg_relation_size('t'));   -- 8192 bytes (1 页)

-- 反复更新同一行 100 万次
DO $$
BEGIN
  FOR i IN 1..1000000 LOOP
    UPDATE t SET val = 'hello-' || i WHERE id = 1;
  END LOOP;
END$$;

SELECT pg_size_pretty(pg_relation_size('t'));   -- 35 MB ❗
SELECT count(*) FROM t;                         -- 1 行

1 行数据，物理占用 35MB？ 这就是 MVCC 的「副作用」：老版本不会被 UPDATE 覆盖，而是堆在表里，等待 VACUUM 来收拾。理解这一点，是理解 PG 一切性能问题的钥匙。

8.2 MVCC 的核心思想 ​

8.2.1 三句话讲清 MVCC ​

🏦 生活类比：MVCC 就像银行的「流水账」。你存 100、取 50、再存 30，账户里的数字虽然变了，但每一笔交易都单独写一行流水，绝不擦掉前面的记录。任何一刻想看「过去某时点的余额」，把那时之前的所有流水加起来就行。

MVCC = Multi-Version Concurrency Control（多版本并发控制）：

  1. 写不覆盖：UPDATE / DELETE 不修改原数据，而是产生一个新版本，旧版本继续存在；
  2. 读不加锁：读操作根据「快照」决定看哪个版本，永远不需要等写者；
  3. 可见性靠规则判断：每行数据带「出生证（xmin）」和「死亡证（xmax）」，每个事务带「快照」，组合起来就能精确判断「这一行该不该让我看到」。

8.2.2 PG 的 MVCC vs MySQL InnoDB 的 MVCC ​

📌 与 MySQL 的最大区别

┌─────────────────────────┬────────────────────────────┬────────────────────────────┐
│        对比项            │      MySQL InnoDB          │      PostgreSQL            │
├─────────────────────────┼────────────────────────────┼────────────────────────────┤
│ 旧版本存放在哪           │  Undo Log（独立的回滚段）   │  ★ 直接堆在表里 ★          │
│ UPDATE 后堆表是否变大    │  否（原地更新 + Undo）     │  是（产生新元组）          │
│ 读历史版本的开销         │  顺着 Undo 链反向构造       │  直接读，但要扫死元组      │
│ 死元组怎么清理           │  Purge 线程后台清理 Undo    │  VACUUM / autovacuum       │
│ 长事务的危害             │  Undo 段膨胀               │  ★ 表 + 索引膨胀 ★         │
│ 事务 ID                  │  无回卷问题（64 位）        │  ★ 32 位有回卷风险 ★       │
│ 索引存什么               │  聚簇索引存数据 + 二级索引索引到主键 │  所有索引都存 ctid       │
└─────────────────────────┴────────────────────────────┴────────────────────────────┘

PG 这套设计的优点：

  • 实现简单：没有独立的 Undo 段，崩溃恢复时不需要 Undo Log；
  • 读历史数据快：直接读元组，不需要构造；
  • 回滚极快：只要标记事务为 ABORT，所有未来的可见性判断会自动忽略；
  • DDL 可回滚：DDL 只是写一些系统表元组，事务回滚就当没发生。

缺点：

  • 表会膨胀，必须靠 VACUUM 清理；
  • 长事务会让 VACUUM 没法清理；
  • 32 位事务 ID 会回卷，必须 FREEZE。

这一切的代价——就是本章要讲的 VACUUM。

8.3 元组的隐藏字段：xmin / xmax / cmin / cmax / ctid ​

PG 的每一行数据，除了你看到的列，还默默带着 6 个系统字段。最重要的是这 5 个：

字段含义数据类型
xmin创建这行的事务 ID（出生证）xid（4 字节）
xmax删除 / 更新这行的事务 ID（死亡证；0 = 还活着）xid（4 字节）
cmin事务内创建命令的序号cid（4 字节）
cmax事务内删除命令的序号cid（4 字节）
ctid物理位置 (块号, 块内偏移)tid（6 字节）

💡 小实验：直接 SELECT 它们！

text
learn_pg=# SELECT xmin, xmax, cmin, cmax, ctid, * FROM ch8_accounts ORDER BY id;
 xmin  | xmax | cmin | cmax | ctid  | id | name  | balance |          updated_at
-------+------+------+------+-------+----+-------+---------+-------------------------------
 12345 |    0 |    0 |    0 | (0,1) |  1 | Alice | 1000.00 | 2026-04-17 10:00:00+08
 12345 |    0 |    0 |    0 | (0,2) |  2 | Bob   | 1000.00 | 2026-04-17 10:00:00+08
 12345 |    0 |    0 |    0 | (0,3) |  3 | Carol | 1000.00 | 2026-04-17 10:00:00+08
 12345 |    0 |    0 |    0 | (0,4) |  4 | David | 1000.00 | 2026-04-17 10:00:00+08
 12345 |    0 |    0 |    0 | (0,5) |  5 | Eve   | 1000.00 | 2026-04-17 10:00:00+08
(5 rows)

5 行都是同一个事务（xmin=12345）一次性 INSERT 的，xmax 都为 0 表示「还活着」。

8.3.1 看 UPDATE 如何产生新版本 ​

text
learn_pg=# BEGIN;
BEGIN
learn_pg=*# SELECT txid_current();
 txid_current
--------------
        12350
learn_pg=*# UPDATE ch8_accounts SET balance = 1500 WHERE id = 1;
UPDATE 1
learn_pg=*# SELECT xmin, xmax, ctid, id, balance
            FROM ch8_accounts WHERE id = 1;
 xmin  | xmax | ctid  | id | balance
-------+------+-------+----+---------
 12350 |    0 | (0,6) |  1 | 1500.00
learn_pg=*# COMMIT;

观察：

  • 原来 (0,1) 的元组 xmax 被默默标记为 12350（已死亡）；
  • 同时在 (0,6) 写了一个全新的元组，xmin=12350、xmax=0、balance=1500；
  • 老元组并没有被覆盖、删除——它还在物理空间里。
Page 0：
┌─────────────────┬─────────────────┬─────┬─────────────────┬────────┐
│ (0,1) 老元组    │ (0,2) Bob       │ ... │ (0,5) Eve       │ (0,6)  │
│ xmin=12345      │ xmin=12345      │     │ xmin=12345      │ NEW    │
│ xmax=12350 ★死  │ xmax=0          │     │ xmax=0          │ Alice  │
│ id=1, bal=1000  │                 │     │                 │ 1500   │
└─────────────────┴─────────────────┴─────┴─────────────────┴────────┘

🤔 新手疑问：为什么不直接覆盖？因为可能还有别的事务正用旧 snapshot 在读 (0,1)！MVCC 的本质就是「让读者看到自己事务时间点的数据」，所以不能动旧版本。

8.3.2 cmin / cmax 是干嘛的？ ​

cmin / cmax 是事务内部的命令序号（每条 SQL 一个），主要用途是处理**「同一个事务内多条命令的可见性」**：例如先 INSERT 再 SELECT，应该能看到自己刚 INSERT 的数据；但 PL/pgSQL 函数中可能想看「函数开始时的快照」。详细规则比较冷门，理解为「事务内的微版本号」即可。

⚠️ 实际上 PG 内部为了节约空间，xmin/xmax/cmin/cmax 在元组头里共享存储（HeapTupleFields union），细节见源码 htup_details.h。

8.3.3 ctid 与 ItemPointer ​

ctid 是 (块号 block, 偏移 offset) 的二元组。它非常重要：

  • 所有索引都存 ctid：B-Tree 索引中的「叶子项」存的是 (索引列值, ctid)；
  • UPDATE 后 ctid 变了，老索引项必须被重写或 通过 HOT 链跳转（见 8.4 节）。
text
learn_pg=# SELECT ctid, id, balance FROM ch8_accounts WHERE id = 1;
 ctid  | id | balance
-------+----+---------
 (0,6) |  1 | 1500.00

learn_pg=# SELECT * FROM ch8_accounts WHERE ctid = '(0,6)';
 id |  name  | balance | updated_at
----+--------+---------+--------
  1 | Alice  | 1500.00 | ...

ctid 不是稳定主键，任何 UPDATE / VACUUM FULL 都可能改变它，业务代码不要依赖。

8.4 可见性判断算法 ​

🧙 生活类比：图书管理员（事务）走进图书馆（数据库），手里拿着一张「借书时刻凭据」（snapshot）。这张凭据上写着：

  • 我进馆的时候，第 N 号读者刚刚到达 → 比他后到的人借的书我都看不见
  • 我进馆的时候，第 1、3、5 号读者还在馆里没还书 → 他们改的书我也看不见
  • 我进馆的时候，第 2、4 号读者已经离开了 → 他们改的书可见

8.4.1 Snapshot 的结构 ​

每个事务在某一时刻获取的 snapshot 包含：

c
struct Snapshot {
    TransactionId xmin;     // 当前活跃事务中最小的 xid
    TransactionId xmax;     // 下一个将被分配的 xid
    TransactionId xip[];    // 所有正在执行（还没结束）的事务 xid 列表
}

8.4.2 可见性判断伪代码 ​

对一个元组 t 和当前事务的 snapshot S：

python
def is_visible(t, S, my_xid):
    # 第一步：判断元组的 xmin（创建者）
    if t.xmin == my_xid:
        # 是我自己创建的，看 cmin（同事务内已执行的命令）
        return cmin_visible(t)

    if status(t.xmin) == ABORTED:
        return False                    # 创建它的事务回滚了 -> 不可见

    if t.xmin >= S.xmax:
        return False                    # 创建者比 snapshot 还新 -> 不可见

    if t.xmin in S.xip:
        return False                    # 创建者还在跑（snapshot 时点未提交）

    # 到这里：创建者已 COMMIT 且在 snapshot 之前 -> 这行「曾经」可见
    # 第二步：判断元组的 xmax（删除者）
    if t.xmax == 0 or status(t.xmax) == ABORTED:
        return True                     # 没人删 -> 可见

    if t.xmax == my_xid:
        return cmax_invisible(t)        # 我自己删的，看 cmax

    if t.xmax >= S.xmax:
        return True                     # 删除者比 snapshot 新 -> 还可见

    if t.xmax in S.xip:
        return True                     # 删除者尚未提交 -> 还可见

    return False                        # 删除者已提交 -> 不可见

🎯 一句话总结：「创建者已提交且 snapshot 之前」AND「（没删 / 删除者未提交 / 删除者比我新）」。

8.4.3 一个完整例子 ​

设有 3 个事务串行进行：

T0 (xid=100): INSERT (id=1, val='A')   -> 元组 t1: xmin=100, xmax=0
T1 (xid=101): UPDATE id=1 SET val='B'  -> t1.xmax=101; 新元组 t2: xmin=101, xmax=0
T2 (xid=102): DELETE id=1              -> t2.xmax=102

现在一个新事务 T3 进来，snapshot = {xmin=103, xmax=104, xip=[]}（即所有 < 103 的事务都已 COMMIT）。

  • t1：xmin=100 已 COMMIT 且 < 104；xmax=101 已 COMMIT 且 < 104 → 不可见；
  • t2：xmin=101 已 COMMIT 且 < 104；xmax=102 已 COMMIT 且 < 104 → 不可见。

结论：T3 看不到 id=1 这行（已被删除），符合直觉。

但如果 T3 在 T1 提交前就开始（snapshot.xip = [101]）：

  • t1：xmin=100 已 COMMIT；xmax=101 在 xip 里 → 可见！
  • t2：xmin=101 在 xip 里 → 不可见。

T3 看到的还是 val='A'，正确反映了 T3 开始时的世界。

8.5 HOT 更新（Heap-Only Tuple） ​

UPDATE 产生新版本后，老索引项还指向旧 ctid，必须重新插入索引——这会让索引不停膨胀。PG 8.3 引入 HOT 优化解决了这个问题。

8.5.1 HOT 的两个条件 ​

✓ 新版本必须写在同一个数据页内（页内有空闲空间）
✓ 没有任何索引涉及被更新的列

满足条件时：

  • 新版本写在同一页里（节省空间）；
  • 不写新索引项！老索引项继续指向旧 ctid；
  • 旧元组的 t_ctid 字段指向新元组（形成 HOT chain）；
  • 查询走索引时，先到旧元组，发现是 HOT chain，再跳到新元组。

8.5.2 ASCII 图：HOT chain ​

索引                堆表 Page 0
[idx_name='Alice']  ┌──────────────────────────────────┐
       │            │  (0,1) Alice  bal=1000  ★HOT_UPDATED│ ───┐
       │            │  (0,2) Bob ...                       │    │ HOT 链
       └──────────► │ ↓ (t_ctid points to)                 │    ▼
                    │  (0,6) Alice  bal=1500  ★HEAP_ONLY   │ ── 新版本
                    └──────────────────────────────────────┘

效果：索引不增长，更新极快。这对「计数器、状态字段、热点行频繁更新」的场景至关重要。

8.5.3 fillfactor：留余地的艺术 ​

fillfactor 控制新插入数据时每页的填充率（默认 100%）。改成 70% 意味着：

  • 每页只填到 70% 就开新页；
  • 留 30% 空间给将来的 HOT 更新。
sql
CREATE TABLE ch8_accounts (...) WITH (fillfactor = 70);
-- 或修改已有表
ALTER TABLE ch8_accounts SET (fillfactor = 70);

📌 经验值：高频 UPDATE 表（订单状态、计数器）建议 70~80；只追加的日志表用 95~100。

8.5.4 怎么知道是不是 HOT 了？ ​

sql
SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'ch8_accounts';

--  n_tup_upd | n_tup_hot_upd
-- -----------+---------------
--   1000000  |    998542       ← 99.85% 是 HOT，非常理想

n_tup_hot_upd / n_tup_upd 是 HOT 比例，越高越好。

实战陷阱：你不小心给 ch8_accounts.balance 建了索引，结果所有 balance 更新都不能 HOT，索引和表会一起膨胀！

8.6 死元组 Dead Tuple ​

「死元组」= 已被某个看得见的事务删除（或被 UPDATE 替换）、且没有任何活跃事务可能再看到它的版本。

sql
-- 查看每张表的死元组情况
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup,0), 4) AS dead_ratio
FROM pg_stat_user_tables
WHERE relname LIKE 'ch8_%'
ORDER BY n_dead_tup DESC;

--  relname        | n_live_tup | n_dead_tup | dead_ratio
-- ----------------+------------+------------+------------
--  ch8_hot_demo   |       1000 |     500000 | 500.0000  ← 严重膨胀！
--  ch8_accounts   |          5 |        200 |  40.0000

死元组 = 占空间但没用的「僵尸」。它们：

  1. 占用堆表空间（导致 SeqScan 变慢）；
  2. 占用索引空间（索引也得跟着扫死元组）；
  3. 直到 VACUUM 才会被回收。

8.7 VACUUM：清理死元组的「保洁阿姨」 ​

🧹 生活类比：你的桌子上摆了 100 个空快递盒（死元组）。VACUUM 不会把盒子搬走，但会贴个「这个位置可重用」的标签。下次有新东西来，就直接放到空盒子位置上。这就是普通 VACUUM。

VACUUM FULL 是搬家公司：把所有有用的东西打包到新桌子上，老桌子整个扔掉，过程中你不能用桌子（锁表）。

8.7.1 三种 VACUUM 命令 ​

sql
-- 1) 普通 VACUUM：标记空间可重用，不锁表（仅 ShareUpdateExclusiveLock）
VACUUM ch8_accounts;

-- 2) VACUUM FULL：物理重写表，回收所有空间，但持有 AccessExclusiveLock，整表锁住
VACUUM FULL ch8_accounts;

-- 3) VACUUM (VERBOSE, ANALYZE)：清理 + 更新统计信息 + 详细输出
VACUUM (VERBOSE, ANALYZE) ch8_accounts;

8.7.2 真实 VACUUM VERBOSE 输出解读 ​

text
learn_pg=# VACUUM (VERBOSE, ANALYZE) ch8_event_log;
INFO:  vacuuming "ch8_event_log"
INFO:  finished vacuuming "ch8_event_log": index scans: 1
pages: 0 removed, 200 remain, 0 skipped using visibility map
tuples: 5000 removed, 5000 remain, 0 are dead but not yet removable
removable cutoff: 12500, which was 0 XIDs old when operation ended
new relfrozenxid: 12100, which is 100 XIDs ahead of previous value
index scan needed: 50 pages from table (25.00% of total) had 5000 dead item identifiers removed
index "ch8_event_log_pkey": pages: 30 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 12.345 MB/s, avg write rate: 0.123 MB/s
buffer usage: 234 hits, 12 misses, 8 dirtied
WAL usage: 12 records, 0 full page images, 1234 bytes
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.05 s
INFO:  analyzing "ch8_event_log"
INFO:  "ch8_event_log": scanned 200 of 200 pages, containing 5000 live rows
VACUUM

逐行解读：

行含义
tuples: 5000 removed真正被回收（标记可重用）的死元组数
5000 remain还活着的元组数
0 are dead but not yet removable关键指标：死了但因为有老快照在用，暂时不能回收（长事务的罪证！）
removable cutoff: 12500xid < 12500 的死元组都能回收
new relfrozenxid: 12100这张表的「最老 xmin」推进到 12100（防回卷有用）
index scan needed因为有死元组要从索引中删除，触发了一次索引扫描

8.7.3 autovacuum 守护进程 ​

autovacuum 是 PG 后台一直在跑的「保洁班」，决定何时对哪张表跑 VACUUM。

触发条件（每张表独立判断）：

触发 VACUUM     当 n_dead_tup > autovacuum_vacuum_threshold
                          + autovacuum_vacuum_scale_factor × reltuples

触发 ANALYZE   当 n_mod_since_analyze > autovacuum_analyze_threshold
                          + autovacuum_analyze_scale_factor × reltuples

默认值：

参数默认含义
autovacuum_vacuum_threshold50死元组绝对阈值
autovacuum_vacuum_scale_factor0.2比例阈值（= 表大小的 20%）
autovacuum_analyze_threshold50analyze 绝对阈值
autovacuum_analyze_scale_factor0.1analyze 比例（10%）
autovacuum_max_workers3并发的 autovacuum 进程数
autovacuum_naptime1min检查间隔

📌 生产经验：默认的 20% 阈值对大表来说太晚了！1 亿行的表要等 2000 万死元组才触发，性能早就崩了。建议：

sql
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,    -- 改成 5%
  autovacuum_vacuum_threshold = 5000
);

8.7.4 VACUUM 的限速：不要把 IO 打爆 ​

VACUUM 自带「cost-based delay」机制：

autovacuum_vacuum_cost_limit = 200       -- 累积 cost 达到 200 就 sleep
autovacuum_vacuum_cost_delay = 2ms       -- sleep 时长
vacuum_cost_page_hit  = 1                -- 命中 buffer 的页贡献 1 cost
vacuum_cost_page_miss = 2
vacuum_cost_page_dirty = 20              -- 弄脏一页贡献 20 cost

线上紧急清理大表时可以临时放开（小心 IO 打爆）：

sql
SET vacuum_cost_delay = 0;
VACUUM (VERBOSE) huge_table;

8.8 表膨胀（bloat）排查 ​

「膨胀」= 表 / 索引的物理大小远超实际数据所需。

8.8.1 快速判断（用 pg_stat_user_tables） ​

sql
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100 * n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS bloat_pct,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

8.8.2 精确测量（用 pgstattuple） ​

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('ch8_event_log');
--  table_len   | tuple_count | tuple_len | tuple_percent | dead_tuple_count
--              |             |           |               | dead_tuple_len  | dead_tuple_percent
-- 1,638,400    | 5000        | 250,000   | 15.26         | 5000            | 250,000  | 15.26
--                                                          ↑ 死元组占了 15% 的物理空间

或对索引：

sql
SELECT * FROM pgstatindex('ch8_event_log_pkey');

8.8.3 四种处理方式对比 ​

手段锁速度是否需要双倍空间场景
VACUUM弱锁快否日常清理，只标记空间可重用
VACUUM FULL整表写锁慢是离线重建，真正回收磁盘
CLUSTER整表写锁慢是重写 + 按某索引物理排序
pg_repack 扩展弱锁慢是生产首选，在线重建

8.9 事务 ID 回卷（XID Wraparound）—— 数据库末日 ​

8.9.1 32 位 xid 与「环形比较」 ​

PG 的 xid 是 32 位无符号整数，最大约 42 亿（2³² ≈ 4.29B）。当 xid 用完时怎么办？

PG 用「环形比较」：

   xid 数轴是个圆环：
              0
           ┌─────┐
           │     │
   2^31  ─┤     ├─  2^31 - 1
           │     │
           └─────┘
            2^32

    任意两个 xid x, y 比较：
        在圆上互为 2^31 的关系，看哪个更"靠前一半"

具体：x < y 当且仅当 (int32)(x - y) < 0。

这意味着 xid 12345 与 xid 4294967296+12300（差 45）的比较取决于环上的距离。

回卷灾难：如果某行的 xmin 是 5（很老），新事务是 4294967300（环上看回到 4），两者比较时新事务会觉得「5 比我还新」，于是这一行对所有人不可见——数据消失了！

8.9.2 防冻结：FROZEN 标记 ​

PG 用「冻结」机制规避回卷：

🧊 生活类比：把太老的快递（早就该收的）打上「永久标记」——以后所有人见到这个标记都直接当作「比我老，可见」，不再参与 xid 比较。

技术上：当一个元组的 xmin 足够老（早于 vacuum_freeze_min_age），VACUUM 会把元组的 t_infomask 标记为 HEAP_XMIN_FROZEN，对所有快照都可见，xid 字段从此失效。

正常元组:    xmin=12345 → 与每个 snapshot 比较
冻结元组:    xmin=FROZEN ✓ → 对所有人直接可见，跳过 xid 比较

8.9.3 关键参数 ​

参数默认含义
vacuum_freeze_min_age50,000,000元组 xid 比当前 xid 老这么多才考虑冻结
vacuum_freeze_table_age150,000,000表的 relfrozenxid 老这么多就强制扫全表
autovacuum_freeze_max_age200,000,000表 relfrozenxid 老这么多就强制启动 autovacuum 防回卷
vacuum_failsafe_age1,600,000,000接近灾难时启动「failsafe 模式」（PG 14+）

8.9.4 监控 xid 年龄 ​

sql
SELECT datname,
       age(datfrozenxid) AS xid_age,
       2^31 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;

--  datname   |   xid_age   | xids_remaining
-- -----------+-------------+----------------
--  learn_pg  | 50,000,000  |  2,097,483,648    ← 还很安全
--  postgres  | 1,500,000   |  2,146,983,648

紧急情况（接近 21 亿）：

sql
-- 强制冻结整库
VACUUM FREEZE;

-- 单表强制冻结
VACUUM (FREEZE, VERBOSE) huge_table;

8.9.5 真实灾难案例 ​

🔥 案例 1：Sentry 2015 年大宕机

Sentry 的事件表 update 极频繁，autovacuum 跟不上。某天 xid 接近 21 亿，PG 进入「停止接受新事务」的保护模式（xid_warn_limit），整个 Sentry 服务挂了 2 小时。事后他们调小了 autovacuum_freeze_max_age 并加了监控告警。

🔥 案例 2：Mailchimp 2019

Mailchimp 有个超大日志表（500GB），autovacuum 永远跑不完一次。最终触发回卷保护，需要离线 8 小时手工 VACUUM FREEZE。修复后他们改用了分区表，让每个月分区单独 VACUUM。

🔥 案例 3：GitLab 多次踩坑

GitLab.com 不止一次因为 autovacuum 配置不合理 + 长事务卡住，导致 xid 大量积压。他们的应对：禁用所有持续 > 1 小时的事务、监控 pg_stat_activity 中的 xact_start。

8.10 Visibility Map（VM）与 Free Space Map（FSM） ​

每个表（关系，relation）在物理上对应三个文件：

$PGDATA/base/<dboid>/
├── 16384            主数据文件（堆 / 索引）
├── 16384_fsm        Free Space Map：每页剩多少空闲空间
└── 16384_vm         Visibility Map：每页是否「全部元组对所有事务都可见」

8.10.1 Visibility Map (VM) ​

作用：对每个数据页用 2 个 bit 标记两个属性：

bit 0 (ALL_VISIBLE)  : 这一页的所有元组对所有快照都可见
bit 1 (ALL_FROZEN)   : 这一页的所有元组都已冻结

两个杀手级用途：

  1. Index-Only Scan：B-Tree 索引存了 (col, ctid)，但 PG 必须回表确认元组对当前事务可见——除非 VM 说「这一页全可见」，那就完全跳过回表！
  2. VACUUM 跳过：标记为 ALL_FROZEN 的页 VACUUM 直接跳过，大表 VACUUM 速度从 1 小时降到 1 分钟。
text
learn_pg=# EXPLAIN (ANALYZE, BUFFERS)
            SELECT id FROM ch8_event_log WHERE id < 100;
                                      QUERY PLAN
-----------------------------------------------------------------------------
 Index Only Scan using ch8_event_log_pkey on ch8_event_log
   Index Cond: (id < 100)
   Heap Fetches: 0    ← 0 次回表，全靠 VM
   Buffers: shared hit=4
 Planning Time: 0.123 ms
 Execution Time: 0.456 ms

Heap Fetches: 0 表示 100% 利用了 VM。如果 > 0，说明有些页的 VM 位没设置（可能死元组或新写入），手动 VACUUM 一下能让 Heap Fetches 归零。

8.10.2 Free Space Map (FSM) ​

作用：记录每个数据页还剩多少空闲空间，INSERT 时根据 FSM 找一个能容纳新行的页。

VACUUM 后空间被释放，FSM 也会更新；FSM 不准确时（比如 crash 后）可以：

sql
SELECT pg_freespace('ch8_accounts');
-- 或在 PG 17+ 里用 ALTER TABLE ... TRUNCATE FREESPACE 重建（实验特性）

8.11 长事务的危害：MVCC 模型的最大敌人 ​

🐛 生活类比：图书馆有个读者借了一本书，借了 3 年没还。这 3 年里，所有人对这本书的修改（涂改、补丁、撕掉某页）都不能真的执行——因为他还可能要回来对照原版！结果图书馆里堆满了「保留备份」。

PG 的 VACUUM 判断「可回收元组」的依据：这个元组的 xmin/xmax 比所有活跃事务的 xmin 都老。

活跃事务的最老 xmin = 100  ← 一个跑了 3 小时的事务持有
死元组 t1: xmax = 200  ←  > 100
死元组 t2: xmax = 5000 ←  > 100
死元组 t3: xmax = 10000← > 100
                          全部都比 100 新 -> 全部不能回收！

后果：

  1. VACUUM 跑不动：哪怕表里 99% 是死元组，VACUUM 也不能回收；
  2. autovacuum 持续触发但无效：消耗 IO 但解决不了膨胀；
  3. xid 持续增长：长事务持有的最老 xid 永远在那里，relfrozenxid 推不动；
  4. 复制延迟：流复制场景下，主库的长事务也会让从库的死元组无法回收（需要 hot_standby_feedback）。

8.11.1 监控长事务 ​

sql
SELECT pid,
       usename,
       state,
       now() - xact_start  AS xact_duration,
       now() - query_start AS query_duration,
       backend_xmin,
       LEFT(query, 80)     AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

--  pid  | usename  | state               | xact_duration   | backend_xmin
-- ------+----------+---------------------+-----------------+--------------
--  4321 | report   | idle in transaction | 03:14:15        |       12100   ← 罪魁祸首！
--  5678 | api      | active              | 00:00:01        |       19999

idle in transaction 是最大的敌人：事务开着但没在执行 SQL，通常是应用 bug（忘了 commit / 异常分支没回滚）。

sql
-- 强行结束某个长事务
SELECT pg_terminate_backend(4321);

8.11.2 预防方案 ​

sql
-- 限制事务最长存活时间（PG 17+）
SET idle_in_transaction_session_timeout = '5min';
SET transaction_timeout = '30min';      -- PG 17+
SET statement_timeout = '60s';

8.12 与 MySQL 全方位对比 ​

维度MySQL InnoDBPostgreSQL
多版本数据存哪Undo Log（独立段）堆表内（与活元组并排）
UPDATE 后表会膨胀吗否（原地 + Undo）是
死元组 / 旧版本回收Purge 线程后台清理VACUUM / autovacuum
长事务影响Undo 段膨胀（system tablespace 涨）整表 + 索引膨胀
事务 ID 大小64 位（不会回卷）32 位（有回卷风险）
防回卷机制不需要FREEZE 元组
索引存什么二级索引存主键所有索引存 ctid
HOT 类似机制类似（page 内更新 + 二级索引指 PK）HOT chain（PK 也能 HOT）
Index Only ScanInnoDB 自带（覆盖索引）需 Visibility Map 支持
表统计信息更新自动 + ANALYZE TABLEautovacuum 自动 + ANALYZE
VACUUM FULL 等价OPTIMIZE TABLE / pt-online-schema-changeVACUUM FULL / CLUSTER / pg_repack

8.13 小结 ​

  1. PG 的 MVCC 把多版本直接放在堆表里，与 MySQL 的 Undo Log 完全不同。这带来「实现简单、读旧版本快、回滚极快、DDL 可回滚」四大好处，代价是「表会膨胀，必须 VACUUM」。
  2. 每个元组带 xmin、xmax、cmin、cmax、ctid 5 个隐藏字段，可见性靠 snapshot + xid 比较决定。
  3. HOT 更新：同页 + 索引列没动，新版本不写新索引项，对 OLTP 至关重要。靠 fillfactor < 100 留出空间。
  4. 死元组靠 VACUUM 回收。普通 VACUUM 标记空间可重用（不锁表），VACUUM FULL 重写整表（锁表）。
  5. autovacuum 是后台守护进程，默认阈值是「死元组 > 50 + 表大小 × 20%」。大表必须调小比例。
  6. 表膨胀用 pg_stat_user_tables.n_dead_tup 快速看，pgstattuple 精确测，生产用 pg_repack 在线重建。
  7. 事务 ID 回卷是 32 位 xid 的固有缺陷，靠 FREEZE 把老元组永久标记为可见。长事务 + 大表是回卷灾难的标配，必须监控告警。
  8. Visibility Map 让 Index Only Scan 跳过回表、让 VACUUM 跳过冻结页；Free Space Map 帮 INSERT 找空位。
  9. 长事务（特别是 idle in transaction）是 PG 的头号杀手，会让 VACUUM 失效、表无限膨胀、xid 不能推进。生产必加 idle_in_transaction_session_timeout。

🎮 配套演示 ​

用浏览器打开 ./08_mvcc_vacuum/demo.html，跟着可视化动画再走一遍本章核心概念。

配套代码在 ./08_mvcc_vacuum/code/，每个脚本都可以独立 python xxx.py 运行，先跑 init.sql 准备数据。

8.14 面试高频题 ​

Q1：MVCC 是什么？PostgreSQL 与 MySQL 的 MVCC 实现有何根本区别？ ​

考察点：MVCC 本质、跨数据库的实现差异。

答案要点：

MVCC（多版本并发控制）：让数据库的每次写产生一个新版本而不是覆盖原数据，读操作根据「快照」决定看哪个版本，从而实现「读不阻塞写、写不阻塞读」。

两家实现对比：

维度PostgreSQLMySQL InnoDB
旧版本存放位置直接堆在表里（产生新元组）独立的 Undo Log 段
可见性判断xmin / xmax + snapshot 比较隐藏列 DB_TRX_ID / DB_ROLL_PTR + Read View
UPDATE 后表大小增加（产生新版本）几乎不变（原地更新 + Undo）
回滚开销极低（标记 xid 为 ABORT）需要顺着 Undo Log 反向恢复
崩溃恢复不需要 Undo，重放 WAL 即可需要 Undo + Redo 一起
DDL 是否可回滚几乎全部可以大多数不可以

PG 的优劣：

  • 优势：实现简单、读旧版本快、回滚便宜、DDL 可回滚；
  • 劣势：必须有 VACUUM、长事务危害大、xid 回卷风险（32 位）。

InnoDB 的优劣：

  • 优势：表大小稳定、不需要 VACUUM 这种重型操作；
  • 劣势：构造历史版本要遍历 Undo 链（长事务下慢）、Undo 段也会膨胀。

加分项：

  • 提到「读不阻塞写、写不阻塞读」是 MVCC 的核心承诺；
  • 解释 PG 的「snapshot 是什么时候拿的」（RC 是每条 SELECT，RR/SR 是事务首次读）；
  • 提到 Heap-Only Tuple、Index-Only Scan 是 PG 缓解膨胀的关键优化。

Q2：xmin / xmax 是什么？请描述 PG 的可见性判断算法 ​

考察点：MVCC 元组结构、可见性规则。

答案要点：

xmin、xmax 是元组的两个隐藏字段：

  • xmin：创建这行的事务 ID（出生证）；
  • xmax：删除 / 更新这行的事务 ID，0 表示还活着（死亡证）。

snapshot 三元组：

{
  xmin: 当前活跃事务中最小的 xid,
  xmax: 下一个将要分配的 xid,
  xip:  快照拍下时正在跑的事务 xid 列表
}

可见性算法（对元组 t、快照 S、当前事务 my_xid）：

1. 判断 t.xmin 是否「已经看见」：
   - t.xmin == my_xid          → 是我自己创建的（看 cmin）
   - status(t.xmin) == ABORTED → 创建者回滚 → 不可见
   - t.xmin >= S.xmax          → 创建者比快照新 → 不可见
   - t.xmin in S.xip           → 创建者在快照时未提交 → 不可见
   - 否则                       → 「曾经可见」，进入下一步

2. 判断 t.xmax 是否「已经把它干掉了」：
   - t.xmax == 0                → 活着 → 可见
   - status(t.xmax) == ABORTED  → 删除者回滚 → 可见
   - t.xmax >= S.xmax           → 删除者比快照新 → 可见
   - t.xmax in S.xip            → 删除者快照时未提交 → 可见
   - 否则                        → 删除者已提交 → 不可见

一句话总结：「创建者已提交且在快照之前」AND「（没人删 / 删除者未提交 / 删除者比我新）」。

SQL 实操：

sql
SELECT xmin, xmax, ctid, * FROM ch8_accounts;

直接看到这些隐藏字段。

加分项：

  • 解释 cmin / cmax 是事务内的命令序号，处理事务内多命令可见性；
  • 提到 t_infomask 中的 HEAP_XMIN_COMMITTED / HEAP_XMIN_ABORTED 提示位（cache 已知状态，避免反复查 pg_xact）；
  • 冻结元组 HEAP_XMIN_FROZEN 跳过 xmin 检查；
  • 子事务的可见性走 pg_subtrans。

Q3：什么是 HOT 更新？触发条件是什么？为什么重要？ ​

考察点：HOT 优化、索引膨胀。

答案要点：

HOT（Heap-Only Tuple） 是 PG 8.3 引入的优化，目的是让 UPDATE 不必为新版本写新索引项，从而避免索引膨胀。

触发条件（两个全部满足）：

  1. 新版本能放在同一页（页内有空间，需要 fillfactor < 100）；
  2. 没有任何索引涉及被 UPDATE 的列（包括所有二级索引、表达式索引、部分索引的 WHERE）。

HOT 链结构：

索引项 ───► 旧元组 (xmax=新事务，HOT_UPDATED 标记)
                │ t_ctid
                ▼
             新元组 (xmin=新事务，HEAP_ONLY_TUPLE 标记)

查询时通过索引到旧元组，发现是 HOT 链头，沿 t_ctid 跳到新版本。

为什么重要：

  1. 索引大小稳定：高频 UPDATE 的字段不会让索引爆炸；
  2. 页内更新：写入更快、产生 WAL 更少；
  3. 索引不需要 VACUUM：HOT 老元组不在索引里，VACUUM 不用扫索引；
  4. autovacuum 压力小。

怎么检查 HOT 比例：

sql
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0),2) AS hot_ratio
FROM pg_stat_user_tables;

实战建议：

  • 高频 UPDATE 表设置 fillfactor = 70~80；
  • 慎给「频繁变化的列」建索引（如 status、updated_at）；
  • 可考虑用「生成列」分离索引列与业务列。

加分项：

  • 解释 fillfactor 的工作机制（仅影响新插入页，不重写已有页）；
  • 在 INSERT 后查看 pageinspect 扩展的 heap_page_items() 能看到 t_ctid 链；
  • 「BRIN 索引上的 HOT」行为更宽松（BRIN 不存 ctid）；
  • HOT 链过长会变慢，autovacuum 会触发 prune。

Q4：什么是 VACUUM 和 VACUUM FULL？什么场景用哪个？ ​

考察点：VACUUM 命令的差异、运维实战。

答案要点：

VACUUM（普通）：

  • 不锁表（仅持有 ShareUpdateExclusiveLock，与 SELECT/INSERT/UPDATE 兼容）；
  • 不释放磁盘空间给操作系统——只是把死元组占用的空间标记为「可重用」，下次 INSERT 时复用；
  • 速度快、可在线运行；
  • 同时更新 Visibility Map、Free Space Map；
  • 适用场景：日常清理、autovacuum 自动跑。

VACUUM FULL：

  • 持有 AccessExclusiveLock，整表锁住（任何 SELECT 也不能并发）；
  • 物理重写整张表 + 所有索引到一个新文件，把死元组完全消灭；
  • 真正回收磁盘空间给操作系统；
  • 需要 2 倍磁盘空间（同时存在新旧两份）；
  • 速度慢；
  • 适用场景：表严重膨胀（>50%）、磁盘紧张、计划停机维护。

生产替代方案：

工具锁级别特点
pg_repack 扩展弱锁（瞬时 ACCESS EXCLUSIVE 切换）生产首选，在线重建表 + 索引
CLUSTERAccessExclusiveLock重建并按某索引物理排序
分区表 + DETACH PARTITION弱锁老分区直接 DROP，没有 VACUUM 问题

实战决策树：

死元组 < 20%       → 等 autovacuum 自动处理
死元组 20% ~ 50%   → 业务低峰期手工 VACUUM ANALYZE
死元组 > 50%       → 用 pg_repack 在线重建（绝不用 VACUUM FULL）
关键大表           → 改造成分区表

加分项：

  • VACUUM 期间死元组若被「最老 backend_xmin」覆盖到则不可回收，必须先消灭长事务；
  • 解释 vacuum_cost_delay 限速、autovacuum_vacuum_cost_limit 节流；
  • PG 13+ 的 pg_stat_progress_vacuum 可以实时监控 VACUUM 进度。

Q5：什么是事务 ID 回卷（XID Wraparound）？怎么预防与解决？ ​

考察点：PG 的核心隐患、运维深度。

答案要点：

问题本质：

PG 的 xid 是 32 位无符号整数，最多 ~42 亿（2³²）。事务一直跑下去会用完。PG 用「环形比较」处理：任意两个 xid x、y 的「前后」由它们在数轴上靠左 / 靠右一半决定。

回卷灾难：当 current_xid - oldest_xmin 超过 2^31 ≈ 21 亿 时，老的元组在比较中变成「未来的事务」，对所有事务变得不可见——数据消失。

PG 的防御机制：

  • 冻结：当元组的 xmin 比当前 xid 老 vacuum_freeze_min_age（默认 5000 万）时，VACUUM 把它打上 HEAP_XMIN_FROZEN 标记，对所有快照永远可见，跳过 xid 比较；
  • 强制 autovacuum：当表的 relfrozenxid 比当前 xid 老 autovacuum_freeze_max_age（默认 2 亿）时，autovacuum 强制启动 VACUUM FREEZE，即使表上没有死元组；
  • failsafe 模式（PG 14+）：当 xid 接近 16 亿时，VACUUM 进入紧急模式，跳过索引清理、跳过 cost delay，全力推进 freeze；
  • 拒绝写入：当 xid 仅剩 100 万 (xid_stop_limit) 时，整个数据库进入只读模式，必须用 single-user 模式手动 VACUUM。

监控：

sql
SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_used
FROM pg_database
ORDER BY xid_age DESC;

报警阈值建议：age > 10 亿 warning，> 15 亿 critical。

预防：

  1. 消灭长事务：idle_in_transaction_session_timeout + 监控 pg_stat_activity.xact_start；
  2. 大表分区：每个分区独立 VACUUM，FREEZE 工作量小；
  3. 调小 autovacuum 阈值：高写入大表 autovacuum_vacuum_scale_factor=0.05；
  4. 定期手工 VACUUM FREEZE：业务低峰期主动 freeze 老表；
  5. 升级 PG 版本：PG 14+ 有 failsafe，PG 17 有更多监控视图。

真实灾难案例：Sentry 2015、Mailchimp 2019、GitLab 多次——共同特征都是「autovacuum 跟不上 + 长事务」。

加分项：

  • 解释 PG 17 引入的 64-bit xid 议题（pluggable storage 探索方向）；
  • pg_stat_activity.backend_xmin 是定位「卡住 freeze 推进」元凶的关键字段；
  • 单表的 pg_class.relfrozenxid 与 pg_database.datfrozenxid 关系；
  • single-user mode 应急恢复流程：postgres --single -D $PGDATA learn_pg。

Q6：长事务为什么对 PostgreSQL 危害特别大？怎么发现和处理？ ​

考察点：MVCC 模型的最大软肋、生产实战。

答案要点：

长事务（Long Transaction）的危害：

PG 的 VACUUM 判断「死元组是否可回收」的规则：死元组的 xmax 必须比所有当前活跃事务的 backend_xmin 都老。一个跑了 3 小时的事务持有的 backend_xmin 是 3 小时前的值，导致：

  1. 死元组不能回收：哪怕表里 99% 是死元组，VACUUM 看到「最老 xmin」太小就退避；
  2. 表 / 索引膨胀：autovacuum 持续触发但「dead but not yet removable」，没有效果；
  3. xid 不能推进：表 relfrozenxid 推不动，回卷风险持续累积；
  4. 复制延迟 / 从库膨胀：开了 hot_standby_feedback 时，主库不能 vacuum；没开则从库会因 conflict 终止查询；
  5. 执行计划变差：表统计信息过时，optimizer 走错路径。

最毒的形态：idle in transaction（事务开着但没在执行任何 SQL）。这通常是应用 bug：

  • 业务异常没 ROLLBACK；
  • 连接池里的连接长期空闲；
  • 程序员忘了 commit；
  • 第三方调用挂起；
  • 调试器停在断点。

怎么发现：

sql
-- 事务持续时间排序
SELECT pid, usename, state, now() - xact_start AS duration,
       backend_xmin, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state = 'idle in transaction'
ORDER BY xact_start;

-- 检查活跃事务里最老的 xmin
SELECT min(backend_xmin), age(min(backend_xmin)) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL;

加监控：pg_exporter 上报 pg_stat_activity_xact_seconds_max、pg_database_xid_age 两个指标。

怎么处理：

  1. 立即止血：SELECT pg_terminate_backend(pid) 杀掉嫌疑事务；

  2. 设置自动 timeout：

    sql
    ALTER ROLE app SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE report SET statement_timeout = '30min';

  3. 审查应用代码：所有 BEGIN 后必须有 COMMIT/ROLLBACK，建议用框架的事务装饰器；

  4. 长报表用 SERIALIZABLE READ ONLY DEFERRABLE：拿一致快照但不阻止 VACUUM 推进（细节：DEFERRABLE 等到没冲突才开始，且不会增加数据库的 xmin horizon）；

  5. 大表分区 + 独立 vacuum：把痛点局部化。

加分项：

  • 解释 backend_xmin 与 backend_xid 的区别（前者是「我读什么」，后者是「我占用 xid」）；
  • 提到 PG 14 的 track_io_timing 帮助识别 vacuum IO 瓶颈；
  • 提到「prepared transaction 悬挂」也是隐形长事务源头（pg_prepared_xacts）；
  • 推荐 pg_stat_progress_vacuum、pg_stat_progress_analyze 实时观察 autovacuum 工作量。

🐘 本章一句话总结：PostgreSQL 用「多版本元组堆在表里 + xmin/xmax 决定可见性」实现 MVCC，代价是必须有 VACUUM 来收拾死元组。理解 HOT、autovacuum 调优、长事务危害、xid 回卷防御，你就掌握了 PG 内核的灵魂。

🔗 延伸阅读 ​

🎬 可视化演示 ​

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码 ​

python
"""
01_mvcc_basic.py —— MVCC 基础：观察 xmin / xmax / ctid 随事务的变化

依赖：
    pip install "psycopg[binary]>=3.1"

要点：
    1. 同一行 UPDATE 多次，看 xmin 不停变化、ctid 跳到新位置
    2. 演示「同一时刻不同事务看到不同版本」
"""
from __future__ import annotations

import psycopg
from psycopg import IsolationLevel

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def show_row(conn: psycopg.Connection, label: str) -> None:
    print(f"\n--- {label} ---")
    with conn.cursor() as cur:
        cur.execute(
            "SELECT xmin::text, xmax::text, ctid::text, id, balance "
            "FROM ch8_accounts WHERE id = 1"
        )
        row = cur.fetchone()
        print(f"  xmin={row[0]:>6}  xmax={row[1]:>6}  ctid={row[2]:>8}  "
              f"id={row[3]}  balance={row[4]}")


def reset() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("UPDATE ch8_accounts SET balance = 1000.00")
        conn.execute("VACUUM ch8_accounts")


def demo_xmin_change() -> None:
    print("=== 1) UPDATE 后 xmin 与 ctid 变化 ===")
    reset()
    with psycopg.connect(DSN, autocommit=True) as conn:
        show_row(conn, "初始状态")
        for i in range(3):
            conn.execute(
                "UPDATE ch8_accounts SET balance = balance + 1 WHERE id = 1"
            )
            show_row(conn, f"第 {i+1} 次 UPDATE 后")


def demo_snapshot_view() -> None:
    """两个 RR 事务并发，演示「快照决定看见什么」。"""
    print("\n=== 2) 双事务快照视图差异 (REPEATABLE READ) ===")
    reset()
    a = psycopg.connect(DSN); a.isolation_level = IsolationLevel.REPEATABLE_READ
    b = psycopg.connect(DSN); b.isolation_level = IsolationLevel.REPEATABLE_READ

    a.execute("SELECT 1")  # 触发 A 的 snapshot
    b.execute("SELECT 1")  # 触发 B 的 snapshot

    # A 修改 + 提交
    a.execute("UPDATE ch8_accounts SET balance = 9999 WHERE id = 1")
    a.commit()

    # B 还能看到旧值（snapshot 时 A 未提交）
    show_row(b, "B 在自己的 snapshot 中看到（应为 1000）")
    b.commit()

    # 新事务 C 看到 9999
    with psycopg.connect(DSN, autocommit=True) as c:
        show_row(c, "新事务 C 看到（应为 9999）")

    a.close(); b.close()


def demo_dead_tuple_count() -> None:
    """连续 UPDATE 后查看 n_dead_tup。"""
    print("\n=== 3) UPDATE 产生死元组的统计 ===")
    reset()
    with psycopg.connect(DSN, autocommit=True) as conn:
        for _ in range(50):
            conn.execute(
                "UPDATE ch8_accounts SET balance = balance + 1 WHERE id = 2"
            )
        # 强制刷新统计
        conn.execute("SELECT pg_stat_reset_single_table_counters("
                     "'ch8_accounts'::regclass)")
        for _ in range(50):
            conn.execute(
                "UPDATE ch8_accounts SET balance = balance + 1 WHERE id = 2"
            )

        cur = conn.execute("""
            SELECT n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd
            FROM pg_stat_user_tables
            WHERE relname = 'ch8_accounts'
        """)
        live, dead, upd, hot = cur.fetchone()
        print(f"  n_live_tup={live}  n_dead_tup={dead}  "
              f"n_tup_upd={upd}  n_tup_hot_upd={hot}")
        if upd:
            print(f"  HOT 比例 = {100*hot/upd:.1f}%")


if __name__ == "__main__":
    demo_xmin_change()
    demo_snapshot_view()
    demo_dead_tuple_count()
python
"""
02_hot_update.py —— HOT 更新 vs 非 HOT 更新对比

依赖：
    pip install "psycopg[binary]>=3.1"

要点：
    ch8_hot_demo 表：
        - score 列没有索引   -> UPDATE score 可以 HOT
        - nickname 列有索引 -> UPDATE nickname 不能 HOT

    跑大量更新后对比：
        n_tup_upd / n_tup_hot_upd
        表大小 / 索引大小
"""
from __future__ import annotations

import psycopg

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def reset_table() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("TRUNCATE ch8_hot_demo")
        conn.execute(
            "INSERT INTO ch8_hot_demo (id, nickname, score) "
            "SELECT g, 'user_' || g, g * 10 "
            "FROM generate_series(1, 1000) g"
        )
        conn.execute("VACUUM ch8_hot_demo")
        conn.execute("ANALYZE ch8_hot_demo")


def stats() -> tuple[int, int, str, str]:
    with psycopg.connect(DSN, autocommit=True) as conn:
        cur = conn.execute("""
            SELECT n_tup_upd, n_tup_hot_upd
            FROM pg_stat_user_tables
            WHERE relname='ch8_hot_demo'
        """)
        upd, hot = cur.fetchone() or (0, 0)
        cur = conn.execute(
            "SELECT pg_size_pretty(pg_relation_size('ch8_hot_demo')), "
            "pg_size_pretty(pg_relation_size('idx_ch8_hot_demo_nickname'))"
        )
        ts, idxs = cur.fetchone()
    return upd or 0, hot or 0, ts, idxs


def reset_stats() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("SELECT pg_stat_reset_single_table_counters("
                     "'ch8_hot_demo'::regclass)")


def run_updates(n: int, on_indexed_col: bool) -> None:
    sql = ("UPDATE ch8_hot_demo SET nickname = nickname || '+' "
           "WHERE id = %s") if on_indexed_col else (
          "UPDATE ch8_hot_demo SET score = score + 1 WHERE id = %s")
    with psycopg.connect(DSN, autocommit=True) as conn:
        for _ in range(n):
            for i in range(1, 11):
                conn.execute(sql, (i,))


def main() -> None:
    print("=== A. UPDATE 「无索引列 score」（应大量 HOT） ===")
    reset_table()
    reset_stats()
    run_updates(50, on_indexed_col=False)
    upd, hot, ts, idxs = stats()
    print(f"  n_tup_upd = {upd}, n_tup_hot_upd = {hot}, "
          f"HOT 比例 = {100*hot/max(1,upd):.1f}%")
    print(f"  表大小 = {ts}, 索引大小 = {idxs}")

    print("\n=== B. UPDATE 「有索引列 nickname」（应几乎无 HOT） ===")
    reset_table()
    reset_stats()
    run_updates(50, on_indexed_col=True)
    upd, hot, ts, idxs = stats()
    print(f"  n_tup_upd = {upd}, n_tup_hot_upd = {hot}, "
          f"HOT 比例 = {100*hot/max(1,upd):.1f}%")
    print(f"  表大小 = {ts}, 索引大小 = {idxs}")

    print("\n=== C. 在 B 之后再 VACUUM 看死元组与索引项 ===")
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("VACUUM (VERBOSE) ch8_hot_demo")


if __name__ == "__main__":
    main()
python
"""
03_vacuum_bloat.py —— 制造死元组并观察 VACUUM 前后大小变化

依赖：
    pip install "psycopg[binary]>=3.1"

要点：
    1. 反复 UPDATE 同一批行，观察 pg_stat_user_tables.n_dead_tup 与表大小
    2. 普通 VACUUM 不缩小磁盘，但 n_dead_tup 归零
    3. VACUUM FULL 真正缩小到接近实际数据
"""
from __future__ import annotations

import psycopg

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
TABLE = "ch8_event_log"


def stats(label: str) -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        cur = conn.execute(f"""
            SELECT pg_size_pretty(pg_relation_size('{TABLE}'))
        """)
        size = cur.fetchone()[0]
        cur = conn.execute("""
            SELECT n_live_tup, n_dead_tup
            FROM pg_stat_user_tables WHERE relname='ch8_event_log'
        """)
        live, dead = cur.fetchone()
    print(f"  [{label:<24}] size={size:>12}  live={live:>6}  dead={dead:>6}")


def make_dead_tuples(rounds: int) -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        for r in range(rounds):
            conn.execute(
                f"UPDATE {TABLE} SET payload = payload || '+' "
                f"WHERE id BETWEEN 1 AND 5000"
            )


def main() -> None:
    print("=== VACUUM / VACUUM FULL 演示 ===\n")

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"VACUUM FULL {TABLE}")
        conn.execute(f"ANALYZE {TABLE}")

    stats("初始（VACUUM FULL 后）")

    make_dead_tuples(rounds=5)
    # 让 PG 把统计同步出来
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"ANALYZE {TABLE}")
    stats("反复 UPDATE 5 轮后")

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"VACUUM (VERBOSE) {TABLE}")
        conn.execute(f"ANALYZE {TABLE}")
    stats("普通 VACUUM 后（大小不变）")

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"VACUUM FULL {TABLE}")
        conn.execute(f"ANALYZE {TABLE}")
    stats("VACUUM FULL 后（缩回去）")

    print("\n说明：")
    print("  - 普通 VACUUM 把死元组空间标记为可重用，不归还 OS")
    print("  - VACUUM FULL 重写整表，物理大小回到最小，但锁表")
    print("  - 生产环境建议用 pg_repack 在线代替 VACUUM FULL")


if __name__ == "__main__":
    main()
python
"""
04_long_transaction_pain.py —— 长事务阻止 VACUUM 回收死元组

依赖：
    pip install "psycopg[binary]>=3.1"

实验设计：
    1. 主线程：开一个 REPEATABLE READ 长事务并持有快照
    2. 后台线程：反复 UPDATE 同一批行，制造死元组
    3. 主线程发起 VACUUM (VERBOSE)
       -> 输出会有 "0 are dead but not yet removable" 字样
    4. 主线程提交长事务后再 VACUUM
       -> 死元组终于被回收
"""
from __future__ import annotations

import threading
import time

import psycopg
from psycopg import IsolationLevel

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
TABLE = "ch8_long_tx_demo"


def setup() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"TRUNCATE {TABLE}")
        conn.execute(
            f"INSERT INTO {TABLE} SELECT g, g FROM generate_series(1, 1000) g"
        )
        conn.execute(f"VACUUM FULL {TABLE}")


def stats(label: str) -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"ANALYZE {TABLE}")
        cur = conn.execute("""
            SELECT n_live_tup, n_dead_tup,
                   pg_size_pretty(pg_relation_size(%s))
            FROM pg_stat_user_tables WHERE relname='ch8_long_tx_demo'
        """, (TABLE,))
        live, dead, size = cur.fetchone()
    print(f"  [{label:<32}] live={live:>5} dead={dead:>5} size={size}")


def churn() -> None:
    """后台线程：制造死元组"""
    with psycopg.connect(DSN, autocommit=True) as conn:
        for _ in range(20):
            conn.execute(f"UPDATE {TABLE} SET v = v + 1 WHERE id BETWEEN 1 AND 500")


def vacuum_verbose(tag: str) -> None:
    """运行 VACUUM (VERBOSE) 并捕获 NOTICE 输出。"""
    print(f"\n--- VACUUM {tag} ---")
    captured: list[str] = []
    with psycopg.connect(DSN, autocommit=True) as conn:
        # psycopg v3：通过 notice handler 收集 NOTICE/INFO
        conn.add_notice_handler(
            lambda diag: captured.append(diag.message_primary or "")
        )
        with conn.cursor() as cur:
            cur.execute(f"VACUUM (VERBOSE) {TABLE}")
    for note in captured:
        if any(kw in note for kw in
               ("removable", "tuples:", "pages:", "scan", "removed",
                "remain", "frozen", "index")):
            print(f"    {note.strip()}")


def main() -> None:
    setup()
    print("=== 第一阶段：没有长事务，VACUUM 正常工作 ===")
    stats("初始")
    churn()
    stats("UPDATE 后")
    vacuum_verbose("（无长事务干扰）")
    stats("VACUUM 后")

    print("\n=== 第二阶段：开一个长事务持有 snapshot ===")
    long_conn = psycopg.connect(DSN)
    long_conn.isolation_level = IsolationLevel.REPEATABLE_READ
    long_conn.execute(f"SELECT count(*) FROM {TABLE}")  # 触发 snapshot
    print("  长事务已开启并持有 snapshot")

    setup()
    churn()
    stats("制造死元组（长事务在跑）")

    vacuum_verbose("（长事务正在跑，应大量 not yet removable）")
    stats("VACUUM 后（死元组没回收）")

    print("\n=== 第三阶段：长事务 COMMIT 后 VACUUM 才有效 ===")
    long_conn.commit()
    long_conn.close()
    print("  长事务已提交")

    vacuum_verbose("（长事务已结束，应能完整回收）")
    stats("VACUUM 后（终于回收）")

    print("\n💡 结论：长事务（特别是 idle in transaction）会让 PG 表无限膨胀！")
    print("    生产必须设置 idle_in_transaction_session_timeout 并监控告警。")


if __name__ == "__main__":
    main()
python
"""
05_xid_age_check.py —— 监控 XID 回卷剩余空间 + 长事务 + 表 freeze 年龄

依赖：
    pip install "psycopg[binary]>=3.1"

用途：放进监控脚本里定期跑，给 DBA 提供回卷预警。
"""
from __future__ import annotations

import psycopg

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"

# 安全阈值（xid age）
WARN  = 1_000_000_000   # 10 亿，warning
CRIT  = 1_500_000_000   # 15 亿，critical
LIMIT = 2_000_000_000   # 20 亿，紧急


def colorize(age: int) -> str:
    if age >= LIMIT: return f"\033[31m{age:>13,}  [EMERGENCY]\033[0m"
    if age >= CRIT:  return f"\033[31m{age:>13,}  [CRITICAL]\033[0m"
    if age >= WARN:  return f"\033[33m{age:>13,}  [WARNING]\033[0m"
    return                f"\033[32m{age:>13,}  [OK]\033[0m"


def main() -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        print("=== 1) 数据库级 XID 年龄 ===")
        cur = conn.execute("""
            SELECT datname,
                   age(datfrozenxid) AS xid_age,
                   2147483648 - age(datfrozenxid) AS remaining
            FROM pg_database
            ORDER BY xid_age DESC
        """)
        print(f"  {'datname':<20} {'xid_age':>22}  {'remaining':>14}")
        for db, age, remaining in cur.fetchall():
            print(f"  {db:<20} {colorize(age)}  {remaining:>14,}")

        print("\n=== 2) Top 10 老 freeze 年龄的表 ===")
        cur = conn.execute("""
            SELECT n.nspname || '.' || c.relname AS tbl,
                   age(c.relfrozenxid) AS xid_age,
                   pg_size_pretty(pg_relation_size(c.oid)) AS size
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r','m','t')
              AND n.nspname NOT IN ('pg_catalog','information_schema')
            ORDER BY age(c.relfrozenxid) DESC
            LIMIT 10
        """)
        print(f"  {'table':<40} {'xid_age':>22}  {'size':>10}")
        for tbl, age, size in cur.fetchall():
            print(f"  {tbl:<40} {colorize(age)}  {size:>10}")

        print("\n=== 3) 当前活跃事务的最老 backend_xmin（卡 vacuum 推进的元凶） ===")
        cur = conn.execute("""
            SELECT pid, usename, state,
                   now() - xact_start AS xact_duration,
                   backend_xmin,
                   age(backend_xmin) AS xmin_age,
                   LEFT(query, 60) AS query
            FROM pg_stat_activity
            WHERE backend_xmin IS NOT NULL
            ORDER BY age(backend_xmin) DESC
            LIMIT 10
        """)
        rows = cur.fetchall()
        if not rows:
            print("  无活跃事务持有 xmin")
        else:
            print(f"  {'pid':>6} {'user':<10} {'state':<24} "
                  f"{'duration':>12} {'xmin':>10} {'xmin_age':>14}  query")
            for pid, user, state, dur, xmin, xage, q in rows:
                print(f"  {pid:>6} {user or '-':<10} {state or '-':<24} "
                      f"{str(dur):>12} {xmin:>10} {xage:>14,}  {q}")

        print("\n=== 4) 处于 idle in transaction 的连接（最毒形态） ===")
        cur = conn.execute("""
            SELECT pid, usename, application_name,
                   now() - state_change AS idle_dur,
                   LEFT(query, 80) AS last_query
            FROM pg_stat_activity
            WHERE state = 'idle in transaction'
            ORDER BY state_change
        """)
        rows = cur.fetchall()
        if not rows:
            print("  无 idle in transaction 连接 ✓")
        else:
            for pid, u, app, dur, q in rows:
                print(f"  pid={pid} user={u} app={app} idle={dur}")
                print(f"    last query: {q}")
            print("\n  💡 用 SELECT pg_terminate_backend(pid) 杀掉")

        print("\n=== 5) autovacuum 配置摘要 ===")
        cur = conn.execute("""
            SELECT name, setting
            FROM pg_settings
            WHERE name IN (
                'autovacuum',
                'autovacuum_max_workers',
                'autovacuum_naptime',
                'autovacuum_vacuum_threshold',
                'autovacuum_vacuum_scale_factor',
                'autovacuum_freeze_max_age',
                'vacuum_freeze_min_age',
                'vacuum_failsafe_age',
                'idle_in_transaction_session_timeout'
            )
            ORDER BY name
        """)
        for n, s in cur.fetchall():
            print(f"  {n:<40} = {s}")


if __name__ == "__main__":
    main()
markdown
# 第 8 章 MVCC 与 VACUUM - 可运行代码

## 准备

```bash
pip install "psycopg[binary]>=3.1"
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql

脚本说明 ​

脚本主题看什么
01_mvcc_basic.pyMVCC 基础UPDATE 后 xmin / xmax / ctid 的变化；快照下不同事务看到不同版本
02_hot_update.pyHOT 更新「更新无索引列」与「更新有索引列」的 HOT 比例和索引大小对比
03_vacuum_bloat.py表膨胀与回收普通 VACUUM 不缩盘 vs VACUUM FULL 缩盘的对比
04_long_transaction_pain.py长事务的危害长事务持有 snapshot 时 VACUUM 死活回收不了死元组
05_xid_age_check.py回卷监控直接可放进定时任务的 XID 年龄 + 长事务 + 配置体检

运行 ​

bash
python 01_mvcc_basic.py
python 02_hot_update.py
python 03_vacuum_bloat.py
python 04_long_transaction_pain.py
python 05_xid_age_check.py

04 脚本需要超过 5~10 秒（涉及长事务），耐心等待。

01_mvcc_basic.py ↗ · 02_hot_update.py ↗ · 03_vacuum_bloat.py ↗ · 04_long_transaction_pain.py ↗ · 05_xid_age_check.py ↗ · README.md ↗