返回文章列表

PostgreSQL WAL、Checkpoint、提交路径与 Crash Recovery

从 WAL Record、LSN、Group Commit、Checkpoint、Full Page Write、Torn Page、复制与 Crash Recovery 出发，理解 PostgreSQL 提交路径和崩溃恢复机制。

第 13 章　WAL、Checkpoint、提交路径与 Crash Recovery

13.1 本章定位

PostgreSQL 的持久性不是通过“每次提交都把所有数据页写回磁盘”实现的，而是通过 Write-Ahead Logging，WAL 实现的。

最重要的认知是：

事务提交成功，通常只要求相关 WAL 已经达到规定的持久化位置；被修改的数据页可以稍后再落盘。

只要在写数据页之前，描述该数据页修改的 WAL 已经可靠持久化，崩溃后 PostgreSQL 就能从最近的 Redo Point 开始重放 WAL，把磁盘上的旧数据页恢复到一致状态。这就是 “Write-Ahead” 的含义。

WAL 将随机的数据页修改转换为更适合存储设备处理的顺序日志写入，并允许多个事务通过 Group Commit 共用一次同步操作。Checkpoint 则控制恢复起点、脏页刷盘节奏、WAL 回收边界及 Full Page Write 的产生节奏。(PostgreSQL)

本章回答以下生产问题：

  • COMMIT 返回时，哪些内容已经持久化，哪些还没有？
  • 为什么一次提交有时会等待 WALSync，有时几乎不等待？
  • Checkpoint 为什么可能推高写延迟和 P99？
  • 为什么 Checkpoint 之后 WAL 会突然增多？
  • full_page_writes 如何应对 Torn Page？
  • wal_compression 压缩的究竟是什么？
  • pg_wal 持续膨胀时，应先检查 Checkpoint、归档还是复制槽？
  • PostgreSQL 18 的异步 I/O 是否会让提交不再需要 WAL Flush？
  • 网络在 COMMIT 期间断开时，应用为什么不能直接重新创建订单？
  • 崩溃恢复为什么主要是 Redo，而不是回滚每个未提交事务？

13.2 可验证学习目标

完成本章后，你应能够：

  1. 区分 WAL Record、WAL Page、WAL Segment 和 LSN。
  2. 画出 Tuple 修改至 COMMIT 返回的完整路径。
  3. 解释 WAL Buffer、WAL Writer、Backend WAL Flush 和 Group Commit 的职责。
  4. 解释 Dirty Buffer、Background Writer 和 Checkpointer 的边界。
  5. 区分 Checkpoint Record、Checkpoint 完成位置和 Redo Point。
  6. 解释 Full Page Write、Torn Page 与 wal_compression 的关系。
  7. 说明 fsync、synchronous_commit、wal_level 对性能、RPO 和功能的影响。
  8. 使用 pg_stat_wal、pg_stat_io、pg_stat_checkpointer 和相关 LSN 函数观察提交及刷盘行为。
  9. 解释 WAL 在物理复制、逻辑复制和 PITR 中扮演的不同角色。
  10. 分析大事务、批量导入和索引构建产生的 WAL。
  11. 在专用测试实例中验证 Crash Recovery 和 Unlogged Table 的行为。
  12. 在 Go 服务中使用 Idempotency Key 处理 Commit 结果不确定问题。

13.3 核心术语

术语准确定义常见误解
WAL Record描述一次数据库状态变更的日志记录，由具体 Resource Manager 解释一个 SQL 对应一个 WAL Record
WAL PageWAL 流内部的分页单位，通常为 8 KiB等同于数据页
WAL Segmentpg_wal 中的 WAL 文件，初始化集群时确定大小，通常为 16 MiBSegment 越小提交越快
LSNLog Sequence Number，WAL 字节流中的逻辑位置事务编号或时间戳
WAL Buffer共享内存中尚未全部写入 WAL 文件的日志缓冲区WAL Buffer 越大越不需要刷盘
WAL Writer周期性将 WAL Buffer 中的内容写出，并按策略同步负责所有提交同步
Backend WAL Flush提交后端发现所需 WAL 尚未持久化时，自己推进写入及同步后端永远不参与 WAL I/O
Group Commit多个并发事务共享一次 WAL 写入或同步操作将多个事务合并成一个事务
Commit Record标识事务成功提交的 WAL 记录每个只读事务也产生 Commit Record
Dirty BufferShared Buffers 中已修改、但对应版本尚未写入数据文件的缓冲页提交前必须全部刷盘
Background Writer提前写出部分脏缓冲，提高后续缓冲区复用概率负责创建 Checkpoint
Checkpointer执行 Checkpoint，写出检查点范围内的脏页并建立新的恢复边界只负责写一个 WAL 标记
Checkpoint建立可供恢复使用的新一致性边界，并推进脏页持久化相当于备份
Redo Point崩溃恢复必须开始重放 WAL 的最早位置Checkpoint 完成时的当前 LSN
Full Page Write页面在 Checkpoint 后第一次修改时，可能写入整个页面镜像每次 UPDATE 都写完整页面
Torn Page数据页写入仅完成一部分，磁盘上出现新旧内容混合数据库校验和可自动修复
wal_compression压缩 Full Page Image 等适用的完整页面镜像压缩所有普通 WAL 字段
fsync控制 PostgreSQL 是否要求关键数据达到可靠持久化状态关闭后只会损失最后几秒数据
synchronous_commit控制事务何时向客户端报告提交成功与同步复制完全等价
wal_level控制 WAL 中包含的恢复、复制及逻辑解码信息级别越高一定导致数量级 WAL 增长
Unlogged Table不为普通数据变更生成可恢复 WAL 的表永久表的低成本加速选项
TimelineWAL 历史的分支标识，通常在恢复后提升或故障切换时产生新分支每次重启都增加 Timeline
WAL Recycling将不再需要的旧 Segment 重命名并复用等同于删除历史数据
Commit 结果不确定客户端无法确认服务器最终是否提交，而不是数据库内部不知道状态Commit 报错就一定回滚

WAL Segment 通常为 16 MiB、WAL Page 通常为 8 KiB，但 Segment 大小可以在初始化集群时改变，因此监控和运维程序不应把 16 MiB 永久硬编码。(PostgreSQL)

13.4 整体心智模型

PostgreSQL WAL、Checkpoint、提交路径与 Crash Recovery flow 1

13.4.1 数据路径

SQL 修改
  → Tuple 或索引项在 Shared Buffers 中变化
  → 页面成为 Dirty Buffer
  → 对应 WAL Record 进入 WAL Buffers
  → WAL 写入 pg_wal
  → 数据页由 Checkpointer、Background Writer 或 Backend 稍后写出

13.4.2 控制路径

Checkpoint 定时器或 WAL 压力
  → 请求 Checkpoint
  → 确定恢复所需的 Redo Point
  → 分散写出脏页
  → 同步相关文件
  → 更新 Checkpoint 控制信息
  → 更早的 WAL 在满足归档、复制槽等条件后可回收

13.4.3 状态路径

事务进行中
  → 已写普通 WAL
  → 已插入 Commit Record
  → Commit WAL 已写入内核
  → Commit WAL 已持久化
  → 客户端收到成功

客户端只会看到其中的“成功”或“错误”，但超时、连接断开或进程终止可能发生在服务器已经提交、客户端尚未收到成功消息的窗口内。

13.4.4 失败路径

崩溃
  → 内存中的脏页和未持久化 WAL 丢失
  → 从最近可用 Checkpoint 的 Redo Point 开始读取 WAL
  → 重放已持久化的记录
  → 没有 Commit Record 的事务不被视为已提交
  → 恢复到一致状态并开放连接

需要特别说明：本章使用“修改 Tuple → Dirty Buffer → WAL Record”作为概念时间线，但底层实现中页面修改、WAL 构造、WAL 插入和 Page LSN 更新是在临界区内交错进行的。真正不可违反的约束是：

数据页写入持久化存储前，至少要先持久化到该页 Page LSN 为止的 WAL。

13.5 使用方式：配置、函数与监控视图

13.5.1 关键参数

参数主要作用性能影响生产注意事项
wal_level决定 WAL 包含的恢复与复制信息级别提高可能增加 WALminimal 不支持常规 PITR、流复制；逻辑复制需要 logical
fsync要求关键写入可靠持久化影响提交和 Checkpoint 同步成本生产环境不得把关闭它当普通调优
synchronous_commit控制 Commit 返回前等待到哪个持久化阶段直接影响提交延迟与崩溃窗口可按事务设置，但必须有明确业务 RPO
full_page_writesCheckpoint 后首次修改页面时记录页面镜像增加 WAL，尤其在 Checkpoint 后不应为了减少 WAL 随意关闭
wal_compression压缩可压缩的 Full Page Image以 CPU 换 WAL、网络和存储效果依赖页面内容、压缩算法和 CPU 预算
wal_buffersWAL 共享缓冲区大小过小可能出现 wal_buffers_full大并不等于提交无需同步
wal_writer_delayWAL Writer 唤醒周期影响异步提交潜在损失窗口和后台写节奏不应脱离存储与工作负载单独调整
wal_writer_flush_after控制 WAL Writer 刷新节奏影响写合并与脏缓存规模需结合操作系统和设备观察
checkpoint_timeout最长 Checkpoint 间隔过短增加 Checkpoint/FPI，过长增加恢复量不能只追求减少 Checkpoint
max_wal_size基于 WAL 量触发 Checkpoint 的软目标过小可能造成频繁 Checkpoint它不是严格磁盘上限
min_wal_size控制保留用于回收复用的 WAL影响文件创建与磁盘占用应结合正常峰值
checkpoint_completion_target将 Checkpoint 写工作分散到周期中的比例越集中越可能出现写入尖峰需结合 P99、恢复目标与设备能力
checkpoint_flush_after控制 Checkpoint 写回节奏可减少结束阶段的大量写回不同平台支持情况不同
log_checkpoints记录 Checkpoint 起止、写入和同步信息通常开销低排障时非常重要
track_io_timing统计数据 I/O 时间有计时开销先在目标平台测量开销
track_wal_io_timing统计 WAL I/O 时间有计时开销[PG18] 结果主要进入 pg_stat_io
io_method[PG18] 选择同步、worker AIO 或 io_uring 等 I/O 方法影响支持异步预取的数据读取路径不是异步提交开关

wal_level=replica 是常见默认基线；logical 增加逻辑解码所需信息。wal_compression 主要针对完整页面镜像，而不是对整个 WAL 流做通用压缩。synchronous_commit=off 允许服务器在本地 WAL 尚未可靠同步时报告成功，因此主机崩溃可能丢失一小段已经向客户端报告成功的事务。(PostgreSQL)

13.5.2 LSN 函数

SELECT
    pg_current_wal_insert_lsn() AS insert_lsn,
    pg_current_wal_lsn()        AS write_lsn,
    pg_current_wal_flush_lsn()  AS flush_lsn;

含义：

  • pg_current_wal_insert_lsn()：当前 WAL 逻辑插入末端。
  • pg_current_wal_lsn()：当前 WAL 写入位置。
  • pg_current_wal_flush_lsn()：当前已持久化位置。

计算两个位置之间的 WAL 字节数：

SELECT pg_wal_lsn_diff(
    pg_current_wal_insert_lsn(),
    '0/00000000'::pg_lsn
);

记录一段操作产生的 WAL：

SELECT pg_current_wal_insert_lsn() AS start_lsn \gset

-- 被测 SQL

SELECT
    pg_current_wal_insert_lsn() AS end_lsn,
    pg_wal_lsn_diff(
        pg_current_wal_insert_lsn(),
        :'start_lsn'::pg_lsn
    ) AS wal_bytes;

手动切换到新 WAL Segment：

SELECT pg_switch_wal();

pg_switch_wal() 主要用于归档边界和测试；它不是 Checkpoint，不表示所有脏数据页已经落盘，也不应作为常规“强制持久化”接口。(PostgreSQL)

13.6 PostgreSQL 14—18 监控差异

版本主要变化
[PG14]已有 pg_stat_wal，可观察 WAL 记录数、FPI、字节以及当时版本的写入和同步统计
[PG15]恢复期间 Checkpointer、Background Writer 参与工作；恢复预取能力继续增强
[PG16]引入 pg_stat_io，统一观察多类后端与 I/O 上下文
[PG17]引入独立的 pg_stat_checkpointer；部分 Checkpoint 指标不再放在 pg_stat_bgwriter
[PG18]pg_stat_io 增加 WAL 对象和字节维度；WAL 写入、同步次数及耗时从 pg_stat_wal 移至 pg_stat_io，pg_stat_wal 聚焦 WAL Record、FPI、字节和缓冲区耗尽

因此，旧监控 SQL 不应直接复制到 PostgreSQL 18。(PostgreSQL)

13.6.1 [PG18] pg_stat_wal

SELECT
    wal_records,
    wal_fpi,
    wal_bytes,
    wal_buffers_full,
    stats_reset
FROM pg_stat_wal;

关键解释：

  • wal_records：生成的 WAL Record 数量。
  • wal_fpi：Full Page Image 数量。
  • wal_bytes：生成的 WAL 字节数。
  • wal_buffers_full：WAL Buffers 不足而需要主动写出的次数。
  • stats_reset：统计开始时间，比较数据时必须保留。

13.6.2 [PG18] WAL I/O

SELECT
    backend_type,
    object,
    context,
    writes,
    write_bytes,
    write_time,
    fsyncs,
    fsync_time,
    stats_reset
