← 返回文章列表

2026年6月20日

PostgreSQL 架构与请求生命周期

从连接池、Backend、查询处理器、共享内存、Buffer、WAL 与超时取消路径，建立 PostgreSQL 一条请求的完整心智模型。

第 1 章：PostgreSQL 架构与一条请求的完整生命周期

技术基线：PostgreSQL 18；Go 使用当前稳定版与 github.com/jackc/pgx/v5、pgxpool。 版本标记：[PG16+] 表示自 PostgreSQL 16 起可用，[PG17+] 表示自 PostgreSQL 17 起可用，[PG18] 表示 PostgreSQL 18 新增或显著变化。 本章边界：建立全局位置关系，不深入 Tuple 可见性规则、B-tree 页面格式、完整锁冲突矩阵和 WAL Record 二进制格式。

---

1. 本章定位

1.1 本章解决什么问题

当应用发出一条 SQL 时，真正发生的事情远不只是“数据库执行 SQL”：

1. Go goroutine 先在连接池中申请一个连接；
2. 连接对应 PostgreSQL 实例中的一个 Backend Process；
3. 客户端和服务端通过前后端协议交换消息；
4. Backend 依次完成解析、语义分析、重写、规划与执行；
5. Executor 通过 Buffer Manager、Storage Manager、共享内存和操作系统访问数据；
6. 更新语句还会生成 WAL、修改共享缓冲区，并在提交时满足持久性要求；
7. 任一步都可能等待网络、连接池、锁、CPU、I/O、WAL、复制确认或客户端；
8. 取消、超时、连接中断和故障转移会沿不同路径传播，产生不同的客户端错误和服务端状态。

本章的目标是把这些组件放进同一张地图，使后续的索引、MVCC、锁、VACUUM、WAL、连接池和高可用章节都有明确坐标。

1.2 本章总览图

这张图先把 PostgreSQL 实例结构、共享内存、WAL、后台进程、存储层和一条 SQL 请求的生命周期放到同一张地图里。后面的 4.1 会再用更细的 Mermaid 图拆开进程、共享内存与操作系统之间的关系。

1.3 为什么生产环境必须掌握

没有全局心智模型时，常见误判包括：

• 把“连接很多”直接等同于“业务并发很高”；
• 把 state = 'active' 误认为 Backend 正在消耗 CPU；
• 看到客户端超时就断言 SQL 已回滚；
• 把 shared_buffers 命中率当作磁盘是否繁忙的唯一证据；
• 把 Background Writer、Checkpointer、WAL Writer 的职责混为一谈；
• 认为 COMMIT 必须把所有脏数据页立即写进数据文件；
• 将 PostgreSQL 18 AIO 理解成“所有 SQL 都自动异步并更快”；
• 通过提高 max_connections 解决连接池排队，最终制造进程、内存和调度风暴。

这些误判会直接影响性能、并发控制、故障恢复和事故处置。

1.4 与前后章节的关系

• 前置知识：基本 SQL、事务概念、Go context.Context、Linux 进程和文件系统常识。
• 后续依赖：第 3 章存储页与 Buffer、第 6 章 Planner/Executor、第 9 章 MVCC、第 11 章锁、第 13 章 WAL、第 16 章 pgx 连接池、第 18 章监控与准入控制、第 21—23 章复制与高可用。
• 本章不展开：Tuple 的可见性判定、索引页分裂、行锁实现细节、WAL Record 字段、复制协议细节。

---

2. 可验证的学习目标

完成本章后，你应能：

1. 准确区分 Cluster、Instance、Database、Schema 和 Relation；
2. 画出 Postmaster、Backend、辅助进程、共享内存、操作系统缓存和存储设备的关系图；
3. 使用 pg_backend_pid()、pg_stat_activity 和 ps 将客户端连接映射到服务端进程；
4. 按顺序解释 Simple Query Protocol 与 Extended Query Protocol 的消息流；
5. 解释 Parser、Analyzer、Rewriter、Planner、Executor 的职责边界；
6. 说明 Buffer Manager、Storage Manager、Memory Context 分别解决什么问题；
7. 对比一条 SELECT 和一条 UPDATE 在锁、Buffer、WAL 和提交路径上的差异；
8. 从 state、wait_event_type、wait_event 判断 Backend 是运行、等待还是等待客户端；
9. 区分应用 context 取消、PostgreSQL CancelRequest、statement_timeout、lock_timeout、idle_in_transaction_session_timeout 和 [PG17+] transaction_timeout；
10. 解释为什么连接数、goroutine 数、活跃查询数、TPS 和业务请求并发不是同一个指标；
11. 根据实例容量与 SLO 设计连接池上限、队列和 Admission Control；
12. 解释故障转移后为什么旧连接不能迁移，以及为什么 COMMIT 错误可能产生“提交结果不确定”。

---

3. 核心术语

中文名称	英文名称	准确定义	容易混淆的概念	所属层次
数据库集群	Database Cluster	由一个 PostgreSQL Server Instance 管理的一组数据库及其共享的全局对象和物理数据目录，通常由 initdb 创建	Kubernetes Cluster、HA 集群、单个 Database	物理部署
实例	Server Instance	一个 Main Server Process、其 Backend/辅助进程和共享内存组成的运行中服务，通常管理一个 Cluster	Database、进程、云厂商“实例”商品	运行时
数据库	Database	Cluster 内一个具名、逻辑隔离的 SQL 对象集合；客户端连接时选择一个数据库	Cluster、Schema	SQL 命名空间
模式	Schema	Database 内的命名空间；普通对象属于某个 Schema	Database、用户	SQL 命名空间
关系	Relation	PostgreSQL 对表、索引、序列、物化视图等具名关系对象的通用内部称呼	仅等同于 Table	系统目录/存储
主服务进程	Main Server Process / Postmaster	监听连接、启动和监督辅助进程、为客户端创建 Backend 的父进程；“postmaster”是历史与源码中的常用称呼	Backend Process	进程管理
后端进程	Backend Process	为一个客户端会话服务的 PostgreSQL 进程；一个连接在其生命周期内绑定一个 Backend	应用后端服务、并行 Worker	会话/执行
前端	Frontend	使用 PostgreSQL 协议连接服务端的客户端或驱动，例如 pgx	Web Frontend	协议
共享内存	Shared Memory	实例内多个 PostgreSQL 进程共同访问的内存区域	每个 Backend 的私有内存、OS Page Cache	进程间共享
共享缓冲区	shared_buffers	PostgreSQL 管理的数据页缓冲池；缓存 relation block，并维护 pin、引用与脏页状态	OS Page Cache、work_mem	缓存/存储
WAL 缓冲区	WAL Buffers	尚未写入 WAL 段文件的 WAL 数据所在共享内存缓冲区	shared_buffers、WAL 文件	持久化
重量级锁表	Lock Table	共享内存中的锁管理结构，主要管理 relation/object/advisory 等锁及等待队列	行版本上的行锁信息、LWLock	并发控制
进程数组	ProcArray	记录活跃 Backend/事务相关状态的共享结构，支持运行事务判定与快照构造	pg_stat_activity、操作系统进程表	MVCC/事务
轻量级锁	Lightweight Lock / LWLock	保护 PostgreSQL 内部共享数据结构的短期同步原语	SQL 层表锁、行锁	内核同步
Buffer Manager	Buffer Manager	将逻辑 relation block 映射到 shared_buffers 中的 Buffer，负责查找、装载、pin、脏页标记和替换协调	Storage Manager、OS Page Cache	存储访问
Storage Manager	Storage Manager / smgr	PostgreSQL 关系物理存储操作的抽象接口，负责 relation 文件的创建、扩展、读写和截断等分派	Buffer Manager、文件系统	存储抽象
内存上下文	Memory Context	Backend 私有的层次化内存生命周期管理机制，可按查询、Portal、事务等粒度整体释放	共享内存、Go Context	进程私有内存
后台写进程	Background Writer	持续、平滑地将部分脏共享缓冲区写给操作系统，减少 Backend 突发写盘压力	Checkpointer、WAL Writer	辅助进程
检查点进程	Checkpointer	执行 Checkpoint，确保检查点前要求的数据页和 WAL 达到恢复所需状态	Background Writer	恢复/持久化
WAL 写进程	WAL Writer	周期性将 WAL Buffers 中的数据写入 WAL 文件	Checkpointer、Archiver	WAL
自动清理启动器/工作进程	Autovacuum Launcher / Worker	Launcher 调度，Worker 对数据库中的表执行自动 VACUUM/ANALYZE 等维护	Background Writer	维护
归档进程	Archiver	在归档开启时将已完成 WAL 段复制到归档目标	WAL Sender	备份/PITR
WAL 发送/接收进程	WAL Sender / Receiver	Sender 从上游流式发送 WAL；Receiver 在 Standby 接收 WAL	Archiver、逻辑复制 Worker	复制
简单查询协议	Simple Query Protocol	一个 Query 消息携带 SQL 文本，服务端返回结果并以 ReadyForQuery 收尾	SQL 简单、没有预编译	协议
扩展查询协议	Extended Query Protocol	使用 Parse、Bind、可选 Describe、Execute、Sync 等消息，将语句、参数、Portal 和执行分离	SQL PREPARE 命令	协议
Prepared Statement	Prepared Statement	Parse 后保存在会话中的已分析语句对象，可重复 Bind 参数	Portal、SQL PREPARE 与驱动缓存	协议/会话
Portal	Portal	Prepared Statement 绑定参数与结果格式后形成的可执行对象，可支持分批获取结果	Cursor、Prepared Statement	协议/执行
等待事件	Wait Event	Backend 当前等待的具体资源或活动，由 wait_event_type 与 wait_event 表示	state	观测
异步 I/O	Asynchronous I/O / AIO	[PG18] PostgreSQL 内部可同时准备、提交和完成多个 I/O 请求的框架	Go async、所有 I/O 非阻塞	I/O 子系统
准入控制	Admission Control	在资源饱和前限制进入数据库的并发工作量，并通过有界队列、拒绝或降级形成背压	仅设置 max_connections	架构/流控

---

4. 整体心智模型

4.1 PostgreSQL 进程、共享内存与操作系统架构图

4.2 数据流

典型读取数据流为：

客户端参数
  → Backend 协议解码
  → Parser / Analyzer / Rewriter / Planner
  → Executor 请求 relation block
  → Buffer Manager 查 shared_buffers
  → 未命中时由 Storage Manager 发起文件 I/O
  → OS Page Cache 或存储设备返回数据
  → 数据页进入 shared_buffers
  → Executor 形成结果行
  → 协议编码
  → 网络返回客户端

典型更新数据流在读取路径之外增加：

新行版本/索引变化
  → 数据页在 shared_buffers 中变脏
  → WAL Record 写入 WAL Buffers
  → WAL 写入/刷盘
  → COMMIT 响应
  → 数据脏页可在之后由 Backend、Background Writer 或 Checkpointer 写出

关键结论：提交通常依赖 WAL 持久化，而不是要求所有修改过的数据页在提交瞬间都写入数据文件。 这正是 WAL 能兼顾性能与崩溃恢复的基础。

4.3 控制流

• Main Server Process 接受新连接并建立对应 Backend；
• Backend 负责当前会话的认证后协议、事务状态和 SQL 执行；
• Planner 决定“怎么执行”，Executor 按计划驱动各执行节点；
• Buffer Manager 决定数据块是否已在 PostgreSQL 缓冲池中；
• Storage Manager 把关系存储请求映射到物理文件操作；
• Checkpointer、WAL Writer、Autovacuum 等辅助进程在后台推进系统级工作；
• 应用连接池决定请求何时获得一个会话，是数据库之前的第一道准入边界。

4.4 状态变化

客户端 Backend 的典型状态不是单一“忙/闲”，而是两个维度：

1. state：会话处于 active、idle、idle in transaction、idle in transaction (aborted) 等哪种 SQL 生命周期状态；
2. wait_event_type / wait_event：当前是否在等待锁、I/O、客户端、超时、LWLock 等。

因此：

• state = 'active' 且 wait_event_type = 'Lock'：SQL 活跃，但正在等待锁，不一定消耗 CPU；
• state = 'active' 且 wait_event = 'PgSleep'：正在执行 pg_sleep，但实际处于定时等待；
• state = 'idle' 且 wait_event = 'ClientRead'：Backend 已准备好，正在等客户端发下一条消息；
• state = 'idle in transaction'：客户端停在开放事务中，可能继续持锁并保留旧快照，生产风险高。

4.5 故障路径

故障点	直接表现	传播方向	关键风险
连接池耗尽	Acquire 等待、请求超时	应用内部	队列放大、P99 上升
网络中断	读写错误、连接关闭	客户端与 Backend	SQL 是否已执行可能不确定
锁等待	Backend active + Lock wait	数据库内部	阻塞链、重试风暴
存储抖动	I/O wait、Checkpoint/WAL 延迟	OS → PostgreSQL	全局尾延迟上升
WAL 刷盘失败	提交失败、严重时 PANIC	存储 → 实例	可用性与恢复
Backend 崩溃	单会话中断，主进程可能触发实例重启以保护共享状态	进程 → 实例	全部连接重建
Primary 故障转移	旧连接全部失效	HA 层 → 应用	RTO、提交结果不确定
客户端取消竞态	客户端已返回，服务端短暂仍运行或取消了当前语句	应用 → 协议 → Backend	误判回滚、重复执行

---

5. 使用方式

5.1 确认对象层次与当前会话

SELECT
    current_database()                  AS database_name,
    current_schema()                    AS current_schema,
    current_user                        AS current_user,
    session_user                        AS session_user,
    pg_backend_pid()                    AS backend_pid,
    inet_server_addr()                  AS server_addr,
    inet_server_port()                  AS server_port,
    inet_client_addr()                  AS client_addr,
    inet_client_port()                  AS client_port,
    current_setting('cluster_name', true) AS cluster_name;

查看 Database 和 Schema：

SELECT datname, datallowconn, datconnlimit
FROM pg_database
ORDER BY datname;

SELECT schema_name, schema_owner
FROM information_schema.schemata
ORDER BY schema_name;

查看 Relation：

SELECT
    c.oid::regclass AS relation,
    n.nspname       AS schema_name,
    c.relkind,
    c.relpersistence
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;

relkind 不只表示普通表；它还可表示索引、序列、视图、物化视图、分区表等。这也是 Relation 不应被简单翻译成“表”的原因。

5.2 查看实例进程与会话

SELECT
    pid,
    backend_type,
    datname,
    usename,
    application_name,
    client_addr,
    backend_start,
    xact_start,
    query_start,
    state_change,
    state,
    wait_event_type,
    wait_event,
    left(query, 200) AS query
FROM pg_stat_activity
ORDER BY backend_type, pid;

pg_stat_activity 一行代表一个 Server Process 的活动信息，不应只筛选 state = 'active' 后就下结论。排障时至少一起查看：

• backend_type：普通客户端 Backend、WAL Sender、Autovacuum Worker 等；
• query_start：当前或最近一条查询何时开始；
• state_change：进入当前 state 的时间；
• xact_start：事务何时开始；
• wait_event_type / wait_event：当前在等什么；
• application_name：请求来自哪个服务或作业。

[PG17+] 可以把等待事件与说明关联：

SELECT
    a.pid,
    a.state,
    a.wait_event_type,
    a.wait_event,
    w.description
FROM pg_stat_activity AS a
LEFT JOIN pg_wait_events AS w
  ON w.type = a.wait_event_type
 AND w.name = a.wait_event
WHERE a.wait_event IS NOT NULL
ORDER BY a.pid;

5.3 查看共享内存和关键参数

SHOW shared_buffers;
SHOW wal_buffers;
SHOW max_connections;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_io_concurrency;

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'wal_buffers',
    'max_connections',
    'work_mem',
    'effective_io_concurrency',
    'maintenance_io_concurrency',
    'io_method',
    'io_workers'
)
ORDER BY name;

pg_shmem_allocations 可用于观察主要共享内存分配；通常需要较高权限：

SELECT
    name,
    pg_size_pretty(size)           AS requested_size,
    pg_size_pretty(allocated_size) AS allocated_size
FROM pg_shmem_allocations
ORDER BY allocated_size DESC
LIMIT 30;

注意：

• shared_buffers 是实例共享内存，不是每个连接各一份；
• work_mem 是每个执行节点可能使用的私有工作内存，并会被并发查询、并行 Worker 和多个排序/哈希节点放大；
• 提高 max_connections 可能同时放大 Backend 私有内存、锁表规模、调度开销和峰值 work_mem 风险。

5.4 查看 Backend 私有 Memory Context

当前会话可查看自己的内存上下文：

SELECT
    name,
    ident,
    parent,
    level,
    total_bytes,
    free_bytes,
    used_bytes
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC
LIMIT 30;

