第 10 章 WAL 与 Checkpoint

「先记账，后挪货」是数据库持久化的不二法门。
这一章我们要把 PostgreSQL 的「日志先行 + 检查点 + 崩溃恢复」三件套讲透——它们决定了 PG 能不能在断电瞬间不丢数据，能不能做主从复制，能不能恢复到任意时间点。

0. 导读：本章你能学到什么

读完本章你应该能够回答：

为什么 PG 修改一行数据要「先写 WAL 再改数据页」？这种顺序能解决什么问题？
LSN 到底是什么？怎么用它衡量主从延迟？
一段 WAL Segment 文件名 000000010000000000000005 是怎么算出来的？
wal_level = minimal / replica / logical 三档究竟差在哪？
synchronous_commit 关掉之后到底「不安全」在哪？
Checkpoint 触发后做了什么？为什么调 checkpoint_completion_target 能让 IO 抖动变小？
数据库崩溃重启时，PG 是怎么从一堆 WAL 文件里恢复出一致状态的？

1. WAL 是什么：先记账，后挪货

1.1 生活类比：仓库管理员的账本

想象你是仓库管理员，老板让你保证一件事：任何时候断电、地震、火灾，账本和库存都对得上。

简单粗暴的做法：每次入库 / 出库都立刻把货搬进出，再去更新库存表。问题是——

搬货是机械动作（IO 慢），万一搬到一半断电，库存和账面就不一致了。
老板想知道某个时刻库存的样子，你也回忆不出来。

更聪明的做法（这就是 Write-Ahead Logging）：

每次有变更，先在账本上顺序记一笔：「今天 14:23，把 5 个 A 放到 3 号货架」（账本是顺序写，超快）。
账本一旦确认写入磁盘，就告诉客户「OK，已成功」。
实际搬货可以攒一堆异步批量做（去 shared_buffers 的脏页里慢慢刷）。
出事重启后，比对账本和实际库存，按账本「重做」一遍没来得及搬的活——库存就一致了。

WAL（Write-Ahead Log） 的核心思想：任何对数据页的修改，必须先把对应的日志记录写入并持久化（fsync）后，才允许修改数据页本身。

1.2 WAL 解决的三大问题

崩溃恢复：服务器突然断电，重启时从最近的 Checkpoint 开始重放 WAL，所有「已提交但还没刷到数据文件」的修改全部 redo。
复制：备库源源不断接收主库的 WAL 流并重放，保持几乎实时一致（流复制）。
PITR（Point-in-Time Recovery）：拿一个基础备份 + 从那时刻起的所有归档 WAL，可以恢复到任何一秒。

📌 与 MySQL 的区别
MySQL（InnoDB） PostgreSQL
持久化日志 Redo Log（物理）+ Binlog（逻辑） WAL 一份扛两个角色
双写缓冲 InnoDB Doublewrite Buffer 防 page tear PG 用 Full Page Image（FPI）：checkpoint 后第一次修改某页，把整页写入 WAL
复制基础 Binlog（逻辑） WAL（物理）+ 逻辑解码

	MySQL（InnoDB）	PostgreSQL
持久化日志	Redo Log（物理）+ Binlog（逻辑）	WAL 一份扛两个角色
双写缓冲	InnoDB Doublewrite Buffer 防 page tear	PG 用 Full Page Image（FPI）：checkpoint 后第一次修改某页，把整页写入 WAL
复制基础	Binlog（逻辑）	WAL（物理）+ 逻辑解码

2. WAL 文件结构与 LSN

2.1 pg_wal/ 目录与 segment

$PGDATA/pg_wal/
├── 000000010000000000000001       ← WAL segment，默认 16MB
├── 000000010000000000000002
├── 000000010000000000000003
├── archive_status/                ← 归档进程标记 .ready / .done
└── ...

文件名是 24 位 16 进制，分成 3 段：

00000001    00000000    00000001
└─时间线 ID─┘└──LSN 高 32 位──┘└──LSN 低 32 位 / 16MB──┘
   (TLI)        (logId)              (segNo)

举例：000000010000000000000005 表示「时间线 1 的第 5 个 16MB 段」，覆盖 LSN 范围 0/05000000 ~ 0/06000000。

📌 PG 13 起改名 pg_wal（之前叫 pg_xlog）。永远不要用 rm 直接删 pg_wal 文件！会让备库挂掉、归档断链、PITR 丢窗口；正确做法是 pg_archivecleanup 或让 checkpoint 自动回收。

2.2 LSN（Log Sequence Number）

LSN = WAL 流的字节偏移，64 位无符号整数，严格单调递增。

显示格式 XX/XXXXXXXX（高 32 位/低 32 位，每段 8 位 16 进制）。例如 1A/3F000028。

核心 API：

sql

-- 当前已写入 WAL 的位置
SELECT pg_current_wal_lsn();
--   pg_current_wal_lsn
-- ----------------------
--  0/16E7A2D0

-- 当前已 flush 到磁盘的位置
SELECT pg_current_wal_flush_lsn();

-- 当前已 insert 到 wal_buffers 的位置（最新）
SELECT pg_current_wal_insert_lsn();

-- 计算两个 LSN 之间相差多少字节（很常用，用来量复制延迟）
SELECT pg_wal_lsn_diff('1A/3F000028', '1A/3F000010');
--  pg_wal_lsn_diff
-- -----------------
--               24

-- 把 LSN 转成 segment 文件名
SELECT pg_walfile_name('0/05000028');
--      pg_walfile_name
-- --------------------------
--  000000010000000000000005

📌 量主从延迟：

sql

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

2.3 WAL 记录的结构

每条 WAL 记录大致长这样：

┌────────────────────────────────────────────┐
│ XLogRecord 头 (24 字节)                    │
│  ├── xl_tot_len   : 整条记录长度           │
│  ├── xl_xid       : 关联事务 ID            │
│  ├── xl_prev      : 前一条记录的 LSN       │
│  ├── xl_info      : 操作类型标志           │
│  ├── xl_rmid      : Resource Manager ID    │
│  └── xl_crc       : CRC32                  │
├────────────────────────────────────────────┤
│ Block References (块引用，可多个)          │
│  └── 描述这条修改了哪些 (rel, fork, blkno) │
├────────────────────────────────────────────┤
│ FPI（Full Page Image，可选）               │
│  └── checkpoint 后该页第一次被改 → 整页备份│
├────────────────────────────────────────────┤
│ Main Data（按 RM 类型不同而不同）          │
└────────────────────────────────────────────┘

Resource Manager（RM） 是不同模块对自己日志的「编解码器」：Heap（堆元组操作）、Btree（索引）、Transaction（提交/回滚）、Standby、XLOG、Storage、SeqRel 等等。pg_waldump 输出的 rmgr 列就是这个。

3. wal_level：决定 WAL 记录什么

sql

SHOW wal_level;
--  wal_level
-- -----------
--  replica  -- PG 10 起的默认值

三档说明：

