跳转到主要内容

42 检查清单

每个习惯一行，从成本最低的办法开始，依次检查昂贵的工作、查询、索引、搜索、模式、模式变更与生产运维，再到更大的机器、横向扩展，以及“现在就很慢”的故障。每项都链接到证明其理由的章节。

检查清单，就是去掉论证的这本书：每个习惯一行，都链接到说明理由的小节。各节遵循实战手册的顺序，先用成本最低的办法（顺序就是方法）。找出昂贵的工作，修好计划、索引和模式，做好日常维护、连接和配置，之后才考虑换更大的机器。

从上往下做。只有测量证明某项没问题，才跳过它；不能因为后面的项目看起来更容易就先跳过去。最后一节留给数据库已经很慢的那一天。

动手修之前，先找出昂贵的工作

你想到的那条慢查询，很少就是成本最高的那条。先测量，每次改动后继续测量。

  • 安装 pg_stat_statements 并开启 track_io_timing = on（设置方法），在繁忙时段取两份快照，比较差值（快照与差值）。
  • 按总耗时和每次调用访问的共享块数给语句排名（四种排名）。
  • 在造成问题的负载下采样等待事件，说清楚受限的资源是什么（等待采样）。
  • 改动前先写一句话：瓶颈是 X，因为 Y 显示 Z（找出昂贵的工作）。
  • 每次只改一件事，在具有生产数据特征的数据上，用相同负载测量改动前后的结果（端到端测量）。
  • 每次修复都记录前后每次调用访问的页数；从 35,249 到 91，就是一条好记录（第一个索引）。

审查的是计划，不是代码

一条看似无害的 ORM 调用，可能读取整张表。每条随请求执行的查询，上线前都跑一次 explain (analyze, buffers)。

吃过一次亏才会记住的边界

下面这些语句都能运行，返回看似合理的结果，或规规矩矩地报错，却往往让团队吃过一次亏才记住。在审查时抓住它们，让意外发生在测试里。

能运行，却不正确的 SQL

  • 要的是实际时钟时间，就用 clock_timestamp()；now() 是事务开始的时间（时间戳）。
  • 整数相除前，把一侧转换为 numeric；7/2 是 3（除法、|| 与 NULL 排序）。
  • 拼接可能含 NULL 的字符串，用 concat_ws() 或 coalesce()，不要直接用 ||（同节）。
  • 可空列按最新在前排序时，加上 nulls last，服务这个查询的索引也要加（同节）。
  • 按所有选出的列或主键分组；任意值都行时，明确写 any_value()（其他小差异）。
  • 按键选取批次来更新；没有 UPDATE ... LIMIT（回填）。
  • 跳过没有任何变化的更新；空操作仍会写入一个行版本及其 WAL（WAL 从哪里来）。

事务

  • 记住：一个错误会中止整个事务；用 on conflict 避免它，或用保存点限制影响（一个错误就会中止）。
  • 不要在循环里放保存点、异常块和 ORM 嵌套事务；一个事务里超过 64 个，所有会话都要付出代价（子事务）。
  • 只对 40001 和 40P01 重试整个事务，使用有上限、带抖动的退避；其他错误不重试（重试）。
  • 锁父行用 for no key update，除非要删除它或修改它的键；for update 会阻塞子行插入（行锁）。
  • truncate 只留给暂存表和测试；旧快照会看到空表（删除只给行盖章）。

索引要对得起写入成本，否则就删掉