Memory Context 的核心价值是按生命周期批量释放：查询结束时可重置查询上下文，事务结束时可释放事务上下文，无需逐对象手工 free。它属于每个进程的私有内存管理机制，不是共享缓冲区。

5.5 协议选择与 pgx API

pgx 默认使用 Extended Query Protocol，并可自动准备和缓存语句。常用入口：

// 参数使用 $1，禁止拼接用户输入。
row := pool.QueryRow(ctx,
    `SELECT id, status FROM orders WHERE id = $1`,
    orderID,
)

连接配置中可控制默认执行模式：

cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}

// 默认通常是 QueryExecModeCacheStatement，即扩展协议与语句缓存。
cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement

选择原则：

需求	推荐方式	说明
普通参数化查询	pgx 默认 Extended Protocol	参数独立编码，支持类型与语句缓存
同一连接重复执行固定 SQL	允许 pgx 自动语句缓存	减少重复 Parse/Describe 成本
执行可能含多条 SQL 的管理脚本	谨慎使用 Simple Protocol	事务和错误边界更复杂，不用于用户输入
经 Transaction Pooling 代理且 Prepared Statement 兼容性受限	评估 QueryExecModeExec、代理版本和配置	不要未经验证直接切到字符串插值式 Simple Protocol
降低多条命令网络往返	Batch/Pipeline	牺牲错误处理简单性，需明确 Sync 边界

安全要求：

• 数据值必须通过 $1、$2 等参数传递；
• 表名、列名等标识符不能作为普通参数，必须由可信白名单映射；
• 不将用户输入拼接进 SQL；
• 设置 application_name，方便将 Backend 映射到服务和版本；
• 连接串从环境或 Secret 管理系统读取，不写进源码和日志；
• 生产连接启用合适的 TLS 和身份认证，不把 sslmode=disable 当作默认方案。

5.6 PostgreSQL 18 AIO 的观察方式

[PG18] AIO 位于 Buffer Manager/Storage Manager 与操作系统 I/O 之间，使部分路径可以准备并提交多个 I/O 请求，而不是每次只等待一个请求完成。它不是客户端协议异步，也不表示每个 SQL 都并行执行。

SHOW io_method;
SHOW io_workers;
SHOW effective_io_concurrency;
SHOW maintenance_io_concurrency;

SELECT
    pid,
    io_id,
    io_generation,
    state,
    operation,
    off,
    length,
    target,
    target_desc,
    f_sync,
    f_buffered,
    result
FROM pg_aios
ORDER BY pid, io_id;

pg_aios 只列出当前正在使用的 I/O handle，可能瞬间为空；它主要用于内核开发和高级调优。权限通常需要超级用户或 pg_read_all_stats。

---

6. 底层原理

6.1 连接建立：从监听 Socket 到 Backend

连接建立的大致时间线：

1. Main Server Process 监听 TCP 或 Unix Domain Socket；
2. 客户端建立网络连接并发送 StartupMessage；
3. 服务端执行认证与启动参数处理；
4. Main Server Process 为该连接创建专属 Backend；
5. Backend 发送 ParameterStatus 等启动信息；
6. Backend 发送 BackendKeyData，客户端保存 PID 与取消密钥；
7. Backend 发送 ReadyForQuery；
8. 此后该 TCP 连接、会话状态、临时表、Prepared Statement、GUC 和事务都绑定到这个 Backend。

这一模型通常称为 process-per-connection。需要补充两个边界：

• 一个会话可按需使用 Parallel Worker，因此“一条查询只有一个进程”并非总是成立；
• 外部连接池或 Transaction Pooling 代理可以让多个逻辑客户端分时复用较少的 Server Connection，但每个实际 Server Connection 在某一时刻仍对应一个 Backend。

6.2 Simple Query Protocol

特点：

• 一个 Query 消息可包含一条或多条 SQL；
• 服务端先解析整个 SQL 字符串，再按语句执行；
• 没有独立的参数 Bind 阶段；
• 多语句错误边界、隐式事务边界和结果读取更复杂；
• 从 PostgreSQL 13 起，Simple Query 消息中的多条语句按每条语句分别应用 statement_timeout；
• 适合交互式工具与可信管理脚本，不应被用来拼接用户输入。

6.3 Extended Query Protocol

典型消息序列：

Parse → Bind → [Describe] → Execute → Sync

职责：

• Parse：提交一条 SQL 和可选参数类型，创建 Prepared Statement；一次 Parse 只允许一条 SQL；
• Bind：把参数值、参数格式、结果格式绑定到 Prepared Statement，创建 Portal；规划通常在这一阶段或执行前发生；
• Describe：可选，询问 Prepared Statement 参数或 Portal 结果列元数据；
• Execute：执行 Portal，可设置最多返回多少行；
• Sync：建立同步点，结束一个隐式事务边界，并让错误后的协议恢复到可预测状态；
• Close：释放 Prepared Statement 或 Portal，可按需要发送。

发生错误后，Backend 在 Extended Protocol 中通常会忽略后续消息，直到遇到 Sync，然后返回 ReadyForQuery。这也是自己实现低层协议或 Pipeline 时必须严谨处理 Sync 的原因。

Extended Protocol 的主要收益：

• 参数和值分离，避免 SQL 注入式字符串拼接；
• 可使用二进制参数与结果格式；
• Prepared Statement 可复用解析/分析结果，并可能复用计划；
• Batch/Pipeline 可降低网络往返。

主要代价：

• 会话状态更多；
• Prepared Statement 与代理、DDL、search_path、类型变化之间存在兼容性问题；
• Pipeline 中错误传播与结果对应更复杂；
• 参数敏感计划可能需要后续章节讨论的 generic/custom plan 权衡。

6.4 SQL 处理链：Parser 到 Executor

SQL 文本
  ↓
Raw Parser
  - 只按语法生成 Raw Parse Tree
  ↓
Analyzer / Transform
  - 解析对象名、列名、类型、函数和运算符
  - 查询系统目录
  - 生成语义明确的 Query Tree
  ↓
Rewriter
  - 展开视图
  - 应用 Rule System
  - 可能生成一个或多个 Query Tree
  ↓
Planner / Optimizer
  - 枚举访问路径和连接顺序
  - 根据统计信息与成本模型选择 Plan Tree
  ↓
Executor
  - 初始化执行状态
  - 按计划节点拉取/生成 Tuple Slot
  - 访问 Buffer、锁和存储
  - 发送结果或修改数据

注意：DDL、事务控制等 Utility Statement 的处理路径与普通可规划 DML 不完全相同，本章只建立主要位置关系。

6.5 Buffer Manager、Storage Manager 与 OS 的调用边界

假设 Executor 要读取 relation 的第 N 个 block：

1. Executor/访问方法向 Buffer Manager 请求 (relation, fork, block number)；
2. Buffer Manager 在共享 Buffer Table 中查找该 BufferTag；
3. 命中时增加 pin，确保使用期间 Buffer 不被替换；
4. 未命中时选择可替换 Buffer，必要时先处理其脏页；
5. Buffer Manager 通过 Storage Manager 请求物理 relation 文件的对应范围；
6. [PG18] 某些读取路径可通过 AIO 框架批量准备/提交请求；
7. 操作系统可能从 Page Cache 返回，也可能访问块设备；
8. 数据进入 shared_buffers，校验并设置 Buffer 状态；
9. Executor 读取页内数据；
10. 使用完毕后释放 pin。

这三层的职责不同：

层	负责	不负责
Buffer Manager	PostgreSQL Buffer 身份、pin、脏页、替换协调	SQL 优化、文件系统持久化实现
Storage Manager	Relation 物理文件操作抽象与分派	Buffer 替换、OS 调度
操作系统	进程调度、Page Cache、文件系统、设备 I/O、系统调用	MVCC、SQL 锁、WAL 逻辑顺序

shared_buffers 与 OS Page Cache 不是简单重复：前者知道 PostgreSQL 页身份、事务与 Buffer 状态；后者负责通用文件缓存和设备访问。一次 shared_buffers miss 仍可能在 OS Page Cache 命中，因此不能只凭 PostgreSQL Buffer 指标断言发生了物理磁盘读取。

6.6 完整 SQL 请求时间线

整条链路的端到端延迟可拆成：

连接池排队
+ 网络往返
+ 协议编码/解码
+ Parse/Analyze/Rewrite/Plan
+ Executor CPU
+ 锁等待
+ Buffer/OS/设备 I/O
+ WAL 写入与刷盘
+ 同步复制等待（如配置）
+ 结果传输

仅观察 SQL 执行时间会漏掉连接池排队和客户端网络时间；仅观察 HTTP 延迟又无法区分数据库内部等待。

6.7 SELECT 与 UPDATE 的路径差异

项目	SELECT	UPDATE
关系锁	通常获取读所需关系锁	获取写所需关系锁，并可能等待行/事务
快照	读取可见版本	既读取目标版本，也进行更新并发检查
Buffer	主要读；也可能因内部维护产生脏页	读取并修改数据/索引 Buffer
WAL	普通业务读取通常不为结果数据生成 WAL	数据与索引变化通常生成 WAL
索引	选择访问路径	除用于定位外，必要时还需维护索引项
提交成本	单语句隐式事务结束，通常没有业务数据 WAL flush 主成本	事务提交需要满足 WAL 持久性策略
后台影响	大扫描挤压缓存、产生临时文件或 I/O	增加 WAL、Checkpoint、Vacuum 和复制压力
失败语义	可重试通常较简单，但仍需考虑事务上下文	可能已部分执行后回滚；COMMIT 断线可能结果不确定

6.8 WAL、Checkpoint 与提交的最小正确模型

对一条成功提交的 UPDATE，可用以下最小模型理解：

1. Backend 修改 shared_buffers 中的数据页，页面变脏；
2. Backend 生成描述该修改的 WAL Record，放入 WAL Buffers；
3. 提交时写入 Commit Record；
4. 在默认同步提交语义下，相关 WAL 必须达到要求的持久化位置后才能向客户端确认；
5. 如果配置同步复制，还可能等待指定 Standby 达到相应确认级别；
6. 数据页不必在此刻写入数据文件；
7. Checkpoint 保证恢复起点和数据页持久化推进；
8. 崩溃后通过数据文件中的页加 WAL 重放恢复一致状态。

因此：

• WAL Writer 写 WAL，不等于 Checkpointer 写数据页；
• Background Writer 主要平滑脏页写出，不定义事务提交点；
• Checkpoint 太频繁会增加数据页写放大与尾延迟；
• COMMIT 返回之前网络断开时，客户端可能不知道 Commit Record 是否已持久化。

6.9 取消与超时的状态机

机制	发起位置	作用对象	服务端动作	连接是否通常保留	常见 SQLSTATE/客户端错误	关键语义
Go context 取消，pgx 默认行为	客户端	当前阻塞调用	pgx 立即返回，并在多数情况下关闭底层连接；服务端稍后感知连接关闭	通常不保留	常见为 context deadline exceeded、连接关闭类错误；不保证有 PgError	客户端本地 deadline，不等于收到服务端取消确认
PostgreSQL CancelRequest	客户端通过新连接发送 PID+secret	目标 Backend 当前语句	匹配成功后中断当前查询	通常保留；显式事务会进入失败状态	常见 57014 query_canceled	发送成功没有响应 ACK，且存在完成/取消竞态
statement_timeout	服务端	当前语句	语句超时后取消	保留；事务内当前事务进入失败状态	57014	从命令到达服务端开始计时；Extended Protocol 有明确消息边界
lock_timeout	服务端	每次锁获取等待	仅在等待锁超过阈值时取消语句	保留；事务内进入失败状态	常见 55P03 lock_not_available	不限制 CPU 或 I/O 执行时间；若不小于 statement_timeout，通常后者先触发
idle_in_transaction_session_timeout	服务端	开放事务中等待客户端的会话	终止整个会话	不保留	25P03	释放会话锁并防止长期旧快照；不是慢 SQL 超时
[PG17+] transaction_timeout	服务端	超过总事务时长的会话	终止整个会话	不保留	25P04	覆盖显式和隐式事务；Prepared Transaction 不受此限制

时间参数建议按层级设计，而不是全部设为同一个值：

数据库 lock_timeout
    < 数据库/会话 statement_timeout
    ≤ 应用数据库操作 context deadline
    < 上游 HTTP/RPC deadline

这不是固定公式。必须预留网络、序列化、错误传递和清理时间，并对 OLTP、报表、迁移、维护作业使用不同策略。statement_timeout、lock_timeout、transaction_timeout 不建议不加区分地全局写进 postgresql.conf 影响所有会话。

---

7. 内部数据结构和状态

7.1 本章涉及的数据结构地图

结构/状态	大致归属	本章需要掌握的作用	本章不深入
PGPROC / ProcArray	共享内存	表示活跃进程及事务相关状态，支持快照和运行事务判断	XID 数组优化、子事务细节
LOCK / PROCLOCK / LOCALLOCK	共享与 Backend 私有	表示被锁对象、持有者关系及本地缓存	完整锁模式冲突矩阵
Buffer Descriptor + BufferTag	共享内存	将 relation/fork/block 映射到共享 Buffer，维护 pin、dirty 等状态	时钟扫描算法细节
WAL Buffers	共享内存	暂存 WAL，再由 Backend/WAL Writer 等写出	WAL Record 二进制布局
Memory Context Tree	Backend 私有	按查询、Portal、事务等生命周期管理内存	allocator 实现细节
Portal	Backend 私有会话状态	保存绑定参数、结果格式和执行状态	Cursor 完整实现
Prepared Statement	Backend 私有会话状态	保存已解析/分析的语句及相关计划状态	generic/custom plan 细节
pg_stat_activity 状态	统计共享机制	观测 Backend 生命周期和等待	统计快照内部实现
LSN	WAL 逻辑位置	表示 WAL 中的位置，用于提交、恢复和复制进度	具体编码格式

7.2 ProcArray 的位置

ProcArray 不是 pg_stat_activity 的同义词。它是内核共享结构，维护活跃 Backend 与事务相关信息，主要用于回答：

• 哪些事务仍在运行；
• 构造快照时哪些 XID 应视为进行中；
• Standby 上已知分配事务等状态；
• 事务结束时如何从活跃集合移除。

高并发、频繁快照和大量连接会增加对相关共享结构和 LWLock 的访问，因此“空闲连接几乎零成本”不是严格成立；不过真正的瓶颈仍需通过等待事件和测量确认，不能仅凭连接数猜测 ProcArray 争用。

7.3 Lock Table 的位置

重量级锁管理通常包含：

• 每个被锁对象的共享 LOCK 结构；
• 每个持有者与锁对象组合的 PROCLOCK；
• Backend 私有的 LOCALLOCK 缓存；
• 某些 relation lock 的 fast-path 优化。

需要避免的误解：每一条行锁都不是简单地占据一条共享 Lock Table 记录。 行级锁信息主要与 Tuple/事务状态相关，等待方可能通过事务 ID 等锁机制排队。完整细节留到锁与 MVCC 章节。

7.4 Buffer 状态

一个共享 Buffer 至少需要理解这些状态：

• 有效：其中有可用的数据页；
• Pinned：当前 Backend 正在使用，不能被替换；
• Dirty：内存内容比持久化数据文件新，需要后续写出；
• I/O in progress：正在装载或写出；
• 引用/使用计数：参与替换决策。

“Buffer dirty”不等于“事务未提交”；未提交和已提交修改都可能对应脏页，事务可见性与恢复依赖 Tuple 状态、WAL 和事务状态，而不是单靠 dirty bit。

7.5 Memory Context 状态

Backend 常见生命周期层次包括长期上下文、消息上下文、事务上下文、Portal 上下文和每次查询的 Executor 上下文。核心模式是：

创建 Context
  → 在 Context 中批量分配对象
  → 查询/事务/Portal 生命周期结束
  → Reset 或 Delete 整个 Context

这减少了异常路径上的逐对象清理复杂度。若扩展把短命对象错误分配到长寿命 Context，就会表现为 Backend RSS 持续增长；这与 shared_buffers 增长是完全不同的问题。

7.6 状态与时间字段

字段	含义	常见误判
backend_start	Backend 建立时间	不是事务开始时间
xact_start	当前事务开始时间	idle 时通常为空；长事务排查关键
query_start	当前或最近查询开始时间	idle 时仍可显示上一条查询时间
state_change	当前 state 开始时间	不等于 SQL 开始时间
state	SQL/会话状态	active 不代表正在占 CPU
wait_event_type	等待类别	为空不代表必然占 CPU，采样有瞬时性
wait_event	具体等待事件	需结合类型、查询、锁链和 OS 指标解释

统计视图是观测快照，不同字段可能在极短瞬间来自相邻状态；生产排障应连续采样，而不是用一次查询建立绝对因果。

---

8. 场景和选型决策