取值	记录内容	能干什么	不能干什么
`minimal`	仅崩溃恢复必需	单机崩溃恢复	不能流复制、不能 PITR
`replica` (默认)	+ 备库需要的所有信息	物理流复制、PITR、热备查询	逻辑解码
`logical`	+ 行级变更（按 REPLICA IDENTITY）	逻辑复制、CDC、Debezium	（无更高档）

生产几乎都用 replica 或 logical，不要为了「省空间」改 minimal，会瘫痪你的备份与复制。

4. WAL 写入流程：从一次 INSERT 看完整链路

关键参数：

参数	默认	含义
`wal_buffers`	-1 (= shared_buffers/32, 上限 16MB)	wal_buffers 大小
`wal_writer_delay`	200ms	WAL Writer 后台 flush 周期
`wal_writer_flush_after`	1MB	累计多少未 flush 数据触发 flush
`commit_delay`	0 µs	COMMIT 时延迟一会儿，攒一波（组提交）
`commit_siblings`	5	commit_delay 生效的最少并发事务数

5. fsync 与 synchronous_commit：耐久性 vs 性能

这两个参数经常被混淆，必须一刀切清楚。

5.1 `fsync = on/off`

on（默认，绝对不要关）：每次 commit 把 WAL 落到磁盘前都调用 fsync()，确保内核 Page Cache 落到物理盘。
off：只写到 OS Page Cache 就返回。断电就可能损坏整个数据库（不只是丢最后几条），可能要从备份恢复。
仅适合「批量初始化数据，跑完再开 fsync」这类场景。

5.2 `synchronous_commit`：commit 何时回应客户端

取值	含义	风险
`on` (默认)	等本地 WAL flush+fsync 完，且如果有同步备库还要等远端	最安全
`off`	写 wal_buffers 就返回，不等 flush	崩溃可能丢失 *最后 ≤ 3wal_writer_delay** 的事务，但不会导致数据损坏
`local`	等本地 fsync 完，但不等同步备库	备库未及时跟上，崩溃时本地不丢
`remote_write`	等同步备库 wrote（OS 收下）	备库 OS 崩溃可能丢
`remote_apply`	等同步备库 apply 完（最严格）	延迟最高，读备库强一致

实战建议：

强一致系统（金融）：synchronous_commit = on + synchronous_standby_names 设同步备库。
高吞吐弱一致（埋点日志、消息队列）：synchronous_commit = off，吞吐能涨 5～10 倍。
整库迁移导入数据：临时 SET LOCAL synchronous_commit = off，导入完再开。

📌 fsync = off ≠ synchronous_commit = off · 前者是全局关闭耐久性（危险） · 后者只是事务提交不等 fsync（异步落盘，崩溃只丢最后几条）

6. Checkpoint：把脏页一锅端到磁盘

6.1 什么是 Checkpoint

WAL 一直在写，但数据页脏了不会立刻刷盘。如果一直不刷：

崩溃恢复要重放从「数据库出生」到现在的所有 WAL，会几小时起步。
pg_wal 目录无限膨胀。

Checkpoint = 周期性地「把当前所有脏页都刷到磁盘」并标记一个安全点：「比这个 LSN 老的 WAL 都不再需要」。

6.2 触发条件

条件	参数 / 默认	何时触发
时间到	`checkpoint_timeout = 5min`	距上次 checkpoint 超过此时间
WAL 量到	`max_wal_size = 1GB`	自上次 checkpoint 后写 WAL 超过此值
手动	`CHECKPOINT;`	立即触发
pg_basebackup / shutdown	—	启动备份 / 正常关库时

6.3 一次 Checkpoint 做了什么

关键控制项：checkpoint_completion_target（默认 0.9）
- 表示「希望在两次 checkpoint 间隔的 90% 时间内摊开写完」。
- 调小（比如 0.5）→ checkpoint 集中刷 → IO 瞬时压力大、延迟尖刺
- 调大到 0.9 → 平滑分摊 → 业务 IO 抖动小

6.4 监控 Checkpoint 健康度

sql

SELECT * FROM pg_stat_bgwriter;
-- checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time
-- ------------------+-----------------+-----------------------+----------------------
--               123 |               5 |                234567 |                 4321
-- buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend |

checkpoints_timed vs checkpoints_req：req（被 max_wal_size 撑爆触发）远大于 timed（计时正常触发）说明 max_wal_size 太小、应该调大。
buffers_backend / buffers_checkpoint：如果 backend 自己刷脏页很多，说明 bgwriter / checkpoint 跟不上。

7. 崩溃恢复（Crash Recovery）流程

掉电、kill -9、内核 oops，PG 进程意外死亡。重启时它怎么知道从哪里开始恢复？

7.1 关键判断：Page LSN

每个数据页的 PageHeader 里有 pd_lsn，记录该页最近一次修改对应的 WAL LSN。重放某条 WAL 记录时：

python

if page.pd_lsn < record.lsn:
    apply(record)           # 数据页是旧的，需要 redo
    page.pd_lsn = record.lsn
else:
    skip()                  # 数据页已经包含此修改（之前刷过盘了）

这就是为什么 WAL 是 idempotent（幂等）的——重复重放安全。

7.2 Full Page Image（FPI）的作用

物理写到一半断电会出现「部分写入（torn page）」：8KB 页只刷了前 4KB，后 4KB 还是老内容。光靠 WAL 增量 redo 没法修。

PG 的解法：checkpoint 后某页第一次被修改时，把整页（8KB）写入 WAL——这就是 FPI。重启时直接用整页覆盖，治本。

副作用：FPI 会让 checkpoint 刚结束那段 WAL 暴增。可以打开 wal_compression = on 缓解。

8. WAL 归档与 PITR

8.1 归档（Archiving）

ini

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'
# 或更专业的：
# archive_command = 'pgbackrest --stanza=demo archive-push %p'

每个 WAL 段填满后，PG 调一次 archive_command 把它复制到归档目录。
写完会标记 archive_status/000000010000000000000005.done，否则 .ready 还在。

8.2 PITR 时间点恢复

bash

# 1. 周期性做基础备份
pg_basebackup -h prod -D /backup/base -Fp -X stream -P

# 2. 配合归档 WAL，恢复到任意时间
# 在 PG 14+ 用 postgresql.conf 而非 recovery.conf
restore_command       = 'cp /backup/wal_archive/%f %p'
recovery_target_time  = '2026-04-17 11:30:00'
recovery_target_action = 'promote'
# 然后启动 PG，自动重放 WAL 到指定时刻并提升为可读写

9. pg_waldump 实战：解码 WAL 看每条记录

bash

# 看某个 WAL 段
pg_waldump 000000010000000000000005 | head

# 输出示例：
# rmgr: Heap        len (rec/tot):     85/    85, tx:        752, lsn: 0/05000028, prev 0/05000000, desc: INSERT off 1 flags 0x00, blkref #0: rel 1663/16384/16400 blk 0
# rmgr: Transaction len (rec/tot):     34/    34, tx:        752, lsn: 0/05000080, prev 0/05000028, desc: COMMIT 2026-04-17 11:00:00.123 UTC
# rmgr: Btree       len (rec/tot):     72/    72, tx:        753, lsn: 0/050000A8, prev 0/05000080, desc: INSERT_LEAF off 1, blkref #0: rel 1663/16384/16401 blk 1