每个索引都是一场押注：换来某些查询更快的读取，但只要它还在，每次写入就得交税。每个索引都要说清自己服务的是哪条查询。

  • 从查询出发设计：等值条件列在前，接着一个范围列或排序列，并说清服务的是哪条查询（逐步设计）。
  • 只有少数取值的列，不要单独建索引（为什么 2% 的行会读到 68% 的表）。
  • 对热点子集用部分索引，把谓词写成字面量（部分索引）。
  • 查询必须用某个表达式时，就建表达式索引，例如 lower(email)（表达式索引）。
  • 日期表达式固定时区，例如 ((created_at at time zone 'UTC')::date)；绝不为通过检查就把函数标为 immutable（为什么 created_at::date 不能建索引）。
  • 在语言学排序规则下，like 'abc%' 用 text_pattern_ops 索引，'%abc%' 用三字符组（前缀搜索）。
  • 用 include 覆盖热点查询，不求覆盖所有查询（覆盖索引）。
  • 每个 Index Only Scan 都要比较 Heap Fetches 与返回行数；若接近行数，说明清理没有让可见性映射保持最新（仅索引扫描）。
  • 按运算符选择索引类型：GIN、三字符组、GiST、BRIN 或哈希（选择索引类型）。
  • 包含查询用 jsonb_path_ops；没有哪种 GIN 能服务 payload->>'key' = 'x'（jsonb 包含查询）。
  • 找出没有索引的外键，为它们建索引（查询语句）。
  • 新增索引前先算写入税：每次插入都会访问每个索引，它还可能让更新变成非 HOT 更新（写入税）。
  • 长时间窗口内 idx_scan = 0 的索引，在每个副本上都检查过后就删除，支撑约束的除外；重复索引也删除（未使用与重复索引）。
  • 在大表上动工前先问 HypoPG（假设性问题）。
  • 用 reindex concurrently 重建膨胀索引（测量膨胀）。
  • 普通 create index 只用于新表、空表或没有写入的表；所有有写入的表都用 concurrently（判断规则）。
  • 并发构建后，检查是否留下 INVALID 索引；重试前先删除它（构建失败时）。
  • 每次升级操作系统、glibc 或 ICU 后，重建文本索引，然后才能刷新记录的排序规则版本（排序规则、发现问题）。

添搜索引擎之前，先在 PostgreSQL 内搜索

全文搜索和向量搜索也是索引，有各自的成本。像测量其他查询那样，测量召回率和页数。

  • 每次调用都传入文本搜索配置，例如 'english'；按一种配置建的索引不能服务另一种配置（配置）。
  • 把 tsvector 存在生成列里，建 GIN 索引；通过表达式索引做排名耗时 170 ms，而前者只需 4.6 ms（存储 tsvector）。
  • 用户键入的任何内容都用 websearch_to_tsquery 解析（用户输入）。
  • 排名前先按租户或日期缩小范围，只对返回的行运行 ts_headline（摘要片段）。
  • 信任近似向量索引之前，先用自己的查询，对照精确搜索测量 recall@10（精确搜索）。
  • HNSW 的 hnsw.ef_search 保持在 40 或以上（我们测试中的召回率为 0.97）；漏召代价高的查询，单独提高它（ef_search 用延迟换召回率）。
  • IVFFlat 的 ivfflat.probes 绝不能停在 1：探测一次时 recall@10 是 0.636，五次时是 1.000（IVFFlat 的默认一次探测是陷阱）。
  • 向量搜索有过滤条件时，开启 hnsw.iterative_scan，或让 B-树在高选择性过滤下完成精确搜索（过滤条件）。
  • 用倒数排名融合合并关键词与向量结果，不要把分数直接相加（混合搜索）。

第一行插入前，先审查模式

只要表还在，模式就决定着每一行的成本。事后修改它是一场模式变更，不是发个版就行。

类型

  • 每个 ID 及引用它的列都用 bigint（ID 使用 bigint）。
  • 每个时刻都用 timestamptz，会话采用 UTC（始终用 timestamptz）。
  • 用 text 加检查约束，不用 varchar(n)，更不用 char(n)（就用 text）。
  • 金额用 numeric 或以分为单位的 bigint 存储，旁边放货币列，绝不用浮点数（金额）。
  • 用 archived_at 代替 is_archived；将来可能改变的枚举，改用 text 加检查约束（小型取值集合）。
  • 每列选定一种不区分大小写的策略，并在模式中强制执行（选定一种）。
  • 新数据库默认采用内置的 C.UTF-8 排序规则，只有人要看排序结果的地方才选语言学排序规则（三种提供者）。
  • 标识符绝不加引号：用不带引号的 snake_case，第一次模式变更前就设置好 ORM 的命名策略（其他小差异）。
  • 用标识列，不用 serial；避开 money、char(n) 和 timetz（应避开的类型）。