业务场景	推荐方案	不推荐方案	原因	性能代价	并发代价	一致性代价	高可用代价	运维复杂度
低延迟 OLTP	有界 pgxpool、短事务、参数化 Extended Protocol、会话级超时	每请求新建连接	连接建立和进程创建成本高	连接池占用固定资源	池满时排队，需要背压	无直接代价	故障后需重建池	中
大量短查询	语句缓存、Batch/Pipeline 经压测启用	把所有 SQL 合并成多语句 Simple Query	Extended Protocol 可减少重复解析并安全传参	缓存占会话内存	Prepared Statement 与代理需兼容	错误边界更清晰	重连后缓存重建	中
长报表查询	独立角色/池/资源队列，单独 timeout	与 OLTP 共用同一池和全局超时	防止报表占满 Backend 与 I/O	资源隔离有容量成本	减少 OLTP 排队	读副本可能有延迟	路由到副本需接受一致性差异	高
高峰突发流量	Admission Control、有界队列、快速拒绝/降级	无限 goroutine 等连接	保护数据库不进入过载崩溃区	可能牺牲部分吞吐	限制活跃查询，降低排队瀑布	无直接代价	故障恢复时避免重连风暴	中
经 PgBouncer Transaction Pooling	验证 Prepared Statement 支持，按代理版本选择 exec mode	假设会话状态永久绑定	Transaction Pooling 可能切换 Server Connection	可能失去部分语句缓存收益	可显著减少服务端连接	会话变量/临时表语义受限	代理成为额外故障域	高
高写入系统	控制并发写、监控 WAL/Checkpoint/复制	只提高连接数求吞吐	写路径受 WAL、锁、存储上限约束	WAL 与脏页写放大	热点与 WAL 争用	异步复制存在 RPO	同步复制增加提交延迟	高
频繁取消的交互查询	评估 pgx CancelRequest watcher 与服务端 timeout	每次取消都默认关闭连接且无容量评估	可减少连接重建，但需理解竞态	CancelRequest 额外连接/消息	保留 Backend，降低连接风暴	取消不等于事务一定未提交	故障时仍需重连	中
Schema 迁移/维护	专用连接池、较长 timeout、明确锁超时	使用线上 API 池执行 DDL	DDL 锁与超时策略不同	占用维护资源	可能阻塞业务	失败需事务化设计	发布失败影响 RTO	高

8.1 连接池容量的正确问题

不要问“max_connections 是 500，所以每个服务池设 500 可以吗”。应问：

1. 在目标 SLO 下，数据库可同时高效运行多少活跃查询？
2. CPU、内存、I/O、WAL 和锁热点中，先饱和的是哪一个？
3. 所有 Pod、所有服务、作业、管理连接和复制连接的上限之和是多少？
4. 池满时排队多久，何时拒绝？
5. 故障转移或扩容时是否会同时重连？

容量预算可先用以下形式审计：

理论应用连接上限
= Σ（服务实例数 × 每实例 MaxConns）
+ 批处理/迁移连接
+ 运维与应急保留
+ 复制/监控等专用连接

这个总数只是连接槽位预算，不是数据库可以高效承受的活跃查询数。真实 MaxConns 必须通过工作负载压测，使连接池排队、数据库 CPU/I/O、锁等待和 P95/P99 同时满足目标。

---

9. 高性能分析

9.1 CPU

Backend 是操作系统进程。活跃 Backend 远多于 CPU 核心时，可能出现：

• run queue 增长和上下文切换；
• CPU cache 命中下降；
• 各 Backend 在共享数据结构上竞争；
• 单查询变慢，连接占用时间更长，池排队进一步增长；
• 吞吐不再上升，但 P95/P99 急剧恶化。

因此性能容量常由“高效活跃查询数”决定，而不是由 max_connections 决定。

9.2 内存

实例内存至少包含：

共享内存
+ 所有 Backend 基础私有内存
+ 并发执行节点的 work_mem
+ maintenance_work_mem
+ 并行 Worker
+ 操作系统 Page Cache
+ 监控、代理与其他进程

work_mem 不是“每连接最多一份”，同一查询的多个 Sort/Hash 节点、并行 Worker 和多个并发查询都可能各自使用。连接数上调会放大最坏情况内存风险。

9.3 shared_buffers 与 OS Page Cache

官方文档常给出专用数据库服务器从内存约 25% 开始评估 shared_buffers 的经验起点，并提醒很大的比例未必更好，因为 PostgreSQL 依赖 OS Cache。该经验不是通用固定值，容器限额、Huge Pages、工作集大小、存储延迟和同机服务都会改变结果。

诊断时同时看：

• pg_stat_database.blks_hit / blks_read；
• [PG16+] pg_stat_io；
• EXPLAIN (ANALYZE, BUFFERS, ...)；
• iostat、vmstat、进程 RSS、Page Fault；
• 冷缓存与热缓存分别压测。

9.4 随机 I/O、顺序 I/O 与 [PG18] AIO

AIO 的价值主要在能预先发出多个 I/O，隐藏单次设备等待，适合某些顺序扫描、Bitmap Heap Scan、VACUUM 等读取模式。它不能：

• 消除物理 I/O；
• 修复错误执行计划；
• 自动让随机单行 OLTP 查询更快；
• 替代索引和数据模型；
• 消除 OS、文件系统和云盘限速。

观察 AIO 时记录：

• io_method、io_workers、effective_io_concurrency；
• pg_aios 是否出现请求及其状态；
• pg_stat_io 读次数、字节与时间；
• 操作系统磁盘队列和吞吐；
• 同一数据集、同一缓存状态下的 P50/P95/P99。

9.5 网络往返与协议

对于单行短查询，网络 RTT 可能比 Executor 时间更大。Extended Protocol 未必每次都需要五个独立 RTT，驱动可将多条消息打包；Prepared Statement 缓存和 Batch/Pipeline 可进一步减少往返。但代价是：

• Pipeline 错误处理复杂；
• 批量过大时首行延迟增加；
• 结果集过大会消耗客户端与服务端内存/网络；
• 连接在读完所有 Rows 前通常不能复用。

Go 代码必须及时 rows.Close() 并检查 rows.Err()，否则连接归还和错误发现都可能延迟。

9.6 WAL、Checkpoint 和写放大

UPDATE 的性能不仅取决于目标行定位：

• 数据页和索引页修改会产生 WAL；
• Full-page image、索引维护和重复更新会增加 WAL；
• Checkpoint 过密会造成大量脏页集中写出；
• 同步复制把远端确认加入提交路径；
• WAL 归档或复制跟不上会占用 pg_wal 空间；
• 后续 VACUUM 还要处理旧版本。

关键指标包括 pg_stat_wal、pg_stat_checkpointer、pg_stat_bgwriter、复制延迟、WAL 目录使用量、存储 fsync 延迟和应用 Commit P99。

9.7 Temporary File

排序、哈希、物化等执行节点超出内存预算时可能写临时文件。临时 I/O 与普通 relation I/O、WAL I/O是不同路径。不要通过无限提高 work_mem 消除临时文件；应根据并发数、执行计划和总内存预算评估。

9.8 吞吐量与尾延迟

一个系统可能 TPS 看似稳定，但用户 P99 已恶化。至少分开测量：

请求总延迟
连接池 Acquire 等待
数据库执行时间
锁等待时间
网络与结果传输
事务 Commit 时间

过载时最危险的模式是：

查询变慢
→ 连接占用更久
→ 池耗尽
→ 请求队列增长
→ deadline 到达后大量取消/重试
→ 新连接与更多 SQL 加剧过载

这要求通过 Admission Control 在过载前限制进入量，而不是继续堆积。

9.9 参数建议的前置数据

任何 shared_buffers、MaxConns、work_mem、AIO 或 Checkpoint 建议都应先记录：

• 数据规模与工作集；
• 行宽、索引数量与数据分布；
• 读写比例和 SQL 类型；
• 并发请求、活跃查询、连接池排队；
• CPU 核数、NUMA、内存和容器限额；
• 文件系统、存储介质、云盘 IOPS/吞吐/延迟；
• 同步复制与归档要求；
• P50/P95/P99、TPS、错误率；
• 冷热缓存状态和测试时长。

没有这些上下文的固定参数值不具备生产可信度。

---

10. 高并发分析

10.1 五个必须分开的指标

指标	定义	例子
应用 goroutine 并发	同时执行或等待的 goroutine 数	1000 个 HTTP handler
连接数	已建立到 PostgreSQL 的 Server Connection 数	pgxpool 总连接 50
活跃查询数	当前处于执行/等待状态的 SQL 数	20 个 active，其中 12 个等锁
TPS	单位时间提交的事务数	3000 tx/s
排队请求数	尚未获得连接或准入许可的请求数	200 个 Acquire waiters

一个有 1000 个并发请求的服务可以只使用 30 个数据库连接；一个连接可以在一秒内完成许多事务；一个长事务可以持有连接却几乎没有 TPS；一条并行查询又可能使用多个 Worker。

10.2 为什么连接数量不等于业务并发量

1. 池中有 Idle Connection，不代表正在处理请求；
2. 多个 goroutine 会在连接池前排队；
3. 一个业务请求可能串行执行多条 SQL；
4. 一个事务在业务计算期间持续占用连接；
5. 一个连接同一时刻通常串行处理协议命令；
6. Parallel Query 可让一个请求使用多个进程；
7. 多个服务副本各有独立池，总连接数是乘法；
8. Transaction Pooling 可让更多逻辑会话复用更少 Server Connection；
9. TPS 取决于每个事务耗时，不等于连接数；
10. 锁等待中的连接占用槽位，却不产生有效吞吐。

10.3 为什么 process-per-connection 需要池与准入

每个连接带来：

• 一个 Backend Process；
• 进程栈与私有内存；
• 会话状态和缓存；
• ProcArray、锁管理和统计相关开销；
• OS 调度对象；
• 潜在的每查询 work_mem；
• 故障时重连和认证成本。

连接池解决“复用昂贵连接”；Admission Control 解决“限制同时进入的工作量”。两者不是一回事：池上限仍可能高于数据库高效并发，且池等待队列也可能无限增长。因此应用还应：

• 使用有界 goroutine/worker pool；
• 给 Acquire 设置 deadline；
• 监控 AcquireDuration、EmptyAcquireWaitTime、CanceledAcquireCount；
• 在入口按租户、接口或工作负载隔离；
• 超载时快速失败、降级或排队到外部消息系统；
• 对连接生命周期使用随机抖动，避免同时重建。

10.4 MVCC、锁和长事务在本章的位置

• MVCC 让读写在很多场景下并发，但不消除所有锁；
• UPDATE 同一热点行仍会串行等待；
• 大量活跃事务会增加快照与 ProcArray 工作；
• 长事务会长期占用连接、锁和旧快照；
• idle in transaction 尤其危险，因为它不做有效工作却可能阻塞清理；
• 热点索引页、WAL 插入、Buffer Content Lock 等内部争用需要通过等待事件和后续章节分析；
• 死锁由锁依赖环产生，连接池大小不能修复业务锁顺序错误。

10.5 事务边界

生产事务应尽量只包围必须原子完成的数据库操作：

Acquire connection
→ BeginTx
→ 执行数据库读写
→ Commit
→ Release connection
→ 再调用无关慢外部服务

不要在事务中等待用户输入、HTTP API、文件上传或消息系统长时间响应。否则连接、锁和快照会被业务等待时间放大。

10.6 重试风暴与幂等

取消、死锁、序列化失败和故障转移都可能触发重试。如果每个请求立即无限重试：

故障导致容量下降
→ 请求失败
→ 所有客户端同时重试
→ 流量倍增
→ 数据库更慢
→ 更多失败与重试

必须有：

• 只对明确可重试错误重试；
• 最大次数；
• 指数退避与随机抖动；
• 服从上游 context；
• 重试完整事务；
• 幂等键或唯一约束；
• 对 Commit 结果不确定进行业务对账，而不是盲目重放。

---

11. 高可用分析

本章与高可用的关系主要是连接、进程、WAL 与状态恢复边界，不在此深入复制配置。

11.1 RPO 与 RTO

• 连接池不能决定 RPO；RPO 由 WAL 持久性、备份、归档、同步/异步复制策略决定；
• 连接池和重连策略会显著影响 RTO；
• 异步复制故障转移可能丢失尚未复制的已提交事务；
• 同步复制可降低数据丢失风险，但将副本和网络等待加入 Commit 路径；
• PITR 依赖有效 Base Backup、连续 WAL 归档和恢复演练。

11.2 Primary 与 Standby 的进程位置

• Primary 为每个复制连接运行 WAL Sender；
• Standby 运行 WAL Receiver 接收 WAL，并由恢复相关进程应用；
• Archiver 将完成的 WAL 段送往归档目标，它不是流复制 Sender；
• pg_stat_replication 观察 Primary 上 WAL Sender；
• pg_stat_wal_receiver 观察 Standby 上 Receiver；
• 复制延迟必须同时考虑发送、写入、刷盘、重放和客户端读一致性需求。

11.3 故障转移与旧连接

连接绑定到特定 Instance 的特定 Backend。Primary 切换后：

1. 旧 TCP 连接不会自动迁移到新 Primary；
2. pgxpool 必须发现错误、丢弃旧连接并新建连接；
3. DNS、VIP、代理或多 Host 连接串负责把新连接导向新 Primary；
4. 新连接还应验证目标是否可写，例如使用 target_session_attrs 或 pgx ValidateConnect；
5. 所有会话级状态、临时表、Prepared Statement 和事务状态都需要重建；
6. 应用必须承受一段 Acquire/Connect 错误，并用有界退避避免重连风暴。

11.4 提交结果不确定

最关键的故障窗口：

客户端发送 COMMIT
→ Primary 持久化 Commit Record
→ Primary 发送成功响应
→ 网络在响应到达客户端前中断

客户端只看到错误，但事务可能已经提交。此时：

• 不能因为 Commit 返回错误就断言未提交；
• 自动重试可能造成重复扣款、重复订单或重复消息；
• 应使用业务幂等键、唯一约束、状态查询与对账；
• 驱动的连接检查只能降低某些不确定窗口，不能从根本上消除分布式通信中的歧义。

11.5 Planned Switchover、Unplanned Failover 与 Failback

场景	应用侧重点	数据侧重点
Planned Switchover	先排空流量、缩短连接生命周期、验证新 Primary	确认复制追平、受控提升
Unplanned Failover	快速发现断连、有界重试、避免所有 Pod 同时重连	根据复制模式评估 RPO，执行 Fencing
Failback	明确重新拓扑和连接目标，避免旧连接复活	防止双 Primary，重新同步旧节点

Fencing 用于阻止旧 Primary 在失去仲裁后继续接受写入，是防脑裂的核心。应用连接池本身不能解决脑裂。

11.6 备份与数据恢复验证

共享内存和 Backend 状态在重启后都会消失；真正可恢复的数据来自数据文件、WAL、备份和归档。高可用不等于可恢复：

• 副本可能同步复制逻辑删除或人为错误；
• 必须有独立备份与 PITR；
• 必须在隔离环境执行恢复演练；
• 验证恢复点、数据一致性、应用可登录和关键查询；
• 记录实际 RTO，而不是只记录“备份成功”。

---

12. 三维影响矩阵

维度	相关度	核心收益	主要风险	关键指标
高性能	高	识别连接、协议、规划、Buffer、I/O、WAL 的真实耗时位置	盲目加连接、误调缓存、忽略 OS 与网络	Acquire P95/P99、active queries、CPU run queue、pg_stat_io、WAL/Checkpoint、SQL P99
高并发	高	建立 Backend、ProcArray、锁、事务与连接池的关系	无限 goroutine、长事务、锁队列、重试风暴	pool waiters、AcquiredConns、state/wait event、xact_start、blocking PIDs、TPS
高可用	中	理解连接绑定、WAL 持久化、故障转移与提交歧义	旧连接、重连风暴、脑裂、错误重试	connect error rate、failover RTO、replication lag、WAL archive、ambiguous commits

---

13. 实验

实验一：把客户端连接映射到 Backend Process

1. 实验目标

使用 pg_backend_pid()、pg_stat_activity 与 Linux ps 证明：

• 每个直连 PostgreSQL 的客户端会话绑定一个 Backend PID；
• Backend 的父进程是 Main Server Process；
• SQL 执行、等待和会话关闭会反映到 PostgreSQL 视图与 OS 进程表；
• state 与 wait event 是两个独立观察维度。

2. PostgreSQL 版本要求

PostgreSQL 14—18 均可完成。[PG17+] pg_wait_events 的描述关联仅适用于 17 及以上。本章基线为 PostgreSQL 18。

3. 必要扩展

无。观察其他用户完整查询通常需要超级用户或 pg_read_all_stats；普通用户通常可完整查看自己的会话。