读法要点：

rmgr = Resource Manager 类型（Heap / Btree / Transaction / XLOG…）
tx = 事务 ID
lsn / prev = 当前与上一条 LSN
blkref #N = 修改了哪张表（rel = tblspc/db/relfilenode）的第几页

常用标志：

bash

pg_waldump --start=0/05000000 --end=0/06000000 --rmgr=Heap         # 只看堆操作
pg_waldump --xid=752                                                # 只看某事务
pg_waldump --stats                                                  # 各 RM 统计

10. WAL 压缩

wal_compression = on 让 PG 对 FPI 进行压缩（PG 9.5 起，PG 15+ 默认 pglz，可选 lz4、zstd）。

sql

ALTER SYSTEM SET wal_compression = 'lz4';
SELECT pg_reload_conf();

效果：FPI 多的负载（高 OLTP、checkpoint 完一阵子）能省 30%~70% 的 WAL 体积；CPU 开销可忽略（lz4/zstd 极快）。

11. 与 MySQL 的全面对比

维度	MySQL（InnoDB）	PostgreSQL
持久化日志	Redo Log（物理，循环） + Binlog（逻辑，串行）	WAL 一份，承担两个角色
默认刷盘策略	`innodb_flush_log_at_trx_commit = 1` (每次 commit fsync)	`synchronous_commit = on`
关闭刷盘的安全性	`innodb_flush_log_at_trx_commit=0/2` 可丢 1s 事务	`synchronous_commit=off` 可丢 ≤ 3200ms 事务，且不损坏库*
防止 page tear	Doublewrite Buffer（写两次）	Full Page Image 写到 WAL
复制基础	主要靠 Binlog（行 / 语句 / 混合）	主要靠 WAL（物理流复制 / 逻辑解码）
日志压缩	5.7+ binlog `--binlog-transaction-compression`	`wal_compression`（FPI），PG14+ lz4/zstd
检查点	InnoDB Sharp/Fuzzy Checkpoint，由 `innodb_io_capacity` 控速	PG Checkpoint，由 `checkpoint_completion_target` 控速
崩溃恢复	重放 Redo + 应用 Undo	重放 WAL（无 Undo，因为 MVCC 多版本就在堆里）

12. 小结

WAL 核心原则：先写日志（顺序、可 fsync），再改数据页（异步、批量）。
WAL 文件：pg_wal/ 下每段 16MB；文件名 = TLI(8) + LSN高32位(8) + 段号(8)。
LSN 是单调递增的字节偏移，pg_current_wal_lsn() / pg_wal_lsn_diff() 是日常监控的扳手。
wal_level 三档：minimal / replica（默认）/ logical。生产至少 replica。
fsync 与 synchronous_commit 别混淆：前者关掉是数据损坏，后者关掉只是丢最后几条事务。
Checkpoint 触发条件：timeout / max_wal_size / 手动；checkpoint_completion_target 摊开 IO。
崩溃恢复：从 pg_control 拿到最近 checkpoint redo LSN，顺序重放 WAL；FPI 解决 torn page。
PITR = 基础备份 + 归档 WAL + recovery_target_time，可恢复到任意秒。
pg_waldump 是排查 WAL 异常的瑞士军刀。
PG vs MySQL：PG 一份 WAL 抗下 InnoDB Redo + Binlog 两份职责，无 Doublewrite。

🎮 配套演示

用浏览器打开 ./10_wal_checkpoint/demo.html，跟着可视化动画再走一遍本章核心概念。
配套代码在 ./10_wal_checkpoint/code/，每个脚本都可以独立 python xxx.py 运行（脚本会自建临时表 ch10_*，无需额外 init.sql）。

13. 面试高频题（≥ 5 题）

题 1：什么是 WAL？为什么必须「先写日志再改数据页」？

考察点：持久化原理、IO 顺序、ACID 中的 D。

参考答案：

WAL（Write-Ahead Logging）是 PostgreSQL 实现持久性（D）和崩溃恢复的核心机制：任何对数据页的修改，必须先把对应的日志记录顺序写入并 fsync() 到 pg_wal/，事务才被认定为提交，才能修改 / 刷新 shared_buffers 里的数据页。

之所以这么做，根本原因是磁盘 IO 的两个事实：

顺序写极快：HDD 顺序写 100~200 MB/s、SSD 上 GB/s；而随机写慢一两个数量级。WAL 是单一文件追加，是顺序写。
fsync 很贵：保证数据真正落盘的 fsync() 是毫秒量级。如果每次改数据页都 fsync，吞吐会跌穿。

WAL 把昂贵的两步（确认提交 + 持久化）合并到「顺序追加 + 一次 fsync」上，把数据页的随机写攒到 Checkpoint 异步批量做。事务的耐久性靠日志保证，性能靠脏页延迟刷新换取。

带来的另外两个红利：

崩溃恢复：从最近 checkpoint 的 redo LSN 顺序重放 WAL，能把所有「已提交但未刷盘」的修改 redo 出来。
复制 / PITR：备库吃 WAL 即可保持一致；归档 WAL + 基础备份 = 任意时间点恢复。

易错点：很多人误以为「数据要立刻落盘才安全」，其实只要日志落盘，数据页晚点刷甚至崩溃丢失都能 redo 回来——这是 WAL 的精髓。

题 2：LSN 是什么？怎么用它衡量主从复制延迟？

考察点：日志结构、复制监控。

参考答案：

LSN（Log Sequence Number）是 64 位无符号整数，等于 WAL 流的字节偏移量，单调递增。显示格式 XX/XXXXXXXX（高 32 位 / 低 32 位的 16 进制）。

每个数据页在 PageHeader 里也保存了 pd_lsn，记录这一页最近被修改的 WAL 位置；崩溃恢复时根据 page.pd_lsn < record.lsn 决定是否需要 redo。

衡量主从复制延迟：在主库执行：

sql

SELECT
  application_name,
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)    AS sent_lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)   AS write_lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)   AS flush_lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)  AS replay_lag_bytes,
  write_lag, flush_lag, replay_lag                   -- PG10+ 提供时间维度
FROM pg_stat_replication;

四个 LSN 阶段含义：

sent_lsn ：主库 walsender 已发送到此 LSN
write_lsn：备库 walreceiver 已 write 到 OS Page Cache
flush_lsn：备库已 fsync 落盘
replay_lsn：备库 startup 进程已 redo 到此 LSN（即查询能看到的 LSN）

正常情况下四个值贴得很近；如果 replay_lag 持续增大，说明备库 redo 跟不上（常见原因：单线程 redo 撞上备库长查询持锁、或备库磁盘 IO 太慢）。

加分项：可以提到 pg_walfile_name(lsn) 把 LSN 翻译成文件名，配合 pg_replication_slots.restart_lsn 排查复制槽是否积压 WAL。