FROM pg_stat_io
WHERE object = 'wal'
ORDER BY backend_type, context;

write_time 和 fsync_time 是否累计，取决于 WAL I/O timing 是否启用。pg_stat_io 观察到的是 PostgreSQL 发起的 I/O，不能单独证明数据是否真正命中物理盘、设备缓存还是操作系统页缓存，因此必须结合系统级磁盘延迟、队列深度和吞吐量。(PostgreSQL)

13.6.3 Checkpointer 与 Background Writer

SELECT
    num_timed,
    num_requested,
    num_done,
    restartpoints_timed,
    restartpoints_requested,
    restartpoints_done,
    write_time,
    sync_time,
    buffers_written,
    slru_written,
    stats_reset
FROM pg_stat_checkpointer;
SELECT
    buffers_clean,
    maxwritten_clean,
    buffers_alloc,
    stats_reset
FROM pg_stat_bgwriter;

解释：

  • num_timed：由时间触发的 Checkpoint。
  • num_requested：由请求或 WAL 压力触发的 Checkpoint。
  • write_time：Checkpointer 分散写脏页所耗时间。
  • sync_time：Checkpoint 结束阶段同步文件所耗时间。
  • buffers_written：Checkpointer 写出的缓冲页。
  • buffers_clean：Background Writer 主动清理的缓冲页。
  • maxwritten_clean：Background Writer 因达到本轮写入上限而停止的次数。
  • buffers_alloc：新分配缓冲区的次数，不等同于内存分配次数。

相关列的准确定义以当前版本视图为准。(PostgreSQL)

13.7 底层原理：从 Tuple 修改到 Commit 返回

13.7.1 第一步：修改 Shared Buffers 中的数据页

执行：

UPDATE account
SET balance = balance - 100
WHERE id = 42;

典型过程包括：

  1. 定位目标 Heap Page。
  2. 将页面读入 Shared Buffers，或者命中已有 Buffer。
  3. 创建新的 Tuple Version。
  4. 更新原 Tuple 的事务可见性相关字段。
  5. 修改相关索引页，或在满足 HOT 条件时避免部分索引修改。
  6. 将被修改的缓冲页标记为 Dirty。
  7. 为页面设置与修改对应的 Page LSN。

这时，数据文件中的页面可能完全没有变化。

13.7.2 第二步：生成 WAL Record

负责 Heap、B-tree、事务状态等不同对象的代码，会生成对应 Resource Manager 的 WAL Record。一个 Record 通常包含：

  • 记录类型。
  • 前一条关联记录位置等头部信息。
  • 事务编号。
  • 被修改块的引用。
  • Redo 所需的差异数据。
  • 必要时的 Full Page Image。
  • 对齐和校验相关信息。

一条 SQL 可能产生大量 WAL Record。例如一次 UPDATE 可能涉及：

  • Heap 新 Tuple。
  • 原 Tuple 头部变化。
  • 一个或多个索引项。
  • Visibility Map 变化。
  • Commit Record。
  • 页面首次修改时的 Full Page Image。

因此不能使用“SQL 数量”推断“WAL Record 数量”。

13.7.3 第三步：进入 WAL Buffers

WAL Record 被插入共享 WAL Buffer。并发后端会协调：

  • 预留 WAL 空间。
  • 拷贝 Record。
  • 处理 WAL Page 边界。
  • 推进插入位置。
  • 在需要时等待 WAL Buffer 映射或写锁。

如果 WAL 生成速度超过后台写出速度，或者缓冲区过小，wal_buffers_full 可能上升。此时后端不得不更频繁地帮助推进 WAL 写出。

13.7.4 第四步：生成 Commit Record

对于真正修改了数据库状态的事务，提交路径需要记录 Commit Record。它表明该事务已成功提交，并可能包含：

  • 提交时间。
  • 事务及子事务信息。
  • 需要失效的缓存消息。
  • 关系删除等提交后动作。
  • 与复制、逻辑解码有关的信息。

纯只读事务通常不需要产生同样的 Commit WAL。

13.7.5 第五步：WAL Flush

在通常的 synchronous_commit=on 下，提交后端需要确保 WAL 至少持久化到本事务 Commit Record 的末端。

概念上：

commit_lsn = 本事务 Commit Record 末端
如果 durable_wal_lsn < commit_lsn:
    推进 WAL 写入
    执行必要的同步
等待 durable_wal_lsn >= commit_lsn
向客户端返回成功

这里的关键不是“只同步本事务的字节”，而是 WAL 是一个全局有序字节流。一旦系统将 WAL 持久化到某个 LSN，位于其之前的其他事务 WAL 也随之持久化。

13.7.6 Backend WAL Flush

WAL Writer 周期性工作，但提交事务不能假定 WAL Writer 恰好已经完成自己所需的持久化。

如果提交时目标 LSN 尚未可靠持久化，Backend 可能：

  1. 获得相关 WAL 写锁。
  2. 将 WAL Buffer 内容写入 WAL 文件。
  3. 调用平台对应的同步操作。
  4. 唤醒等待较早或相同 LSN 的其他后端。

因此，在高提交速率下，可以看到 Client Backend 自己承担 WAL I/O。

13.7.7 Group Commit

假设多个后端几乎同时提交：

T1 需要 flush 到 LSN 100
T2 需要 flush 到 LSN 130
T3 需要 flush 到 LSN 150

若其中一个后端把 WAL 持久化到 150，那么三个事务都满足本地持久化条件。

这就是 Group Commit 的核心收益：

  • 减少同步操作次数。
  • 摊薄固定持久化延迟。
  • 提高高并发短事务吞吐量。

但它也具有边界：

  • 单连接串行提交无法充分形成组。
  • 并发过低时，每个事务仍可能独自同步。
  • 并发无限增加会引发锁竞争、队列膨胀和尾延迟。
  • Group Commit 不会把多个事务的原子性合并。
  • commit_delay 等参数只能在明确观测支持下调整。

13.8 synchronous_commit 的语义

常见设置可以概括为：

设置本地 WAL同步备库等待典型用途
off返回时不保证已本地同步不等待可接受少量已确认事务丢失的派生数据
local等待本地持久化不等待远端同步确认配置了同步复制但特定事务不要求远端确认
remote_write等待本地，并等待同步备库写入其操作系统等待远端写入，不要求远端持久化或重放延迟与远端保障之间的折中
on等待本地，并按同步复制协议等待远端 Flush通常要求同步备库持久化关键 OLTP 常用语义
remote_apply等待本地，并等待同步备库完成重放等待远端可见提交后立即从同步备库读取的特殊需求

必须区分：

  • fsync=off 是集群级地放弃关键持久化保护，崩溃后可能不仅是丢失最近事务，还可能造成不可恢复的数据不一致。
  • synchronous_commit=off 不关闭 WAL，也不停止 WAL Writer；它主要改变“何时向客户端报告成功”。
  • 按事务使用 SET LOCAL synchronous_commit = off，仍然必须经过业务 RPO 评审。
BEGIN;
SET LOCAL synchronous_commit = off;

INSERT INTO disposable_event_buffer(payload)
VALUES ('rebuildable');

COMMIT;

此类设置只适合可从其他权威来源重建的数据，不能因为“它更快”就用于订单、余额或库存最终状态。

13.9 数据页为什么可以稍后写

数据页通常由三类主体写出：

Background Writer

Background Writer 尝试提前把部分脏 Buffer 写出，使后续需要 Buffer 的 Backend 更可能找到干净、可立即复用的页。

它的目标偏向：

  • 平滑缓冲区复用成本。
  • 减少 Backend 被迫写脏页的概率。
  • 降低前台查询的偶发延迟。

它不保证完成一个 Checkpoint，也不负责确定 Redo Point。

Checkpointer

Checkpointer 的目标是：

  • 将 Checkpoint 范围内应持久化的脏页写出。
  • 在完成阶段同步相关文件。
  • 建立新的有效恢复边界。
  • 允许恢复从更晚的 Redo Point 开始。
  • 为旧 WAL 的回收创造必要条件。

Backend

当 Backend 需要复用某个脏 Buffer，且后台进程尚未处理时，Backend 自己可能被迫写出该页。这会把写放大和存储延迟直接带入请求路径。

无论由谁写数据页，都必须遵守 WAL-before-data 规则。PostgreSQL 会确保页面所引用的 WAL 已经先持久化，然后才允许对应数据页达到持久化存储。(PostgreSQL)

13.10 Checkpoint 与 Redo Point

Checkpoint 不是“把当前内存瞬间全部写盘”的单一动作，而是一段具有起点、写出阶段、同步阶段和完成边界的过程。

13.10.1 为什么需要 Checkpoint

如果从集群创建以来的第一条 WAL 开始重放，恢复时间会不断增长。Checkpoint 建立新的恢复基础，使 Crash Recovery 只需要从较新的 Redo Point 开始。

13.10.2 Redo Point 不是 Checkpoint 完成 LSN

假设：

Checkpoint 启动     LSN 1000
Checkpoint 写页中   LSN 1000—5000
Checkpoint 完成     LSN 5200

Redo Point 可能靠近 Checkpoint 启动阶段所确定的位置，而不是简单等于 5200。原因是 Checkpoint 进行期间，事务仍在继续修改页面并生成 WAL。

13.10.3 Checkpoint 触发来源

主要包括：

  • 达到 checkpoint_timeout。
  • WAL 生成接近 max_wal_size 目标。
  • 执行 CHECKPOINT。
  • 干净关闭。
  • 某些管理操作或恢复流程。

max_wal_size 是软目标，不是 pg_wal 目录的绝对上限。归档失败、复制槽滞后、大事务、快速 WAL 生成和恢复需求都可能使实际占用显著超过该值。

13.10.4 Checkpoint 的性能矛盾

Checkpoint 过于频繁：

  • Full Page Image 增多。
  • WAL 放大。
  • Checkpointer 写入增加。
  • 存储队列可能持续偏高。
  • P95/P99 可能恶化。

Checkpoint 间隔过长：

  • Crash Recovery 需要重放更多 WAL。
  • pg_wal 正常工作集可能增大。
  • Checkpoint 单次需要处理的脏页可能更多。
  • RTO 可能恶化。

因此参数选择必须同时考虑：

正常吞吐量
+ 尾延迟
+ 存储能力
+ WAL 归档与复制能力
+ 峰值 WAL 速率
+ Crash Recovery RTO
+ 可接受磁盘空间

不存在对所有系统通用的 max_wal_size、checkpoint_timeout 或 checkpoint_completion_target 常数。

13.11 Full Page Write、Torn Page 与校验和

13.11.1 Torn Page

PostgreSQL 数据页通常为 8 KiB，但底层设备可能以更小单位完成写入。若系统在数据页写入中途崩溃，磁盘页可能出现：

前半部分：新版本
后半部分：旧版本

普通的逻辑差异 WAL 可能假设它面对的是完整旧页，无法可靠地对这种混合页面执行 Redo。

13.11.2 Full Page Write

启用 full_page_writes 时，一个页面在 Checkpoint 后第一次被修改，WAL 通常会包含该页的完整镜像。

崩溃恢复时，如果数据页被撕裂，可以直接用完整页面镜像恢复到一个已知状态，然后继续重放后续增量 WAL。

13.11.3 为什么 Checkpoint 后 WAL 增长

Checkpoint 建立新边界后，页面的“本轮首次修改”状态重新开始。因此，大量活跃页面在 Checkpoint 后首次被触碰时，会产生一波 Full Page Image。

典型现象：

Checkpoint 完成
  → 热点工作集页面陆续首次修改
  → wal_fpi 快速上升
  → wal_bytes/WAL 网络流量增加
  → 同一页面后续修改通常不再重复写 FPI
  → 下一个 Checkpoint 后重新开始

13.11.4 wal_compression

wal_compression 可以降低 Full Page Image 占用，代价是：

  • 主库压缩 CPU。
  • 恢复或备库重放时的解压 CPU。
  • 压缩效果随页面内容变化。
  • 已经高熵或压缩过的数据可能收益有限。

它不会消除普通 Tuple、索引、事务状态等 WAL，也不能解决由不必要索引或低效业务更新造成的逻辑 WAL 放大。

13.11.5 数据校验和不是替代品

数据校验和可以帮助检测页面损坏，但检测不等于恢复。Full Page Write、可靠存储、备份、PITR 和校验和解决的是不同层面的问题。

13.12 Crash Recovery

13.12.1 恢复流程

非正常关闭后，Startup Process 大体执行：

  1. 读取 pg_control。
  2. 找到最近有效 Checkpoint 及其 Redo Point。
  3. 从 Redo Point 开始读取 WAL。
  4. 按 WAL Record 的 Resource Manager 重放变更。
  5. 恢复事务状态、数据页、索引页和必要的元数据。
  6. 到达一致性位置。
  7. 完成恢复并允许普通连接。

PostgreSQL 的 Crash Recovery 以 Redo 为核心。未完成事务不需要逐条执行反向 SQL；没有可靠 Commit Record 的事务不会成为已提交事务，其遗留 Tuple 后续可由 Vacuum 清理。(PostgreSQL)

13.12.2 Redo 的幂等性

Redo 逻辑会结合页面 LSN 判断某条记录是否已经反映在页面中：

如果 page_lsn >= record_lsn:
    通常无需再次应用
否则:
    应用该 WAL Record
    更新页面状态

这使恢复过程能够安全处理“数据页已经写出，但 Checkpoint 尚未完成”等中间状态。