键与公开 ID

  • 每张表都给一个 bigint generated always as identity 代理键；自然键用 unique（自然键会变）。
  • 绝不用序列生成发票号码或构建变更流：序列会有空缺，也不按提交排序（空缺）。
  • 需要 UUID 时，用 uuidv7()，存为 uuid；随机 v4 键产生的 WAL 是其 3.8 倍（随机键）。
  • 客户端生成 ID 时，保留服务端的 created_at，校验每个 ID；发生冲突，用 do nothing 再在调用者账户范围内查询，绝不用 do update（客户端的 UUIDv7 是输入，不是时钟）。
  • 绝不对外暴露计数器：另设公开 ID，存为 uuid，输出时加前缀（存储 uuid，打印前缀）。
  • 每张租户表都带 account_id，键也以它开头（带上租户）。
  • 用复合外键，让引用无法跨越租户（让跨租户引用不可能发生）。

有意选择约束

  • 每个事实只存一次，每个有意保留的副本都恰好指定一个写入者（一个写入者）。
  • 账户、用户、项目、成员关系和钱款保留外键（我们保留外键的地方）。
  • 事件、日志、指标、审计记录、跨边界引用和导入，删除外键或声明为 not enforced（同节）。
  • 每条停止强制检查的引用，都安排孤立记录检查（检查你不再强制执行的规则）。
  • 每个强制执行的外键，都在引用方建索引（引发扫描的删除）。
  • 级联删除只用于数量少的依赖行（谨慎使用级联）。
  • 除非“未知”确实是一种状态，否则列都设为 not null，其余规则用 check 表达（检查约束）。
  • 不要信任可空列上的 unique，因为它允许任意多个 NULL：把列设为 not null，或写 unique nulls not distinct（NULL 默认互不相同）。
  • “不允许重叠”用排他约束，不用应用代码（排他约束）。
  • 采用软删除的表，配上部分唯一索引（软删除）。
  • 把多态 type/id 对换成真正的外键列（多态关联）。
  • 跨行规则用约束、可串行化隔离或父行锁来保证，绝不用读取其他行的检查约束（约束看不见的规则）。

布局与 HOT

jsonb 还是列

  • 文档用 jsonb，绝不用 json（jsonb）。
  • 任何用于过滤、连接、排序或约束的字段，都提升为有明确类型的列；规划器看不到 jsonb 内部（何时提升为列）。
  • 绝不建实体—属性—值表（一个陷阱）。

函数与触发器

  • 只有一条查询的函数，用 SQL 编写，标为 stable 或 immutable，让规划器能将其内联（内联）。
  • 易变性标注在符合事实的前提下尽可能严格，绝不能比事实更严格（易变性）。
  • 给要跑批处理作业的表加触发器前，先按每行约一微秒估算行触发器成本，审计触发器则约两微秒（触发器成本）。
  • 绝不通过子表触发器维护父表计数器；这会让所有写入者在同一个父行上串行排队（计数器触发器）。
  • 只有工作必须与写入保持原子性，且对每个写入者都必须执行时，才添加触发器（证明它值得存在）。
  • 开启 track_functions，让函数出现在 pg_stat_user_functions 中；设置 pg_stat_statements.track = all，让函数内部语句也有自己的条目（跟踪）。

每次模式变更，都检查它在等什么锁