题 3：`fsync = off` 和 `synchronous_commit = off` 有什么区别？哪个能用？

考察点：耐久性参数、数据安全。

参考答案：

两者关掉的效果天差地别：

fsync = off（绝对不要在生产关）：

关闭 PG 在 commit、checkpoint 时调用 fsync() 把 OS Page Cache 强制落盘。
后果：操作系统宕机或断电时，WAL 和数据文件本身都可能损坏——可能整个数据库文件结构都坏掉，需要从备份恢复，不只是丢最后几条。
仅在「初始化导入大量数据，跑完立刻开 fsync 并 checkpoint」这种一次性场景使用。

synchronous_commit = off（生产高吞吐场景常用）：

COMMIT 时只把 WAL 写到 wal_buffers / OS Page Cache 就返回，不等 fsync。
后台 WAL Writer 进程异步周期性 flush（最多 3 * wal_writer_delay ≈ 600ms）。
后果：进程 crash 不丢；OS 崩溃 / 断电最多丢失最后约 600ms 内已返回成功的事务，但数据库结构是一致的，绝对不会损坏。
业务上能丢最近几条但不能容忍数据损坏的场景（埋点、消息、统计、缓存型副本），开它能换 5~10 倍吞吐。

更精细的：

local：等本地 fsync 但不等同步备库（默认 on 且未设同步备库时等价）。
remote_write：等同步备库 OS write 完。
remote_apply：等同步备库 redo 完，备库读强一致（最严格，延迟最高）。

易错点：

把两个混为一谈，以为关 synchronous_commit 同样会损坏库——错。
在多备库场景设 synchronous_commit = on 但忘了配 synchronous_standby_names，结果还是退化为本地 fsync。
在长事务中执行 SET LOCAL synchronous_commit = off 是合法且推荐的「单事务异步提交」用法。

题 4：Checkpoint 触发条件有哪些？为什么 `checkpoint_completion_target` 设成 0.9 比 0.5 好？

考察点：检查点机制、参数调优、IO 抖动。

参考答案：

触发条件（任一即触发）：

时间到：上次 checkpoint 之后超过 checkpoint_timeout（默认 5min）。
WAL 量到：自上次 checkpoint 以来新写入的 WAL 超过 max_wal_size（默认 1GB）。
手动：CHECKPOINT; 命令或 pg_basebackup、pg_ctl stop -m fast。
数据库正常关闭 / 启动备份。

Checkpoint 做的事：

把当前 shared_buffers 里所有 dirty page 异步写到数据文件；
对涉及到的所有数据文件 fsync()；
在 WAL 里写一条 CHECKPOINT 记录（含 redo 起点 LSN）；
更新 pg_control 的最近 checkpoint LSN，老的 WAL 段（不再被复制 / 归档需要的）可回收。

checkpoint_completion_target 的意义：

它是一个 0~1 的比例，告诉 PG「希望在两次 checkpoint 间隔的多少比例时间内摊开写完所有脏页」。

设成 0.5（旧默认）：脏页要在 5min × 0.5 = 2.5min 内全部写完，剩下 2.5min 等下次。集中刷 → IO 尖刺明显，业务查询延迟突然飙升。
设成 0.9（PG14+ 新默认）：脏页摊在 4.5min 内慢慢写，IO 速率更平滑，不与业务争 IO 带宽。代价是 checkpoint 拖得久，崩溃恢复要 redo 的 WAL 略多。

调参实战：

通过 pg_stat_bgwriter 看 checkpoints_req（被 max_wal_size 撑爆触发）远大于 checkpoints_timed 时，说明 max_wal_size 太小，应该调大（比如 4GB、8GB），让 checkpoint 主要靠 timeout 触发。
buffers_backend / buffers_alloc 比例高说明后台进程跟不上脏页生成速度，要增大 bgwriter_lru_maxpages、bgwriter_lru_multiplier。

题 5：详细描述 PostgreSQL 的崩溃恢复流程，FPI 的作用是什么？

考察点：崩溃恢复、torn page、原子性。

参考答案：

崩溃恢复完整流程：

读取 pg_control（位于 global/pg_control），获取最近一次 checkpoint 的 redo 起点 LSN（不是 checkpoint 自己写入的 LSN，而是它开始时记录的「之前所有日志已生效」的 LSN）。
从 redo LSN 开始顺序读 pg_wal/ 中的 WAL 段，逐条 XLogRecord 处理。
对每条记录的每个 BlockReference：
- 读取对应数据页的 pd_lsn；
- 若 page.pd_lsn < record.lsn：执行 redo() 把变更应用到页上，并把 pd_lsn 更新为 record.lsn；
- 若 page.pd_lsn >= record.lsn：跳过（已经包含此修改）。
持续重放到 WAL 末尾（或 PITR 指定的目标 LSN/时间）。
如果是主库：开放连接、清空 backend 共享内存中的临时事务状态、回滚未提交事务（其实不需要，PG 用 MVCC，未提交事务的元组天然对其他事务不可见）。
如果是备库：进入持续 streaming / 应用归档 WAL 的循环（hot standby）。

FPI（Full Page Image）的作用：解决 partial write / torn page 问题。

物理硬件的写单位通常是 4KB（扇区 / 页），而 PG 一页是 8KB。8KB 页写到一半断电，磁盘上可能就是「前 4KB 是新内容、后 4KB 是旧内容」——这种页 PG 没法靠 WAL 增量 redo 修复（因为它假设老页是「正确的旧版本」）。

PG 的解法：

每次 checkpoint 之后，对某个页的第一次修改，把整页 8KB 完整地写入 WAL（这就是 FPI）。
崩溃重放时，看到 FPI 直接整页覆盖写回数据文件，无视磁盘上是否半写。
之后这个 checkpoint 周期内的修改，只用增量记录。

副作用与缓解：

FPI 让 checkpoint 刚结束那段 WAL 体积暴涨（一个高更新场景的 16MB segment 可能要写好几个）。
缓解：开 wal_compression（PG 14+ 支持 lz4 / zstd），FPI 压缩后体积减半。
如果文件系统 / 存储能保证 8KB 原子写（如 ZFS recordsize=8K），可以 full_page_writes = off，但几乎没人这么做，风险太大。

题 6：PostgreSQL 一份 WAL 怎么同时承担「Redo + Binlog」两个角色？跟 MySQL 比有什么优势 / 劣势？

考察点：架构对比、复制原理、双写问题。

参考答案：

MySQL InnoDB 的双日志：

Redo Log：物理日志，记录页面级修改，循环写、固定大小，只用于崩溃恢复（页内偏移 + 数据）。
Binlog：逻辑日志，记录 SQL 或行变更，串行追加，用于复制 / PITR。
双写缓冲（Doublewrite Buffer）：因为 InnoDB 页 16KB 而 OS 页 4KB，要先把脏页写到一段连续的 doublewrite 区域，fsync 后再写到真实位置，防 torn page。代价是写放大 2 倍。
主从一致性靠 Binlog；崩溃一致性靠 Redo + Undo + Binlog 三方协调（XA-like 提交）。