13.12.3 Timeline

Timeline 表示 WAL 历史分支。

典型产生场景：

  • PITR 恢复到历史时间点后继续写入。
  • Standby 被提升为新 Primary。
  • 故障切换后产生新的 WAL 历史。

普通 Crash Recovery 后继续原主库历史，通常不会仅因一次崩溃重启就创建新 Timeline。

故障切换后，旧 Primary 不能未经隔离就重新接受写入，否则可能形成两个不同 Timeline 上的双主写入，即 Split Brain。

13.12.4 Unlogged Table

Unlogged Table 的普通数据修改不进入可用于崩溃恢复的 WAL 路径，因此：

  • 写入开销通常较低。
  • 非正常关闭后表会被重置为空。
  • 内容不能通过物理流复制正常复制到 Standby。
  • 其索引同样是 Unlogged。
  • 不适合权威业务数据。
CREATE UNLOGGED TABLE transient_import_buffer (
    id      bigint,
    payload jsonb
);

适用场景包括可重建的导入中间区、临时计算缓存等，但前提是业务确实接受崩溃后全部丢失。(PostgreSQL)

13.13 WAL 生命周期与回收

WAL Segment 可能处于以下状态：

正在写入
  → 已完成但仍被恢复、复制或归档需要
  → 已归档并不再被任何槽需要
  → 删除或回收复用

13.13.1 WAL Recycling

PostgreSQL 常常不立即删除旧 Segment，而是将其重命名后复用，减少反复创建文件的成本。

13.13.2 阻止回收的常见因素

  • 归档命令持续失败。
  • 物理复制槽的 restart_lsn 长期不前进。
  • 逻辑复制槽未被消费。
  • Standby 长时间断开但槽仍保留。
  • wal_keep_size 等保留需求。
  • 正在运行的备份。
  • 快速 WAL 生成导致回收速度跟不上。
  • Checkpoint 尚未推进到允许回收的位置。

检查复制槽：

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status,
    safe_wal_size,
    pg_size_pretty(
        pg_wal_lsn_diff(
            pg_current_wal_lsn(),
            restart_lsn
        )
    ) AS retained_wal
FROM pg_replication_slots
ORDER BY retained_wal DESC NULLS LAST;

检查归档：

SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time,
    stats_reset
FROM pg_stat_archiver;

删除复制槽会放弃该槽对应消费者的恢复或解码位置，不能只因为磁盘告警就直接删除。

13.14 WAL 与复制、PITR

使用方式消费的内容主要目标典型限制
Crash Recovery本地 WAL恢复本实例一致性只能恢复到本地可用 WAL 末端
物理流复制WAL 中的物理变化构建块级一致的 Standby主备版本和物理结构限制较强
逻辑复制从 WAL 解码出的逻辑变化表级发布、跨版本迁移、数据集成DDL、序列、冲突处理需单独设计
WAL 归档与 PITR连续归档 WAL + Base Backup恢复到指定时间或 LSN依赖归档连续性和恢复演练
同步复制Commit 等待指定 Standby 状态降低故障切换 RPO增加提交延迟和可用性耦合

wal_level 决定 WAL 是否包含满足这些能力所需的信息。PITR 不能只靠 WAL 文件，必须有一个可用 Base Backup 作为恢复起点。

13.15 PostgreSQL 18 AIO 与 WAL/Data I/O 的职责边界

[PG18] 引入新的异步 I/O 基础设施及 io_method 配置，可使用 worker 模式，并在支持的平台上使用 io_uring。当前异步能力重点用于适合预取的关系数据读取路径，例如顺序扫描、Bitmap Heap Scan 和 Vacuum 等。系统还提供 pg_aios 用于观察异步 I/O。(PostgreSQL)

必须避免以下错误推论：

开启 io_method=io_uring
    ≠ COMMIT 不再等待 WAL
    ≠ WAL Flush 变成可忽略
    ≠ fsync 可以关闭
    ≠ synchronous_commit 自动变成异步

职责边界：

路径PG18 AIO 的主要作用
顺序扫描数据页可通过异步预取提高 I/O 并行性
Bitmap Heap Scan可提前提交数据页读取
Vacuum 读取可提高扫描阶段 I/O 重叠
WAL Record 插入仍受 WAL Buffer、锁和 WAL 顺序约束
Commit WAL Flush仍需按 synchronous_commit 和同步复制策略满足持久性
WAL-before-data不改变
Checkpoint 数据页写出仍受 Checkpointer、写回和同步机制约束
Crash Recovery仍依赖 Checkpoint、Redo Point 与 WAL 重放

异步 I/O 可以改变部分数据读取的并行方式，但不会改变数据库的持久性协议。

13.16 大事务和索引构建的 WAL 特征

13.16.1 大事务

大事务可能造成：

  • 持续大量 WAL 生成。
  • 复制与归档突发压力。
  • WAL Segment 快速增长。
  • 长时间占用事务快照，间接加剧 Vacuum 压力。
  • 提交时需要记录大量子事务或失效信息。
  • 故障重试成本巨大。
  • 逻辑复制解码端需要缓存或流式处理大事务。
  • Commit 结果不确定时，业务影响范围扩大。

但“大事务一定在 Commit 时一次性写出所有 WAL”是错误的。普通变更 WAL 在事务执行过程中已经持续插入和写出；Commit 阶段主要需要保证 Commit Record 及之前所需 WAL 达到要求的持久化位置。

13.16.2 CREATE INDEX

普通 CREATE INDEX 可能：

  • 顺序扫描基础表。
  • 使用大量 CPU 进行排序。
  • 使用临时文件。
  • 生成索引页及其 WAL。
  • 增加 WAL 网络传输和归档量。
  • 影响缓存和存储队列。

CREATE INDEX CONCURRENTLY 降低长时间阻塞写入的风险，但：

  • 需要多阶段扫描和等待。
  • 总工作量可能更高。
  • 执行时间通常更长。
  • 失败后可能留下 INVALID 索引。
  • 仍然会产生 WAL。

13.16.3 WAL 放大

可定义一个业务观察指标：

WAL 放大比 =
    某操作产生的 wal_bytes
    ÷ 实际业务有效载荷字节

它受以下因素影响：

  • 行宽。
  • 索引数量和索引键宽度。
  • HOT UPDATE 命中率。
  • Full Page Image。
  • Checkpoint 频率。
  • wal_compression。
  • TOAST。
  • 页面填充度。
  • 是否反复更新相同页面。
  • 是否使用批量协议。
  • wal_level 和逻辑解码要求。

13.17 场景与选型决策

场景推荐方向性能一致性/HA运维成本
金融余额、订单最终状态fsync=on，关键事务同步提交，配置可验证的 HA/PITR提交延迟较高强持久性目标较高
可重建搜索索引队列可按事务评估 synchronous_commit=off吞吐可能提高崩溃可损失近期确认数据需重建机制
导入中间区可考虑 Unlogged Table减少 WAL崩溃清空、不可正常复制需可重复导入
大批量历史导入分批、控制并发、监控 WAL/归档/槽降低峰值需保持恢复链完整中高
跨版本迁移逻辑复制或导出导入有解码及应用开销表级迁移灵活冲突与 DDL 管理复杂
同版本灾备物理流复制 + WAL 归档主库多网络/WAL 成本低 RPO、可快速切换需演练切换和回切
审计事件表可靠 WAL + 备份/PITR，必要时同步复制较高写成本强恢复要求高
高吞吐遥测原始缓存分区、批量写；可重建时才考虑 Unlogged吞吐优先接受部分丢失需清晰数据等级

13.18 高性能分析

13.18.1 观测指标

至少同时收集：

  • TPS。
  • 事务 P50、P95、P99。
  • wal_bytes/s。
  • wal_records/s。
  • wal_fpi/s。
  • wal_buffers_full/s。
  • WAL writes/s、write_bytes/s、fsyncs/s。
  • WAL write_time 和 fsync_time。
  • Checkpoint 次数、write_time、sync_time。
  • Checkpointer 与 Background Writer 写页数量。
  • 前台 Backend 写页。
  • 存储吞吐、延迟、队列深度和利用率。
  • CPU user/system/iowait。
  • 物理复制发送、Flush、Replay 延迟。
  • 归档失败和归档积压。
  • 复制槽保留 WAL 字节数。
  • pg_wal 实际目录占用。

单独看到 wal_bytes 上升，不能判定系统有问题；它可能只是吞吐增长。必须换算为：

每事务 WAL 字节
每业务行 WAL 字节
每有效载荷 WAL 字节
每秒 WAL 字节
每次 Checkpoint 后的 FPI 比例

13.18.2 常见性能瓶颈

WAL 同步延迟

特征：

  • P99 与 WAL fsync_time 同方向上升。
  • 等待事件集中于 WAL 同步。
  • 单事务延迟接近设备持久化延迟。
  • TPS 提升后 Group Commit 先带来吞吐收益，继续提高并发后尾延迟恶化。

方向：

  • 验证存储是否提供真实持久化保证。
  • 检查虚拟化、云盘突发额度和写缓存策略。
  • 使用批量事务降低提交次数，但控制事务大小。
  • 检查同步复制网络与 Standby Flush。
  • 不通过关闭 fsync 掩盖问题。

WAL Buffers 压力

特征：

  • wal_buffers_full 快速增长。
  • 大量并发写或大批量操作。
  • Client Backend 参与 WAL 写出增多。

方向：

  • 判断是否确实为 Buffer 约束，而非设备太慢。
  • 检查 WAL 生成峰值及后台写出能力。
  • 调整前先建立相同负载基线。
  • 采用准入控制降低瞬时并发。

Checkpoint 写入尖峰

特征：

  • Checkpoint 日志显示单次写页和同步量很高。
  • P99 在 Checkpoint 窗口升高。
  • 存储队列深度上升。
  • num_requested 频繁增长。
  • Checkpoint 后 wal_fpi 增长明显。

方向：

  • 检查 max_wal_size 是否与实际 WAL 速率匹配。
  • 检查 Checkpoint 是否能均匀推进。
  • 区分写阶段慢和同步阶段慢。
  • 调整前评估 RTO 和磁盘空间。

WAL 放大

特征：

  • 业务行数稳定，但 wal_bytes/row 增长。
  • 新增索引后明显变化。
  • Checkpoint 频率升高。
  • wal_fpi/wal_records 比例升高。
  • UPDATE 从 HOT 退化为非 HOT。

方向：

  • 删除无价值索引。
  • 缩小更新列集合。
  • 优化表填充度和 HOT 条件。
  • 评估 wal_compression。
  • 减少不必要的全量更新。
  • 控制 Checkpoint 频率。

13.19 高并发分析

13.19.1 提交并发不是越高越好

提高并发最初可能增加 Group Commit 密度，但超过系统处理能力后会出现：

  • WAL Insert 竞争。
  • WAL Buffer 映射竞争。
  • WAL Write Lock 竞争。
  • WAL Sync 队列。
  • 存储排队。
  • 同步复制确认排队。
  • 连接池等待。
  • CPU 调度和上下文切换。
  • 应用超时与重试风暴。

因此应区分：

数据库 max_connections
应用连接池大小
业务并发数
Goroutine 数量
同时在执行事务的数量
等待准入的请求数量

它们不是同一个参数。

13.19.2 背压与准入控制

高峰时更安全的结构：

请求入口
  → 有界业务队列
  → 有界并发执行
  → 有界数据库连接池
  → 数据库

而不是：

无限请求
  → 每请求一个 Goroutine
  → 无限等待连接
  → 超时
  → 全量重试
  → 更严重过载

重试必须：

  • 仅针对可重试 SQLSTATE。
  • 重试整个事务。
  • 有最大次数。
  • 指数退避。
  • 加随机抖动。
  • 遵守 Context 截止时间。
  • 经过并发门控。
  • 对有外部业务效果的操作使用 Idempotency Key。

13.20 高可用分析

13.20.1 RPO 与提交语义

架构Primary 崩溃Primary 主机永久丢失典型 RPO
单机、同步提交本地 Crash Recovery 可恢复已持久化 WAL主机及存储同时丢失则无法恢复取决于存储和备份
异步物理复制本地提交可靠，但 Standby 可能尚未收到故障切换可能丢失复制延迟窗口非零
同步复制 on等待指定 Standby Flush满足配置与法定人数时可接近零目标可为零，但需验证
remote_apply等待 Standby 重放故障切换后数据通常立即可见延迟最高
synchronous_commit=off主机崩溃可能丢失近期已确认事务故障切换风险叠加明确非零

“同步复制已配置”不等于每个提交都一定等待同步备库。还需要检查：

  • synchronous_standby_names。
  • 当前 sync_state。
  • 所需同步备库数量。
  • 事务自身 synchronous_commit。
  • Standby 是否真正处于可用状态。
  • 故障切换流程是否有 Fencing。

13.20.2 切换与回切

故障切换必须处理：

  1. 旧 Primary 隔离。
  2. Standby 提升。
  3. 新 Timeline 建立。
  4. 客户端连接信息更新。
  5. 连接池中旧连接淘汰。
  6. 写入流量恢复。
  7. 未知提交事务对账。
  8. 旧 Primary 通过 pg_rewind 或重新构建加入。
  9. 验证复制和备份链。
  10. 演练回切。

仅修改 DNS 而不清理连接池，可能使旧连接继续指向错误节点。

13.20.3 Commit 结果不确定

考虑以下时间线：

客户端发送 COMMIT
服务器写入 Commit Record
服务器完成 WAL Flush
服务器提交事务
网络连接断开
客户端未收到 CommandComplete

