第 07 章

自测

前六章把"堆 → 行版本 → 死元组 → VACUUM → 索引 → 规划器"这条因果链铺开了——这一章检验一件事：遇到一个没见过的现象，你能不能把多章的机制接起来用。

这一章怎么用

题目分三层：概念层（回忆"是什么"）、原理层（讲清"为什么"）、应用判别层（在场景里判断"该用哪一章的机制"）。最后一层是这份教程真正的目标——能迁移，才算学会。

所有答案集中在本页最末一个折叠块里。先合上前面的章节，把答案写在纸上或编辑器里，写完再展开对照。直接展开答案，等于把这一章当成又读了一遍——那不会留下任何东西。

§三层梯度：你现在在哪一层

下面这张图是这套题的结构。能答对底层不代表学会了；真正的标志是能爬到顶层——把陌生场景拆回到某几章的机制上。

越往上越难 越接近真正学会 概念层 · 回忆 xmin/xmax、ctid、TOAST、默认隔离级别是什么 原理层 · 为什么 读不阻塞写、HOT、bitmap、回卷 应用判别层 这个场景该用哪一章
图 7.1三层梯度。注意：题量底层多、顶层少，但权重相反——顶层的判别题决定你是否真的把这份教程读进去了，底层答得再快也只是回忆。

§ 1概念层 · 对应第 1–2 章

每题一句话能答完。卡住就回 第 1 章 / 第 2 章。

  1. 一个 heap tuple 的隐藏列 xmin 和 xmax 分别记录什么？§1.4
  2. ctid 是什么？把一行 UPDATE 一次后，旧版本的 ctid 会改变吗？§1.3
  3. 一行大约超过多大会触发 TOAST？大字段被怎么处理？§1.5
  4. PostgreSQL 的默认事务隔离级别是哪一个？§2.3
  5. 用一句话定义"死元组"。它被回收前还占用什么？§2.4
  6. 8KB Page 内部，line pointer 数组和 tuple 数据各自从页的哪一端开始生长？§1.2

§ 2原理层 · 对应第 2–5 章

每题要讲到机制，不能只给结论。

  1. 为什么 PostgreSQL 的"读不阻塞写、写不阻塞读"？用 xmin/xmax 加快照说明，不要只说"因为 MVCC"。
  2. HOT 更新成立需要同时满足哪两个条件？破坏其中之一会带来什么后果？
  3. 普通 VACUUM 和 VACUUM FULL，在"是否把空间还给操作系统"和"加什么锁"两点上分别有什么区别？
  4. 事务 ID 回卷为什么是灾难性的？"冻结"（freeze）如何防止它？
  5. 可见性图（visibility map）被哪两个机制同时用到？分别用它来做什么？
  6. bitmap heap scan 为什么在"中等选择性"时胜过 index scan 和 seq scan？讲它对 IO 做了什么。
  7. WAL 让一次 COMMIT 只需要等什么落盘，而不必等什么落盘？
  8. 在 EXPLAIN ANALYZE 里，某节点 estimated rows 和 actual rows 差了几个数量级，这通常指向什么根因？会引发什么连锁后果？

§ 3应用判别层 · 跨章场景

这一层是这份教程的重心。每个场景都故意横跨多章——先判断它牵涉哪几章的机制，再给诊断/方案。没有"标准答案模板"，看你能不能把因果链接起来。

  1. 场景 A（牵涉第 2、3、5 章）：一张订单表，应用每天 DELETE 一批旧行、INSERT 一批新行，行数基本不变，但一个月内磁盘占用涨了 5 倍，count(*) 也越来越慢。给出你的诊断顺序：该看哪些指标、为什么跑了普通 VACUUM 占用还是没降、根因出在哪里。
  2. 场景 B（牵涉第 2、4 章）：一个每秒更新多次的 last_seen timestamptz 列，产品要求支持按它做范围查询。直接加 B-tree 索引后，写入吞吐掉了一半。解释机制根因，并给出两种不同取舍的方案。
  3. 场景 C（牵涉第 3、5 章）：同一条两表 JOIN，数据量小时毫秒返回，涨到百万行后突然慢到几十秒。EXPLAIN ANALYZE 显示某节点 estimated rows=1 而 actual rows=800000。判断规划器多半选了哪种 join、为什么选错、两种修法。
  4. 场景 D（牵涉第 3、4 章）：一个报表查询只 SELECT 两个列，且这两列都在一个覆盖索引里，但 EXPLAIN 显示走的是 Index Scan 而非 Index Only Scan，且 Heap Fetches 很高。这张表刚批量导入完。解释为什么没走 index-only，怎么让它走。
  5. 场景 E（牵涉第 1、2、6 章）：一个团队从 MySQL（InnoDB）迁到 PostgreSQL，原表用 UUID 主键、依赖 RR 隔离级别、用 INSERT ... ON DUPLICATE KEY UPDATE。逐条说明：迁到 PG 后哪一项行为会变、哪一项必须改写、哪一项反而从负担变成无负担，并各自说出机制原因。