PostgreSQL 的单日志（WAL）：

物理 + 增量描述同时具备，既能 redo 又能给备库 / 逻辑解码。
崩溃恢复：直接重放 WAL；
物理复制：备库吃同一份 WAL；
逻辑复制：从 WAL 通过逻辑解码插件（pgoutput）解码出行变更；
防 torn page：用 FPI（checkpoint 后第一次写整页到 WAL），不用 doublewrite。

优势：

架构简洁：一份日志一种语义，复制和恢复链路一致，不会出现「Binlog 写了 Redo 没写」的边界 case（MySQL 早期常见问题，靠两阶段提交解决）。
写路径短：没有 doublewrite 的 2 倍写放大；FPI 只在 checkpoint 后第一次修改才写整页，平均开销远小于 2x。
物理复制效率高：备库直接 redo 物理日志，比 MySQL 解析 binlog 重放快得多。

劣势 / 难点：

跨版本物理复制不行：物理复制要求主备 PG 大版本完全一致，不能在线升级（要靠逻辑复制或 pg_upgrade）。
WAL 体积大：包含 FPI、物理修改细节，比 binlog 行格式大不少。归档存储要预留更多容量。
逻辑复制晚熟：直到 PG10 才有原生逻辑复制；早期跨版本同步要靠 Slony、Bucardo 等外部工具。
复制槽（Replication Slot）需要谨慎：未消费的 slot 会导致 pg_wal 不能回收 → 容易把磁盘撑爆。

加分项：可以提到 PG 的逻辑复制底层叫 logical decoding，从 WAL 解码出 (xid, table, op, old_row, new_row) 流；上层封装出 publication / subscription 才方便用。这就是 Debezium / Maxwell 等 CDC 工具能直接吃 PG WAL 的原因。

至此，第 10 章「WAL 与 Checkpoint」结束。它和第 9 章一起，组成了你在面试中讲清「PG 到底怎么把数据安全地存到磁盘」的两根支柱。

🔗 延伸阅读

第 8 章 MVCC 与 VACUUM：xid、CLOG（pg_xact）和 WAL 的关系——freeze 操作必须等 checkpoint 与 WAL 配合才能安全推进。
第 14 章备份与恢复：基础备份 + 归档 WAL 怎么拼出 PITR；本章的 LSN / archive_command 在那里被用满。
第 15 章复制与高可用：流复制就是「主库把自己的 WAL 字节流推给备库」，本章的 sent/write/flush/replay LSN 是排查延迟的标尺。

🎬 可视化演示

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

💻 示例代码

python

"""
01_lsn_walk.py
--------------
观察每次 INSERT 之后 LSN 是怎么递增的，以及每行平均产生多少 WAL。

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

运行：
    python 01_lsn_walk.py
"""

import psycopg

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


def banner(title: str) -> None:
    print("\n" + "=" * 70)
    print(f"  {title}")
    print("=" * 70)


def lsn_now(cur: psycopg.Cursor) -> str:
    cur.execute("SELECT pg_current_wal_lsn();")
    return cur.fetchone()[0]


def lsn_diff(cur: psycopg.Cursor, a: str, b: str) -> int:
    cur.execute("SELECT pg_wal_lsn_diff(%s, %s);", (a, b))
    return int(cur.fetchone()[0])


def main() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS ch10_lsn_demo;")
        cur.execute(
            "CREATE TABLE ch10_lsn_demo (id SERIAL PRIMARY KEY, payload TEXT);"
        )

        banner("1. LSN 与 WAL Segment 文件名")
        cur.execute(
            "SELECT pg_current_wal_lsn() AS lsn, pg_walfile_name(pg_current_wal_lsn()) AS file;"
        )
        lsn, fname = cur.fetchone()
        print(f"当前 LSN     = {lsn}")
        print(f"对应文件     = {fname}")
        cur.execute(
            "SELECT pg_current_wal_insert_lsn(), pg_current_wal_flush_lsn();"
        )
        ins, flush = cur.fetchone()
        print(f"insert_lsn   = {ins}  (写入 wal_buffers 的位置)")
        print(f"flush_lsn    = {flush} (已 fsync 到磁盘的位置)")

        banner("2. 每次 INSERT 之后 LSN 增量")
        prev = lsn_now(cur)
        print(f"{'op':<30} {'lsn':<16} {'+bytes':>10}")
        print("-" * 60)
        print(f"{'起点':<30} {prev:<16} {'-':>10}")

        for i in range(1, 6):
            cur.execute(
                "INSERT INTO ch10_lsn_demo (payload) VALUES (%s);",
                (f"row-{i:04d}",),
            )
            now = lsn_now(cur)
            delta = lsn_diff(cur, now, prev)
            print(f"{'INSERT row-' + str(i):<30} {now:<16} {delta:>10}")
            prev = now

        banner("3. 一次性插入 1000 行的 WAL 总量")
        before = lsn_now(cur)
        cur.execute(
            "INSERT INTO ch10_lsn_demo (payload) "
            "SELECT 'bulk-' || g FROM generate_series(1, 1000) g;"
        )
        after = lsn_now(cur)
        total = lsn_diff(cur, after, before)
        print(f"插入前 LSN   = {before}")
        print(f"插入后 LSN   = {after}")
        print(f"总写入 WAL   = {total} 字节  ≈ {total/1024:.1f} KB")
        print(f"平均每行     ≈ {total/1000:.1f} 字节")
        print(
            "\n注意：FPI 会让某些行特别贵——尤其是 checkpoint 之后第一次写到的页。"
        )

        banner("4. 不同操作对 WAL 的开销")
        cases = [
            ("INSERT 100 rows",
             "INSERT INTO ch10_lsn_demo (payload) SELECT 'x' FROM generate_series(1,100);"),
            ("UPDATE 100 rows (WAL 含老元组的死指针 + 新元组)",
             "UPDATE ch10_lsn_demo SET payload = 'y' WHERE id <= 100;"),
            ("DELETE 100 rows (只标 t_xmax，不实际删)",
             "DELETE FROM ch10_lsn_demo WHERE id <= 100;"),
            ("CREATE INDEX (大量元组要写)",
             "CREATE INDEX idx_ch10_lsn_demo_payload ON ch10_lsn_demo (payload);"),
            ("DROP INDEX (轻量元数据)",
             "DROP INDEX idx_ch10_lsn_demo_payload;"),
        ]
        for name, sql in cases:
            b = lsn_now(cur)
            cur.execute(sql)
            a = lsn_now(cur)
            d = lsn_diff(cur, a, b)
            print(f"  {name:<55} +{d:>8} B")

        banner("5. WAL Segment 文件 (16MB) 与 LSN 的换算")
        cur.execute(
            """
            SELECT pg_walfile_name('0/00000000') AS f0,
                   pg_walfile_name('0/01000000') AS f1,
                   pg_walfile_name('1/00000000') AS f256,
                   pg_walfile_name('FF/FFFFFFFF') AS f_high;
            """
        )
        f0, f1, f256, fh = cur.fetchone()
        print(f"  LSN 0/00000000 → {f0}")
        print(f"  LSN 0/01000000 → {f1}   (跨过一个 16MB 段)")
        print(f"  LSN 1/00000000 → {f256} (跨过 256 个段)")
        print(f"  LSN FF/FFFFFFFF → {fh}  (基本上是上界)")