客户端得到的是错误或超时，但事务已经提交。

另一种时间线：

客户端发送 COMMIT
网络在请求到达前断开
服务器未执行 COMMIT
事务最终回滚

客户端看到的现象可能相同。

所以：

传输错误只能说明客户端不知道结果，不能证明事务已回滚。

13.21 三维影响矩阵

机制性能影响HA/一致性影响运维影响
WAL 顺序写降低随机数据页写对提交的影响提供 Crash Recovery 基础需监控 WAL 速率和磁盘
Group Commit提高并发短事务吞吐不改变事务原子性需控制并发和尾延迟
synchronous_commit=off降低部分提交等待可能丢失近期已确认事务需按数据等级隔离
频繁 CheckpointFPI、写放大、P99 可能上升缩短恢复重放范围增加日志与容量压力
较长 Checkpoint 间隔降低部分 FPIRTO 可能恶化需要更多 WAL 空间
wal_compression增加 CPU，降低 WAL I/O不降低持久性需测压验证算法
Unlogged Table显著减少普通 WAL崩溃清空、不可正常复制需可重建流程
同步复制增加网络和提交延迟可降低故障切换 RPO需法定人数和降级策略
逻辑复制增加解码与应用成本提供表级复制能力DDL、冲突、槽积压复杂
PG18 AIO改善部分数据读取并发不改变 Commit 持久性需内核、驱动及指标支持

13.22 实验一：比较不同操作的 WAL 趋势

13.22.1 目标

比较以下操作在相同业务规模下的 WAL 趋势：

  • 单行 INSERT。
  • 多值 INSERT。
  • UPDATE。
  • COPY。
  • CREATE INDEX。

实验不预设固定耗时或固定 WAL 排名。结果必须记录：

  • PostgreSQL 版本。
  • wal_level。
  • full_page_writes。
  • wal_compression。
  • 数据校验和状态。
  • 行宽。
  • 索引数量。
  • 缓存冷热状态。
  • 并发数。
  • 总行数。
  • P50、P95、P99。
  • wal_records、wal_fpi、wal_bytes。
  • CPU、磁盘和 WAL I/O。
  • Checkpoint 是否发生。

13.22.2 安全条件

  • 使用专用测试数据库。
  • 不重置共享统计，除非该实例没有其他测试。
  • 不在生产实例执行高量 CREATE INDEX 或批量更新。
  • DML 的 EXPLAIN ANALYZE 会真正执行语句。

13.22.3 建表

CREATE SCHEMA IF NOT EXISTS ch13;

DROP TABLE IF EXISTS ch13.wal_insert;
DROP TABLE IF EXISTS ch13.wal_update;
DROP TABLE IF EXISTS ch13.wal_index;