4. 建表和准备数据

在实验数据库中执行：

CREATE SCHEMA IF NOT EXISTS ch1_lab;

CREATE TABLE IF NOT EXISTS ch1_lab.backend_probe (
    id   integer PRIMARY KEY,
    note text NOT NULL
);

INSERT INTO ch1_lab.backend_probe (id, note)
VALUES (1, 'backend-process-probe')
ON CONFLICT (id) DO UPDATE SET note = EXCLUDED.note;

没有建表权限时，可以把后面的查询替换为单独的 SELECT pg_sleep(30);，不影响进程映射结论。

5. Session A：被观察客户端

启动一个 psql：

psql "$DATABASE_URL"

执行：

SET application_name = 'ch1-exp1-session-a';

SELECT
    pg_backend_pid() AS backend_pid,
    current_database(),
    current_user,
    inet_client_addr(),
    inet_client_port();

记下 PID，随后执行长查询：

SELECT id, note, pg_sleep(30)
FROM ch1_lab.backend_probe
WHERE id = 1;

6. Session B：数据库内部观察

在另一个 psql 中持续执行：

SELECT
    pid,
    backend_type,
    datname,
    usename,
    application_name,
    client_addr,
    client_port,
    backend_start,
    xact_start,
    query_start,
    state_change,
    state,
    wait_event_type,
    wait_event,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE application_name = 'ch1-exp1-session-a';

[PG17+] 查看等待事件说明：

SELECT
    a.pid,
    a.state,
    a.wait_event_type,
    a.wait_event,
    w.description
FROM pg_stat_activity AS a
LEFT JOIN pg_wait_events AS w
  ON w.type = a.wait_event_type
 AND w.name = a.wait_event
WHERE a.application_name = 'ch1-exp1-session-a';

7. Session C：操作系统进程观察

必须在 PostgreSQL Server 所在主机或同一 PID namespace 中运行：

PID=<Session_A_输出的_backend_pid>

ps -o pid,ppid,stat,lstart,etime,comm,args -p "$PID"
ps -eo pid,ppid,stat,comm,args | grep '[p]ostgres'

查看 Backend 的父进程：

PPID=$(ps -o ppid= -p "$PID" | tr -d ' ')
ps -o pid,ppid,stat,lstart,etime,comm,args -p "$PPID"

在容器、Kubernetes 或托管数据库中，SQL PID 与宿主机 PID 可能处于不同 PID namespace，或者用户根本无权执行 ps。此时在数据库主机容器内部观察，或仅完成 SQL 侧验证。

8. 明确时间线

时间	Session A	Session B	Session C
T0	建立连接并设置 application_name	—	—
T1	输出 pg_backend_pid()	找到同一 PID，通常为 idle	找到同一 OS 进程
T2	开始 pg_sleep(30)	state = active，等待事件通常为 Timeout/PgSleep	进程仍存在，可能处于睡眠状态
T3	30 秒后返回结果	状态变为 idle，通常等待客户端读取下一条命令	同一 PID 仍存在
T4	执行 \q 关闭会话	对应行消失	对应 Backend 进程退出

9. 哪一步等待

T2 至 T3 期间，Session A 的 SQL 正在执行，但 Backend 通常显示：

state           = active
wait_event_type = Timeout
wait_event      = PgSleep

这证明 active 不等于“正在使用 CPU”。

10. 哪一步失败

正常流程无失败。可选地在 Session B 中执行：

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'ch1-exp1-session-a';

若权限足够且取消到达时查询仍在执行，Session A 通常收到 SQLSTATE 57014。函数返回 true 只表示已向目标 Backend 发出取消信号，不应被解释为客户端已获得可靠的事务结果。

11. 哪一步提交

长 SELECT 在没有显式 BEGIN 时处于隐式单语句事务；语句成功结束后隐式事务结束。该实验没有数据修改。

12. 预期结果

• pg_backend_pid() 与 pg_stat_activity.pid 相同；
• 在同一 PID namespace 中，ps 能找到该 PID；
• Session A 连接期间 PID 保持不变；
• 查询期间 query_start 保持为长查询开始时间；
• 查询结束时 state_change 更新；
• 会话断开后 pg_stat_activity 行和 OS Backend 进程消失。

13. 诊断 SQL

观察所有普通客户端 Backend 的会话年龄、事务年龄和查询年龄：

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    clock_timestamp() - backend_start AS session_age,
    clock_timestamp() - xact_start    AS transaction_age,
    clock_timestamp() - query_start   AS query_age,
    clock_timestamp() - state_change  AS state_age,
    left(query, 100)                  AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;

字段解释：

• session_age 长并不一定异常，连接池会长期保留连接；
• transaction_age 长通常比 session_age 更危险；
• query_age 在 idle 状态表示最近一条查询的时间，不一定仍在执行；
• state_age 可定位长时间 idle in transaction；
• wait event 为空只表示采样瞬间未报告等待，不能单独证明 CPU 饱和。

14. EXPLAIN 或统计指标

本实验重点是进程和状态，不需要对 pg_sleep 做执行计划分析。应记录：

• backend_pid；
• backend_start、query_start、state_change；
• state、wait_event_type、wait_event；
• OS STAT、ETIME；
• 查询开始和结束的实际时间。

可对表访问部分验证：

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT id, note
FROM ch1_lab.backend_probe
WHERE id = 1;

不要把 pg_sleep 放进性能基准结果；它是人为等待，不代表数据库处理耗时。

15. 结果解释

一个连接对应一个 Backend，是会话状态得以存在的基础：临时表、Prepared Statement、GUC、当前事务和错误状态都保存在该 Backend 的会话生命周期中。连接池复用的是这些 Backend 连接，而不是让一个物理连接同时执行无限条 SQL。

16. 清理语句

DROP SCHEMA IF EXISTS ch1_lab CASCADE;

如果还要继续实验二，可暂不清理，在实验二结束后统一执行。

17. 生产安全警告

• 不要在生产库批量运行长时间 pg_sleep；
• 不要无条件 pg_cancel_backend 或 pg_terminate_backend；
• pg_terminate_backend 会关闭会话并回滚未提交事务，影响范围比取消当前语句更大；
• 视图中的 query 可能含敏感参数，监控系统应脱敏并限制访问；
• 不要把 ps 中的进程标题当作唯一事实，配置、平台和 PID namespace 会影响显示。

---

实验二：用 Go、pgxpool 与 context 观察取消路径

1. 实验目标

运行一个 30 秒查询，并在 3 秒后取消，对比两种 pgx 行为：

1. CANCEL_MODE=default：pgx 默认 context 取消，方法立即返回，并在多数情况下关闭底层连接；
2. CANCEL_MODE=cancel-request：配置 CancelRequestContextWatcherHandler，通过 PostgreSQL CancelRequest 请求服务端取消当前语句，并设置 Socket deadline 作为后备。

实验观察：

• state；
• wait_event_type；
• wait_event；
• query_start；
• state_change；
• 客户端错误类型和 SQLSTATE；
• 客户端返回后服务端 Backend 是否仍存在或短暂继续执行；
• 连接是否可复用；
• pgxpool 连接统计如何变化。

2. PostgreSQL 版本要求

• 主实验适用于 PostgreSQL 14—18；
• [PG17+] transaction_timeout 只在 PostgreSQL 17 及以上存在；
• [PG17+] pg_wait_events 可用于显示等待事件说明；
• [PG18] CancelRequest 协议可协商新版协议能力，但驱动与中间代理是否启用需单独验证，不能假设所有链路都使用最新协议。

3. 必要扩展

无。Go 模块只需要当前 pgx/v5：

mkdir ch1-cancel-demo
cd ch1-cancel-demo
go mod init example.com/ch1-cancel-demo
go get github.com/jackc/pgx/v5

不要写死 pgx 补丁版本。

4. 建表和准备数据

主查询使用 pg_sleep，不依赖业务表。为 lock_timeout 对照实验建立一张专用表：

CREATE SCHEMA IF NOT EXISTS ch1_lab;

CREATE TABLE IF NOT EXISTS ch1_lab.lock_probe (
    id      integer PRIMARY KEY,
    payload text NOT NULL
);

INSERT INTO ch1_lab.lock_probe (id, payload)
VALUES (1, 'initial')
ON CONFLICT (id) DO NOTHING;

5. Session A：Go 程序

保存为 main.go：

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"os"
	"os/signal"
	"sync"
	"syscall"
	"time"

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

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

	dsn := os.Getenv("DATABASE_URL")
	if dsn == "" {
		log.Fatal("DATABASE_URL is required")
	}

	mode := os.Getenv("CANCEL_MODE")
	if mode == "" {
		mode = "default"
	}
	if mode != "default" && mode != "cancel-request" {
		log.Fatalf("unsupported CANCEL_MODE %q", mode)
	}

	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		log.Fatalf("parse pool config: %v", err)
	}

	// 实验上限，不是生产推荐值。
	cfg.MaxConns = 4
	if cfg.ConnConfig.RuntimeParams == nil {
		cfg.ConnConfig.RuntimeParams = make(map[string]string)
	}
	cfg.ConnConfig.RuntimeParams["application_name"] = "ch1-cancel-" + mode

	if mode == "cancel-request" {
		// pgx.ConnConfig 匿名嵌入 pgconn.Config，因此可直接设置该字段。
		cfg.ConnConfig.BuildContextWatcherHandler = func(
			conn *pgconn.PgConn,
		) ctxwatch.Handler {
			return &pgconn.CancelRequestContextWatcherHandler{
				Conn:               conn,
				CancelRequestDelay: 25 * time.Millisecond,
				DeadlineDelay:      2 * time.Second,
			}
		}
	}

	pool, err := pgxpool.NewWithConfig(rootCtx, cfg)
	if err != nil {
		log.Fatalf("create pool: %v", err)
	}
	defer pool.Close()

	pingCtx, cancelPing := context.WithTimeout(rootCtx, 5*time.Second)
	err = pool.Ping(pingCtx)
	cancelPing()
	if err != nil {
		log.Fatalf("ping database: %v", err)
	}

	target, err := pool.Acquire(rootCtx)
	if err != nil {
		log.Fatalf("acquire target connection: %v", err)
	}
	defer target.Release()

	observer, err := pool.Acquire(rootCtx)
	if err != nil {
		log.Fatalf("acquire observer connection: %v", err)
	}
	defer observer.Release()

	// 排除角色级 statement_timeout 对主实验的干扰。
	if _, err = target.Exec(rootCtx, `SET statement_timeout = '0'`); err != nil {
		log.Fatalf("disable statement_timeout for experiment: %v", err)
	}

	var targetPID int32
	if err = target.QueryRow(rootCtx, `SELECT pg_backend_pid()`).Scan(&targetPID); err != nil {
		log.Fatalf("get target PID: %v", err)
	}
	fmt.Printf("mode=%s target_pid=%d\n", mode, targetPID)

	done := make(chan struct{})
	var wg sync.WaitGroup
	wg.Add(1)
	go func() {
		defer wg.Done()
		observeBackend(rootCtx, observer, targetPID, done)
	}()

	// 让观察器先采到一次 idle 状态。
	time.Sleep(500 * time.Millisecond)

	queryCtx, cancelQuery := context.WithTimeout(rootCtx, 3*time.Second)
	started := time.Now()
	_, queryErr := target.Exec(
		queryCtx,
		`SELECT pg_sleep($1::double precision)`,
		30.0,
	)
	cancelQuery()

	fmt.Printf("query_returned_after=%s\n", time.Since(started).Round(time.Millisecond))
	classifyError(queryErr)

	// 客户端方法已经返回，但继续观察两秒，验证服务端状态变化。
	time.Sleep(2 * time.Second)
	close(done)
	wg.Wait()

	reuseCtx, cancelReuse := context.WithTimeout(rootCtx, 2*time.Second)
	var one int
	reuseErr := target.QueryRow(reuseCtx, `SELECT 1`).Scan(&one)
	cancelReuse()
	fmt.Printf("reuse_result=%d reuse_error=%v\n", one, reuseErr)

	stat := pool.Stat()
	fmt.Printf(
		"pool total=%d acquired=%d idle=%d max=%d acquire_count=%d "+
			"acquire_duration=%s canceled_acquire=%d empty_wait=%s\n",
		stat.TotalConns(),
		stat.AcquiredConns(),
		stat.IdleConns(),
		stat.MaxConns(),
		stat.AcquireCount(),
		stat.AcquireDuration(),
		stat.CanceledAcquireCount(),
		stat.EmptyAcquireWaitTime(),
	)
}

func observeBackend(
	rootCtx context.Context,
	conn *pgxpool.Conn,
	pid int32,
	done <-chan struct{},
) {
	ticker := time.NewTicker(200 * time.Millisecond)
	defer ticker.Stop()

	for {
		select {
		case <-rootCtx.Done():
			return
		case <-done:
			return
		case at := <-ticker.C:
			sampleCtx, cancel := context.WithTimeout(
				context.Background(),
				800*time.Millisecond,
			)

			var state string
			var waitType string
			var waitEvent string
			var queryStart string
			var stateChange string
			var query string

			err := conn.QueryRow(sampleCtx, `
                SELECT
                    state,
                    COALESCE(wait_event_type, ''),
                    COALESCE(wait_event, ''),
                    COALESCE(to_char(query_start, 'HH24:MI:SS.MS'), ''),
                    COALESCE(to_char(state_change, 'HH24:MI:SS.MS'), ''),
                    left(COALESCE(query, ''), 80)
                FROM pg_stat_activity
                WHERE pid = $1
            `, pid).Scan(
				&state,
				&waitType,
				&waitEvent,
				&queryStart,
				&stateChange,
				&query,
			)
			cancel()

			if errors.Is(err, pgx.ErrNoRows) {
				fmt.Printf("%s server_backend=gone\n", at.Format("15:04:05.000"))
				return
			}
			if err != nil {
				fmt.Printf("%s observer_error=%v\n", at.Format("15:04:05.000"), err)
				continue
			}

			fmt.Printf(
				"%s state=%-6s wait_type=%-8s wait=%-12s "+
					"query_start=%s state_change=%s query=%q\n",
				at.Format("15:04:05.000"),
				state,
				waitType,
				waitEvent,
				queryStart,
				stateChange,
				query,
			)
		}
	}
}

func classifyError(err error) {
	if err == nil {
		fmt.Println("query_error=<nil>")
		return
	}

	fmt.Printf("query_error_type=%T query_error=%v\n", err, err)
	fmt.Printf(
		"is_deadline_exceeded=%v is_context_canceled=%v\n",
		errors.Is(err, context.DeadlineExceeded),
		errors.Is(err, context.Canceled),
	)

	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		fmt.Printf(
			"postgres_error severity=%s sqlstate=%s message=%q detail=%q\n",
			pgErr.Severity,
			pgErr.Code,
			pgErr.Message,
			pgErr.Detail,
		)
	} else {
		fmt.Println("postgres_error=<none>")
	}
}

运行默认模式：

DATABASE_URL='postgres://user:password@host:5432/dbname?sslmode=require' \
CANCEL_MODE=default \
go run .

运行 CancelRequest 模式：

DATABASE_URL='postgres://user:password@host:5432/dbname?sslmode=require' \
CANCEL_MODE=cancel-request \
go run .

本地无 TLS 实验环境可按实际连接方式调整 sslmode；不要把明文密码提交到 Shell history、源码或仓库。

6. Session B：外部数据库观察

程序会打印 target_pid。在另一个 psql 中使用该 PID：

SELECT
    pid,
    backend_type,
    application_name,
    state,
    wait_event_type,
    wait_event,
    query_start,
    state_change,
    clock_timestamp() - query_start AS query_age,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE pid = $1;

在 psql 里不能直接使用 $1，可改成具体实验 PID，或者：

\set target_pid 12345

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    query_start,
    state_change,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE pid = :target_pid;

7. Session C：超时机制对照

以下对照应使用独立实验连接，不要在生产业务会话直接执行。

A. statement_timeout

SET statement_timeout = '1s';
SELECT pg_sleep(10);
-- 预期：当前语句取消，常见 SQLSTATE 57014；会话仍存在。
RESET statement_timeout;

B. lock_timeout

Session C1：

BEGIN;
UPDATE ch1_lab.lock_probe
SET payload = 'held-by-c1'
WHERE id = 1;
-- 保持事务不提交。

Session C2：

SET lock_timeout = '1s';
UPDATE ch1_lab.lock_probe
SET payload = 'attempt-by-c2'
WHERE id = 1;
-- 预期：等待锁约 1 秒后失败，常见 SQLSTATE 55P03。
RESET lock_timeout;

Session C1 最后执行：

ROLLBACK;

C. idle_in_transaction_session_timeout