if __name__ == "__main__":
    main()

python

"""
02_synchronous_commit.py
------------------------
对比 synchronous_commit = on vs off 的吞吐：
  · on  ：每次 commit 都等 WAL fsync()，安全但慢
  · off ：commit 后立即返回，最多丢最后 ~600ms 事务，但不损坏库

实际差距取决于磁盘 fsync 性能：本地 SSD 可能 3 倍，云盘可能 10 倍。
"""

import time

import psycopg

CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
N = 5000


def banner(title: str) -> None:
    print("\n" + "=" * 70)
    print(f"  {title}")
    print("=" * 70)


def setup() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS ch10_sc_demo;")
        cur.execute(
            "CREATE TABLE ch10_sc_demo (id BIGSERIAL PRIMARY KEY, payload TEXT);"
        )


def run(mode: str) -> float:
    """跑 N 次单行 INSERT 单独提交，返回总耗时秒。"""
    with psycopg.connect(CONN_STR) as conn, conn.cursor() as cur:
        cur.execute(f"SET synchronous_commit = {mode};")
        t0 = time.perf_counter()
        for i in range(N):
            cur.execute(
                "INSERT INTO ch10_sc_demo (payload) VALUES (%s);", (f"row-{i}",)
            )
            conn.commit()
        return time.perf_counter() - t0


def main() -> None:
    setup()

    banner(f"对比 synchronous_commit  ({N} 次单行单独 commit)")

    print("【warm-up...】")
    run("on")  # 预热

    results = {}
    for mode in ("on", "off", "local"):
        try:
            t = run(mode)
            results[mode] = t
            tps = N / t
            print(f"  synchronous_commit = {mode:<6}  耗时 {t:6.2f}s   ≈ {tps:>8.1f} tps")
        except psycopg.Error as e:
            print(f"  synchronous_commit = {mode:<6}  失败：{e}")

    if "on" in results and "off" in results:
        print(f"\n  off 相对 on 提速 ≈ {results['on']/results['off']:.2f}x")

    banner("观察 wal_buffers / WAL Writer 状态")
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        for k in ("synchronous_commit", "fsync", "wal_buffers",
                  "wal_writer_delay", "wal_writer_flush_after",
                  "commit_delay", "commit_siblings"):
            cur.execute("SHOW %s;" % k)
            v = cur.fetchone()[0]
            print(f"  {k:<25} = {v}")

    banner("结论与建议")
    print(
        """
  · synchronous_commit = on  → 强一致；金融、订单核心必须开。
  · synchronous_commit = off → 业务能容忍丢最后 ~600ms 事务时（埋点、消息队列、
    缓存型副本、监控数据），打开能换数倍吞吐，且不会损坏数据库。
  · 单事务级别细粒度控制：BEGIN; SET LOCAL synchronous_commit = off; ...; COMMIT;
  · ⚠ 禁止把 fsync 设为 off！与 synchronous_commit 完全两码事，掉电会损坏库。
"""
    )


if __name__ == "__main__":
    main()

python

"""
03_checkpoint_observe.py
------------------------
观察 Checkpoint 的触发与效果：
  · 写入大量数据制造脏页
  · 通过 pg_stat_bgwriter 看 buffers_checkpoint / checkpoint_*_time 增量
  · 手动触发 CHECKPOINT 并测耗时
  · 看 pg_control 里的最近 checkpoint LSN
"""

import time

import psycopg

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


def banner(title: str) -> None:
    print("\n" + "=" * 70)
    print(f"  {title}")
    print("=" * 70)


def get_bgwriter_stats(cur: psycopg.Cursor) -> dict:
    cur.execute(
        """
        SELECT checkpoints_timed, checkpoints_req,
               checkpoint_write_time, checkpoint_sync_time,
               buffers_checkpoint, buffers_clean,
               buffers_backend, buffers_alloc
        FROM pg_stat_bgwriter;
        """
    )
    cols = [d.name for d in cur.description]
    return dict(zip(cols, cur.fetchone()))


def diff(after: dict, before: dict) -> dict:
    return {k: after[k] - before[k] for k in after}


def main() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS ch10_ckpt_demo;")
        cur.execute("CREATE TABLE ch10_ckpt_demo (id BIGSERIAL PRIMARY KEY, body TEXT);")

        # ---------- 1. 控制相关参数 ----------
        banner("1. Checkpoint 相关配置")
        for k in ("checkpoint_timeout", "max_wal_size", "min_wal_size",
                  "checkpoint_completion_target", "wal_compression",
                  "shared_buffers", "bgwriter_delay"):
            cur.execute("SHOW %s;" % k)
            print(f"  {k:<30} = {cur.fetchone()[0]}")

        # ---------- 2. 看最近 checkpoint 信息 ----------
        banner("2. 最近一次 checkpoint 的元数据 (pg_control)")
        cur.execute("SELECT * FROM pg_control_checkpoint();")
        row = cur.fetchone()
        cols = [d.name for d in cur.description]
        for c, v in zip(cols, row):
            print(f"  {c:<30} = {v}")

        # ---------- 3. 写一波脏数据 ----------
        banner("3. 写入 50000 行制造脏页")
        before = get_bgwriter_stats(cur)
        before_lsn = cur.execute("SELECT pg_current_wal_lsn();").fetchone()[0]
        t0 = time.perf_counter()
        cur.execute(
            "INSERT INTO ch10_ckpt_demo (body) "
            "SELECT repeat(md5(g::text), 4) FROM generate_series(1, 50000) g;"
        )
        elapsed = time.perf_counter() - t0
        after_lsn = cur.execute("SELECT pg_current_wal_lsn();").fetchone()[0]
        wal_size = cur.execute(
            "SELECT pg_wal_lsn_diff(%s, %s);", (after_lsn, before_lsn)
        ).fetchone()[0]
        print(f"  写入耗时 = {elapsed:.2f}s")
        print(f"  生成 WAL = {int(wal_size)/1024/1024:.2f} MB")

        # ---------- 4. 手动 CHECKPOINT ----------
        banner("4. 手动触发 CHECKPOINT 并计时")
        t0 = time.perf_counter()
        cur.execute("CHECKPOINT;")
        ckpt_time = time.perf_counter() - t0
        after = get_bgwriter_stats(cur)
        print(f"  CHECKPOINT 耗时 = {ckpt_time:.3f}s")
        d = diff(after, before)
        print("\n  pg_stat_bgwriter 增量：")
        for k, v in d.items():
            print(f"    {k:<25} +{v}")
        print(
            "\n  解读：\n"
            "  · buffers_checkpoint 增量 = 这次 checkpoint 实际刷了多少 buffer\n"
            "  · checkpoint_write_time / sync_time 单位是毫秒\n"
            "  · 如果 buffers_backend 比 buffers_checkpoint 还多，说明 backend\n"
            "    自己刷脏页太频繁，要调大 bgwriter / max_wal_size"
        )

        # ---------- 5. 比较之前/之后的 checkpoint LSN ----------
        banner("5. 最近 checkpoint LSN 已经前移")
        cur.execute("SELECT * FROM pg_control_checkpoint();")
        row2 = cur.fetchone()
        d2 = dict(zip(cols, row2))
        for c in ("checkpoint_lsn", "redo_lsn", "next_xid", "time"):
            print(f"  {c:<25} = {d2[c]}")
        print(
            "\n  下次崩溃恢复将从 redo_lsn 开始重放。\n"
            "  比这个 LSN 老的 WAL 段（且不被复制 / 归档需要的）可被回收。"
        )

        # ---------- 6. checkpoint 触发统计 ----------
        banner("6. 累计 checkpoint 健康度")
        cur.execute(
            """
            SELECT checkpoints_timed AS by_time,
                   checkpoints_req   AS by_size_or_manual,
                   ROUND(checkpoints_req::numeric /
                         NULLIF(checkpoints_timed + checkpoints_req, 0) * 100, 1)
                                       AS req_ratio_pct
            FROM pg_stat_bgwriter;
            """
        )
        bt, br, ratio = cur.fetchone()
        print(f"  checkpoints_timed (按 timeout) = {bt}")
        print(f"  checkpoints_req   (按 size/手动) = {br}")
        print(f"  req 触发占比 = {ratio}%")
        if ratio is not None and float(ratio) > 30:
            print("  ⚠ req 占比 > 30%：建议调大 max_wal_size 让 timeout 主导。")