模式变更不会让整个站点排在它后面等，才算安全。ALTER 本身很少慢；等待它的锁才会造成故障。

  • 每个模式变更会话都把 lock_timeout 设为 1 至 5 秒，配合重试，并设置较宽裕的 statement_timeout（第一条规则）。
  • 绝不让 DDL 在锁队列里等待：一个等待中的 ALTER 会阻塞该表上后来的所有查询，包括读取（锁队列）。
  • 开始前检查长事务和事务中空闲的会话（长事务）。
  • 执行前，给每一步标明：只改系统目录、扫描，还是重写（元数据、扫描或重写）。
  • 添加列时用常量默认值；大表绝不一步加入带易变默认值的列（默认值）。
  • 添加 check 约束时用 not valid，再在另一个事务中 validate（检查约束）。
  • 外键先 not valid，再 validate；两步都会锁住子表和父表（外键）。
  • 通过已验证的检查约束实现 not null，或在 PostgreSQL 18 上用 not null ... not valid（先通过 CHECK，再设置 NOT NULL）。
  • 根据表的访问流量选择索引构建方式（选择构建方式）。
  • 并发构建在事务块外单独运行，关闭 lock_timeout，然后检查 indisvalid（四条规则）。
  • 用并发构建的唯一索引建立唯一约束和主键（唯一约束）。
  • 在线表绝不运行普通 reindex（用 REINDEX CONCURRENTLY 重建；普通 REINDEX 也会阻塞读取）。
  • 除扩宽以外，类型变更都按重写来对待；int 转 bigint 按在线替换规划（int 转 bigint 的问题）。
  • 重命名按先扩展、后收缩的方式跨多个版本发布（先扩展，后收缩）。
  • 回填按主键范围分批，在模式变更之外执行，每批一个事务（回填）。
  • 先删除代码，再删除列（先删代码）。
  • 每个模式变更事务都保持简短；DDL 的所有锁都持有到提交（事务性 DDL）。
  • 挂接分区时带上已经验证的 check，分离时用 concurrently（改造在线表）。
  • 在与生产规模相当的克隆上演练（复制生产环境）。

批量加载与分区

  • 加载用 COPY，或用 unnest 数组（如果必须 insert）；绝不每行执行一次自动提交的插入（COPY 是批量加载路径）。
  • 先加载到没有索引和外键的表，再建索引，以 not valid 加上 validate 添加外键：我们的加载实验快了六倍，WAL 减半（加载之后）。
  • 对同一事务内创建或截断的表用 copy ... freeze，让首次读取不必写入（COPY FREEZE 会写入已完成的行）。
  • 大批加载前提高 max_wal_size；结束后，在业务访问表之前，执行 vacuum (freeze, analyze)，至少也要执行 analyze（开放访问之前）。
  • 从去重后的暂存表出发，用一条语句批量插入或更新（批量插入或更新）。
  • 按删除数据所用的键分区，通常是时间；分区数量保持在几十到小几百（选择键）。
  • 对分区表的每条热点查询运行 explain，确认发生剪枝：只出现匹配的分区，或 Subplans Removed 大于零（剪枝）。
  • 提前创建未来的分区，剩余数量不足时告警（提前创建）。
  • 加载和按保留期限清理后，自己对分区父表做分析；自动清理从不做这件事（其他小陷阱）。

生产就绪检查

本书中的多数故障，都是悄悄积累几个月，再突然爆发。上线前检查这些项目，之后数据每增长十倍，再检查一次。

备份与恢复

  • 先写下 RPO 和 RTO；副本不是备份（先写下 RPO 和 RTO）。
  • 做物理基础备份，并持续归档 WAL，存到数据库主机无法删除的存储上（使用备份工具）。
  • 在另一台服务器上演练时间点恢复，设置 recovery_target_action = 'pause'（恢复到时间点）。
  • 自动执行恢复测试，记录真实 RTO（测试恢复）。
  • 托管服务也要实际恢复一次并计时；它会创建新服务器（恢复时间）。

监控与告警

  • 告警只盯短清单：不可达、事务 ID 年龄、长事务、复制、磁盘、归档（该对什么告警）。
  • 开启慢查询、锁等待、临时文件和自动清理日志，再加上 auto_explain（日志）。

连接与超时

  • 应用都要用连接池；进程很多或采用 serverless 时，再加外部连接池（两处都用连接池）。
  • 按核心与磁盘设置连接池大小，不按用户数（连接池大小）。
  • max_connections 保持在小几百，并给管理员预留连接槽位（max_connections）。
  • 在应用角色上设置超时，从 statement_timeout = '5s'、lock_timeout = '3s'、idle_in_transaction_session_timeout = '30s' 开始，PostgreSQL 17+ 再加 transaction_timeout = '5min'；模式变更和批处理作业使用独立角色，各设自己的限制（超时）。
  • 应用要能在故障切换过程中重连并重试（故障切换就是 DNS）。
  • 连接池改为事务模式前，替换会话级 set、会话级咨询锁、listen、SQL prepare 和临时表（事务连接池）。
  • 事务模式要支持预备语句，运行 PgBouncer 1.21 或更高版本；1.24 默认开启这项支持（新版 PgBouncer）。
  • 用表构建队列，以 for update skip locked 领取任务，与业务写入一起提交；listen/notify 的使用保持克制（队列）。