SET idle_in_transaction_session_timeout = '2s';
BEGIN;
SELECT 1;
-- 此处停顿超过 2 秒，不发送任何 SQL。
SELECT 1;
-- 预期：会话已被服务端终止，SQLSTATE 25P03。

D. [PG17+] transaction_timeout

SET statement_timeout = '0';
SET transaction_timeout = '3s';
BEGIN;
SELECT pg_sleep(10);
-- 预期：事务总时长超过 3 秒后，会话被终止，SQLSTATE 25P04。

PostgreSQL 14—16 不存在 transaction_timeout，会返回“unrecognized configuration parameter”。

8. 明确时间线

时间	Go Client	Target Backend	Observer
T0	Pool 建立，Acquire 两个连接	Target/Observer 各有一个 Backend	采到 Target idle
T1	Target 执行 pg_sleep(30)	active + Timeout/PgSleep	query_start 固定，state_change 进入 active
T2≈3s	queryCtx 到期	根据取消模式处理	继续采样
T3	Exec 返回错误	default 模式通常连接关闭；CancelRequest 模式通常取消当前语句	可能看到 Backend 消失，或回到 idle/ClientRead
T4≈5s	尝试 SELECT 1	验证连接是否可复用	输出最终状态

9. 哪一步等待

• T1—T2：Target Backend 通常在 PgSleep 等待；
• 连接池 Acquire 若无空闲连接，会在客户端池队列等待，此时 PostgreSQL 看不到尚未获得连接的请求；
• CancelRequest 使用独立短连接发送取消消息，可能受 DNS、网络、TLS、代理和认证路径影响；
• default 模式关闭连接后，服务端感知断开与清理 Backend 不是与客户端函数返回绝对同时发生。

10. 哪一步失败

• Exec 在 context deadline 后失败；
• default 模式常见 context deadline exceeded 或连接关闭类错误，不保证存在 *pgconn.PgError；
• CancelRequest 成功命中当前语句时，常见 *pgconn.PgError，SQLSTATE 57014；
• CancelRequest 发送函数没有服务端成功 ACK，传输无错误也不能证明查询必然被取消；
• 若查询刚好在取消到达前完成，结果和取消之间存在竞态。

11. 哪一步提交

SELECT pg_sleep 使用隐式只读事务：

• 正常完成时隐式事务结束；
• 被取消时当前语句失败，隐式事务回滚；
• 若把同样取消放在显式事务中，语句取消通常使事务进入失败状态，必须 ROLLBACK 后才能继续；
• 对 UPDATE/COMMIT，客户端 deadline 不能作为“肯定未提交”的证明。

12. 预期结果

default 模式常见表现：

• 约 3 秒后客户端立即返回；
• errors.Is(err, context.DeadlineExceeded) 可能为真；
• Target 连接通常不可复用，SELECT 1 失败；
• Observer 可能短暂看到原查询仍在 Backend 上，随后 Backend 行消失；
• Pool 后续使用时会补建连接。

cancel-request 模式常见表现：

• 约 3 秒后发送 CancelRequest；
• 客户端常收到 SQLSTATE 57014；
• Target Backend 通常从 active/PgSleep 回到 idle/ClientRead；
• 连接通常可以执行后续 SELECT 1；
• 若网络或代理阻断 CancelRequest，Socket deadline 后备可能仍导致连接关闭。

上述是预期轨迹而非硬编码断言，应以实际输出为准。

13. 诊断 SQL

观察实验程序的两个连接：

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    backend_start,
    xact_start,
    query_start,
    state_change,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE application_name LIKE 'ch1-cancel-%'
ORDER BY pid;

确认是否存在长期未结束查询：

SELECT
    pid,
    application_name,
    clock_timestamp() - query_start AS query_age,
    state,
    wait_event_type,
    wait_event,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < clock_timestamp() - interval '5 seconds'
ORDER BY query_start;

连接池指标应由应用周期性导出：

• TotalConns()：池中总连接；
• AcquiredConns()：当前被借出的连接；
• IdleConns()：空闲连接；
• AcquireDuration()：累计获取等待时长；
• EmptyAcquireWaitTime()：池空时累计等待；
• CanceledAcquireCount()：Acquire context 被取消次数。

累计值应计算速率或区间差值，而不是直接把总数当作瞬时值。

14. EXPLAIN 或统计指标

pg_sleep 不适合 EXPLAIN 性能分析。本实验应记录：

• 客户端返回延迟；
• 客户端错误类型、errors.Is 与 SQLSTATE；
• Backend PID 是否消失；
• state 和 wait event 的状态转移；
• query_start 与 state_change；
• 连接复用结果；
• Pool 的新建连接数与 Acquire 指标；
• PostgreSQL 日志中的 cancel/FATAL/断连信息。

在对真实 SQL 做取消实验时，可先在测试环境使用：

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

对写语句使用 EXPLAIN ANALYZE 会真实执行；即使包在 BEGIN/ROLLBACK 中，Sequence、外部调用和某些副作用也未必完全回滚。

15. 结果解释

• Context 是应用取消信号，不是数据库事务证明。
• CancelRequest 是协议级“请求取消当前语句”，不是可靠事务消息。
• 服务端 timeout 有明确 SQLSTATE，更适合作为数据库资源护栏。
• 默认关闭连接能快速解除客户端阻塞，但频繁取消会增加重连和认证压力。
• 保留连接的 CancelRequest 降低连接抖动，却要求正确处理竞态、显式事务失败状态和代理兼容性。
• 应用 deadline 应与服务端 timeout 分层，不能只配一侧。

16. 清理语句

DROP SCHEMA IF EXISTS ch1_lab CASCADE;

删除本地实验目录：

cd ..
rm -rf ch1-cancel-demo

执行删除前确认目录中没有其他文件。

17. 生产安全警告

• 不要用业务关键 UPDATE 验证取消，除非有隔离环境和幂等保护；
• 不要假设客户端 deadline 到达后数据库已停止；
• 不要把 57014 一律重试，先判断操作幂等性、事务边界和取消来源；
• 不要把 statement_timeout、transaction_timeout 作为全库统一固定值；
• Transaction Pooling 代理可能改变会话与 CancelRequest 行为，必须端到端验证；
• 取消显式事务中的语句后必须回滚；
• 频繁默认 context 取消可能导致连接池持续补建连接，应监控连接创建率和认证负载；
• 任何 COMMIT 网络错误都必须按“结果可能未知”设计，而不是盲目重试。

---

14. 生产排障 Runbook

本 Runbook 用于“数据库请求变慢、超时、连接耗尽或故障转移后错误激增”这一类入口级事故。执行任何取消、终止和配置变更前，先保留证据。

14.1 第一步：确认现象、范围和时间线

首先回答：

• 是单个 SQL、单个接口、单个租户，还是全实例？
• 延迟增加发生在连接池 Acquire、SQL 执行、Commit、结果读取还是重连？
• 错误是 SQLSTATE、context deadline、网络错误、too many connections，还是只表现为 P99？
• Primary 是否发生重启、切换、Checkpoint、存储抖动或版本发布？
• 问题开始的绝对时间、时区和持续时间是什么？
• PostgreSQL 主版本、参数来源、应用版本和连接代理拓扑是什么？

立即记录：

SELECT
    version(),
    current_database(),
    clock_timestamp(),
    pg_postmaster_start_time(),
    pg_is_in_recovery();

pg_postmaster_start_time() 变化说明实例近期重启；pg_is_in_recovery() 可判断当前连接是否在 Standby。

14.2 第二步：从应用侧区分排队与数据库执行

查看 pgxpool：

• AcquiredConns / MaxConns 是否长期接近 1；
• EmptyAcquireWaitTime 增长速度；
• CanceledAcquireCount 增长速度；
• 新建连接速率和连接错误；
• HTTP/RPC 并发、队列长度、拒绝率；
• 数据库操作 context deadline 与上游 deadline；
• 同一时间是否扩容了 Pod，导致总 MaxConns 乘法增长。

判断：

• Acquire 等待高、数据库 active 不高：池太小、连接泄漏、事务持有过久或网络重连；
• Acquire 等待高、数据库 active 也高：数据库或工作负载饱和，需要准入和根因分析；
• Acquire 等待不高、SQL P99 高：重点查锁、I/O、计划和 WAL；
• SQL 快但 HTTP 慢：数据库之外的网络、序列化或外部调用问题。

14.3 第三步：抓取活动快照与等待分布

SELECT
    backend_type,
    state,
    wait_event_type,
    wait_event,
    count(*) AS sessions,
    max(clock_timestamp() - query_start) AS max_query_age,
    max(clock_timestamp() - xact_start)  AS max_xact_age
FROM pg_stat_activity
GROUP BY backend_type, state, wait_event_type, wait_event
ORDER BY sessions DESC;

再抓取具体会话：

SELECT
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    backend_type,
    state,
    wait_event_type,
    wait_event,
    clock_timestamp() - backend_start AS session_age,
    clock_timestamp() - xact_start    AS xact_age,
    clock_timestamp() - query_start   AS query_age,
    clock_timestamp() - state_change  AS state_age,
    left(query, 300)                  AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;

重点解释：

• ClientRead 大量出现通常表示服务端在等客户端，不是数据库内部锁；
• ClientWrite 可能是客户端读取慢、网络拥塞或结果集过大；
• Lock 表示 SQL 层锁等待；
• LWLock 表示内部共享结构等待，需结合具体名称；
• IO 表示 PostgreSQL 正在等待 I/O，但仍需 OS 指标确认设备瓶颈；
• Timeout/PgSleep 是主动定时等待；
• active 加 wait event 表明语句仍活跃但被阻塞。

14.4 第四步：找到 blocker 和阻塞链

先查看等待者及其直接 blocker：

SELECT
    a.pid AS waiting_pid,
    a.application_name AS waiting_app,
    a.query_start AS waiting_since,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocking_pids,
    left(a.query, 200) AS waiting_query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

展开 blocker：

WITH waiting AS (
    SELECT
        a.pid AS waiting_pid,
        unnest(pg_blocking_pids(a.pid)) AS blocking_pid
    FROM pg_stat_activity AS a
    WHERE cardinality(pg_blocking_pids(a.pid)) > 0
)
SELECT
    w.waiting_pid,
    wa.application_name AS waiting_app,
    wa.xact_start        AS waiting_xact_start,
    left(wa.query, 160)  AS waiting_query,
    w.blocking_pid,
    ba.application_name AS blocking_app,
    ba.state            AS blocking_state,
    ba.xact_start       AS blocking_xact_start,
    ba.state_change     AS blocking_state_change,
    left(ba.query, 160) AS blocking_query
FROM waiting AS w
JOIN pg_stat_activity AS wa ON wa.pid = w.waiting_pid
JOIN pg_stat_activity AS ba ON ba.pid = w.blocking_pid
ORDER BY wa.query_start;

优先关注处于 idle in transaction 的 blocker。终止前确认业务影响、事务内容、是否为迁移或关键结算。

14.5 第五步：找到最早出现的执行计划估算错误

对可安全重放的 SELECT，在测试环境或受控生产窗口执行：

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

从执行计划叶子向根节点读取，寻找第一个 actual rows 与 rows 估算出现数量级偏差的节点，而不是只看最终慢节点。例如：

Index Scan: estimated rows=10, actual rows=500000
  ↓
Nested Loop: 偏差被放大
  ↓
Sort/Hash: 内存与临时文件问题只是后果

判断比值时同时看 loops：实际总行数约为 actual rows × loops。根因可能是统计信息过期、列相关性、参数敏感、数据倾斜、表达式统计缺失或错误谓词。EXPLAIN 不带 ANALYZE 可在线安全查看估算，但不能验证实际行数；ANALYZE 会真实执行 SQL。

可选安装 pg_stat_statements 后先找总耗时、平均耗时和调用次数异常的规范化语句，再捕获其具体参数做计划验证。不要仅凭平均值忽略 P99。

14.6 第六步：判定 CPU、内存、I/O、锁、连接池、WAL、Vacuum 或复制

类别	PostgreSQL 证据	OS/应用证据	常见结论
CPU	active 多但 wait event 少；高调用 SQL	top/pidstat CPU、run queue、context switch	计划低效或活跃并发过高
内存	temp file、Backend Memory Context、OOM 日志	RSS、cgroup memory、swap、OOMKill	work_mem 放大或连接过多
I/O	[PG16+] pg_stat_io、I/O waits、Buffers read	iostat -x latency/queue/util、云盘限速	冷读、大扫描、Checkpoint 或设备瓶颈
锁	pg_blocking_pids、Lock wait	应用长事务日志	blocker、热点行、DDL 冲突
连接池	数据库连接槽位与 active 数	Acquire wait、CanceledAcquire、Pod 数	池耗尽、泄漏、总连接预算失控
WAL	pg_stat_wal、WAL wait、pg_wal 增长	WAL 盘延迟、归档失败	高写入、fsync 慢、归档/复制跟不上
Checkpoint	pg_stat_checkpointer、写入高峰	周期性磁盘尖峰	Checkpoint 太密或写入突发
Vacuum	dead tuples、Autovacuum worker、长事务	表膨胀与 I/O	清理跟不上或旧快照阻塞
复制	pg_stat_replication、pg_stat_wal_receiver	网络与 Standby 存储	发送/写/刷/重放某阶段滞后

OS 命令示例：

vmstat 1
pidstat -p <backend_pid> 1
iostat -xz 1
ss -tanp | grep 5432

托管数据库无法运行 OS 命令时，使用云平台等价指标，但不要把供应商单一“CPU 使用率”当作完整证据。

14.7 第七步：确认 PostgreSQL 与 OS Cache 边界

SELECT
    datname,
    blks_read,
    blks_hit,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_bytes,
    blk_read_time,
    blk_write_time
FROM pg_stat_database
WHERE datname = current_database();

若需 I/O 时间，确认：

SHOW track_io_timing;
SHOW track_wal_io_timing;

启用计时本身有平台相关开销，需评估。blks_read 表示进入 PostgreSQL Buffer 的块读取，不保证每次都访问物理盘；OS Page Cache 可能命中。

14.8 第八步：可在线执行的低风险操作

通常低风险但仍应遵守权限与查询成本：

• 查询 pg_stat_activity、pg_locks、pg_stat_io、pg_stat_wal、复制视图；
• EXPLAIN 不带 ANALYZE；
• 查看 pg_settings、pg_file_settings；
• 对单个已确认的非关键长查询执行 pg_cancel_backend；
• 在应用入口降并发、暂停重试、关闭高成本功能；
• 使用 SET LOCAL 为单个事务设置 timeout；
• 对监控采样设置自身 statement_timeout，避免诊断查询成为负担。

14.9 第九步：高风险操作

必须变更审批、备份或回滚方案：

• pg_terminate_backend；
• 批量取消或终止会话；
• CHECKPOINT；
• EXPLAIN ANALYZE 写语句；
• 在线修改大范围 max_connections、shared_buffers 等需重启参数；
• 提高 work_mem 到所有会话；
• OS kill -9 PostgreSQL 进程；
• 关闭 fsync、full_page_writes、autovacuum、校验或同步复制保护；
• 在故障期间执行未经评估的 DDL、VACUUM FULL、REINDEX 或大批量更新；
• 未 Fencing 旧 Primary 就提升新 Primary。

14.10 第十步：临时止损

按根因选择最小影响措施：

• 在应用层降低并发和队列上限，暂停非关键流量；
• 停止自动重试或增加带抖动的退避；
• 取消已确认的失控查询；
• 协调持锁事务尽快 Commit/Rollback，必要时经审批终止 blocker；
• 暂停报表、批处理、迁移或大批量写入；
• 将允许陈旧读的报表流量切到健康 Standby；
• 故障转移后限制连接重建速率；
• 磁盘空间紧张时先停止制造 WAL 的非关键任务，并修复归档/复制，而不是直接删除 WAL 文件。

14.11 第十一步：根本修复

常见根治方向：

• 修正 SQL、索引、统计信息和数据模型；
• 缩短事务，禁止事务内慢外部调用；
• 统一锁顺序，拆分热点；
• 将每服务 MaxConns 纳入全局连接预算；
• 配置有界队列、Admission Control 和工作负载隔离；
• 对 OLTP/报表/迁移使用不同角色、池和 timeout；
• 改善 WAL/数据盘、Checkpoint、归档和复制容量；
• 为取消与 Commit 歧义增加幂等键、状态查询和对账；
• 完善故障转移目标验证、Fencing 和连接抖动；
• 建立冷/热缓存和不同并发下的基准容量曲线。

14.12 第十二步：验证修复并设置告警

验证至少包括：

• 相同流量模型下 P50/P95/P99 与 TPS；
• Pool Acquire P95/P99、队列长度和取消率；
• 数据库 active/wait 分布；
• CPU run queue、上下文切换、内存和 I/O；
• 锁等待数、最长事务、idle in transaction；
• WAL 速率、Checkpoint、归档、复制延迟；
• 错误率与 SQLSTATE 分布；
• 故障转移后的连接恢复时间；
• 幂等与提交结果未知场景的业务对账。