CREATE TABLE ch13.wal_insert (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    group_id   integer NOT NULL,
    payload    text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE ch13.wal_update (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    group_id   integer NOT NULL,
    payload    text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp()
) WITH (fillfactor = 90);

CREATE TABLE ch13.wal_index (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    group_id   integer NOT NULL,
    payload    text NOT NULL
);

准备 UPDATE 和索引构建数据：

INSERT INTO ch13.wal_update(group_id, payload)
SELECT
    g % 1000,
    repeat(md5(g::text), 8)
FROM generate_series(1, 100000) AS g;

INSERT INTO ch13.wal_index(group_id, payload)
SELECT
    g % 10000,
    repeat(md5(g::text), 8)
FROM generate_series(1, 500000) AS g;

ANALYZE ch13.wal_update;
ANALYZE ch13.wal_index;

13.22.4 通用基线

在每个被测操作前记录：

SELECT
    version(),
    current_setting('wal_level')           AS wal_level,
    current_setting('full_page_writes')    AS full_page_writes,
    current_setting('wal_compression')     AS wal_compression,
    current_setting('synchronous_commit')  AS synchronous_commit,
    current_setting('shared_buffers')      AS shared_buffers;

SELECT
    wal_records AS before_records,
    wal_fpi     AS before_fpi,
    wal_bytes   AS before_bytes,
    stats_reset
FROM pg_stat_wal;

SELECT pg_current_wal_insert_lsn() AS before_lsn;

在操作后记录：

SELECT pg_current_wal_insert_lsn() AS after_lsn;

SELECT
    wal_records,
    wal_fpi,
    wal_bytes,
    wal_buffers_full,
    stats_reset
FROM pg_stat_wal;

Session B：持续观察

SELECT
    clock_timestamp(),
    wal_records,
    wal_fpi,
    wal_bytes,
    wal_buffers_full
FROM pg_stat_wal;
\watch 1

[PG18] WAL I/O：

SELECT
    clock_timestamp(),
    backend_type,
    writes,
    write_bytes,
    write_time,
    fsyncs,
    fsync_time
FROM pg_stat_io
WHERE object = 'wal'
ORDER BY backend_type;
\watch 1

13.22.5 场景 A：单行 INSERT

single_insert.sql：

\set group_id random(1, 1000)

INSERT INTO ch13.wal_insert(group_id, payload)
VALUES (
    :group_id,
    repeat(md5(random()::text), 8)
);

执行 10,000 个事务：

pgbench "$DATABASE_URL" \
  --client=4 \
  --jobs=4 \
  --transactions=2500 \
  --file=single_insert.sql \
  --log

这里每个事务插入一行，因此 Commit Record 和同步频率是重要成本来源。

13.22.6 场景 B：多值 INSERT

multi_insert.sql：

\set group_id random(1, 1000)

INSERT INTO ch13.wal_insert(group_id, payload)
VALUES
    (:group_id,     repeat(md5(random()::text), 8)),
    (:group_id + 1, repeat(md5(random()::text), 8)),
    (:group_id + 2, repeat(md5(random()::text), 8)),
    (:group_id + 3, repeat(md5(random()::text), 8)),
    (:group_id + 4, repeat(md5(random()::text), 8)),
    (:group_id + 5, repeat(md5(random()::text), 8)),
    (:group_id + 6, repeat(md5(random()::text), 8)),
    (:group_id + 7, repeat(md5(random()::text), 8)),
    (:group_id + 8, repeat(md5(random()::text), 8)),
    (:group_id + 9, repeat(md5(random()::text), 8));

执行 1,000 个事务，同样写入 10,000 行：

pgbench "$DATABASE_URL" \
  --client=4 \
  --jobs=4 \
  --transactions=250 \
  --file=multi_insert.sql \
  --log

重点比较：

  • 总 wal_bytes。
  • 每行业务 WAL。
  • Commit Record 数量趋势。
  • WAL fsyncs。
  • TPS 与 P99。

预期上，多值 INSERT 能摊薄协议、语句和提交成本，但普通 Heap 和索引变化仍需要 WAL。

13.22.7 场景 C：UPDATE

确保没有其他会修改该表的负载，然后：

SELECT pg_current_wal_insert_lsn() AS start_lsn \gset

UPDATE ch13.wal_update
SET
    payload = repeat(md5((id + 1)::text), 8),
    updated_at = clock_timestamp();

SELECT
    pg_wal_lsn_diff(
        pg_current_wal_insert_lsn(),
        :'start_lsn'::pg_lsn
    ) AS update_wal_bytes;

执行计划：

BEGIN;

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
UPDATE ch13.wal_update
SET payload = repeat(md5((id + 2)::text), 8)
WHERE id BETWEEN 1 AND 10000;

ROLLBACK;

虽然事务回滚，EXPLAIN ANALYZE 已经执行 UPDATE 并生成 WAL；序列变化及数据库外部副作用也不一定可回滚。

观察：

  • Heap Fetches 与 Buffer。
  • WAL: records、fpi、bytes。
  • 是否发生 HOT UPDATE。
  • 更新列是否参与索引。
  • Checkpoint 后 FPI 是否显著增加。

13.22.8 场景 D：COPY

生成测试文件：

python3 - <<'PY'
from pathlib import Path

path = Path("/tmp/ch13_copy.csv")
with path.open("w", encoding="utf-8") as f:
    for i in range(1, 10001):
        f.write(f"{i % 1000},{'x' * 256}\n")
print(path)
PY

清空目标表后：

TRUNCATE ch13.wal_insert RESTART IDENTITY;

SELECT pg_current_wal_insert_lsn() AS start_lsn \gset

\copy ch13.wal_insert(group_id, payload) \
FROM '/tmp/ch13_copy.csv' \
WITH (FORMAT csv)

SELECT
    pg_wal_lsn_diff(
        pg_current_wal_insert_lsn(),
        :'start_lsn'::pg_lsn
    ) AS copy_wal_bytes;

COPY 可以降低客户端协议和语句解析成本，但对普通 Logged Table 的持久化修改仍会产生 WAL。

13.22.9 场景 E：CREATE INDEX

DROP INDEX IF EXISTS ch13.wal_index_group_payload_idx;

SELECT pg_current_wal_insert_lsn() AS start_lsn \gset

CREATE INDEX wal_index_group_payload_idx
ON ch13.wal_index(group_id, payload);

SELECT
    pg_wal_lsn_diff(
        pg_current_wal_insert_lsn(),
        :'start_lsn'::pg_lsn
    ) AS create_index_wal_bytes;

另一个 Session 观察：

SELECT
    pid,
    datname,
    relid::regclass,
    index_relid::regclass,
    phase,
    blocks_total,
    blocks_done,
    tuples_total,
    tuples_done
FROM pg_stat_progress_create_index;
\watch 1

13.22.10 延迟分位数

pgbench --log 普通逐事务日志的延迟字段以微秒记录。可用以下脚本计算近似分位数：(PostgreSQL)

python3 - <<'PY'
import glob
import math

values = []

for filename in glob.glob("pgbench_log.*"):
    with open(filename, encoding="utf-8") as f:
        for line in f:
            fields = line.split()
            if len(fields) >= 3:
                values.append(int(fields[2]))

values.sort()

if not values:
    raise SystemExit("没有找到延迟记录")

def percentile(p: float) -> float:
    pos = max(0, min(len(values) - 1, math.ceil(p * len(values)) - 1))
    return values[pos] / 1000.0

print(f"count={len(values)}")
print(f"P50={percentile(0.50):.3f} ms")
print(f"P95={percentile(0.95):.3f} ms")
print(f"P99={percentile(0.99):.3f} ms")
PY

13.22.11 结果表

场景业务行数事务数WAL bytesWAL/行FPIfsyncsTPSP50P95P99
单行 INSERT
多值 INSERT
UPDATE
COPY
CREATE INDEX

结果解释

不要只按总 WAL 排名。应回答：

  • 单行和多值 INSERT 的主要差异是否来自提交次数？
  • UPDATE 是否修改了索引列？
  • UPDATE 是否满足 HOT 条件？
  • 测量期间是否发生 Checkpoint？
  • 哪个场景的 FPI 占比最高？
  • CREATE INDEX 是否使用临时文件？
  • 缓存是冷还是热？
  • WAL I/O 是 WAL Writer 还是 Client Backend 主导？
  • P99 是 WAL 同步、数据 I/O、锁还是 CPU 导致？

13.22.12 清理

DROP SCHEMA ch13 CASCADE;

13.23 实验二：观察 Checkpoint 对 Buffers、WAL 和延迟的影响

13.23.1 目标

比较：

  1. 手动 CHECKPOINT。
  2. 时间触发的自动 Checkpoint。
  3. WAL 压力触发的自动 Checkpoint。

观察：

  • Checkpointer 写页。
  • Background Writer 写页。
  • WAL FPI。
  • WAL 字节。
  • WAL 与数据 I/O。
  • TPS、P95、P99。
  • Checkpoint 日志中的写入和同步时间。

13.23.2 安全要求

  • 只在专用测试实例执行。
  • 修改参数前保存原值。
  • 确认磁盘至少能容纳测试峰值 WAL。
  • 不通过关闭 fsync 或 full_page_writes“简化实验”。

记录原参数：

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN (
    'checkpoint_timeout',
    'checkpoint_completion_target',
    'max_wal_size',
    'min_wal_size',
    'log_checkpoints',
    'track_io_timing',
    'track_wal_io_timing'
)
ORDER BY name;

专用实例可以设置便于观察的实验值：

ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET track_wal_io_timing = on;

-- 以下值仅为小型一次性实验示例，不是生产建议。
ALTER SYSTEM SET checkpoint_timeout = '30min';
ALTER SYSTEM SET max_wal_size = '256MB';

SELECT pg_reload_conf();

确认实际值：

SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name IN (
    'checkpoint_timeout',
    'max_wal_size',
    'log_checkpoints',
    'track_io_timing',
    'track_wal_io_timing'
);

13.23.3 创建写热点

CREATE SCHEMA IF NOT EXISTS ch13;

CREATE TABLE ch13.checkpoint_hot (
    id      bigint PRIMARY KEY,
    value   bigint NOT NULL,
    payload text NOT NULL
) WITH (fillfactor = 90);

INSERT INTO ch13.checkpoint_hot(id, value, payload)
SELECT
    g,
    0,
    repeat(md5(g::text), 8)
FROM generate_series(1, 500000) AS g;

VACUUM (ANALYZE) ch13.checkpoint_hot;

checkpoint_update.sql：

\set id random(1, 500000)

UPDATE ch13.checkpoint_hot
SET
    value = value + 1,
    payload = repeat(md5((value + 1)::text), 8)
WHERE id = :id;

Session A：持续写负载

pgbench "$DATABASE_URL" \
  --client=16 \
  --jobs=8 \
  --time=300 \
  --file=checkpoint_update.sql \
  --log

并发数必须根据测试机容量选择；目标是形成稳定负载，而不是无界压垮实例。

Session B：观察 Checkpointer

SELECT
    clock_timestamp(),
    num_timed,
    num_requested,
    num_done,
    write_time,
    sync_time,
    buffers_written
FROM pg_stat_checkpointer;
\watch 1

Session B：观察 WAL

SELECT
    clock_timestamp(),
    wal_records,
    wal_fpi,
    wal_bytes,
    wal_buffers_full
FROM pg_stat_wal;
\watch 1

Session B：观察 Background Writer

SELECT
    clock_timestamp(),
    buffers_clean,
    maxwritten_clean,
    buffers_alloc
FROM pg_stat_bgwriter;
\watch 1

Session B：[PG18] 观察 I/O

SELECT
    clock_timestamp(),
    backend_type,
    object,
    context,
    writes,
    write_bytes,
    write_time,
    writebacks,
    writeback_time,
    fsyncs,
    fsync_time
FROM pg_stat_io
WHERE object IN ('relation', 'wal')
  AND backend_type IN (
      'client backend',
      'checkpointer',
      'background writer',
      'walwriter'
  )
ORDER BY backend_type, object, context;
\watch 1

Session C：触发手动 Checkpoint

负载稳定后：

SELECT clock_timestamp(), pg_current_wal_insert_lsn();

CHECKPOINT;

SELECT clock_timestamp(), pg_current_wal_insert_lsn();

观察：

  • num_requested 是否增加。
  • Checkpoint 日志中写入时间与同步时间。
  • P99 是否与 Checkpoint 窗口重叠。
  • 数据文件 I/O 是否升高。
  • WAL FPI 是否在 Checkpoint 后加速增长。

13.23.4 验证 FPI 周期

手动 Checkpoint 前记录：

SELECT
    wal_fpi   AS fpi_before_checkpoint,
    wal_bytes AS bytes_before_checkpoint
FROM pg_stat_wal;

执行：

CHECKPOINT;

第一次更新大量不同页面：

UPDATE ch13.checkpoint_hot
SET value = value + 1
WHERE id % 100 = 0;

记录：

SELECT wal_fpi, wal_bytes
FROM pg_stat_wal;

在不执行新 Checkpoint 的情况下，再次更新大致相同页面：

UPDATE ch13.checkpoint_hot
SET value = value + 1
WHERE id % 100 = 0;

再次记录。

通常第一次更新会出现更多本轮首次页面修改对应的 FPI；第二次修改相同页面时，FPI 增量通常下降。但缓存状态、页面分布、Hint Bit 和并发活动都会影响结果。

13.23.5 观察 WAL 压力触发 Checkpoint

保持 checkpoint_timeout 足够长，持续运行写负载，直到日志显示由于 WAL 量触发 Checkpoint。

应记录：

  • Checkpoint 开始与完成时间。
  • 触发类型。
  • WAL 生成速率。
  • num_requested 变化。
  • buffers_written。
  • write_time 与 sync_time。
  • Checkpoint 期间和之后的 P99。
  • wal_fpi 增量。
  • pg_wal 目录占用。

13.23.6 诊断解释

情况一：write_time 高，sync_time 正常

可能说明：

  • 脏页数量很大。
  • 存储持续写吞吐不足。
  • Checkpoint 写工作没有充分分散。
  • 设备队列长期饱和。

情况二：write_time 正常，sync_time 尖峰

可能说明：

  • 大量已写数据集中在同步阶段落盘。
  • 操作系统缓存积累较多。
  • 设备缓存或 Flush 语义延迟较高。
  • 其他进程同时触发写回。

情况三：Checkpoint 后 WAL 增长，但数据写延迟不高

可能是 FPI 周期效应。应检查 wal_fpi，而不是直接认定业务写入量增加。

情况四：num_requested 频繁增加

可能是：

  • max_wal_size 相对 WAL 速率太小。
  • 大量批处理或索引操作。
  • 归档与复制不能直接解释 Checkpoint 次数，但可能同时造成磁盘压力。
  • Checkpoint 实际完成速度跟不上。

13.23.7 清理与恢复参数

DROP SCHEMA ch13 CASCADE;

-- 按实验前保存的值恢复，而不是照抄下列示意。
ALTER SYSTEM RESET checkpoint_timeout;
ALTER SYSTEM RESET max_wal_size;
ALTER SYSTEM RESET log_checkpoints;
ALTER SYSTEM RESET track_io_timing;
ALTER SYSTEM RESET track_wal_io_timing;

SELECT pg_reload_conf();

13.24 实验三：非正常关闭与 Crash Recovery

13.24.1 目标

验证：

  • 已同步提交的 Logged Table 数据可通过 Crash Recovery 保留。
  • 未提交事务不会在恢复后成为已提交数据。
  • Unlogged Table 在非正常关闭后被重置。
  • 恢复日志能够显示 Redo 起止过程。
  • 普通 Crash Recovery 不必然创建新 Timeline。

13.24.2 最高级别安全限制

本实验只能运行在：

  • 本地一次性实例。
  • CI 临时实例。
  • 明确隔离的实验虚拟机。
  • 不包含任何业务数据的专用集群。

执行前检查：

echo "$PGDATA"
pg_ctl -D "$PGDATA" status

不得对生产实例、共享测试实例、托管数据库或不明确的数据目录执行 stop -m immediate。

13.24.3 创建临时集群

示例端口为 55432：

export CH13_PGDATA=/tmp/pg-ch13-crash
export CH13_LOG=/tmp/pg-ch13-crash.log
export CH13_PORT=55432

rm -rf "$CH13_PGDATA"

initdb -D "$CH13_PGDATA"

pg_ctl \
  -D "$CH13_PGDATA" \
  -l "$CH13_LOG" \
  -o "-p $CH13_PORT" \
  start

createdb -p "$CH13_PORT" ch13_crash

13.24.4 Session A：提交数据

CREATE TABLE logged_orders (
    id      bigint PRIMARY KEY,
    note    text NOT NULL
);

CREATE UNLOGGED TABLE unlogged_cache (
    id      bigint PRIMARY KEY,
    note    text NOT NULL
);

INSERT INTO logged_orders
VALUES (1, 'committed before crash');

INSERT INTO unlogged_cache
VALUES (1, 'will be reset after crash');

SELECT
    pg_current_wal_insert_lsn(),
    pg_current_wal_flush_lsn();

确认：

SELECT * FROM logged_orders;
SELECT * FROM unlogged_cache;

13.24.5 Session B：保持未提交事务

BEGIN;

INSERT INTO logged_orders
VALUES (2, 'not committed');

SELECT pg_backend_pid();

-- 保持事务，不要 COMMIT。

13.24.6 Session C：记录控制信息

pg_controldata "$CH13_PGDATA" > /tmp/ch13-control-before.txt
tail -n 100 "$CH13_LOG"

13.24.7 模拟非正常关闭

pg_ctl \
  -D "$CH13_PGDATA" \
  stop \
  -m immediate

Immediate 模式不执行正常 Checkpoint，下一次启动需要 Crash Recovery。

重新启动：

pg_ctl \
  -D "$CH13_PGDATA" \
  -l "$CH13_LOG" \
  -o "-p $CH13_PORT" \
  start

13.24.8 验证结果

SELECT * FROM logged_orders ORDER BY id;

预期只有：

1 | committed before crash

未提交的 id=2 不应出现。

验证 Unlogged Table：

SELECT * FROM unlogged_cache;

预期为空。

检查恢复日志：

grep -Ei \
  'redo|recovery|checkpoint|database system is ready' \
  "$CH13_LOG"

检查控制信息：

pg_controldata "$CH13_PGDATA" > /tmp/ch13-control-after.txt
diff -u \
  /tmp/ch13-control-before.txt \
  /tmp/ch13-control-after.txt || true

13.24.9 可选：观察异步提交的不确定损失窗口

仅在同一个一次性实例中：

CREATE TABLE async_commit_test (
    id bigint PRIMARY KEY
);

SET synchronous_commit = off;

INSERT INTO async_commit_test VALUES (1);

随后立即执行非正常关闭。

结果可能是：

  • 行存在。
  • 行丢失。

两种结果都符合语义，因为 WAL Writer 可能已在崩溃前完成持久化。实验不能宣称“synchronous_commit=off 必定丢失最后一行”。

13.24.10 清理

pg_ctl -D "$CH13_PGDATA" stop -m fast
rm -rf "$CH13_PGDATA"
rm -f "$CH13_LOG"

13.25 Go：使用 Idempotency Key 处理未知 Commit 结果

13.25.1 订单表设计

CREATE TABLE orders (
    id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    idempotency_key   text NOT NULL UNIQUE,
    request_hash      bytea NOT NULL,
    customer_id       bigint NOT NULL,
    amount_cents      bigint NOT NULL CHECK (amount_cents > 0),
    status            text NOT NULL CHECK (
        status IN ('created', 'paid', 'cancelled')
    ),
    created_at        timestamptz NOT NULL DEFAULT clock_timestamp()
);

设计原则：

  • Idempotency Key 由调用方针对一次业务意图生成。
  • 数据库以唯一约束作为最终并发裁决。
  • 同一个 Key 携带不同请求内容时必须拒绝。
  • Commit 传输错误后先查询该 Key。
  • 即使重试，也必须使用同一个 Key，不能生成新 Key。
  • 不能只在应用内存中记录 Key；进程重启后会丢失。

13.25.2 完整示例

代码使用 pgx/v5 和 pgxpool。pgxpool.Pool 可供多个 Goroutine 并发使用，但业务并发仍应有独立上限。(pkg.go.dev)

package main

import (
	"bytes"
	"context"
	"crypto/sha256"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"math/rand"
	"os"
	"os/signal"
	"strconv"
	"syscall"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

var (
	ErrIdempotencyConflict = errors.New(
		"idempotency key was already used for a different request",
	)
	ErrCommitOutcomeUnknown = errors.New(
		"commit outcome is unknown; reconcile by idempotency key",
	)
)

type CreateOrderRequest struct {
	IdempotencyKey string
	CustomerID     int64
	AmountCents    int64
}

type Order struct {
	ID             int64
	IdempotencyKey string
	CustomerID     int64
	AmountCents    int64
	Status         string
	CreatedAt      time.Time
}

type RetryPolicy struct {
	MaxAttempts int
	BaseDelay   time.Duration
	MaxDelay    time.Duration
}

type Store struct {
	pool  *pgxpool.Pool
	gate  chan struct{}
	retry RetryPolicy
}

func NewStore(
	pool *pgxpool.Pool,
	maxInFlight int,
	retry RetryPolicy,
) (*Store, error) {
	if pool == nil {
		return nil, errors.New("pool is nil")
	}
	if maxInFlight <= 0 {
		return nil, errors.New("maxInFlight must be positive")
	}
	if retry.MaxAttempts <= 0 {
		return nil, errors.New("MaxAttempts must be positive")
	}
	if retry.BaseDelay <= 0 || retry.MaxDelay < retry.BaseDelay {
		return nil, errors.New("invalid retry delays")
	}

	return &Store{
		pool:  pool,
		gate:  make(chan struct{}, maxInFlight),
		retry: retry,
	}, nil
}

func requestHash(req CreateOrderRequest) ([32]byte, error) {
	canonical := struct {
		CustomerID  int64 `json:"customer_id"`
		AmountCents int64 `json:"amount_cents"`
	}{
		CustomerID:  req.CustomerID,
		AmountCents: req.AmountCents,
	}

	data, err := json.Marshal(canonical)
	if err != nil {
		return [32]byte{}, fmt.Errorf("marshal request: %w", err)
	}

	return sha256.Sum256(data), nil
}

func (s *Store) acquire(ctx context.Context) error {
	select {
	case s.gate <- struct{}{}:
		return nil
	case <-ctx.Done():
		return ctx.Err()
	}
}

func (s *Store) release() {
	<-s.gate
}

func (s *Store) CreateOrder(
	ctx context.Context,
	req CreateOrderRequest,
) (Order, error) {
	if req.IdempotencyKey == "" {
		return Order{}, errors.New("idempotency key is required")
	}
	if req.CustomerID <= 0 {
		return Order{}, errors.New("customer id must be positive")
	}
	if req.AmountCents <= 0 {
		return Order{}, errors.New("amount must be positive")
	}

	if err := s.acquire(ctx); err != nil {
		return Order{}, fmt.Errorf("admission control: %w", err)
	}
	defer s.release()

	hash, err := requestHash(req)
	if err != nil {
		return Order{}, err
	}

	for attempt := 0; attempt < s.retry.MaxAttempts; attempt++ {
		order, workErr, commitErr := s.createOrderOnce(
			ctx,
			req,
			hash[:],
		)

		switch {
		case workErr == nil && commitErr == nil:
			return order, nil

		case workErr != nil:
			if !isRetryableTransactionError(workErr) {
				return Order{}, workErr
			}

		case commitErr != nil:
			// PostgreSQL 明确返回 40001 或 40P01，表示本次事务失败，
			// 可以重试整个事务。
			if isRetryableTransactionError(commitErr) {
				break
			}

			// 网络错误、超时或连接终止可能发生在服务器提交之后。
			// 使用独立但有界的 Context 执行一次结果核对。
			reconcileBase := context.WithoutCancel(ctx)
			reconcileCtx, cancel := context.WithTimeout(
				reconcileBase,
				2*time.Second,
			)

			existing, found, reconcileErr := s.lookupByKey(
				reconcileCtx,
				req.IdempotencyKey,
				hash[:],
			)
			cancel()

			if reconcileErr == nil && found {
				return existing, nil
			}

			// 一次“未找到”不能证明旧事务永远不会提交；
			// 查询也可能发生在原后端最终完成 Commit 之前。
			return Order{}, fmt.Errorf(
				"%w: commit error=%v, reconciliation error=%v",
				ErrCommitOutcomeUnknown,
				commitErr,
				reconcileErr,
			)
		}

		if attempt+1 >= s.retry.MaxAttempts {
			break
		}

		if err := sleepBackoff(ctx, s.retry, attempt); err != nil {
			return Order{}, err
		}
	}

	return Order{}, errors.New("transaction retry limit exceeded")
}

// createOrderOnce 分开返回事务主体错误和 Commit 错误。
// 不能把 Commit 错误与普通语句错误混为一谈。
func (s *Store) createOrderOnce(
	ctx context.Context,
	req CreateOrderRequest,
	hash []byte,
) (
	order Order,
	workErr error,
	commitErr error,
) {
	tx, err := s.pool.BeginTx(ctx, pgx.TxOptions{
		IsoLevel: pgx.ReadCommitted,
	})
	if err != nil {
		return Order{}, fmt.Errorf("begin transaction: %w", err), nil
	}

	defer func() {
		// Commit 成功后 Rollback 返回 pgx.ErrTxClosed，可安全忽略。
		_ = tx.Rollback(ctx)
	}()

	const insertSQL = `
		INSERT INTO orders (
			idempotency_key,
			request_hash,
			customer_id,
			amount_cents,
			status
		)
		VALUES ($1, $2, $3, $4, 'created')
		ON CONFLICT (idempotency_key) DO NOTHING
		RETURNING
			id,
			idempotency_key,
			customer_id,
			amount_cents,
			status,
			created_at
	`

	err = tx.QueryRow(
		ctx,
		insertSQL,
		req.IdempotencyKey,
		hash,
		req.CustomerID,
		req.AmountCents,
	).Scan(
		&order.ID,
		&order.IdempotencyKey,
		&order.CustomerID,
		&order.AmountCents,
		&order.Status,
		&order.CreatedAt,
	)

	if errors.Is(err, pgx.ErrNoRows) {
		const selectSQL = `
			SELECT
				id,
				idempotency_key,
				customer_id,
				amount_cents,
				status,
				created_at,
				request_hash
			FROM orders
			WHERE idempotency_key = $1
		`

		var storedHash []byte

		err = tx.QueryRow(
			ctx,
			selectSQL,
			req.IdempotencyKey,
		).Scan(
			&order.ID,
			&order.IdempotencyKey,
			&order.CustomerID,
			&order.AmountCents,
			&order.Status,
			&order.CreatedAt,
			&storedHash,
		)
		if err != nil {
			return Order{}, fmt.Errorf(
				"load existing idempotent result: %w",
				err,
			), nil
		}

		if !bytes.Equal(storedHash, hash) {
			return Order{}, ErrIdempotencyConflict, nil
		}
	} else if err != nil {
		return Order{}, fmt.Errorf("insert order: %w", err), nil
	}

	if err := tx.Commit(ctx); err != nil {
		return order, nil, fmt.Errorf("commit order: %w", err)
	}

	return order, nil, nil
}

func (s *Store) lookupByKey(
	ctx context.Context,
	key string,
	expectedHash []byte,
) (Order, bool, error) {
	const query = `
		SELECT
			id,
			idempotency_key,
			customer_id,
			amount_cents,
			status,
			created_at,
			request_hash
		FROM orders
		WHERE idempotency_key = $1
	`

	var order Order
	var storedHash []byte

	err := s.pool.QueryRow(ctx, query, key).Scan(
		&order.ID,
		&order.IdempotencyKey,
		&order.CustomerID,
		&order.AmountCents,
		&order.Status,
		&order.CreatedAt,
		&storedHash,
	)

	if errors.Is(err, pgx.ErrNoRows) {
		return Order{}, false, nil
	}
	if err != nil {
		return Order{}, false, fmt.Errorf(
			"reconcile order: %w",
			err,
		)
	}
	if !bytes.Equal(storedHash, expectedHash) {
		return Order{}, true, ErrIdempotencyConflict
	}

	return order, true, nil
}

func isRetryableTransactionError(err error) bool {
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) {
		return false
	}

	switch pgErr.Code {
	case "40001", // serialization_failure
		"40P01": // deadlock_detected
		return true
	default:
		return false
	}
}

func sleepBackoff(
	ctx context.Context,
	policy RetryPolicy,
	attempt int,
) error {
	delay := policy.BaseDelay

	for i := 0; i < attempt; i++ {
		if delay >= policy.MaxDelay/2 {
			delay = policy.MaxDelay
			break
		}
		delay *= 2
	}

	if delay > policy.MaxDelay {
		delay = policy.MaxDelay
	}

	jitterLimit := int64(delay / 2)
	if jitterLimit > 0 {
		delay += time.Duration(rand.Int63n(jitterLimit + 1))
	}

	timer := time.NewTimer(delay)
	defer timer.Stop()

	select {
	case <-timer.C:
		return nil
	case <-ctx.Done():
		return ctx.Err()
	}
}

func envPositiveInt(name string, fallback int) (int, error) {
	value := os.Getenv(name)
	if value == "" {
		return fallback, nil
	}

	n, err := strconv.Atoi(value)
	if err != nil || n <= 0 {
		return 0, fmt.Errorf("%s must be a positive integer", name)
	}

	return n, nil
}

func run(ctx context.Context) error {
	databaseURL := os.Getenv("DATABASE_URL")
	if databaseURL == "" {
		return errors.New("DATABASE_URL is required")
	}

	maxInFlight, err := envPositiveInt("APP_MAX_INFLIGHT", 16)
	if err != nil {
		return err
	}

	config, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		return fmt.Errorf("parse DATABASE_URL: %w", err)
	}

	pool, err := pgxpool.NewWithConfig(ctx, config)
	if err != nil {
		return fmt.Errorf("create pool: %w", err)
	}
	defer pool.Close()

	pingCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	if err := pool.Ping(pingCtx); err != nil {
		return fmt.Errorf("ping database: %w", err)
	}

	store, err := NewStore(
		pool,
		maxInFlight,
		RetryPolicy{
			MaxAttempts: 4,
			BaseDelay:   25 * time.Millisecond,
			MaxDelay:    500 * time.Millisecond,
		},
	)
	if err != nil {
		return err
	}

	request := CreateOrderRequest{
		IdempotencyKey: "order-request-2026-000001",
		CustomerID:     42,
		AmountCents:    1999,
	}

	order, err := store.CreateOrder(ctx, request)
	if err != nil {
		if errors.Is(err, ErrCommitOutcomeUnknown) {
			return fmt.Errorf(
				"order status requires reconciliation: %w",
				err,
			)
		}
		return err
	}

	log.Printf(
		"order created or reused: id=%d status=%s",
		order.ID,
		order.Status,
	)

	return nil
}