亲手画一张图

合上整份教程，在纸上或 Excalidraw 里凭记忆重画 起点页那张概念地图——只画中心节点 + 5 个外围节点 + 边上的动词就够。画完回到 index 对照，重点检查一件事：你有没有把"可见性图"同时连到 VACUUM 和 index-only scan？如果漏了这条枢纽连接，回 §3.5 和 §4.7 各读一遍。

进阶挑战 · 刚好够不着

把"一次 UPDATE"讲到底

用一段连贯的话，追踪一条 UPDATE orders SET amount=... WHERE id=42 在 PG 内部从头到尾发生了什么：从 WAL、到堆里的新旧版本、到索引是否更新（HOT 与否）、到这次更新在何时对其它事务可见、到它留下的旧版本何时被谁回收。要求点到至少 4 章的概念。

提示（卡住再展开）

按时间顺序串：① 改动先进 WAL；② 堆里写新版本、旧版本盖 xmax（§2.4）；③ 改的列有没有索引决定走不走 HOT；④ 提交后新版本对后续快照可见（§2.2）；⑤ 旧版本成死元组，等 VACUUM 在没有更老快照需要它时回收。

答案（三层全部做完再展开）

概念层

  1. xmin = 插入这个行版本的事务 ID；xmax = 删除或锁定这个行版本的事务 ID（仍有效时为 0）。两者一起决定该版本对某个快照是否可见。
  2. ctid = 行版本的物理位置 (block号, 页内偏移)。UPDATE 会写一个新版本、它有新的 ctid；旧版本的 ctid 不变（仍指向它自己），只是被盖上 xmax。所以"旧版本的 ctid 变了吗"——不变。
  3. 约超过 page 的 1/4（≈2KB）时触发。大字段被压缩、必要时切片，存进旁置的 TOAST 表，主行只留一个指针。
  4. Read Committed。
  5. 死元组 = 一个已被 xmax 标记、且对所有当前存在的快照都不可见的旧行版本。回收前它仍占用所在 page 的空间，以及指向它的索引项。
  6. line pointer（ItemId）数组从 page 头部往后增长，tuple 数据从 page 尾部往前堆，中间是 free space。

原理层

  1. 读操作只拿每个 tuple 的 xmin/xmax 与自己的快照比对来判断可见性，从不申请行锁、不等待；写操作不覆盖旧行，而是追加一个新版本并给旧版本盖 xmax。读看旧版本、写产生新版本，两者不争用同一份数据，所以互不阻塞。
  2. 两个条件同时满足：被修改的列上都没有索引，且新版本能放进与旧版本相同的 page。破坏任一条（典型是给被更新的列加了索引），HOT 失效——每次更新都要为所有索引插新项，造成写放大和索引膨胀。
  3. 普通 VACUUM：不把空间还给 OS（只在文件内标记可复用），加的是较弱的锁、读写可继续。VACUUM FULL：把表重写进新文件、能把空间还给 OS，但加 ACCESS EXCLUSIVE 锁、全表期间不可读写。
  4. txid 是 32 位、会回卷；若一个很老的行版本没被冻结，回卷后它的 xmin 会显得"在未来"，于是突然对所有人不可见——数据看似消失。冻结把足够老的 xmin 改写成"永远可见"，使它不再受回卷影响。
  5. 被 VACUUM 和 index-only scan 同时用到。VACUUM 用它跳过"整页全可见"的 page（也用它维护这些标记）；index-only scan 用它确认目标 page 全可见，从而免去回堆确认可见性。
  6. 它先扫索引、把命中的 ctid 收进一个按 page 排序的位图，再按物理顺序一次性访问堆。这把"随机 IO + 同一 page 可能重复访问"变成"顺序 IO + 每页只访问一次"。单行命中时 index scan 的随机访问更省，命中大比例时 seq scan 更省，中间区间 bitmap 最优。
  7. COMMIT 只需等这次事务的 WAL 记录 fsync 落盘；被修改的数据页可以稍后异步刷盘。顺序的 WAL 落了，就不会丢。
  8. 通常指向统计信息失真（过期或缺失，没及时 ANALYZE）。连锁后果：上层节点基于错误的行数估算选了错误的 join——例如以为内表只有 1 行而选 nested loop，实际有几十万行，于是内表被探几十万次，查询慢几个数量级。