if __name__ == "__main__":
    main()

python

"""
04_wal_size_estimate.py
-----------------------
精确测量「批量 INSERT / UPDATE / DELETE / TRUNCATE」分别产生多少 WAL：
  · 用 pg_current_wal_lsn() 在操作前后取 LSN
  · pg_wal_lsn_diff 求字节差
  · 进一步对比开 / 关 wal_compression 时 FPI 的变化
"""

import psycopg

CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
N_ROWS = 10000


def banner(title: str) -> None:
    print("\n" + "=" * 70)
    print(f"  {title}")
    print("=" * 70)


def lsn(cur: psycopg.Cursor) -> str:
    cur.execute("SELECT pg_current_wal_lsn();")
    return cur.fetchone()[0]


def diff(cur: psycopg.Cursor, a: str, b: str) -> int:
    cur.execute("SELECT pg_wal_lsn_diff(%s, %s);", (a, b))
    return int(cur.fetchone()[0])


def measure(cur: psycopg.Cursor, label: str, sql: str) -> int:
    cur.execute("CHECKPOINT;")     # 隔离掉之前的脏页
    before = lsn(cur)
    cur.execute(sql)
    after = lsn(cur)
    bytes_ = diff(cur, after, before)
    print(f"  {label:<40} = {bytes_:>10} B ≈ {bytes_/1024:>7.1f} KB")
    return bytes_


def main() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS ch10_ws_demo;")
        cur.execute(
            "CREATE TABLE ch10_ws_demo (id INT PRIMARY KEY, name TEXT, val INT);"
        )

        banner(f"1. 各种 DML 产生的 WAL 量（每批 {N_ROWS} 行）")
        measure(
            cur, "INSERT (批量)",
            f"INSERT INTO ch10_ws_demo (id, name, val) "
            f"SELECT g, 'name-' || g, g FROM generate_series(1, {N_ROWS}) g;"
        )
        measure(
            cur, "UPDATE 全表（非索引列）",
            "UPDATE ch10_ws_demo SET val = val + 1;"
        )
        measure(
            cur, "UPDATE 全表（被索引列：会触发索引页修改）",
            "UPDATE ch10_ws_demo SET id = id + 100000;"
        )
        # 还原便于后续对比
        cur.execute("UPDATE ch10_ws_demo SET id = id - 100000;")
        measure(
            cur, "DELETE 全表",
            "DELETE FROM ch10_ws_demo;"
        )
        measure(
            cur, "INSERT 重建",
            f"INSERT INTO ch10_ws_demo (id, name, val) "
            f"SELECT g, 'n-' || g, g FROM generate_series(1, {N_ROWS}) g;"
        )
        measure(
            cur, "TRUNCATE（不写元组日志，只 metadata）",
            "TRUNCATE ch10_ws_demo;"
        )
        measure(
            cur, "DROP + CREATE",
            "DROP TABLE ch10_ws_demo;"
            f"CREATE TABLE ch10_ws_demo (id INT PRIMARY KEY, name TEXT, val INT);"
            f"INSERT INTO ch10_ws_demo SELECT g, 'n-' || g, g "
            f"FROM generate_series(1, {N_ROWS}) g;"
        )

        # ---------- 2. wal_compression 对比 ----------
        banner("2. wal_compression 对 FPI 的影响")
        cur.execute("SHOW wal_compression;")
        original = cur.fetchone()[0]
        print(f"  当前 wal_compression = {original}")

        try:
            for setting in ("off", "on"):
                cur.execute(f"SET wal_compression = '{setting}';")
                # 重新填表后立刻 checkpoint，确保接下来的 UPDATE 全是 FPI 写
                cur.execute("CHECKPOINT;")
                size = measure(
                    cur,
                    f"UPDATE 全表（wal_compression = {setting:>3}）",
                    "UPDATE ch10_ws_demo SET val = val + 1;"
                )
            cur.execute(f"SET wal_compression = '{original}';")
        except psycopg.Error as e:
            print(f"  跳过：{e}")

        banner("3. 解读")
        print(
            """
  · INSERT 比 UPDATE 便宜：UPDATE 要同时记录老元组的 t_xmax 修改 + 新元组 + 索引项
  · UPDATE 索引列比改非索引列贵：每个索引都要写日志 + 可能页分裂
  · TRUNCATE 极便宜：只在 WAL 写一条「relfilenode 改为 N」元数据，磁盘上立即换文件
  · checkpoint 后第一次写每页都要 FPI（整页 8KB），所以单笔操作的 WAL 量被显著放大
  · wal_compression 会压缩 FPI，对 OLTP 写密集场景能省 30%~70% 的 WAL 体积
"""
        )


if __name__ == "__main__":
    main()

markdown

# 第 10 章 WAL 与 Checkpoint - 配套代码

本章脚本带你「亲眼看见」WAL 在写：从一次 INSERT 之后 LSN 跳了多少字节，到 fsync 开关对吞吐的影响，到一次 CHECKPOINT 实际刷了几个 buffer。

## 准备工作