func main() {
	ctx, stop := signal.NotifyContext(
		context.Background(),
		os.Interrupt,
		syscall.SIGTERM,
	)
	defer stop()

	if err := run(ctx); err != nil {
		log.Fatal(err)
	}
}

安装依赖：

go mod init example.com/ch13
go get github.com/jackc/pgx/v5
go mod tidy

运行：

export DATABASE_URL='postgres://user:password@localhost:5432/app'
export APP_MAX_INFLIGHT=16

go run .

APP_MAX_INFLIGHT=16 只是示例，不是通用推荐值。它应根据数据库连接预算、事务延迟、实例 CPU、锁热点和总体服务数量确定。

13.25.3 为什么唯一约束不可省略

错误方案：

SELECT 是否存在
  → 不存在
  → INSERT

两个并发请求可能同时看到“不存在”，然后都插入。

正确方案依赖数据库唯一约束：

UNIQUE (idempotency_key)

唯一约束是最终并发裁决，应用查询只是结果解释。

13.25.4 Commit 错误分类

错误能否确认事务失败处理
40001能，当前事务失败重试整个事务
40P01能，当前事务被中止重试整个事务
约束错误能，语句或事务失败修正请求，不盲目重试
Commit 网络断开不能按 Idempotency Key 对账
Commit Context 超时通常不能仅凭客户端判断对账或返回处理中
数据库重启导致连接断开不能假设回滚对账
请求尚未调用 Commit 就失败显式事务通常不会提交回滚并按规则重试

13.26 生产排障 Runbook

1. 确认现象与时间窗口

记录：

  • 从何时开始。
  • TPS、P95、P99 如何变化。
  • 仅写请求还是所有请求。
  • 是否发生发布、批处理、索引构建、备份或故障切换。
  • 是否只有一个可用区或一个实例异常。
  • 业务是否出现未知提交或重复请求。

2. 固化版本和参数

SELECT version();

SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN (
    'fsync',
    'synchronous_commit',
    'wal_level',
    'full_page_writes',
    'wal_compression',
    'wal_buffers',
    'max_wal_size',
    'min_wal_size',
    'checkpoint_timeout',
    'checkpoint_completion_target',
    'track_io_timing',
    'track_wal_io_timing',
    'io_method'
)
ORDER BY name;

3. 查看 WAL 生成

SELECT *
FROM pg_stat_wal;

重点看：

  • wal_bytes 斜率。
  • wal_fpi 斜率。
  • wal_buffers_full。
  • stats_reset。

4. 查看 WAL 与数据 I/O

[PG18]：

SELECT *
FROM pg_stat_io
WHERE object IN ('wal', 'relation')
ORDER BY backend_type, object, context;

5. 查看 Checkpoint

SELECT *
FROM pg_stat_checkpointer;

SELECT *
FROM pg_stat_bgwriter;

同时检查数据库日志中的 Checkpoint 起止记录。

6. 查看等待事件

SELECT
    pid,
    backend_type,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    clock_timestamp() - query_start AS query_age,
    left(query, 200) AS query
FROM pg_stat_activity
WHERE wait_event ILIKE '%wal%'
   OR wait_event ILIKE '%checkpoint%'
   OR wait_event ILIKE '%datafile%'
ORDER BY query_age DESC NULLS LAST;

7. 检查复制

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_size_pretty(
        pg_wal_lsn_diff(
            pg_current_wal_lsn(),
            replay_lsn
        )
    ) AS replay_gap,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

8. 检查复制槽和归档

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status,
    safe_wal_size
FROM pg_replication_slots;

SELECT *
FROM pg_stat_archiver;

9. 检查磁盘和操作系统

至少观察：

  • pg_wal 文件系统剩余空间。
  • 数据目录剩余空间。
  • 磁盘读写延迟。
  • 队列深度。
  • IOPS 和吞吐上限。
  • 云盘突发额度。
  • 内核日志和设备错误。
  • 文件系统及挂载参数。

10. 找到最早的计划估算偏差

若 WAL 增长来自异常 SQL：

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT ...;

对于 UPDATE、DELETE、INSERT 和 SELECT FOR UPDATE，必须在可回滚测试环境中执行。

11. 安全止损

按根因选择：

  • 暂停非关键批处理。
  • 降低应用写入并发。
  • 暂停新的索引构建。
  • 修复归档目标或网络。
  • 恢复失联复制消费者。
  • 为磁盘扩容。
  • 对逻辑消费者实施背压和限流。
  • 将未知提交请求转入对账队列。
  • 暂时降低非关键流量，而不是关闭持久性保护。

高风险动作：

  • 删除复制槽。
  • 强制清理 pg_wal 文件。
  • 关闭 fsync。
  • 关闭 full_page_writes。
  • 临时把关键事务改成异步提交。
  • 对正在使用的归档目录手工删除文件。

12. 验证恢复

确认：

  • P99 恢复。
  • WAL 速率回到业务基线。
  • Checkpoint 频率正常。
  • pg_wal 占用开始下降。
  • 归档连续成功。
  • 复制槽位置推进。
  • Standby Replay Gap 缩小。
  • 没有重复订单或遗漏订单。
  • PITR 链仍可用。
  • 告警阈值与容量预测已经更新。

13.27 常见反模式

反模式风险正确做法
认为 Commit 返回表示所有数据页已落盘错误理解持久性和 Checkpoint以 WAL Flush 和恢复协议理解提交
为降低延迟关闭 fsync崩溃后可能出现不可恢复不一致解决存储、批量提交和并发问题
为减少 WAL 关闭 full_page_writesTorn Page 失去关键保护使用可靠默认并评估 WAL 压缩
全局设置 synchronous_commit=off关键数据可能在崩溃中丢失仅对明确可重建事务按事务设置
把 Unlogged Table 用作订单主表崩溃后自动清空且无法正常复制仅存放可重建中间数据
把 pg_switch_wal() 当作 Checkpoint无法保证数据页全部写出根据目标使用 CHECKPOINT、备份或归档工具
看到 P99 高就无限增大 wal_buffers可能掩盖设备或并发瓶颈先检查 wal_buffers_full 和 I/O
将 max_wal_size 设得极小以节省空间频繁 Checkpoint 与 FPI 放大结合峰值 WAL 速率和 RTO 定容
将 max_wal_size 当作磁盘硬上限复制槽或归档失败仍可撑满磁盘单独做容量和滞留告警
事故中直接重置统计丢失根因时间窗口先保存快照，再按流程重置
在共享集群用全局 WAL 差值比较单条 SQL结果被其他会话污染使用专用实例或低噪声窗口
Commit 报错后用新 Key 再创建订单产生重复订单原 Key 对账，必要时同 Key 受控重试
复制槽滞后时直接删除槽消费者失去连续位置评估消费者重建及数据丢失影响
把 Checkpoint 当作备份无法应对文件丢失和历史恢复使用 Base Backup、连续归档和恢复演练
认为 PG18 AIO 会消除 WAL Sync错误容量规划分开分析数据读取和提交持久化路径
只看平均延迟Checkpoint 和同步尖峰被掩盖观察 P95/P99、时间线和队列深度