建议告警：

连接池使用率与 Acquire P99
Acquire 取消速率
数据库连接槽位使用率
active queries 和 Lock waiters
最长事务与 idle in transaction 时长
SQL P99 和 statement timeout 率
WAL 生成速率与 pg_wal 空间
Checkpoint 时长与写入尖峰
归档失败与复制延迟
实例重启/角色切换
客户端重连速率

告警必须有持续时间、基线和 Runbook 链接，避免因瞬时尖峰制造告警风暴。

---

15. 常见错误与反模式

#	反模式	为什么错误	正确做法
1	把 max_connections 调得很大解决慢请求	只增加进程、内存和调度容量，不增加 CPU/I/O/WAL 能力	用池排队指标和压测确定高效活跃并发
2	每个 HTTP 请求新建 PostgreSQL 连接	重复认证、TLS、Backend 创建与缓存冷启动	复用并发安全的 pgxpool
3	每个 Pod 都把 MaxConns 设为数据库上限	Pod 扩容后总连接数乘法爆炸	建立跨服务、跨 Pod 的全局连接预算
4	无限创建 goroutine 等连接	把数据库瓶颈转化为内存与排队瀑布	有界 worker、Acquire deadline、入口拒绝/降级
5	认为连接数就是业务并发	Idle、排队、事务持有和并行 Worker 使两者不同	同时观测 goroutine、pool、active、TPS、queue
6	只看 state='active' 就判断 CPU 很忙	active 可能正在等锁、I/O、PgSleep 或网络	必须结合 wait event 与 OS 指标
7	把 shared_buffers 命中率当作物理盘命中率	PostgreSQL miss 仍可能命中 OS Page Cache	结合 pg_stat_io、EXPLAIN BUFFERS 与 OS I/O
8	认为 COMMIT 会立即写完所有数据页	提交主要依赖 WAL 持久性，脏页通常稍后写出	区分 WAL flush、脏页写和 Checkpoint
9	将 Background Writer、Checkpointer、WAL Writer 混为一谈	三者目标和触发路径不同	分别监控数据页平滑写、Checkpoint 和 WAL 写
10	看到客户端 context 超时就断言 SQL 已回滚	客户端返回与服务端停止之间可能有延迟或竞态	查服务端状态；写操作使用幂等和对账
11	认为 CancelRequest 发送无错误就取消成功	协议没有成功 ACK，查询可能已完成	读取原连接最终结果并监控 Backend 状态
12	在显式事务内语句取消后继续执行	事务通常已进入 failed 状态	立即 Rollback，重试完整事务
13	全局统一设置很短 statement_timeout	OLTP、报表、维护需求不同，会误杀关键任务	按角色、会话、事务和接口分层设置
14	用 lock_timeout 代替 statement_timeout	它只统计每次锁等待，不限制 CPU/I/O 时间	两者分层配置并按 SQLSTATE 分类
15	使用 Simple Protocol 拼接用户输入	SQL 注入、类型与转义风险	Extended Protocol 与 $1 参数
16	事务中调用慢 HTTP/RPC	长时间占连接、锁和快照	事务只包含必要数据库工作
17	故障转移后无限快速重连	新 Primary 认证和进程创建被打爆	有界指数退避、随机抖动、连接准入
18	Commit 返回错误就自动重试	事务可能已经提交，造成重复副作用	幂等键、唯一约束、查询状态、对账
19	把 PostgreSQL 18 AIO 当作万能加速开关	只改善特定 I/O 路径，且受工作负载与存储影响	对相同缓存状态、并发和 SLO 做 A/B 测试
20	用 OS kill -9 处理慢 SQL	可能触发实例级恢复和全部连接中断	优先 pg_cancel_backend，必要时受控 terminate

---

16. 模拟生产事故案例

模拟生产案例一：自动扩容触发连接风暴

1. 系统背景

订单 API 运行 40 个 Pod，每个 Pod 配置 pgxpool.MaxConns=60。PostgreSQL max_connections=1200，另有报表、任务、监控和运维连接。平时每个 Pod 只使用约 8—15 个连接。

2. 故障现象

促销开始后 Pod 自动扩到 100 个：

• 大量连接建立，部分报 too many clients；
• CPU 上升、上下文切换增加；
• SQL 平均耗时增加，P99 从数十毫秒升到数秒；
• Pool Acquire 等待和 context 取消快速增长；
• 应用重试导致 TPS 没增加，入站请求却持续放大；
• Autovacuum 和运维连接也难以获得槽位。

3. 错误假设

团队认为：“数据库允许 1200 个连接，每个 Pod 60 个连接很安全；连接越多吞吐越高。”

实际上，理论应用上限已达到 100 × 60 = 6000，远超实例连接槽位；即使限制到 1200，数据库高效活跃并发也可能只有几十到几百，取决于 SQL 和硬件。

4. 排查过程

1. 查看部署事件，确认 Pod 数在故障开始时跃升；
2. 汇总所有服务的 实例数 × MaxConns；
3. 查看 pgxpool TotalConns、AcquiredConns、EmptyAcquireWaitTime；
4. 查询 pg_stat_activity，发现普通 Backend 数暴涨，但大量为 idle 或短查询；
5. OS vmstat/pidstat 显示上下文切换和 run queue 增长；
6. 等待事件没有单一锁热点，说明是整体过载；
7. 连接建立日志与认证 CPU 同时上升；
8. 重试指标显示每个原始请求平均触发多次数据库尝试。

5. 根因

• 没有全局连接预算；
• Pod Autoscaling 与 Pool 上限独立设计；
• 无 Admission Control；
• Acquire 队列和 goroutine 无界；
• 错误重试无抖动；
• 把 max_connections 当作吞吐参数。

6. 临时止损

• 暂停继续扩容，降低每 Pod 并发入口；
• 关闭非关键接口和批处理；
• 临时下调重试次数并增加随机退避；
• 滚动发布较小 MaxConns，避免同时重建连接；
• 为运维和复制保留连接槽位；
• 监控恢复后逐步放量。

7. 最终修复

• 通过压测确定实例在目标 P99 下的高效活跃查询上限；
• 将连接预算按服务分配，例如 API、任务、报表、运维分别设配额；
• Pool 上限随 Pod 数动态计算或由集中配置控制；
• 入口增加有界并发 Semaphore 和快速拒绝；
• 对 Pool Acquire 设置比上游 deadline 更短的明确超时；
• 对连接 lifetime 增加随机抖动；
• 大规模逻辑连接需求评估 PgBouncer，但仍保留查询级准入。

8. 监控补充

• Σ Pod MaxConns 与实例连接预算；
• Pool Acquire P95/P99、CanceledAcquire、连接新建率；
• PostgreSQL client backend 总数、active 数和 wait 分布；
• OS context switches、run queue、RSS；
• 重试放大系数；
• Autoscaler 事件与数据库指标关联。

9. 如何防止复发

把数据库连接额度作为发布和扩容的硬性容量约束；CI/CD 在服务实例数或 MaxConns 变更时自动计算最坏总连接数，超预算则阻止发布。季度故障演练中模拟 Primary 重启，验证连接抖动与准入是否能保护新实例。

---

模拟生产案例二：客户端超时后重复扣款

1. 系统背景

支付服务在一个事务中插入扣款记录并更新余额。HTTP deadline 为 2 秒，数据库操作也使用同一个 2 秒 context。客户端遇到任何错误就自动重试，没有业务幂等键。

2. 故障现象

存储出现短暂延迟后：

• 应用日志大量 context deadline exceeded；
• 用户看到请求失败并重试；
• 少量账户出现两条扣款记录；
• PostgreSQL 日志中既有取消，也有连接断开；
• 数据库中部分“失败请求”其实已提交；
• Pool 新建连接速率显著升高。

3. 错误假设

开发认为：“context 超时意味着事务肯定回滚，因此安全重试。”

该假设忽略两个窗口：

• SQL 已完成，但客户端在结果返回前超时；
• COMMIT 已持久化，但成功响应在网络中丢失。

4. 排查过程

1. 用请求 ID 关联应用日志、数据库审计字段和支付记录；
2. 发现第一次请求日志报错，但数据库已有相同业务意图的已提交记录；
3. pgx 默认 context 取消导致部分连接关闭，连接创建率升高；
4. pg_stat_activity 采样表明客户端返回后少量 Backend 短暂仍存在；
5. 数据库日志显示部分 57014，另一些只有断连，无统一服务端错误；
6. 分析事务时间线，确认错误发生在 COMMIT 响应边界附近；
7. 检查表约束，发现没有 (merchant_id, idempotency_key) 唯一约束；
8. 重试策略对所有 error 一视同仁且无状态查询。

5. 根因

• 把客户端 context 当作事务结果协议；
• HTTP 与数据库 deadline 没有留清理和响应余量；
• 缺少幂等键和唯一约束；
• Commit 错误后自动重试完整业务副作用；
• 取消模式和连接池重建成本未经验证。

6. 临时止损

• 停止对提交阶段未知错误的自动重试；
• 对重复扣款执行对账和人工/自动冲正；
• 限流以减轻连接重建和存储压力；
• 暂时增加上游 deadline 余量，同时保持服务端语句护栏；
• 按业务请求 ID 查询已有结果后再决定是否继续。

7. 最终修复

• 客户端生成不可重复的 idempotency key；
• 数据库增加唯一约束；
• 事务先写入请求状态，重复请求返回已有结果；
• 单独检查 Commit 错误，并将不确定结果标记为 unknown；
• 通过独立查询或对账流程解析 unknown；
• 分层配置 lock_timeout、statement_timeout、数据库 context 和 HTTP deadline；
• 对可安全取消的读查询评估 CancelRequest watcher，对写事务不以取消结果替代幂等设计。

8. 监控补充

• Commit 错误和 SQLSTATE；
• context deadline 与 57014 分开统计；
• 幂等冲突命中率；
• unknown 事务数量与解析时长；
• Pool 连接销毁/新建率；
• 重复业务键和对账差异；
• WAL/存储 Commit 延迟 P99。

9. 如何防止复发

所有涉及资金、库存、订单、消息发布的写接口必须通过设计评审证明：

• 有数据库级幂等约束；
• 能处理 Commit 结果未知；
• 不依赖错误文本分类；
• 自动重试只针对明确可重试且幂等的完整事务；
• 有故障注入测试覆盖“提交成功但响应丢失”。

---

17. 面试题

17.1 核心概念题（5 题）

题 1：Cluster、Instance、Database、Schema、Relation 有什么区别？

题目 请从物理部署、运行时和 SQL 命名空间三个层次解释五个概念，并说明一个客户端连接选择了哪一层。

30 秒回答 Cluster 是一个数据目录及其中的一组数据库；Instance 是管理该 Cluster 的运行中进程组和共享内存；Database 是 Cluster 内的逻辑数据库；Schema 是单个 Database 内的命名空间；Relation 是表、索引、序列、物化视图等关系对象的通用称呼。普通连接在启动时选择一个 Database，不能靠 schema.table 跨到另一个 Database。

深入回答 一个 Main Server Process 通常管理一个 Cluster，并与 Backend、辅助进程和 Shared Memory 组成 Instance。角色和 Tablespace 等部分对象在 Cluster 层共享；表与 Schema 属于某个 Database；pg_class 中许多对象都属于 Relation。Schema 的优点是同库命名与权限隔离，缺点是不能提供 Database 级物理隔离。替代方案包括独立 Database、独立 Cluster 或独立 Instance，隔离越强，连接池、迁移和运维成本越高。生产中必须澄清云厂商“cluster/instance”的产品术语与 PostgreSQL 内核术语不同。

面试官真正考察什么 是否能把 SQL 对象、数据目录和运行进程放在正确层次，并避免把“库”“实例”“集群”混用。

常见错误回答 “Cluster 就是多台 PostgreSQL；Relation 就是普通表；Schema 可以跨 Database 查询。”

追问 为什么一个连接池通常需要按 Database 建立，而不能在同一连接上随意切换 Database？

追问答案 Database 在 StartupMessage 阶段选择，Backend 会绑定该 Database 的系统目录与对象空间；PostgreSQL 没有类似 USE other_database 的普通切换命令。要访问另一个 Database 需新连接，或使用 FDW/dblink 等显式跨库机制，其事务与性能语义也不同。

---

题 2：PostgreSQL 的 process-per-connection 模型意味着什么？

题目 一个应用连接与 Backend Process 的关系是什么？一条并行查询是否仍然只有一个进程？

30 秒回答 每个实际 Server Connection 在会话期间绑定一个 Backend Process，Backend 保存事务、GUC、Prepared Statement 等会话状态。连接上命令通常串行执行；但并行查询可由 Leader Backend 启动 Parallel Worker，所以一条查询可能使用多个进程。

深入回答 该模型隔离性好，单个 Backend 的私有状态明确，成熟可靠；代价是每连接有进程、私有内存和调度开销。大量 Idle Connection 仍占资源，大量 Active Backend 会产生上下文切换和共享结构竞争。连接池复用 Server Connection，Transaction Pooling 代理还能跨逻辑会话复用，但会牺牲部分会话语义。生产应限制连接和活跃工作量，而不是靠提高 max_connections 追求吞吐。

面试官真正考察什么 是否理解“连接、会话、进程、查询和并行 Worker”不是一一相等。

常见错误回答 “一个业务请求必然一个进程；连接空闲时完全没有成本；设置 5000 连接就能跑 5000 并发。”

追问 为什么 pgxpool 的 MaxConns 不能只按 max_connections 平均分配？

追问答案 还要考虑所有服务 Pod、批处理、运维、复制和故障保留，并且数据库高效 Active Query 上限通常低于连接槽位。应通过工作负载压测，以 CPU、I/O、锁和 P99 确定容量，再反推池与队列。

---

题 3：shared_buffers 与操作系统 Page Cache 有什么区别？

题目 为什么 PostgreSQL 同时需要自己的 Buffer Pool 和 OS Page Cache？

30 秒回答 shared_buffers 知道 PostgreSQL relation block、pin、dirty 和并发状态；OS Page Cache 是通用文件缓存，负责文件系统与设备 I/O。PostgreSQL Buffer miss 可能仍命中 OS Cache，所以两者指标不能互相替代。

深入回答 Buffer Manager 需要保证页使用期间不被替换、协调并发 I/O、标记脏页并执行替换，这是 OS Cache 不理解的数据库语义。OS 则负责进程调度、虚拟内存、文件系统、预读和设备驱动。双层缓存会占内存，但提供不同功能；盲目把 shared_buffers 设得极大可能挤压 OS Cache 和其他进程。Alternative 是某些直接 I/O 路径，但必须以当前版本、平台和 workload 验证，不能从概念推导固定调参结论。

面试官真正考察什么 是否能区分数据库缓存命中、OS 缓存命中和物理设备读取。

常见错误回答 “blks_hit 高就说明磁盘没有 I/O；OS Cache 是重复浪费，应把全部内存给 shared_buffers。”

追问 怎样确认一次慢查询是冷数据读取而不是 CPU 或锁等待？

追问答案 结合 EXPLAIN (ANALYZE, BUFFERS)、[PG16+] pg_stat_io、wait event、track_io_timing 和 iostat。连续对热/冷缓存重复测试；若 Lock wait 明显或 CPU 饱和，不能把 Buffers read 单独当作根因。

---

题 4：Simple Query Protocol 与 Extended Query Protocol 的核心差异是什么？

题目 请解释 Parse、Bind、Describe、Execute、Sync，以及 pgx 默认更常用哪一种协议。

30 秒回答 Simple Protocol 用一个 Query 消息发送 SQL 文本，可含多条语句。Extended Protocol 将 Parse、Bind 参数、可选 Describe、Execute 和 Sync 分开，支持 Prepared Statement、Portal、二进制格式和安全参数化。pgx 默认通常使用 Extended Protocol 与语句缓存。

深入回答 Extended 的优势是参数和值分离、可复用解析结果、降低部分往返并支持 Pipeline；代价是会话状态、错误恢复和代理兼容更复杂。Sync 是隐式事务和错误恢复同步点，出错后服务端通常跳过消息直到 Sync。Simple 适合交互式或可信脚本，但多语句事务边界复杂，且不能成为用户输入拼接的理由。经 Transaction Pooling 时需验证代理对 Prepared Statement 的支持，可考虑 QueryExecModeExec 等替代，而不是未经评估切换到 Simple。

面试官真正考察什么 是否理解前后端协议，而不只是知道“Prepared Statement 更快”。

常见错误回答 “Extended 每一步一定各占一个 RTT；Simple Protocol 也能用 $1 在服务端安全绑定；Sync 就是 COMMIT。”