配置与机器

  • 调优前先算内存预算：先算 shared_buffers，再算 work_mem 乘以计划节点数、再乘以连接数（内存）。
  • 按角色为报表提高 work_mem，绝不全局提高（work_mem）。
  • SSD 上设置 random_page_cost = 1.1（代价模型）。
  • 提高 max_wal_size，让检查点按时间触发，并开启 wal_compression（错开检查点，并按 WAL 速率设置 max_wal_size）。
  • 别让 OOM killer 杀掉 postmaster（OOM killer）。
  • 绝不在 fsync = off 或 full_page_writes = off 下运行（持久性）。

清理与回卷

  • 大表逐表把缩放因子设为 0.01 至 0.05，并把代价上限提高到远高于 200（默认值不合适）。
  • 找出阻碍清理界限前进的因素：会话、复制槽、反馈、预备事务（什么会阻碍清理）。
  • age(datfrozenxid) 达到 5 亿至 10 亿时告警，绝不杀掉防回卷清理（回卷）。
  • 把序列值与其类型最大值画在图上比较（int 转 bigint 的问题）。

复制与故障切换

安全与租户隔离

托管还是自建

  • 在预算内尽量多买托管服务；只有说得清理由、又有人负责时才自建（我们的选择）。
  • 记下存储卷和实例两种 I/O 上限中的较低者（两道上限）。
  • 围绕某个扩展做设计前，先检查扩展允许列表（允许列表）。
  • 在克隆环境上演练大版本升级（升级）。
  • 用到之前，先规划退路（规划退路）。

申请更大的数据库之前

在前面各节做完、数据也证明有必要之前，我们都不会批准扩容申请（用数据争取更大的机器）。在一个应用上，实战手册让每个事务访问的页数在同一台笔记本上从 91,448 降到 38，没有增加任何硬件（证据）。

  • 在排名前五的每条语句旁写清：已修复，为什么无法修复，或为什么已经足够便宜（扩容申请要带什么）。
  • 性能画像若显示 Lock、LWLock 或 Client，就停下来：更大的机器不会缩短队列（购买之前先测量）。
  • 买更多核心前，先找找是否所有事务都在更新同一个热点行（不要用热点行计数）。
  • 测量工作集并与内存比较；已经放得下的话，更多内存毫无收益（根据工作集配置 RAM）。
  • 花钱买更快的存储前，先证明读取占据主要开销（存储是你的瓶颈吗？）。
  • CPU 是瓶颈时，先把逐行格式化和副作用移出主库（主库是你最昂贵的 CPU）。
  • 逐一排除索引、改写、往返、连接池、配置、清理和汇总表，每项写一句话（预先算好要展示的内容）。
  • 说明余量：目前的峰值用量、新规格提供什么，以及按当前增长能撑几个月（只问一个问题）。
  • 按模板提交申请（把申请写成工单）。
  • 不要为修一条慢查询就上副本、分区或分片；它们各自回应不同的信号（上限）。

如果证据表明，机器已经耗尽负载所需的某项资源，就不必争论，直接升级；把证据留下，作为下一次的基线。

横向扩展或引入第二个系统之前

跨出单个主库后的每一步，都回应一个信号，也都会成倍增加运维工作。迈步前，先说清信号和数字。

  • 为读取负载或故障切换增加只读副本，绝不为一条慢查询加副本；必须看到自身写入的读取，路由到主库（读取自身写入）。
  • 为保留期限和维护而分区，不为查找速度；不按分区键过滤的查询会搜索每个分区（查找不会更快）。
  • 只有真正出现信号时才分片：调优和批处理后，写入仍让最大主库饱和；数据多到无法在 RTO 内恢复；或有数据驻留要求（何时分片）。
  • 在此之前，留好余地：每张租户表、每个键、每条查询都带 account_id（留好余地）。
  • 选分片方案之前，检查负载最高的查询的计划中是否有扇出（跨分片查询）。
  • 分析负载先试汇总表、报表副本或 pg_duckdb，再考虑列式存储；整表要读 35,173 页，汇总表只读 12 页（折中办法）。
  • 只有能说清特性、又有实测数字，才增加第二个系统，同时让 PostgreSQL 继续作为权威数据源（实测理由）。