13.28 模拟生产事故一：Checkpoint 风暴导致写入 P99 上升

背景

电商团队在业务高峰前执行大规模历史数据导入并创建两个新索引。实例 max_wal_size 相对峰值 WAL 速率偏小，归档目标同时出现吞吐下降。

症状

  • 订单写入 TPS 下降约三成。
  • P50 变化不大，P99 周期性升高。
  • pg_wal 占用持续增长。
  • Checkpoint 日志频繁出现 requested checkpoint。
  • wal_fpi 在每轮 Checkpoint 后快速增长。
  • 存储队列深度长期接近上限。

错误假设

团队最初认为：

  • 连接池太小。
  • 只需提高 max_connections。
  • PG18 AIO 未启用导致 Commit 慢。

增加应用并发后，P99 进一步恶化。

调查

  1. 对齐 P99 时间线和 Checkpoint 日志。
  2. 查看 pg_stat_checkpointer，发现 num_requested 快速增长。
  3. 查看 pg_stat_wal，发现 wal_fpi 与 wal_bytes 在 Checkpoint 后明显上升。
  4. 查看 pg_stat_io，Checkpointer 关系写入和 WAL 写入同时较高。
  5. 检查 pg_stat_progress_create_index，确认索引仍在构建。
  6. 检查 pg_stat_archiver，发现归档失败与延迟。
  7. OS 指标显示数据盘和 WAL 盘共享同一吞吐额度。

根因

批量导入和索引构建产生大量 WAL
  → WAL 压力频繁请求 Checkpoint
  → 每轮 Checkpoint 后热点页重新产生 FPI
  → Checkpointer、WAL 和业务数据争用同一存储
  → 写队列增长
  → Commit 和前台脏页写出尾延迟恶化

临时止损

  • 暂停第二个索引构建。
  • 降低导入并发。
  • 修复归档目标吞吐。
  • 对非关键批任务限流。
  • 在磁盘容量允许且评估 RTO 后，提高实验验证过的 WAL 空间目标。

最终修复

  • 将索引变更放入独立维护窗口。
  • 为批量导入建立 WAL 和归档容量预算。
  • 将应用并发控制在数据库可持续吞吐内。
  • 调整 Checkpoint 参数，使峰值 WAL 期间不发生连续 requested checkpoint。
  • 分离或扩展存储吞吐。
  • 建立 wal_bytes/s、wal_fpi/s 和 num_requested 联合告警。

预防指标

  • 过去 5 分钟 requested checkpoint 次数。
  • Checkpoint 后 FPI 斜率。
  • pg_wal 剩余容量时间。
  • 归档失败数和归档延迟。
  • WAL/事务字节。
  • Commit P99 与 WAL fsync_time。
  • 索引构建进度。

13.29 模拟生产事故二：Commit 超时导致重复订单

背景

订单服务在事务中写入订单后执行 COMMIT。数据库发生短暂网络抖动，客户端在 Commit 阶段收到连接重置。

旧代码逻辑：

Commit 返回错误
  → 认为订单未创建
  → 生成新请求编号
  → 再次 INSERT

症状

  • 用户看到一次支付请求。
  • 数据库出现两笔业务内容相同、主键不同的订单。
  • 第一笔订单的创建时间早于错误日志。
  • 应用日志中记录第一次 Commit 失败和第二次成功。
  • 数据库没有对应的事务回滚错误。

错误假设

“驱动返回 Commit 错误，所以数据库一定回滚。”

调查

  1. 检查第一笔订单是否实际存在。
  2. 对比数据库提交时间和应用网络错误时间。
  3. 发现连接断开发生在服务端完成 Commit 之后、响应到达客户端之前。
  4. 检查表结构，发现没有 Idempotency Key 唯一约束。
  5. 检查重试逻辑，发现重试生成了新业务 Key。
  6. 发现所有网络错误都按普通可重试错误处理。

根因

客户端把“结果未知”错误地解释成“事务失败”，且数据库没有业务幂等约束。

临时止损

  • 禁止 Commit 传输错误后自动生成新订单编号。
  • 将相关请求转入人工或自动对账队列。
  • 根据支付请求号、客户、金额和时间窗口识别潜在重复。
  • 暂时要求客户端复用原业务请求 Key。

最终修复

  • 增加 idempotency_key UNIQUE。
  • 保存请求内容 Hash。
  • 同 Key 同请求返回原结果。
  • 同 Key 不同请求返回冲突。
  • Commit 传输错误后按 Key 查询。
  • 只对 40001 和 40P01 自动重试整个事务。
  • 所有重试有上限、退避、抖动和并发门控。
  • 对外提供“处理中/查询状态”协议，而不是伪造失败。

监控

  • Idempotency Key 冲突数量。
  • 同 Key 不同请求 Hash 数量。
  • Commit 阶段网络错误。
  • 未知结果对账队列长度。
  • 重试次数分布。
  • 订单与支付请求的一致性审计。
  • 重复订单补偿次数。

13.30 面试题

一、核心概念

1. 为什么事务提交不需要立即刷所有数据页？

30 秒回答： PostgreSQL 使用 WAL。提交通常只需保证 Commit Record 及其之前的 WAL 达到要求的持久化位置；数据页可由 Checkpointer、Background Writer 或 Backend 稍后写出。崩溃后从 Redo Point 重放 WAL。

深入回答： 数据页是随机写，WAL 是顺序追加。WAL-before-data 保证任何持久化数据页都不会引用尚未持久化的日志。优点是提交延迟更低、可形成 Group Commit；代价是必须管理 Checkpoint、WAL 空间和恢复时间。替代方式如每次提交同步所有页会产生不可接受的随机 I/O。生产中需同时观察 WAL Flush 和 Checkpoint，而不是只看数据盘写入。

考察点： 是否真正理解 WAL 持久性。

常见错误： “Commit 会调用 fsync 把所有表文件写盘。”

追问： 数据页先于 WAL 落盘会怎样？

追问回答： 崩溃后可能留下无法根据日志可靠解释的数据页状态，破坏恢复协议。

2. WAL Record、WAL Page、WAL Segment 和 LSN 有什么区别？

30 秒回答： Record 是逻辑恢复记录；Page 是 WAL 流的内部分页；Segment 是 pg_wal 中的文件；LSN 是 WAL 字节流位置。

深入回答： Record 可跨 WAL Page，WAL Page 可跨 Segment 边界连续组织。Segment 通常为 16 MiB，但初始化时可改变。LSN 用于排序、差值、复制位置和恢复位置。优点是形成统一有序日志流；代价是监控必须理解位置而非只数文件。生产程序不要把 Segment 大小硬编码。

考察点： 存储结构与逻辑日志的层次。

常见错误： “一个 Segment 就是一次 Checkpoint。”

追问： pg_wal_lsn_diff 返回什么？

追问回答： 两个 LSN 之间的字节距离。

3. WAL Writer、Checkpointer 和 Background Writer 分别做什么？

30 秒回答： WAL Writer 后台写 WAL；Checkpointer 完成检查点脏页写出并推进恢复边界；Background Writer 提前清理部分脏 Buffer 以减少 Backend 被迫写页。

深入回答： WAL Writer 不能保证在每次提交前恰好同步目标 LSN，因此 Backend 仍可能 Flush。Checkpointer 面向恢复和持久化边界，Background Writer 面向缓冲区复用。三个进程都可能影响 I/O，但优化目标不同。替代方案不是让某一个进程承担全部职责。生产监控应分别看 pg_stat_wal、pg_stat_io、pg_stat_checkpointer 和 pg_stat_bgwriter。

考察点： 后台进程职责边界。

常见错误： “Background Writer 会完成 Checkpoint。”

追问： 为什么 Client Backend 会写数据页？

追问回答： 它需要复用一个脏 Buffer，而后台进程尚未将其写出。

4. Full Page Write 为什么和 Checkpoint 有关？

30 秒回答： 页面在 Checkpoint 后第一次修改时，WAL 通常记录完整页面镜像，用于崩溃后修复可能的 Torn Page。下一次 Checkpoint 后周期重新开始。

深入回答： FPI 提供已知完整页面基础，后续再应用增量 WAL。优点是增强崩溃恢复可靠性；缺点是 Checkpoint 后 WAL 增大。wal_compression 可降低 FPI 字节但增加 CPU。替代方案不能只是依赖校验和，因为校验和主要用于检测而非恢复。生产中应观察 wal_fpi 和 Checkpoint 频率。

考察点： Checkpoint、WAL 放大和存储可靠性的关联。

常见错误： “每次更新都写完整页面。”

追问： 为什么频繁 Checkpoint 可能增加 WAL？

追问回答： 页面首次修改状态更频繁重置，因而产生更多 FPI。

5. synchronous_commit=off 和 fsync=off 的区别是什么？

30 秒回答： 前者主要允许事务在本地 WAL 尚未同步时提前返回；后者集群级放弃关键持久化同步，崩溃后可能造成严重不一致。

深入回答： synchronous_commit=off 仍生成 WAL，后台仍会写出，通常风险是近期已确认事务丢失。fsync=off 可能破坏 WAL 与数据文件的持久化顺序。前者可按事务用于可重建数据；后者不应作为生产性能调优。替代方案是优化存储、批量提交、Group Commit 和并发。

考察点： 数据丢失与数据库损坏风险的区别。

常见错误： “两者都只是可能丢最后几秒数据。”

追问： 订单事务能否使用异步提交？

追问回答： 除非业务明确接受已确认订单在崩溃后消失，否则不应使用。

二、原理与排障

6. Checkpoint 期间 P99 升高，应如何定位？

30 秒回答： 对齐 P99、Checkpoint 日志、pg_stat_checkpointer、pg_stat_io 和存储队列，区分写阶段、同步阶段、FPI 增长及 Backend 被迫写页。

深入回答： 若 write_time 高，关注持续写吞吐；若 sync_time 高，关注集中 Flush；若 Checkpoint 后 wal_fpi 激增，关注频率和 WAL 放大。优点是时间线证据能避免盲调；替代方案不是直接增大所有参数。生产修改需同时评估 RTO、磁盘空间和归档能力。

考察点： 多层指标关联能力。

常见错误： “直接关闭 Checkpoint。”

追问： max_wal_size 越大越好吗？

追问回答： 不一定；它可减少频繁 Checkpoint，但会增加磁盘需求及潜在恢复工作量。

7. wal_buffers_full 快速增长说明什么？

30 秒回答： WAL Buffers 多次无法容纳当前 WAL 生成，需要后端主动推进写出；可能是突发 WAL 太快、缓冲不足或底层写出太慢。

深入回答： 必须结合 WAL 速率、后端类型 I/O 和设备延迟判断。增加 wal_buffers 可能缓解短峰值，但不能修复持续设备瓶颈。替代方案包括批量控制、准入限制和提升 WAL 存储能力。生产中比较单位时间增量，而非累计值。

考察点： 指标不是根因本身。

常见错误： “只要把 WAL Buffer 加到很大即可。”

追问： 为什么更大 Buffer 不消除 Commit Sync？

追问回答： Buffer 解决暂存空间，持久提交仍必须把目标 WAL 同步到可靠存储。

8. pg_wal 持续增长，应按什么顺序检查？

30 秒回答： 先确认增长速率和磁盘剩余时间，再检查归档、物理和逻辑复制槽、Standby 状态、备份、WAL 生成峰值和 Checkpoint。

深入回答： max_wal_size 不是硬限制。复制槽会保留其消费者仍需要的 WAL；归档失败也会阻止正常清理。优点是槽保证连续消费，代价是消费者失联可耗尽磁盘。替代方案是恢复消费者或有计划重建，不能手工删除 pg_wal 文件。生产应设置基于剩余时间的告警。

考察点： WAL 保留链路。

常见错误： “直接删除最老的 WAL 文件。”

追问： 删除逻辑槽有什么后果？

追问回答： 消费者失去原解码位置，通常需要重新初始化或接受数据缺口。

9. PostgreSQL 18 应如何观察 WAL I/O？

30 秒回答： pg_stat_wal 看 Record、FPI、字节和 Buffer 满；pg_stat_io WHERE object='wal' 看写入、字节、同步和耗时。

深入回答： PG18 将 WAL I/O 统计移入统一 I/O 视图。必须确认 timing 参数及 stats_reset，并结合 OS 设备指标，因为数据库统计不能区分 OS Page Cache 和物理介质。旧版 SQL 需要版本适配。生产监控应按版本维护查询。

考察点： 版本差异与指标语义。

常见错误： “PG18 的 pg_stat_wal.wal_sync_time 仍可直接查询。”

追问： 为什么数据库 I/O 时间不能单独证明磁盘慢？

追问回答： 请求可能经过操作系统缓存、设备缓存和异步队列，需结合系统层指标。

10. 大事务为什么会给 WAL 和复制带来压力？