追问 Extended Protocol 中发生错误后，为什么驱动必须正确处理 Sync？

追问答案 服务端需要 Sync 才能结束当前错误处理阶段、建立协议同步点并返回 ReadyForQuery。如果客户端继续把后续响应错误地配对到请求，会污染连接状态；成熟驱动负责该状态机，自行实现 Pipeline 时尤其重要。

---

题 5：Background Writer、Checkpointer 和 WAL Writer 分别做什么？

题目 为什么它们不能统称为“后台刷盘进程”？

30 秒回答 Background Writer 平滑写出部分脏数据 Buffer；Checkpointer 执行 Checkpoint，推进恢复起点并确保所需数据页持久化；WAL Writer 周期写出 WAL Buffers。事务提交主要关心 WAL 持久性，不要求所有数据页立即写完。

深入回答 三者共同影响 I/O，但目标不同。Background Writer 减少 Backend 自己写脏页的突发；Checkpointer 按 Checkpoint 语义集中推进数据页与 fsync，过密会写放大；WAL Writer 减少 WAL 缓冲积压，但 Commit 仍可能由 Backend 主动写/刷 WAL。Alternative 不是关闭它们，而是调节 Checkpoint/WAL/存储容量并平滑写入。生产应分别看 pg_stat_bgwriter、pg_stat_checkpointer、pg_stat_wal 和 OS 延迟。

面试官真正考察什么 是否掌握 WAL-first 与数据页延迟写出的基本恢复模型。

常见错误回答 “COMMIT 时 Checkpointer 把本事务所有数据页写盘；WAL Writer 负责归档；Background Writer 定义事务持久性。”

追问 如果数据库进程在数据页未写出前崩溃，已提交修改如何恢复？

追问答案 只要提交所需 WAL 已持久化，重启恢复会从 Checkpoint 位置读取 WAL，并将记录重放到数据页，使数据文件恢复到一致且包含已提交变化的状态。

---

17.2 原理与排障题（6 题）

题 6：一条 SELECT 从 SQL 文本到返回行经历哪些阶段？

题目 请给出执行路径，并说明最容易出现性能问题的边界。

30 秒回答 Frontend 通过协议发送请求，Backend 经 Parser、Analyzer、Rewriter、Planner 生成计划，Executor 获取快照和锁，经 Buffer Manager/Storage Manager/OS 读取页，执行过滤连接聚合，再编码结果返回。延迟可能在连接池、网络、规划、CPU、锁、I/O 或结果传输。

深入回答 Parser 只处理语法；Analyzer 解析对象和类型；Rewriter 展开视图/规则；Planner 依统计信息选路径；Executor 驱动计划节点。shared buffer miss 并不等同物理盘 miss。大结果集还可能在 ClientWrite 上等待慢客户端。优势是分层清晰、可观测；代价是每层都有状态和缓存。替代方案如 Prepared Statement、读副本、缓存或批处理只能解决特定边界，必须先测量。

面试官真正考察什么 能否把 SQL 引擎、存储、OS 和网络串成完整链路。

常见错误回答 “Planner 直接读取数据；索引命中后不会访问 Buffer；SQL 执行时间就是 HTTP 延迟。”

追问 为什么同一条 SQL 第一次慢、第二次快？

追问答案 可能是 relation 页进入 OS Cache/shared_buffers、语句或计划缓存预热、JIT/元数据缓存、连接建立等。必须控制参数、缓存状态和并发做实验，不能仅凭两次执行断言索引或 AIO 生效。

---

题 7：一条 UPDATE 与 SELECT 的路径有何关键差异？

题目 从锁、Buffer、索引、WAL 和 Commit 解释。

30 秒回答 UPDATE 先读取并定位行，还要做并发更新检查，创建新行版本、维护必要索引、把数据页标脏并生成 WAL；Commit 需满足 WAL 持久性，可能等待同步副本。SELECT 主要读取并返回数据，普通业务读取通常没有同等的数据修改 WAL 路径。

深入回答 UPDATE 的写放大可能包括表页、多个索引页、WAL、Checkpoint 后续写和 Vacuum 清理。热点行会串行等待，索引多会增加写成本。其优势是事务原子性和崩溃恢复，代价是更高 I/O、WAL 与并发冲突。替代方案包括批量化、减少无用索引、拆热点、Append-only 模型，但会改变查询与一致性复杂度。生产要分开测量定位、锁等待和 Commit 延迟。

面试官真正考察什么 是否理解写路径不是“在原行上改一个值”。

常见错误回答 “UPDATE 只比 SELECT 多一次磁盘写；Commit 必须同步写所有索引和数据页；没有索引就没有 WAL。”

追问 客户端在 COMMIT 时断线，能否直接重试 UPDATE？

追问答案 不能。Commit 可能已持久化但响应丢失，结果未知。应使用幂等键、唯一约束、状态查询和对账；只有业务操作具备幂等性并确认重试策略时才能重放完整事务。

---

题 8：pg_stat_activity.state='active' 为什么仍可能没有 CPU 消耗？

题目 怎样用 wait event 判断？

30 秒回答 active 表示查询处于执行生命周期，不表示当前正在运行 CPU。它可能等待 Lock、IO、ClientWrite、Timeout/PgSleep 或内部 LWLock。要结合 wait_event_type、wait_event、查询和 OS 进程指标。

深入回答 状态与等待是正交维度。active + Lock 是阻塞；active + ClientWrite 可能是结果太大或客户端读取慢；active + IO 需结合设备延迟；wait 为空可能正在 CPU，也可能采样错过短等待。优点是 PostgreSQL 提供精细等待观测，缺点是视图是瞬时快照。替代是持续采样、日志和 tracing，生产不能只抓一次截图。

面试官真正考察什么 是否具备等待事件驱动的排障思维。

常见错误回答 “active 数等于正在占 CPU 的进程数；wait_event 为空就证明 CPU 100%。”

追问 大量 ClientRead 是数据库慢吗？

追问答案 通常表示 Backend 空闲等待客户端下一条消息，可能只是连接池保持 Idle Connection。若连接过多仍有进程和内存成本，但它与内部锁或 I/O 慢不同；要结合 pool 和 active 数判断。

---

题 9：连接数暴涨且 CPU 变高，怎样判断是根因还是结果？

题目 给出排查顺序。

30 秒回答 先看应用 Pool Acquire、Pod 数和重试，再看数据库 active/idle 与 wait 分布，最后看 OS run queue、context switch 和高 CPU SQL。连接暴涨可能由请求变慢导致连接占用变久，也可能自身造成调度和内存过载。

深入回答 时间线最重要：如果存储先抖动，查询变慢后连接堆积，连接是放大器；如果发布先把每 Pod MaxConns 或 Pod 数提高，进程和认证先上涨，连接是诱因。优势是多层指标能建立因果；缺点是没有统一时钟和标签时难关联。替代是 tracing、application_name、发布事件和数据库日志统一采集。临时止损通常先限流和停止重试，而不是再加连接。

面试官真正考察什么 是否能避免相关性当因果，并跨应用、数据库、OS 分析。

常见错误回答 “连接多就把 max_connections 加倍；CPU 高就马上加 CPU。”

追问 为什么加 CPU 后 P99 仍可能不变？

追问答案 瓶颈可能是锁、WAL fsync、存储队列、Pool 排队或同步复制；也可能 Active Backend 继续随容量增加。需识别首个饱和资源并维持准入，不是单点纵向扩容。

---

题 10：区分 context 取消、CancelRequest 和四类服务端 timeout。

题目 请说明作用范围、连接存活和错误分类。

30 秒回答 pgx 默认 context 取消是客户端行为，通常立即返回并关闭连接；CancelRequest 用 PID+secret 请求服务端取消当前语句，常见 57014 且连接可保留；statement_timeout 取消慢语句；lock_timeout 只限制锁等待，常见 55P03；idle_in_transaction_session_timeout 终止空闲事务会话，25P03；[PG17+] transaction_timeout 终止超长事务会话，25P04。

深入回答 这些机制的可靠性边界不同。CancelRequest 没有成功 ACK，并存在查询完成竞态；语句取消在显式事务中会使事务失败；会话终止类 timeout 会使池丢弃连接。优势是可分层保护资源，缺点是错误配置会误杀。替代包括应用准入、锁 NOWAIT/SKIP LOCKED 和作业隔离，但不能替代 timeout。生产必须按 SQLSTATE 分类，不依赖错误文本。

面试官真正考察什么 是否理解取消不是单一机制，也不是事务结果确认。

常见错误回答 “所有超时都是 57014；context 超时后连接一定可复用；lock_timeout 限制整条 SQL。”

追问 为什么 lock_timeout 不应大于等于 statement_timeout？

追问答案 如果两者同时非零且 lock_timeout 不更短，等待锁时通常先触发 statement_timeout，lock_timeout 失去区分锁等待的价值。实际值还要给执行和响应留余量。

---

题 11：怎样定位“连接池满，但 PostgreSQL active 查询不多”？

题目 列出至少四种可能原因。

30 秒回答 可能是连接被长事务或应用代码长时间持有、Rows 未关闭、事务内调用外部服务、连接故障正在清理/重建，或池太小而 SQL 很短。要看 AcquiredConns、Acquire wait、pg_stat_activity 的 idle/idle in transaction、goroutine profile 和代码路径。

深入回答 池的 Acquired Connection 不一定正在执行 SQL：应用可在取到连接后做计算、读 Rows 很慢或忘记 Release。idle in transaction 表明事务边界错误；普通 idle 但仍被应用持有可能是连接对象未释放。优势是池统计能暴露数据库之前的等待，缺点是缺少请求标签时难定位。替代是 AcquireFunc、统一 DAO 包和 tracing 自动管理生命周期。生产需为 Acquire、query、transaction 分别计时。

面试官真正考察什么 是否知道连接泄漏与 SQL 慢是不同问题。

常见错误回答 “数据库 active 不多，所以把池扩大即可。”

追问 Query 返回后什么时候连接才可安全复用？

追问答案 通常要把 Rows 读取完或显式 Close()，并检查 rows.Err()；否则结果流仍占用连接。使用 QueryRow 时 Scan 完成结果消费。事务还必须 Commit/Rollback 后才能释放。

---

17.3 架构设计题（4 题）

题 12：如何为 100 个 Go 服务实例设计 PostgreSQL 连接池与准入控制？

题目 数据库 max_connections=1000，请给出设计方法，而不是固定数字。

30 秒回答 先用真实 workload 压测确定目标 P99 下的高效 Active Query 上限，再为复制、运维、迁移和监控留槽位，将剩余连接预算按服务优先级分配。每实例 Pool 上限结合实例数计算，并在 Pool 前使用有界并发和 Acquire deadline；超载时拒绝或降级。

深入回答 设计输入包括 CPU、内存、I/O、WAL、SQL 时长、事务比例和扩容上限。连接池解决复用，Admission Control 限制工作量；两者同时需要。优点是避免数据库进入过载区，缺点是高峰会主动拒绝部分请求。替代包括 PgBouncer 降低 Server Connection，但仍需 Query Admission；读副本可分担允许陈旧的读，但不能解决 Primary 写热点。生产还需按 OLTP、报表和作业分池，并让连接 lifetime 带抖动。

面试官真正考察什么 是否会做容量预算、过载保护和多服务治理。

常见错误回答 “1000/100=10，所以每个实例 10，结束”；忽略其他连接、Pod 扩容、不同服务权重和数据库高效并发。

追问 如何验证池大小？

追问答案 固定数据集与缓存状态，阶梯增加并发，记录 TPS、SQL P99、Acquire P99、CPU、I/O、锁、WAL 和错误率。选择在 SLO 内且继续加并发不再改善吞吐之前的安全区，并保留故障余量。

---

题 13：如何设计 OLTP 的超时层级？

题目 系统有 HTTP 2 秒 SLO，包含连接池等待、SQL、Commit 和响应编码。

30 秒回答 为 Pool Acquire、lock wait、statement、数据库调用 context 和 HTTP deadline 分层留预算，通常让 lock_timeout 最短，statement_timeout 不超过数据库调用 deadline，HTTP deadline 最外层；按接口和角色配置，不全局一刀切。

深入回答 例如先预算连接等待、锁等待、SQL、Commit、网络和清理，但数值必须由 P99 测量决定。服务端 timeout 的优势是数据库能主动停止工作并返回 SQLSTATE；客户端 context 保护调用方；Admission Control 防止过载。代价是过短会误杀，过长会堆积。报表和迁移应使用独立池与策略。写操作还必须处理 Commit 结果未知，timeout 不能替代幂等。

面试官真正考察什么 是否能把端到端 SLO 拆成层级护栏并理解取消语义。

常见错误回答 “所有 timeout 都设 2 秒”；“只设 Go context 就够了”；“超时后自动重试”。

追问 为何数据库 context 不能与 HTTP deadline 完全相同？

追问答案 需要为取消传播、连接清理、错误编码和 HTTP 响应留余量；完全相同会让上游先断开，应用来不及判断数据库结果，也更容易产生连接抖动和提交歧义。

---

题 14：如何设计应用应对 PostgreSQL Primary 故障转移？

题目 请覆盖旧连接、新连接验证、重试、RPO/RTO 和 Commit 歧义。

30 秒回答 旧连接不能迁移，Pool 必须丢弃并重建；新连接通过代理/DNS/多 Host 定位并验证是可写 Primary。重连使用有界退避和抖动。RPO 由复制模式决定，RTO 受检测、提升和重连影响。事务中断要重试完整且幂等的事务，COMMIT 错误按结果未知处理。

深入回答 HA 层需要仲裁和 Fencing 防脑裂；应用层需要短连接检测、目标验证和流量准入。同步复制降低某些 RPO，但增加 Commit 延迟和副本故障耦合；异步复制延迟低但故障转移可能丢数据。替代是业务双写或分布式数据库，但一致性与运维复杂度更高。Failback 也必须重新 Fencing 和同步旧节点，不能简单把 DNS 切回。

面试官真正考察什么 是否能将数据库复制、连接生命周期和业务幂等统一设计。

常见错误回答 “pgxpool 会自动无损迁移事务”；“连接串有两个 Host 就没有 RPO”；“所有错误都重试一次即可。”

追问 怎样处理故障转移时已发送但未收到 COMMIT 响应的支付事务？

追问答案 标记为 unknown，用幂等业务键查询新 Primary 上的最终状态；若异步复制可能丢失，还需结合外部账本/对账。绝不能仅凭客户端错误重复扣款。

---

题 15：为 PostgreSQL 请求生命周期设计可观测性体系。

题目 需要哪些应用、数据库、OS 和 HA 指标，如何关联？

30 秒回答 应用记录请求、Acquire、SQL、Commit 四段耗时和 SQLSTATE；pgxpool 导出连接与等待；PostgreSQL 采集 activity/wait、SQL 统计、I/O、WAL、Checkpoint、Vacuum 和复制；OS 采集 CPU、内存、磁盘和网络。用 application_name、规范化 SQL、trace/request ID 和统一时钟关联。

深入回答 必须同时有 Metrics、Logs、Traces 和受控计划样本。Metrics 适合趋势和告警，日志给错误细节，Trace 给请求链路，EXPLAIN 验证计划；各自有采集成本与敏感数据风险。替代只用 APM 或只用数据库监控都会丢失边界。生产需脱敏 SQL、限制查询文本权限、为高基数标签设上限，并保留发布、扩容、切换事件。

面试官真正考察什么 是否能设计跨层、可建立因果的观测体系，而不是罗列监控项。

常见错误回答 “只监控 CPU 和连接数”；“打开所有 SQL 全量日志且永久保留参数”；“平均延迟足够。”

追问 怎样证明 P99 来自 Pool 排队而不是数据库执行？

追问答案 在同一请求 Trace 中分别记录 Acquire 起止与 SQL 起止；对比 Pool EmptyAcquireWaitTime 区间增量、数据库 query duration 和 active/wait 分布。如果请求慢但 SQL span 短、Acquire span 长，根因在数据库之前的准入或连接占用。

---

18. 练习与完整参考答案

18.1 理论题（5 题）

理论题 1

题目 为什么同一 PostgreSQL Cluster 可以包含多个 Database，但普通 SQL 不能直接用 other_database.schema.table 跨库访问？

参考答案 客户端在 StartupMessage 阶段选择 Database，随后 Backend 绑定该 Database 的系统目录和对象命名空间。Schema 是 Database 内的命名空间，不是 Cluster 级命名空间。因此普通 relation 名称最多解析到 schema.relation。跨 Database 需要建立另一条连接，或显式使用 postgres_fdw、dblink 等机制；这些方案引入独立连接、事务边界、权限和网络成本，不能视为本地表访问。

理论题 2

题目 为什么一次 shared_buffers miss 不能证明发生了物理磁盘读取？