故障排查：数据库现在就很慢

先看，再动手。重启会把证据和缓存一起清掉，流量恢复后，原因也会回来（重启后预热缓存）。按顺序执行下面的步骤；每个答案都会告诉你，接下来哪一步才重要。

**1. 按状态和等待事件统计会话。**遇到任何慢服务器，先跑这条查询：

select state, wait_event_type, wait_event, count(*)
  from pg_stat_activity
 where backend_type = 'client backend'
 group by 1, 2, 3
 order by 4 desc;
  • 解读结果：active 且没有等待事件，表示在用 CPU，要与核心数比较；Lock 表示转到第 2 步；IO 的 DataFileRead 是读页；LWLock 表示内部结构拥挤；大量 idle in transaction 指向应用问题（等待事件）。
  • 将总数与 max_connections 比较：select count(*), current_setting('max_connections') from pg_stat_activity;（统计连接）。

**2. 找出阻塞链的根源。**列出每个等待锁的会话，以及每个阻塞别人的会话：

select pid, pg_blocking_pids(pid) as blocked_by, state, wait_event,
       now() - xact_start as xact_age, left(query, 50) as query
  from pg_stat_activity
 where cardinality(pg_blocking_pids(pid)) > 0
    or pid in (select unnest(pg_blocking_pids(pid)) from pg_stat_activity)
 order by xact_start;
  • 找根源：blocked_by 为空的那一行，往往处于 idle in transaction 状态（找到根阻塞者）。

**3. 列出最老的事务。**它们持有锁，也阻碍清理：

select pid, state, now() - xact_start as xact_age,
       now() - state_change as in_state, left(query, 50) as query
  from pg_stat_activity
 where xact_start is not null and pid <> pg_backend_pid()
 order by xact_start
 limit 5;
  • 在 idle in transaction 状态下停留几分钟就是 bug；query 是它的上一条语句，不是当前语句（寻找长事务）。

4. 结束正确的会话。select pg_cancel_backend(pid) 会停止正在运行的语句；事务中空闲的会话没有正在运行的语句，因此用 select pg_terminate_backend(pid)，它会回滚该会话的事务。

  • 记下结束了什么、为什么结束，再修复那个让事务一直开着的问题（同节）。

**5. 对等待采样一分钟。**看一眼只是照片；每秒十次、持续一分钟，才是性能画像。

**6. 给问题发生后运行的语句排名。**对 pg_stat_statements 取快照，等一分钟，再算差值：

create temp table ss0 as
select userid, dbid, toplevel, queryid, calls, total_exec_time
  from pg_stat_statements;

-- a minute later
select s.calls - coalesce(o.calls, 0) as calls,
       round((s.total_exec_time - coalesce(o.total_exec_time, 0))::numeric) as ms,
       left(s.query, 50) as query
  from pg_stat_statements s
  left join ss0 o using (userid, dbid, toplevel, queryid)
 order by ms desc
 limit 10;
  • 找那些语句很简单、平均耗时却很长的：它在等，不是在干活（购买之前先测量）。
  • 用真实参数，对排在最前的语句运行 explain (analyze, buffers)（排查方法）。
  • 怀疑计划发生了切换：通用计划，或加载后过时的统计信息（计划出错时）。

7. 检查查询背后还在运行什么。

  • 检查清理进度：select pid, relid::regclass, phase from pg_stat_progress_vacuum;（监控清理）。
  • 检查副本延迟：select application_name, state, replay_lag from pg_stat_replication;（监控复制）。
  • 检查归档：select failed_count, last_failed_time from pg_stat_archiver;（归档）。
  • 检查回卷：select datname, age(datfrozenxid) from pg_database order by 2 desc;（回卷）。
  • 检查主机：CPU 使用量与核心数的关系、磁盘延迟，以及 IOPS 是否平在存储卷的上限（I/O 被节流时，会表现为 IOPS 持平而延迟上升）。

8. 收尾闭环。

Eight Kilobytes，© 2026 Flagon, Inc. All rights reserved.，依 All rights reserved 授权使用，详见归属声明。