30 秒回答： 事务执行期间持续生成大量 WAL，增加归档、网络、复制槽和重放压力；提交失败或重试的业务成本也更大。

深入回答： WAL 并非等到 Commit 才一次性生成，但 Commit 需要确保相关 WAL 达到目标持久化位置。大事务还可能持有长期快照并影响 Vacuum。分批能降低峰值和失败半径，但会改变原子性。替代方案包括阶段表、可恢复批次状态和幂等分块。生产中应监控每批 WAL、复制延迟和回滚成本。

考察点： 执行阶段与提交阶段的区别。

常见错误： “大事务的所有 WAL 都在 Commit 时才写。”

追问： 把一个事务拆成 100 个事务有什么代价？

追问回答： 原子性改变，部分成功需要业务恢复协议，且 Commit Record 和同步次数增加。

11. PostgreSQL Crash Recovery 为什么不需要传统的 SQL 级 Undo？

30 秒回答： 恢复通过 WAL Redo 重建已持久化状态；没有 Commit Record 的事务不会被认定为已提交，其 Tuple 对其他事务不可见，之后由 Vacuum 清理。

深入回答： 页面 LSN 使 Redo 可判断记录是否已应用。优点是恢复路径围绕顺序 WAL；代价是恢复后仍可能存在需要清理的死 Tuple。替代方案如逐条反向执行 SQL 既不可靠也缺乏必要上下文。生产中关注 Redo 起点、WAL 完整性和恢复耗时。

考察点： WAL、MVCC 与事务状态协作。

常见错误： “启动时会执行每个未提交事务的反向 UPDATE。”

追问： 未提交 Tuple 为什么不会被读到？

追问回答： MVCC 可见性检查不会把其创建事务视为已提交。

三、架构题

12. 如何为支付订单设计近零 RPO 的提交路径？

30 秒回答： 本地可靠 WAL、同步物理复制、明确同步法定人数、Idempotency Key、PITR、Fencing 和故障切换演练缺一不可。

深入回答： synchronous_commit=on 可等待同步 Standby Flush；remote_apply 提供更强远端可见性但延迟更高。同步复制降低 RPO，却增加网络延迟和可用性耦合。替代方案包括多区域业务账本或异步复制加对账，但语义不同。生产必须定义同步备库不足时是停止写、降级还是接受 RPO。

考察点： 数据库配置与业务协议的结合。

常见错误： “部署一个 Standby 就是零 RPO。”

追问： 同步备库失联后怎么办？

追问回答： 必须遵循预先定义的降级策略，不能临时无审计地切异步。

13. 物理复制、逻辑复制和 PITR 如何选？

30 秒回答： 物理复制用于同集群结构的低 RPO 灾备；逻辑复制用于表级、跨版本和数据集成；PITR 用于恢复到历史时间点。

深入回答： 物理复制重放 WAL 物理变化，切换快但耦合较强。逻辑复制灵活但需处理 DDL、序列和冲突。PITR 依赖 Base Backup 和连续 WAL 归档，不是在线查询副本。生产往往同时使用三者，而不是三选一。

考察点： 恢复、迁移和分发需求分类。

常见错误： “有流复制就不需要备份。”

追问： 误删数据被复制到 Standby 后怎么办？

追问回答： Standby 也会重放误删，需使用 PITR 或其他历史副本恢复。

14. 故障切换为什么必须有 Fencing？

30 秒回答： 提升 Standby 后，如果旧 Primary 仍可写，就会在不同 Timeline 上形成双主，产生不可自动合并的冲突历史。

深入回答： Fencing 可以是电源隔离、存储隔离、网络隔离或可靠租约。优点是保证单写者；代价是增加控制面复杂度。替代方案不能只是依赖 DNS，因为旧连接可能继续存活。生产切换还需清理连接池、处理未知提交和重建旧 Primary。

考察点： Split Brain 与 Timeline。

常见错误： “修改 VIP 后旧主自然不会再写。”

追问： 旧主怎样重新加入？

追问回答： 通过 pg_rewind 或重新做 Base Backup，不能直接按原状态启动为主。

15. 如何设计 Commit 结果不确定的订单 API？

30 秒回答： 请求携带稳定 Idempotency Key，数据库唯一约束保存 Key 和请求 Hash；Commit 传输错误后按 Key 查询，不能生成新 Key 盲目重建。

深入回答： 同 Key 同内容返回原结果，同 Key 不同内容拒绝。SQLSTATE 40001、40P01 可重试整个事务；网络错误属于结果未知。优点是可在进程、连接和数据库故障下去重；代价是需保存状态、定义保留期和对账接口。替代方案如仅在 Redis 去重不能替代数据库最终约束。

考察点： 数据库原子性与分布式协议边界。

常见错误： “Commit 抛异常就换一个订单号再插入。”

追问： 查询 Key 未找到，能否立即断定未提交？

追问回答： 不能；查询可能发生在原后端最终完成 Commit 之前，应返回处理中、继续对账或使用同 Key 的受控协议。

13.31 练习与答案

13.31.1 理论题

题 1

事务 T 修改了 1000 个数据页，COMMIT 返回时是否要求这 1000 个页面全部写入数据文件？

答案：

不要求。通常只需保证 T 的 Commit Record 及其之前所需 WAL 达到当前提交级别要求的持久化位置。数据页可稍后写出。写数据页时必须先保证对应 Page LSN 之前的 WAL 已持久化。

题 2

为什么 synchronous_commit=off 的事务仍然会出现在 WAL 中？

答案：

它改变的是向客户端返回成功的等待点，不是关闭 WAL。事务仍生成普通 WAL 和 Commit Record；WAL Writer 或其他 Backend 稍后会持久化。主机崩溃可能丢失尚未同步但已向客户端确认的事务。

题 3

为什么调大 max_wal_size 可能降低 WAL FPI，但也可能恶化 RTO？

答案：

较大的 WAL 空间目标通常降低 WAL 压力触发 Checkpoint 的频率，使页面在较长周期内只需一次本轮 FPI。但 Checkpoint 间隔和恢复起点可能变远，崩溃后需要重放更多 WAL，增加恢复时间。

题 4

wal_compression 是否能解决新增五个索引后出现的全部 WAL 增长？

答案：

不能。它主要压缩适用的 Full Page Image。新增索引产生的普通索引 WAL、页面分裂和维护成本仍然存在。应先评估索引价值、WAL/行、FPI 比例和更新模式。

题 5

为什么 PG18 AIO 不会自动降低单事务 Commit 的持久化延迟？

答案：

PG18 AIO 当前重点改善部分关系数据读取和预取路径。Commit 仍受 WAL 顺序、写入、同步复制和持久化协议约束。io_method 不是 synchronous_commit 的替代项。

13.31.2 实验题

题 6

设计实验比较 10,000 次单行 INSERT 和 1,000 次十行 INSERT。

答案要点：

  • 总业务行数相同。
  • 相同表结构、行宽和索引。
  • 相同并发及缓存条件。
  • 记录事务数、WAL bytes、FPI、fsyncs、TPS、P50/P95/P99。
  • 避免测量期间其他会话写入。
  • 检查是否发生 Checkpoint。
  • 解释提交次数、Group Commit 和协议开销。

题 7

如何证明 Checkpoint 后第一次更新页面更容易产生 FPI？

答案要点：

  1. 记录 pg_stat_wal 基线。
  2. 执行 CHECKPOINT。
  3. 更新分布在大量页面上的行。
  4. 记录 wal_fpi 增量。
  5. 不执行新 Checkpoint，再更新同一组页面。
  6. 比较第二轮 wal_fpi 增量。
  7. 控制并发、页面分布和其他写入。

题 8

如何验证 Unlogged Table 的崩溃行为？

答案要点：

  • 只在临时集群。
  • 同时创建 Logged 和 Unlogged Table。
  • 两者都写入并提交。
  • 另开一个未提交事务。
  • 使用 pg_ctl stop -m immediate。
  • 重启并查看恢复日志。
  • Logged 已提交数据存在。
  • Logged 未提交数据不存在。
  • Unlogged Table 被重置。

13.31.3 排障题

题 9

pg_wal 预计两小时内写满，但 TPS 没有明显增长。应如何排查？

答案：

  1. 确认 wal_bytes/s 是否真的增长。
  2. 查看归档失败。
  3. 查看物理和逻辑复制槽的 restart_lsn。
  4. 查看 Standby 和逻辑消费者是否失联。
  5. 检查正在运行的备份。
  6. 查看最近是否创建索引或批量更新。
  7. 估算每个保留因素占用的 WAL。
  8. 先恢复消费者、归档或扩容。
  9. 不手工删除 Segment。
  10. 若必须删除槽，先确认消费者重建方案和数据影响。

题 10

应用报告大量 Commit Timeout，同时数据库中部分订单已存在。应如何处理？

答案：

  • 将错误分类为 Commit 结果不确定。
  • 暂停生成新 Idempotency Key 的自动重试。
  • 按原 Key 查询数据库。
  • 找到同 Hash 订单则返回原结果。
  • 同 Key 不同 Hash 返回冲突。
  • 未找到时进入有界对账或“处理中”状态。
  • 只对明确的 40001、40P01 重试整个事务。
  • 检查网络、连接池、数据库重启和同步复制延迟。
  • 审计潜在重复订单并执行补偿。

13.31.4 系统设计题

题 11

设计一个跨可用区订单系统，要求：

  • 正常情况下订单 RPO 接近零。
  • 数据库故障切换后不能重复创建订单。
  • 支持恢复误删数据。
  • 同步备库故障时业务必须有明确策略。

参考答案：

客户端
  → 带 Idempotency Key 的订单 API
  → 有界并发与连接池
  → PostgreSQL Primary
       ├─ 同步物理 Standby，跨可用区
       ├─ 异步只读或灾备 Standby
       ├─ 连续 WAL 归档
       └─ 定期 Base Backup

数据库设计：

  • orders.idempotency_key UNIQUE。
  • 保存规范化请求 Hash。
  • Commit 网络错误后按 Key 对账。
  • 关键订单使用同步提交。
  • 事务内写订单和 Outbox Event。
  • 消费者按事件 ID 幂等处理。

HA 设计：

  • 同步备库法定人数明确。

  • Standby 不足时预先定义：

    • 停止关键写入；或
    • 经审批降级为异步并接受非零 RPO。

  • 使用 Fencing 确保单 Primary。

  • 切换后清理旧连接并验证 Timeline。

  • 旧 Primary 使用 pg_rewind 或重建。

恢复设计：

  • Base Backup + 连续 WAL 归档。
  • 定期执行 PITR 演练。
  • 验证恢复时间、数据一致性和归档连续性。
  • 误删发生时，不依赖已同步重放误删的 Standby，而使用 PITR 恢复到隔离实例后提取数据。

监控：

  • Commit P99。
  • 同步复制等待。
  • Flush/Replay Gap。
  • WAL 归档失败。
  • 复制槽滞留量。
  • pg_wal 剩余时间。
  • Checkpoint 频率。
  • Idempotency 冲突和未知提交队列。

13.32 完成检查清单

完成本章后，应能够逐项确认：

  • 能解释 Commit 成功为何不等于数据页立即落盘。
  • 能区分 WAL Record、Page、Segment 和 LSN。
  • 能说明 WAL Buffer、WAL Writer 和 Backend Flush 的关系。
  • 能解释 Group Commit 的收益和并发边界。
  • 能区分 Background Writer 与 Checkpointer。
  • 能解释 Redo Point 不等于简单的 Checkpoint 完成 LSN。
  • 能说明 FPI、Torn Page 和 wal_compression。
  • 不会通过关闭 fsync 或 full_page_writes进行普通调优。
  • 能根据业务 RPO 选择 synchronous_commit。
  • 能使用 PG18 的 pg_stat_wal 和 pg_stat_io。
  • 能处理 PG14—18 的监控视图差异。
  • 能诊断 pg_wal 增长、归档失败和复制槽滞留。
  • 能解释物理复制、逻辑复制和 PITR 对 WAL 的不同使用。
  • 能说明 PG18 AIO 不改变 WAL 持久化协议。
  • 能在一次性实例验证 Crash Recovery。
  • 能解释 Unlogged Table 非正常关闭后清空的原因。
  • 能区分可重试事务失败和未知 Commit 结果。
  • 能使用数据库唯一约束实现订单幂等。
  • 能为未知提交设计对账和“处理中”协议。
  • 能将 WAL、Checkpoint、复制、PITR 和业务一致性放进同一架构模型。

13.33 官方资料

  • PostgreSQL 18：WAL 可靠性、WAL-before-data、Group Commit 与 PITR 原理。(PostgreSQL)
  • PostgreSQL 18：Checkpoint、Redo Point、WAL 写入和回收。(PostgreSQL)
  • PostgreSQL 18：WAL 配置、wal_level、wal_compression、WAL Buffers。(PostgreSQL)
  • PostgreSQL 18：异步提交语义。(PostgreSQL)
  • PostgreSQL 18：WAL LSN 与 Segment 管理函数。(PostgreSQL)
  • PostgreSQL 18：pg_stat_io、pg_stat_wal、pg_stat_checkpointer、pg_stat_bgwriter。(PostgreSQL)
  • PostgreSQL 18：异步 I/O、io_method 与支持的扫描路径。(PostgreSQL)
  • PostgreSQL 18：Unlogged Table 行为。(PostgreSQL)
  • pgx/v5 与 pgxpool API。(pkg.go.dev)