参考答案 Buffer Manager miss 后会通过 Storage Manager 和操作系统读取 relation 文件；对应文件页可能已经在 OS Page Cache，系统调用无需访问设备。反过来，即使 shared_buffers hit 很高，WAL、Checkpoint、临时文件和其他数据库仍可能产生大量物理 I/O。应结合 pg_stat_io、I/O timing、EXPLAIN BUFFERS 和 OS 设备指标判断。

理论题 3

题目 为什么 state='active' AND wait_event_type='Lock' 不应计入“正在使用 CPU 的查询数”？

参考答案 active 表示查询仍在执行生命周期，Lock 表示采样瞬间正在等待 SQL 层锁。它占用 Backend、连接和事务资源，但通常没有推进有效计算。容量分析应至少区分 CPU-running、锁等待、I/O 等待和客户端等待；锁等待仍会降低吞吐并放大排队，不能因为不耗 CPU 就忽略。

理论题 4

题目 为什么数据页可以在事务提交后才写入数据文件而不破坏 Durability？

参考答案 WAL 先记录足以重做修改的信息。默认同步提交下，Commit Record 及其前序必要 WAL 达到要求的持久化位置后，服务端才能确认提交。若崩溃发生在脏数据页写出之前，恢复过程从 Checkpoint 后重放 WAL，重建已提交变化。前提是不能关闭 fsync、full_page_writes 等保护并声称仍有相同持久性。

理论题 5

题目 为什么一个有 500 个并发 HTTP 请求的应用可能只需要 30 个 PostgreSQL 连接？又为什么 30 个连接仍可能过多？

参考答案 许多请求可在连接池前排队或根本不访问数据库；单个连接可在一秒内串行完成许多短事务，所以业务并发不等于连接数。另一方面，如果 30 条查询都是高 CPU 大扫描、竞争同一热点行或等待 WAL fsync，30 个 Active Backend 也可能超过数据库高效并发。连接数必须按 SQL 工作量和 SLO 测量，而非按请求数线性换算。

---

18.2 实验题（3 题）

实验题 1：证明池中连接是可复用 Backend，而不是每次新建进程

题目 用 pgxpool 设置 MaxConns=3，启动 20 个有界 goroutine，每个 goroutine 执行 SELECT pg_backend_pid(), pg_sleep(0.2)。记录所有 PID，判断最多出现多少个稳定 Backend，并观察 Pool Acquire 等待。

参考答案 核心代码结构：

sem := make(chan struct{}, 20)
var wg sync.WaitGroup
var mu sync.Mutex
pids := map[int32]int{}

for i := 0; i < 20; i++ {
    wg.Add(1)
    go func() {
        defer wg.Done()
        sem <- struct{}{}
        defer func() { <-sem }()

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

        var pid int32
        if err := pool.QueryRow(
            ctx,
            `SELECT pg_backend_pid() FROM (SELECT pg_sleep($1::double precision)) AS s`,
            0.2,
        ).Scan(&pid); err != nil {
            log.Printf("query: %v", err)
            return
        }

        mu.Lock()
        pids[pid]++
        mu.Unlock()
    }()
}
wg.Wait()

更简单可靠的 SQL 是先 Acquire，再在同一连接分别执行 SELECT pg_backend_pid() 与 SELECT pg_sleep($1)，最后 Release。预期稳定 PID 数不超过 3；20 个 goroutine 分批复用这些 Backend，Pool EmptyAcquireWaitTime 会增长。若 context 太短，部分 Acquire 被取消。记录 PostgreSQL pg_stat_activity 与 pool.Stat()，不能以固定耗时作为通用结论。

实验题 2：在同一连接观察 pgx Prepared Statement

题目 使用 pgx 默认执行模式，在同一个 *pgxpool.Conn 上重复执行参数化 SQL，并查询 pg_prepared_statements。然后释放连接再重新 Acquire，分析 Prepared Statement 为什么可能仍在或不在。

参考答案 示例：

conn, err := pool.Acquire(ctx)
if err != nil {
    return err
}
defer conn.Release()

for _, id := range []int{1, 2, 3} {
    var value int
    err = conn.QueryRow(ctx, `SELECT $1::int`, id).Scan(&value)
    if err != nil {
        return err
    }
}

rows, err := conn.Query(ctx, `
    SELECT name, statement, from_sql, generic_plans, custom_plans
    FROM pg_prepared_statements
    ORDER BY name
`)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    var name, statement string
    var fromSQL bool
    var genericPlans, customPlans int64
    if err := rows.Scan(
        &name, &statement, &fromSQL, &genericPlans, &customPlans,
    ); err != nil {
        return err
    }
    fmt.Println(name, statement, fromSQL, genericPlans, customPlans)
}
if err := rows.Err(); err != nil {
    return err
}

Prepared Statement 属于 Backend 会话。释放到 Pool 不会关闭连接，因此下次 Acquire 如果拿到同一个 Backend，语句可能仍在；如果拿到另一连接、连接因 lifetime 被替换、context 取消被关闭或发生故障转移，语句就不在。不要依赖自动生成的 statement name；只验证生命周期和数量。

实验题 3：观察 [PG18] AIO 的位置，而不是制造固定结论

题目 在 PostgreSQL 18 测试环境创建大于 shared_buffers 可用工作集的表，执行顺序扫描，同时在另一会话轮询 pg_aios 和 pg_stat_io。比较 io_method=worker 与经审批重启后的其他可用模式。

参考答案 准备数据时记录行宽和总大小：

CREATE SCHEMA IF NOT EXISTS ch1_aio;

CREATE TABLE ch1_aio.t AS
SELECT
    g AS id,
    repeat(md5(g::text), 8) AS payload
FROM generate_series(1, 5000000) AS g;

ANALYZE ch1_aio.t;

SELECT
    pg_size_pretty(pg_relation_size('ch1_aio.t')) AS heap_size,
    current_setting('shared_buffers') AS shared_buffers,
    current_setting('io_method') AS io_method;

扫描会话：

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT count(*)
FROM ch1_aio.t
WHERE payload LIKE '%not-present%';

观察会话：

SELECT pid, state, operation, length, target, target_desc, result
FROM pg_aios
ORDER BY pid, io_id;

SELECT *
FROM pg_stat_io
ORDER BY backend_type, object, context;

必须分别记录冷/热缓存、io_method、effective_io_concurrency、数据量、测试时长、并发、CPU、设备队列、P50/P95/P99。pg_aios 是瞬时视图，可能采不到；不能据此断言 AIO 未使用。修改 io_method 通常涉及实例级配置和平台支持，严禁在生产临时切换做实验。

清理：

DROP SCHEMA IF EXISTS ch1_aio CASCADE;

---

18.3 排障题（2 题）

排障题 1：大量 active 会话等待 ClientWrite

题目 API 延迟升高，CPU 和磁盘都不高，但许多会话为 active / Client / ClientWrite。你如何分析？

参考答案 这通常表示 Backend 在向客户端发送结果时被阻塞，可能是结果集过大、客户端读取慢、网络拥塞、应用没有及时迭代 Rows，或代理缓冲。步骤：

1. 按 PID、application_name 和 query 找大结果 SQL；
2. 检查接口是否缺少 LIMIT、分页或流式消费；
3. 查看网络吞吐、丢包、代理和客户端 goroutine profile；
4. 确认 rows.Close() 与 rows.Err()；
5. 记录返回行数和响应字节；
6. 临时限制高成本接口、降低结果规模；
7. 根治为分页、只选必要列、异步导出或对象存储交付。

增加数据库连接通常会让更多 Backend 同时阻塞写客户端，进一步占满 Pool。

排障题 2：idle in transaction 阻塞 DDL 与 Vacuum

题目 一个 Backend 已 idle in transaction 40 分钟，其他会话等待 relation lock，表膨胀持续增加。如何处理？

参考答案 先用 pg_blocking_pids 确认它确实是 blocker，查看 xact_start、state_change、application_name 和最近 query，联系业务负责人判断事务内容。临时措施是让应用显式 Rollback/Commit；无响应且影响严重时，经审批 pg_terminate_backend，因为仅 pg_cancel_backend 对当前没有运行语句的 idle 事务无效。根因通常是异常路径未 Rollback、事务内等待外部服务或客户端遗忘读取。修复代码的事务模板，并按角色设置合理 idle_in_transaction_session_timeout；同时验证 Pool 会正确丢弃被服务端终止的连接。

---

18.4 系统设计题（1 题）

系统设计题：多服务、读副本和故障转移下的请求生命周期设计

题目 设计一个订单平台：80 个 API Pod、20 个 Worker Pod、一个 Primary、两个异步 Standby。API P99 目标 300ms，Worker 可接受 5 秒。要求防止连接风暴、支持故障转移，并处理订单创建的 Commit 结果未知。

参考答案

连接与工作负载隔离

• API 与 Worker 使用不同数据库角色、application_name 和 pgxpool；
• 通过压测确定 Primary 高效 Active Query 上限，不直接按 100 个 Pod 平分 max_connections；
• 为复制、运维、迁移和故障恢复预留连接；
• API 使用较小 Pool 和严格有界并发；Worker 使用独立配额，支持暂停；
• 报表读若允许陈旧可路由 Standby，但订单创建、读己之写和库存判断留在 Primary。

准入与超时

• API 入口有请求队列上限和快速拒绝；
• Pool Acquire deadline、较短 lock_timeout、按接口 statement_timeout、数据库调用 context、HTTP 300ms deadline 分层；
• Worker 使用独立的 5 秒策略，不让全局参数影响维护；
• 所有重试有最大次数、指数退避和抖动，故障期间可集中熔断。

事务与幂等

• 订单创建携带 idempotency key；
• 数据库唯一约束 (tenant_id, idempotency_key)；
• 事务中写订单、库存与 Outbox，不调用外部支付 API；
• BeginTx 后 defer Rollback，单独检查 Commit；
• Commit 错误将请求标记为 unknown，通过幂等键查询最终状态，不盲目重试；
• Worker 消费 Outbox 时也使用幂等消费键。

故障转移

• HA 层负责健康检测、提升和 Fencing；
• 应用通过代理、VIP 或多 Host 连接到 Primary，并验证目标可写；
• 旧连接失败后 Pool 丢弃，重连带抖动；
• 切换期间入口降低准入，避免所有 Pod 同时认证；
• 异步复制的 RPO 不是零，业务对账需覆盖可能丢失的最近事务；
• Planned Switchover 先排空写流量和 Worker，再确认复制追平。

监控

• API/Worker 分别监控 Acquire、SQL、Commit P99；
• Pool 连接、等待、取消和新建率；
• Primary active/wait、锁、WAL、Checkpoint；
• 每个 Standby 的发送、写、刷、重放延迟；
• 幂等冲突、unknown 事务、Outbox backlog；
• 切换 RTO 与实际恢复成功率。

取舍

• 异步 Standby 提交延迟低，但故障转移可能丢最近数据；
• 同步复制可降低 RPO，却会把副本延迟加入 300ms 路径；
• 可按订单重要性选择同步级别，但复杂度和可用性代价必须显式评估；
• PgBouncer 可降低 Server Connection 数，但不能替代查询级准入和事务幂等。

---

19. 本章检查清单

架构与术语

• 我能够区分 Cluster、Instance、Database、Schema 和 Relation。
• 我能够解释 Main Server Process 与 Backend Process 的父子关系。
• 我能够说明哪些状态在共享内存，哪些状态属于 Backend 私有内存。
• 我能够区分 shared_buffers、WAL Buffers、OS Page Cache 和 work_mem。
• 我能够说明 Lock Table 与 ProcArray 的大致作用，而不把它们混同于系统视图。
• 我能够区分 Background Writer、Checkpointer、WAL Writer、Archiver、WAL Sender 和 WAL Receiver。

请求生命周期

• 我能够画出一条 SQL 从 pgxpool Acquire 到 ReadyForQuery 的完整路径。
• 我能够解释 Simple Query Protocol 与 Extended Query Protocol。
• 我能够解释 Parse、Bind、Describe、Execute、Sync 的职责。
• 我能够解释 Parser、Analyzer、Rewriter、Planner、Executor 的职责边界。
• 我能够说明 Buffer Manager、Storage Manager 与操作系统的边界。
• 我能够比较 SELECT 与 UPDATE 在锁、Buffer、索引、WAL 和 Commit 上的差异。
• 我能够解释为什么提交不要求全部脏数据页立即写入数据文件。
• 我能够说明 [PG18] AIO 与 pg_aios 的位置和限制。

观测与实验

• 我能够用 pg_backend_pid()、pg_stat_activity 和 ps 映射连接到 Backend。
• 我能够正确解释 backend_start、xact_start、query_start 和 state_change。
• 我不会只看 state='active' 判断 CPU，而会结合 wait event。
• 我能够复现 PgSleep、锁等待和取消后的状态变化。
• 我能够从 pgxpool 统计区分连接池排队和数据库执行慢。

取消、并发与高可用

• 我能够区分 context 取消、CancelRequest、statement_timeout、lock_timeout、idle_in_transaction_session_timeout 和 [PG17+] transaction_timeout。
• 我能够使用 errors.As、*pgconn.PgError 和 SQLSTATE 分类错误。
• 我知道客户端超时不证明 SQL 已回滚。
• 我能够区分 goroutine 并发、连接数、活跃查询数、TPS 和排队请求数。
• 我能够为多个服务和 Pod 建立全局连接预算。
• 我能够设计有界队列、Backpressure 和 Admission Control。
• 我知道故障转移后旧连接不能迁移，Pool 必须重建连接。
• 我能够解释 RPO 与 RTO 分别由哪些机制影响。
• 我能够处理 Commit 结果不确定，而不是盲目重试。
• 我能够分析本章内容对性能、并发和高可用的代价。

---

20. 版本差异速查

能力	PG14	PG15	PG16	PG17	PG18
pg_stat_activity state/wait 观测	✓	✓	✓	✓	✓
pg_stat_io	—	—	[PG16+]	✓	增强
pg_wait_events 描述视图	—	—	—	[PG17+]	✓
pg_stat_checkpointer 独立视图	—	—	—	[PG17+]	✓
transaction_timeout	—	—	—	[PG17+]	✓
内置 AIO 框架与 io_method	—	—	—	—	[PG18]
pg_aios	—	—	—	—	[PG18]
协议 3.2 新能力	—	—	—	—	[PG18]，需客户端/代理协商

版本迁移时不要只检查 SQL 语法，还要检查监控字段、统计视图拆分、驱动协议协商、代理兼容和 timeout 参数。用新版本 pg_dump 产生的设置也可能被旧服务端拒绝，例如旧版本不认识 transaction_timeout。

---

21. 官方资料来源

PostgreSQL 18 官方文档

1. PostgreSQL 架构基础：Client/Server 模型
2. PostgreSQL Glossary：Cluster、Instance、Database、Schema、Relation、Backend 等术语
3. PostgreSQL Internals：The Path of a Query
4. How Connections Are Established
5. The Parser Stage
6. The PostgreSQL Rule System
7. Planner/Optimizer
8. Executor
9. Frontend/Backend Protocol：Message Flow
10. Frontend/Backend Protocol：Message Formats
11. Monitoring Database Activity 与 pg_stat_activity
12. pg_wait_events
13. pg_aios
14. pg_shmem_allocations
15. pg_backend_memory_contexts
16. Resource Consumption：Memory 与 Asynchronous Behavior
17. Client Connection Defaults：各类 Timeout
18. WAL Introduction
19. Error Codes
20. PostgreSQL 18 Release Notes
21. PostgreSQL 17 Release Notes：transaction_timeout、pg_wait_events、pg_stat_checkpointer
22. PostgreSQL 16 Release Notes：pg_stat_io

PostgreSQL 官方源码

1. ProcArray：src/backend/storage/ipc/procarray.c（REL_18_STABLE）
2. Heavyweight Lock Manager：src/backend/storage/lmgr/lock.c
3. Buffer Manager：src/backend/storage/buffer/bufmgr.c
4. Storage Manager：src/backend/storage/smgr/smgr.c
5. Memory Context：src/backend/utils/mmgr/mcxt.c
6. PostgreSQL 18 Asynchronous and Direct I/O README

pgx 官方资料

1. pgx/v5 API
2. pgxpool API 与 Pool Stats
3. pgconn Context、CancelRequest 与 CancelRequestContextWatcherHandler
4. ctxwatch API
5. pgx 官方 GitHub

---

本章结论：PostgreSQL 性能与可靠性问题不能只从 SQL 文本看。请求先经过应用准入和连接池，再进入一个会话专属 Backend，通过协议、查询处理器、共享内存、Buffer/Storage Manager、操作系统、WAL 和后台进程完成。只有把这些边界同时纳入观测，才能正确判断慢在哪里、谁在等待、取消是否生效、连接为何耗尽，以及故障转移后事务是否安全。