1. 本章脚本会自己 `CREATE TABLE ch10_xxx`，**不需要手动跑 init.sql**。
2. 安装依赖：`pip install "psycopg[binary]>=3.1"`。
3. （可选）通过环境变量覆盖默认连接：`export PG_DSN="host=... port=... dbname=... user=..."`。
4. 部分脚本用到 `CHECKPOINT;` 和 `pg_stat_bgwriter`，需要 superuser 权限（默认用 `postgres` 跑就行）。

## 脚本一览（推荐运行顺序）

| 脚本 | 一句话说明 | 关键 PG 特性 |
|------|------------|--------------|
| `01_lsn_walk.py` | 看每次 INSERT 之后 LSN 怎么递增、平均每行多少字节 WAL | `pg_current_wal_lsn` / `pg_wal_lsn_diff` / `pg_walfile_name` |
| `02_synchronous_commit.py` | 对比 `synchronous_commit = on / off / local` 的吞吐 | 异步提交 / WAL Writer |
| `03_checkpoint_observe.py` | 写 5 万行制造脏页 → 手动 CHECKPOINT → 看 `pg_stat_bgwriter` 增量 | checkpoint 触发条件 / `buffers_checkpoint` |
| `04_wal_size_estimate.py` | 量 INSERT/UPDATE/DELETE/TRUNCATE 各产生多少 WAL，对比 `wal_compression` | FPI / `wal_compression` |

运行示例：

```bash
python 01_lsn_walk.py
python 02_synchronous_commit.py
python 03_checkpoint_observe.py
python 04_wal_size_estimate.py

预期输出

01_lsn_walk.py 会打印每次 INSERT 之后 LSN 的字节增量，类似：

op                             lsn               +bytes
------------------------------------------------------------
INSERT row-1                   0/16E7A310               64
INSERT row-2                   0/16E7A350               64
...
总写入 WAL   = 78912 字节  ≈ 77.1 KB
平均每行     ≈ 78.9 字节

02_synchronous_commit.py 会显示 off 模式相对 on 的提速倍数（云盘上常见 5～10x）。

常见报错

connection refused → PG 未启动，检查 pg_isready -h 127.0.0.1
permission denied to execute function pg_current_wal_lsn → 用 superuser 运行；或 GRANT EXECUTE ON FUNCTION pg_current_wal_lsn() TO appuser;
permission denied to execute CHECKPOINT → CHECKPOINT 仅 superuser 可执行
cannot execute CHECKPOINT during recovery → 你连到了备库，请连主库
wal_compression 显示为空 → PG 14 之前只支持 on/off，PG 15+ 才支持 lz4/zstd

01_lsn_walk.py ↗ · 02_synchronous_commit.py ↗ · 03_checkpoint_observe.py ↗ · 04_wal_size_estimate.py ↗ · README.md ↗

第 10 章 WAL 与 Checkpoint ​

0. 导读：本章你能学到什么 ​

1. WAL 是什么：先记账，后挪货 ​

1.1 生活类比：仓库管理员的账本 ​

1.2 WAL 解决的三大问题 ​

2. WAL 文件结构与 LSN ​

2.1 pg_wal/ 目录与 segment ​

2.2 LSN（Log Sequence Number） ​

2.3 WAL 记录的结构 ​

3. wal_level：决定 WAL 记录什么 ​

4. WAL 写入流程：从一次 INSERT 看完整链路 ​

5. fsync 与 synchronous_commit：耐久性 vs 性能 ​

5.1 fsync = on/off ​

5.2 synchronous_commit：commit 何时回应客户端 ​

6. Checkpoint：把脏页一锅端到磁盘 ​

6.1 什么是 Checkpoint ​

6.2 触发条件 ​

6.3 一次 Checkpoint 做了什么 ​

6.4 监控 Checkpoint 健康度 ​

7. 崩溃恢复（Crash Recovery）流程 ​

7.1 关键判断：Page LSN ​

7.2 Full Page Image（FPI）的作用 ​

8. WAL 归档与 PITR ​

8.1 归档（Archiving） ​

8.2 PITR 时间点恢复 ​

9. pg_waldump 实战：解码 WAL 看每条记录 ​

10. WAL 压缩 ​

11. 与 MySQL 的全面对比 ​

12. 小结 ​

🎮 配套演示 ​

13. 面试高频题（≥ 5 题） ​

题 1：什么是 WAL？为什么必须「先写日志再改数据页」？ ​

题 2：LSN 是什么？怎么用它衡量主从复制延迟？ ​

题 3：fsync = off 和 synchronous_commit = off 有什么区别？哪个能用？ ​

题 4：Checkpoint 触发条件有哪些？为什么 checkpoint_completion_target 设成 0.9 比 0.5 好？ ​

题 5：详细描述 PostgreSQL 的崩溃恢复流程，FPI 的作用是什么？ ​

题 6：PostgreSQL 一份 WAL 怎么同时承担「Redo + Binlog」两个角色？跟 MySQL 比有什么优势 / 劣势？ ​

🔗 延伸阅读 ​

🎬 可视化演示 ​

💻 示例代码 ​

预期输出 ​

常见报错 ​

第 10 章 WAL 与 Checkpoint

0. 导读：本章你能学到什么

1. WAL 是什么：先记账，后挪货

1.1 生活类比：仓库管理员的账本

1.2 WAL 解决的三大问题

2. WAL 文件结构与 LSN

2.1 pg_wal/ 目录与 segment

2.2 LSN（Log Sequence Number）

2.3 WAL 记录的结构

3. wal_level：决定 WAL 记录什么

4. WAL 写入流程：从一次 INSERT 看完整链路

5. fsync 与 synchronous_commit：耐久性 vs 性能

5.1 `fsync = on/off`

5.2 `synchronous_commit`：commit 何时回应客户端

6. Checkpoint：把脏页一锅端到磁盘

6.1 什么是 Checkpoint

6.2 触发条件

6.3 一次 Checkpoint 做了什么

6.4 监控 Checkpoint 健康度

7. 崩溃恢复（Crash Recovery）流程

7.1 关键判断：Page LSN

7.2 Full Page Image（FPI）的作用

8. WAL 归档与 PITR

8.1 归档（Archiving）

8.2 PITR 时间点恢复

9. pg_waldump 实战：解码 WAL 看每条记录

10. WAL 压缩

11. 与 MySQL 的全面对比

12. 小结

🎮 配套演示

13. 面试高频题（≥ 5 题）

题 1：什么是 WAL？为什么必须「先写日志再改数据页」？

题 2：LSN 是什么？怎么用它衡量主从复制延迟？

题 3：`fsync = off` 和 `synchronous_commit = off` 有什么区别？哪个能用？

题 4：Checkpoint 触发条件有哪些？为什么 `checkpoint_completion_target` 设成 0.9 比 0.5 好？

题 5：详细描述 PostgreSQL 的崩溃恢复流程，FPI 的作用是什么？

题 6：PostgreSQL 一份 WAL 怎么同时承担「Redo + Binlog」两个角色？跟 MySQL 比有什么优势 / 劣势？

🔗 延伸阅读

🎬 可视化演示

💻 示例代码

预期输出

常见报错