应用判别层

  1. 场景 A：先看 pg_stat_user_tables 的 n_dead_tup 和 pg_relation_size，确认是死元组膨胀。普通 VACUUM 不还盘，所以占用不降（§3.1/§3.3）。但真正要查的是为什么死元组没被回收——最可能是有长事务或 idle in transaction 会话持有老快照，xmin horizon 不前进，VACUUM 无法判定这些旧版本"对谁都不可见"（§2.6）。查 pg_stat_activity 里的长事务，杀掉或修复它，再让 autovacuum 跟上；要立刻还盘才用 VACUUM FULL 或 pg_repack。count(*) 变慢是因为它要扫描所有可见行，膨胀让要扫的 page 变多（§5）。
  2. 场景 B：根因是这个索引让 last_seen 的更新无法走 HOT（§2.5），每秒多次更新都要维护索引，写放大 + 索引膨胀。两种取舍：(1) 接受写代价但降低频率——把 last_seen 的更新合并/降采样（比如最多每分钟落一次），让范围查询仍可用 B-tree；(2) 换索引策略——如果这张表按时间近似追加，last_seen 与物理顺序相关，可用 BRIN（§4.5）：索引极小、维护轻，范围查询配 bitmap scan 仍快，代价是精度低于 B-tree。选哪个取决于查询对精度的要求和写入热度。
  3. 场景 C：estimated 1 / actual 800000 是典型的统计失真（§5.2）。规划器以为内表只有 1 行，于是顶层多半选了 nested loop（§5.4），结果内表被探 80 万次，复杂度爆炸。修法：(1) 对相关表跑 ANALYZE 刷新统计，必要时提高 default_statistics_target 或加多列扩展统计；(2) 若估算因列间相关性长期偏差，用扩展统计或调整查询，让规划器改选 hash join（大表等值连接的正解）。核心是先让行数估准，join 选择会跟着对。
  4. 场景 D：index-only scan 要求目标 page 在可见性图里标为 all-visible（§4.7）。刚批量导入的表还没被 VACUUM 扫过，VM 里几乎没有 all-visible 标记，所以每条索引项都得回堆确认可见性——退化成 Index Scan，Heap Fetches 高。让它走 index-only：手动 VACUUM（或等 autovacuum）这张表，把 VM 填上 all-visible 标记（§3.5），之后查询即可免回堆。
  5. 场景 E：① 会变——默认隔离级别从 InnoDB 的 RR 变成 PG 的 RC（§2.3/§6.4），同一事务里重复读到的结果在 PG 默认下会随其它事务提交而变化；要保持原语义得显式设 REPEATABLE READ，且 PG 的 RR 在写冲突时是抛 40001 让你重试，而不是像 InnoDB 那样用间隙锁阻塞。② 必须改写——ON DUPLICATE KEY UPDATE 在 PG 没有，要改成 INSERT ... ON CONFLICT (...) DO UPDATE 并显式指明冲突目标（§6.6）。③ 反而变好——UUID 主键在 InnoDB 因聚簇索引导致页分裂和二级索引变胖（§6.1）；PG 没有聚簇索引、主键不决定行的物理位置，UUID 主键基本不再有这个惩罚。