本篇沿用第 1 篇的订单系统案例，把性能变更收敛为可验证、可监控、可回滚的生产闭环。

优化完成的判定标准

一项优化只有同时满足下面四点，才可以结束观察：

  • 业务语义不变，结果集、排序、分页、数据时效和错误率符合约定。
  • 目标指标在代表性参数、数据规模与真实并发下达到预先定义的目标。
  • 写入延迟、CPU、I/O、内存、WAL、锁等待和维护任务没有出现不可接受的退化。
  • 观察窗口覆盖正常波动与至少一个业务高峰，且保留了变更记录、证据和可执行的回滚步骤。

只看一次最低耗时会把缓存预热、参数差异和瞬时负载误当成优化收益。应同时比较分位延迟、累计数据库时间、调用次数和实际处理量。

修改前保存基线证据

  • 保存实际 SQL、参数类型和具有代表性的参数值。
  • 记录调用频率、平均延迟、p95/p99 和累计数据库时间。
  • 保存 EXPLAIN (ANALYZE, BUFFERS)，重点记录实际行数、循环次数和块读取。
  • 记录测试数据量、数据分布、缓存状态和 PostgreSQL 配置。
  • 确认是否存在锁等待、连接池排队或长事务。
  • 记录目标表与索引大小、死元组、Vacuum 和 Analyze 状态。
  • 明确业务正确性要求，例如排序稳定性、数据实时性和分页语义。
  • 设计变更的锁级别、执行窗口、超时、取消条件和回滚方案。

pg_stat_statements 是累计统计。比较前后快照时应记录采样起止时间，并按窗口增量计算 calls、total_exec_time、rows、共享块和临时块；不要为了单次发布随意重置整个实例的统计历史。

评估锁级别与执行窗口

先在预发布环境确认 DDL、数据量、额外磁盘和预计时长，再核对生产中的长事务与阻塞链。普通 CREATE INDEX 会阻塞目标表写入；CREATE INDEX CONCURRENTLY 允许写入继续，但需要更多阶段与等待，耗时通常更长，失败后还可能留下 INVALID 索引。并发建索引不能放在事务块中执行，因此“回滚发布事务”不是它的撤销方案。

执行前应写清负责人、开始与最晚结束时间、lock_timeout、statement_timeout、取消命令，以及失败后如何识别并处理无效对象。涉及表重写、参数重启或不可逆数据迁移时，先准备恢复路径，不能把“再执行一条反向 SQL”当作完整回滚。

修改后使用相同口径复测

  • 使用相同参数和数据规模重新获取执行计划。
  • 对比处理行数、块读取、临时文件、排序方式和执行时间，而不只比较一次最低耗时。
  • 检查新增索引的大小、实际使用次数和写入延迟。
  • 对 INSERT、UPDATE、DELETE 和 Vacuum 负载进行回归测试。
  • 检查执行计划是否只对部分参数改善，却让其他参数明显退化。
  • 验证物化视图的数据延迟、刷新耗时和失败恢复。
  • 验证分区裁剪、分区创建和历史数据清理流程。
  • 在真实并发下观察连接数、内存、CPU、I/O、WAL 和锁等待。
  • 保留足够观察窗口，再决定是否删除旧索引或扩大变更范围。

EXPLAIN ANALYZE 会真正执行语句。对写语句取证时应优先使用隔离环境；即使包在事务中回滚，也仍要检查序列、触发器或外部函数等事务外副作用。

同时观察读取、写入与资源副作用

读取侧关注目标 SQL 的 p95/p99、累计执行时间、实际行数、块读取、临时文件和计划变化；写入侧关注事务延迟、锁等待、死锁、索引维护成本与 WAL 生成速率；资源侧关注 CPU、存储延迟、缓存命中、临时空间、连接数和 Vacuum 进度。

pg_stat_activity 反映当前会话与等待事件，pg_stat_statements 反映规范化语句的累计执行统计，两者时间语义不同。应把数据库指标与同一时段的应用吞吐、错误率和链路追踪对齐，避免把业务流量变化解释成数据库改动效果。

定义回滚阈值

阈值必须在上线前填写，并明确“连续多久”才触发，避免现场临时争论。下面的空位是发布单模板，不是 PostgreSQL 的通用推荐值。

指标基线目标回滚阈值观察窗口
目标 SQL p95同业务时段 __ ms不高于 __ ms连续 __ 分钟高于 __ ms__ 分钟滚动窗口
写事务 p95同业务时段 __ ms不高于 __ ms连续 __ 分钟高于 __ ms__ 分钟滚动窗口
错误或超时率__%不高于 __%连续 __ 分钟高于 __%__ 分钟滚动窗口
CPU 或存储延迟CPU __%；I/O __ ms不突破容量预算连续 __ 分钟超过 __高峰期加密观察
WAL 生成速率__ MB/min不高于 __ MB/min复制延迟或磁盘余量越过 __发布后 __ 小时

任何正确性错误、数据丢失风险或持续阻塞都应作为立即停止条件，不必等待性能窗口结束。触发阈值后先停止扩量或取消仍在执行的变更，再按预案撤销，并继续观察指标是否回到基线。

常见症状与优先检查方向

症状优先证据常见方向
估算行数与实际行数差距巨大执行计划、pg_stats、Analyze 时间更新统计信息，提高列统计目标，评估扩展统计
扫描大量行后只返回少量结果Rows Removed by Filter、块读取改写条件，设计匹配查询的索引
深分页越来越慢OFFSET 大小、扫描行数稳定排序键和 Keyset Pagination
排序或哈希写临时文件temp blocks、Sort Method减少输入行数，匹配排序索引，谨慎调整 work_mem
Index Only Scan 仍大量回表Heap Fetches、可见性与更新频率检查 Vacuum，不要过度依赖覆盖索引
查询时间主要在等待wait_event_type、阻塞链缩短事务，统一锁顺序，设置合理超时
表和索引持续增长死元组、更新量、对象大小调整 autovacuum，检查长事务和索引写放大
重复报表聚合消耗很高调用频率、扫描块数、刷新容忍度物化视图或增量汇总表
历史数据删除成本很高删除批次、WAL、Vacuum、保留规则按生命周期设计分区

这张表只用于确定调查方向，不是自动处方。同一个症状可能同时受到数据分布、并发、存储和应用访问模式影响，最终仍要回到执行计划和业务约束。

把优化过程变成持续闭环

每次变更都应留下同一种记录：问题与假设、修改前基线、变更内容、执行窗口、修改后证据、阈值判断和最终处置。未达目标就回滚或缩小假设，出现新副作用就重新取证；只有收益稳定且风险在预算内，才扩大范围并清理旧方案。

这个闭环的价值不是证明某次修改“看起来更快”，而是让下一次优化能够复用口径、对比历史并更早停止错误方向。

参考资料