监控体系与告警设计：从内核机制出发定义该监控什么

PG 的内核暴露了一组统计视图——pg_stat_activity、pg_stat_user_tables、pg_stat_statements、pg_stat_replication、pg_locks 等等。这些视图是内核数据结构的窗口：pg_stat_activity 背后是 ProcArray 和 PGPROC，pg_locks 背后是 LockAcquire() 操作的 lock hash table，pg_stat_user_tables.n_dead_tup 背后是 VACUUM 留下的 dead tuple 计数器，pg_stat_replication 的 LSN 延迟来自 WAL Sender 和 Receiver 之间的协议交互。

大多数 PG 监控指南会给你一个 Grafana dashboard JSON 和一套告警规则，然后告诉你”照着配就行”。这种方式的三个致命缺陷是：第一，你不知道每个指标的内核含义——n_dead_tup 为什么在 VACUUM 间隔期间是陈旧值、write_lag=0 是正常还是 standby 已经挂了，这些阈值判断只有从内核机制出发才能分辨；第二，dashboard 上的阈值（“shared_buffers 命中率低于 95% 告警”）往往是通用模板的产物，不区分负载类型和硬件；第三，dashboard 关注的是事后指标，而内核视角告诉你的是故障模式的前兆信号——在连接数还没打满之前，ProcArrayLock 的等待事件就已经开始堆积了。

本文从内核机制出发，回答三个问题：监控什么、为什么监控这些、阈值的内核依据是什么。每个维度先讲内核数据来源和统计更新机制，再给具体监控 SQL 和指标解读，最后说明告警策略和陷阱。如果你正在接手一个没有监控的 PG 集群，可以参考第十节的接入路径；如果你已经有一套 dashboard，可以用本文的内核视角重新审视每个阈值是否有依据——是否考虑了你的 XID 消耗速率、你的表负载特征、你的复制拓扑。

一、六个监控维度与内核故障模式

PG 的统计视图覆盖六个内核子系统的运行状态。监控不是照搬视图列表，而是按故障模式组织：

这些故障模式不是孤立的。一个 idle in transaction 的会话（连接维度）→ 持锁（锁维度）→ 阻止 VACUUM 回收（膨胀维度）→ dead tuple 堆积导致 seqscan 变慢（查询维度）→ 查询变慢增加活跃连接数（连接维度）→ 恶性循环。监控体系必须能捕捉这个因果链中的每一个节点。

二、连接与进程：从 PGPROC 槽位到 sysidle 的含义

内核数据来源

pg_stat_activity 的数据来自共享内存中的 ProcArray 和 PGPROC 结构体（参见进程模型与共享内存）。ProcArray 是固定大小的数组——总槽位数由 max_connections + autovacuum_max_workers + max_wal_senders + max_worker_processes 决定。pg_stat_activity 视图查询时，遍历 ProcArray 将每个活跃的 PGPROC 条目转换成一行。具体来说，pgstat_read_current_status() 函数扫描 ProcArray，从每个 PGPROC 中读取进程 ID、当前查询文本、事务开始时间、等待事件等字段，然后填充到本地的 localPgStatStatus 缓冲区中。

扫描 ProcArray 本身需要获取 ProcArrayLock 的共享锁（LW_SHARED）。在高并发（2000+ 连接）环境中，查询 pg_stat_activity 本身就会和 GetSnapshotData() 的快照获取竞争 ProcArrayLock——这是在第九节会展开的”监控的代价”问题。

基础连接监控：状态分布

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
```text

`state` 字段有五个取值：`active`（正在执行查询）、`idle`（连接打开但无事务）、`idle in transaction`（事务已开始但当前无活动查询）、`fastpath function call`（执行 C 函数中）、`disabled`（track_activities=off）。仅看总数会错过真正的风险信号。

连接数百分比的常用计算：

```sql
SELECT count(*) AS current_connections,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 2) AS usage_pct
FROM pg_stat_activity;

但这个指标有误导性——max_connections 使用率 80% 本身不是告警条件。真正的风险在于：当连接数增加时，GetSnapshotData() 扫描 ProcArray 的成本线性增长，work_mem 的累积内存占用也更危险。所以监控连接数的同时必须监控活跃连接数趋势和 ProcArrayLock 等待事件。

真正危险的指标：idle in transaction 的持续时间和影响链

idle in transaction 是指事务已经开始（执行过 BEGIN 或隐式事务的第一条 DML），但当前没有在执行任何查询。这个状态下三条破坏链同时运行：

破坏链一：阻止 VACUUM 回收。 事务开始后，GetSnapshotData() 将其 xmin 写入快照。VACUUM 使用系统中所有活跃事务的最小 xmin（GetOldestXmin()）来决定哪些 dead tuple 可以被回收——任何 xmax 大于 GetOldestXmin() 的 dead tuple 都不能被清理，因为仍有事务可能看到旧版本。一个 idle in transaction 的 xmin 就是一个钉子，钉住了 VACUUM 的回收水位线。

破坏链二：持锁不释放。 如果该事务在执行 LOCK TABLE、DDL 或 SELECT ... FOR UPDATE，持有的锁会一直保留到事务结束（COMMIT 或 ROLLBACK）。在此期间，任何需要冲突锁的操作（如 DDL 的 AccessExclusiveLock）都会在锁等待队列中排队。

破坏链三：Catalog 膨胀。 PG 的系统表（pg_class、pg_attribute 等）修改使用 MVCC——每次 DDL 都会在系统表中产生 dead tuple。如果 idle in transaction 的时间长到覆盖一次 DDL，系统表也会膨胀。

监控 SQL：

SELECT pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       xact_start,
       extract(epoch FROM now() - xact_start) AS txn_seconds,
       extract(epoch FROM now() - state_change) AS idle_seconds,
       query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
ORDER BY xact_start;
```text

`xact_start`（事务开始时间）和 `state_change`（进入 idle 的时间）之间的差值是它实际工作的时间。`state_change` 到 `now()` 的间隔是它已经闲置的时间——这才是危险的部分。

告警策略：PG 9.6+ 内置的 `idle_in_transaction_session_timeout` 应设为 5-15 分钟。如果设为 0（默认禁用），监控层必须补位：任何 idle in transaction 超过 15 分钟的会话应触发告警。这并非拍脑袋的数字——一个 15 分钟的 idle in transaction 意味着 15 分钟内所有 UPDATE/DELETE 产生的 dead tuple 都堆积在表中无法回收。

### wait_event 分布：从等待事件定位瓶颈模块

`wait_event_type` 和 `wait_event` 是 PG 9.6+ 引入的等待事件系统。每个 Backend 进程在进入等待状态时，会通过 `pgstat_report_wait_start()` 将当前的等待事件写入共享内存中的 `PGPROC` 条目。等待结束时调用 `pgstat_report_wait_end()` 清除。

```sql
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

关键等待类型的内核含义和排查方向：

当 wait_event_type=Lock 的占比超过活跃连接的 10%，锁等待已经开始影响吞吐——应立即用 pg_blocking_pids() 追踪阻塞者。当 wait_event_type=LWLock 出现 ProcArrayLock 频繁出现，说明 max_connections 设得太大：快照获取和 ProcArray 扫描之间的争用已经显现。

连接数的监控不应只看”用了百分之几”

很多人只监控 max_connections 的使用百分比。从内核角度看，危险不在于连接数接近上限，而在于：

• ProcArray 扫描成本：GetSnapshotData() 必须遍历整个 ProcArray 来确定活跃事务集合。在 PG 14 之前，整个扫描需要在 ProcArrayLock 的共享锁保护下完成。PG 14 优化了 xmin/xid 的原子读取路径，但 nxids（子事务缓存）仍然需要遍历。连接数从 100 增加到 1000，快照获取成本不是增长 10 倍（遍历数组），而是可能更多（cache miss 增加）。
• work_mem 爆炸：每个活跃 Backend 在执行 sort/hash/materialize 操作时各自消耗 work_mem。200 个连接 × 每个连接同时执行 3 个操作 × 默认 4MB work_mem = 2.4GB 内存——而且这还没算 hash_mem_multiplier（PG 15+，默认 2.0）对 hash join 内存的倍增效应。
• 系统资源碎片化：每个 Backend 是一个独立的 OS 进程，有自己的 page table。1000 个 Backend 意味着 1000 套 page table entries——对 TLB 是沉重负担。

所以连接数监控的正确指标组合是：连接总数 + 活跃连接数 + wait_event 分布 + 每个 Backend 的 RSS（通过 OS 层 ps 或 /proc 获取），而不是单一的百分比。

三、存储与膨胀：dead tuple 的动态平衡与 XID 年龄的倒计时

内核数据来源

pg_stat_user_tables 的膨胀相关字段来自两个独立的更新路径：

写入量计数器（实时更新）——n_tup_ins、n_tup_upd、n_tup_del、n_tup_hot_upd：在执行器层面，ExecInsert()、ExecUpdate()、ExecDelete() 在每次 tuple 操作后立即调用 pgstat_count_heap_insert()/pgstat_count_heap_update()/pgstat_count_heap_delete() 递增共享内存中的计数。这些计数器是实时的——每次 DML 操作都立即反映。

但是，n_tup_hot_upd 有一个内核细节需要理解：HOT UPDATE（Heap-Only Tuple update）要求新 tuple 不修改任何索引列，且新 tuple 能放入旧 tuple 所在的同一页面。当 heap_update() 检测到 HOT 可行时（通过 HeapSatisfiesHOTUpdate()），它设置 t_infomask2 中的 HEAP_HOT_UPDATED 和 HEAP_ONLY_TUPLE 位，然后递增 n_tup_hot_upd 计数器。HOT 的关键工程优势是避免了索引更新——如果 UPDATE 频繁修改索引列，HOT 比率下降，每次 UPDATE 都要在索引中插入新条目并在旧条目上标记 dead，这会显著加速索引膨胀。

膨胀量计数器（VACUUM/ANALYZE 更新）——n_dead_tup、n_live_tup：VACUUM 扫描 heap 页面时，对每个 tuple 判断可见性。如果所有活跃事务都看不到这个 tuple（xmax 已经 COMMITTED 且小于 GetOldestXmin()），它被标记为 dead 并计数。VACUUM 结束时，heap_vacuum_rel() 调用 pgstat_report_vacuum() 将本轮的 dead/live tuple 计数写入统计条目。ANALYZE 通过采样估算 n_live_tup，但不由 VACUUM 更新 n_dead_tup。

这意味着 n_dead_tup 在 VACUUM 间隔期间是一个陈旧值——VACUUM 刚结束时它是准的，但随着新的 UPDATE/DELETE 产生新的 dead tuple，它会越来越偏离真实值，直到下一次 VACUUM 的扫描刷新它。所以不能只看 n_dead_tup 的绝对值，要看它的增长趋势和与 VACUUM 频率的关系。

last_autovacuum 和 last_autoanalyze 由 Autovacuum Worker 在完成操作后更新。这给了你判断 VACUUM 频率是否足够的直接依据。

膨胀监控 SQL 和三个核心比率

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
       last_autovacuum, last_autoanalyze,
       now() - last_autovacuum AS time_since_vacuum,
       n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
       round(100.0 * n_tup_del / NULLIF(n_tup_ins + n_tup_upd + n_tup_del, 0), 2) AS del_pct,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_upd_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;
```text

三个关键比率的解读：

`dead_ratio = n_dead_tup / n_live_tup`：当前表中 dead tuple 的比例。超过 0.2（20%）表示 VACUUM 可能跟不上写入速度。超过 1.0 意味着 dead tuple 比 live tuple 还多——表已严重膨胀，seqscan 需要扫描大量无效页面。

`del_pct = n_tup_del / (n_tup_ins + n_tup_upd + n_tup_del)`：删除操作占总写入的比例。高 `del_pct`（如 40%+）说明该表有大量"删除"——但这不是字面意义的 `DELETE`，更可能是 `UPDATE` 的隐式删除（PG 的 UPDATE = DELETE old tuple + INSERT new tuple，DELETE 操作也计入 `n_tup_del`）。如果 `n_tup_upd` 也很高，死元组的主要来源是更新而不是删除。

`hot_upd_pct = n_tup_hot_upd / n_tup_upd`：HOT UPDATE 比率。低于 0.8 意味着 UPDATE 频繁触及索引列或页面空间不足，导致每次 UPDATE 都要写索引。低于 0.5 意味着索引膨胀会是一个严重问题——索引中的 dead entry 增长速度接近表本身。

### 索引膨胀：常被忽略的维度

表膨胀看 `n_dead_tup`，索引膨胀看 `pg_stat_user_indexes`：

```sql
SELECT indexrelid::regclass AS index_name,
       idx_scan, idx_tup_read, idx_tup_fetch,
       round(100.0 * idx_tup_fetch / NULLIF(idx_tup_read, 0), 2) AS fetch_ratio
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_tup_read DESC
LIMIT 20;

idx_tup_read 是索引扫描中读取的索引条目数。idx_tup_fetch 是其中对应 heap tuple 仍然可见的数量。fetch_ratio 越低，索引扫描浪费在 dead index entries 上的比例越高——索引膨胀的直接证据。

还有一个更直接的方法：

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
       round(100.0 * pg_relation_size(indexrelid) / NULLIF(pg_relation_size(indrelid), 0), 2) AS idx_table_ratio
FROM pg_index
WHERE indisvalid AND NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
```text

如果某个索引大小超过表大小的 40-50%，索引可能严重膨胀——通常是因为频繁 UPDATE 索引列或 B-Tree 页面分裂未能有效合并。

### XID 年龄趋势：最危险的监控盲区

膨胀拖慢查询，但 XID wraparound 直接让数据库变成只读。PG 的事务 ID 是 32 位（约 40 亿），使用模运算比较：离当前事务 ID 最近的 20 亿是"未来"，其余是"过去"。每个表在 `pg_class.relfrozenxid` 中记录该表中所有 tuple 的 `xmin` 中已被冻结的最小值。`age(relfrozenxid)` 表示从上次冻结以来已经分配了多少个事务 ID。

数据库级别：

```sql
SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

表级别：

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS table_xid_age,
       greatest(age(c.relfrozenxid), age(pg_database.datfrozenxid)) AS effective_age
FROM pg_class c
JOIN pg_database ON c.relnamespace = pg_database.oid
WHERE c.relkind IN ('r', 't', 'm')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;
```text

当 `age(relfrozenxid)` 达到 `autovacuum_freeze_max_age`（默认 2 亿）时，autovacuum 强制触发 anti-wraparound VACUUM——即使该表没有任何 dead tuple，VACUUM 也会全表扫描来冻结旧 tuple 的 `xmin`。如果年龄继续增长到 `autovacuum_freeze_max_age + 5000 万`（即 2.5 亿），系统会开始拒绝该表上的事务。到 20 亿时数据库 shutdown 到只读模式。

**告警阈值必须提前于 autovacuum 触发点。** Anti-wraparound VACUUM 对于大表可能需要数小时。告警不能在 2 亿才触发，因为从 2 亿到灾难的倒计时钟表取决于 XID 消耗速率。我们将在第八节详细展开告警策略。

---

## 四、WAL 与复制：三层延迟的精确语义与零值陷阱

### 内核数据来源

WAL 写入统计来自 `pg_stat_wal` 视图（PG 14+），它在 `XLogInsertRecord()` 层面更新。主要的计数器包括：

- `wal_records`：自统计重置以来写入的 WAL record 总数。
- `wal_fpi`：Full Page Image (FPI) 的数量。当 `full_page_writes = on` 时，checkpoint 之后每个页面的第一次修改会在 WAL 中写入整个 8KB 页面。
- `wal_bytes`：WAL 字节总量。
- `wal_write`：调用 `write()` 系统调用的次数和时间。
- `wal_sync`：调用 `fsync()`/`fdatasync()` 的次数和时间。

`wal_sync` 的次数和 `wal_sync_time` 是 WAL I/O 延迟的直接指标。如果 `wal_sync_time / wal_sync > 10ms`，说明 WAL 磁盘写入延迟偏高。WAL Writer (`walwriter`) 的职责是定期将 WAL buffer 刷到磁盘以减少每次 COMMIT 的同步开销，它的写入行为由 `wal_writer_delay`（默认 200ms）和 `wal_writer_flush_after`（PG 9.6+ 默认 1MB）控制。

### WAL 监控 SQL

```sql
SELECT extract(epoch FROM now() - stats_reset) AS seconds_since_reset,
       wal_bytes / extract(epoch FROM now() - stats_reset) AS wal_bytes_per_sec,
       wal_records / extract(epoch FROM now() - stats_reset) AS wal_records_per_sec,
       wal_fpi / extract(epoch FROM now() - stats_reset) AS wal_fpi_per_sec,
       round(wal_sync_time::numeric / NULLIF(wal_sync, 0) / 1000, 2) AS avg_sync_ms
FROM pg_stat_wal;

wal_bytes_per_sec 是 WAL 写入带宽。wal_fpi 是 Full Page Image 的数量——当 full_page_writes = on 时，checkpoint 之后每个页面的第一次修改会将整个页面写入 WAL。FPI 是 WAL 体积的主要来源。如果 WAL 写入速率突然飙升，通常是 checkpoint 触发导致大量 FPI 写入（所有 buffer 被标记为 dirty，它们受到的第一次修改都会产生 FPI）。avg_sync_ms 是平均每次 wal_sync 的耗时——这个值在 SSD 上应在 1ms 以内，在旋转磁盘上可能 5-15ms。如果突然跳到几十 ms，说明 WAL 磁盘出现 I/O 拥堵。

复制延迟的三层 LSN

pg_stat_replication 由 WAL Sender 进程定期更新：

SELECT application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag,
       reply_time
FROM pg_stat_replication;
```text

三层 LSN 的内核路径：

- `sent_lsn`：WAL Sender（`WalSndLoop()`）已经通过 `pq_putmessage()` 发送给 standby 的最后一个 LSN。网络层面。
- `write_lsn`：Standby 的 WAL Receiver（`WalRcvLoop()`）已经通过 `write()` 写入 OS buffer 的最后一个 LSN。尚未 `fsync`。
- `flush_lsn`：Standby 已经 `fsync()` 到磁盘的最后一个 LSN。`issue_xlog_fsync()` 调用已返回。
- `replay_lsn`：Standby 的 Startup 进程（`StartupXLOG()` / `RmgrTable` 分发）已经 apply 的最后一个 LSN。这一层决定了 standby 上查询能看到的数据版本。

三层延迟对应的瓶颈：
- `write_lag` 大：网络带宽或延迟瓶颈。
- `flush_lag` 大但 `write_lag` 小：standby 磁盘写入慢。
- `replay_lag` 大但 `flush_lag` 小：standby 在应用 WAL 时遇到瓶颈——可能是因为 `max_standby_streaming_delay` 触发了查询取消（standby 上的只读查询被 WAL application 的冲突取消，replay 暂停等待查询结束）。

### 零值陷阱：延迟为 0 时的两种情况

`write_lag`、`flush_lag`、`replay_lag` 为 0 时不代表没问题。两种截然不同的情况：

1. **真的没延迟**：Standby 健康，网络和磁盘都快，replay 紧追 primary。
2. **Standby 完全卡住**：Standby crash 或网络断开后，WAL Sender 停止发送，`sent_lsn` 不再推进。延迟字段基于 `sent_lsn` 和 `write_lsn` 的差值计算——不推进就没有延迟。此时 `pg_stat_replication` 的延迟列可能返回 0，但 standby 实际上已经不可用。

区分方法：同时检查 `reply_time`（standby 最后一次发送 status update 的时间戳）和 `state` 字段：

```sql
SELECT application_name, state, sync_state,
       write_lag, flush_lag, replay_lag,
       reply_time,
       extract(epoch FROM now() - reply_time) AS seconds_since_reply,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lsn_diff_bytes
FROM pg_stat_replication
WHERE extract(epoch FROM now() - reply_time) > 30;

reply_time 超过 30 秒说明 standby 的 WAL Receiver 可能已经停止工作（crash 或断网）。此时延迟字段的数值毫无意义。

WAL 段堆积与 replication slot

WAL 段（每个 16MB）堆积在 pg_wal 目录中。正常运行时，checkpoint 完成后 PG 回收不再需要的旧 WAL 段。回收水线是当前所有 replication slot 的 restart_lsn 的最小值——slot 是 WAL 回收的下限：

SELECT slot_name, active, slot_type,
       restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_pretty
FROM pg_replication_slots
WHERE NOT active
ORDER BY lag_bytes DESC;
```text

`active = false` 的 slot 最危险——WAL Sender 进程不在运行（可能 standby 永久宕机），但 slot 仍然存在于共享内存中，握着 `restart_lsn` 阻止 WAL 回收。WAL 段持续堆积直到 `pg_wal` 填满磁盘，导致 primary PANIC shutdown。

同时监控 WAL 目录的物理占用：

```sql
SELECT count(*) AS wal_files,
       pg_size_pretty(sum(size)) AS total_size
FROM pg_ls_waldir();

WAL 文件数持续增长 + pg_replication_slots 中有不活跃 slot = slot 溢出危机的早期信号。

五、查询性能：pg_stat_statements 的突变检测与统计信息的保质期

内核数据来源

pg_stat_statements 是一个扩展，在共享内存中维护一个 hash table（pgss_hash），以 queryid 为 key 聚合查询统计。每次查询在 Executor 结束后（确切说是在 executor_finish() 之后），pgss_post_parse_analyze() 计算 queryid，pgss_store() 在 hash table 中更新该 queryid 的累积统计。

queryid 的计算基于标准化后的查询文本。标准化过程（pgss_normalize_query()）去除多余空格和大小写差异，但不替换常量——SELECT * FROM t WHERE id = 1 和 SELECT * FROM t WHERE id = 2 产生不同的 queryid。只有使用 prepared statement（PREPARE / Extended Query Protocol 的 Parse message）时，参数占位符（$1）才会让不同参数值合并到同一 queryid。

核心监控 SQL

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(stddev_exec_time::numeric, 2) AS stdev_ms,
       round(min_exec_time::numeric, 2) AS min_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       rows,
       shared_blks_hit, shared_blks_read,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_ratio,
       wal_bytes,
       left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time * calls DESC
LIMIT 30;
```text

关键排序方式：用 `mean_exec_time * calls` 而不是 `mean_exec_time` 本身。这是"总影响最大"的查询——一个执行 10 万次每次 0.5ms 的查询（总耗时 50 秒）比一个执行 1 次耗时 3 秒的查询消耗更多系统时间。

`stddev_exec_time`（PG 13+）是区分查询延迟模式的核心指标：

- `stddev_exec_time >> mean_exec_time`：延迟波动大。"有时快有时慢"——可能是计划缓存模式切换（generic plan vs custom plan 在第五次执行时的切换导致部分执行使用了差的通用计划）、统计信息不准确导致 optimizer 对某些参数值选择了不同的 plan。
- `stddev_exec_time << mean_exec_time`：延迟稳定地慢。每次执行基本都慢——大概率是查询本身的问题：索引缺失、统计信息不准、或者查询写得太复杂。

同时看 `shared_blks_hit` 和 `shared_blks_read` 的比值：`hit_ratio` 低于 50% 的查询在大量读磁盘——这是 `shared_buffers` 不足或查询涉及未缓存的冷数据的信号。

### 突变检测：两次 snapshot 差值才是趋势

`pg_stat_statements` 的计数器是自 `pg_stat_statements_reset()` 以来的累积值。要看趋势变化，必须做 snapshot 差分：

```sql
-- Snapshot 1
CREATE TEMP TABLE stmt_snap_1 AS SELECT * FROM pg_stat_statements;
-- 等待 5 分钟
-- Snapshot 2
CREATE TEMP TABLE stmt_snap_2 AS SELECT * FROM pg_stat_statements;

SELECT s2.queryid,
       (s2.calls - COALESCE(s1.calls, 0)) AS calls_delta,
       round((s2.mean_exec_time - COALESCE(s1.mean_exec_time, 0))::numeric, 2) AS avg_change_ms,
       round(s2.mean_exec_time::numeric, 2) AS current_avg_ms,
       round(COALESCE(s1.mean_exec_time, 0)::numeric, 2) AS previous_avg_ms,
       (s2.calls - COALESCE(s1.calls, 0)) AS new_calls_count,
       left(s2.query, 120) AS query_preview
FROM stmt_snap_2 s2
LEFT JOIN stmt_snap_1 s1 USING (queryid)
WHERE s2.calls > COALESCE(s1.calls, 0)
  AND s2.mean_exec_time > COALESCE(s1.mean_exec_time, 0) * 2
ORDER BY (s2.mean_exec_time - COALESCE(s1.mean_exec_time, 0)) * (s2.calls - COALESCE(s1.calls, 0)) DESC;

突变检测捕捉的是查询”突然变慢”。突然变慢的典型原因：

• 统计信息漂移：表插入了大量数据但 autoanalyze 还没触发（autovacuum_analyze_scale_factor 默认 0.1 意味着表要变化 10% 才会触发），优化器使用过期统计信息选择了差计划。
• Standby promote 后的统计真空：Failover 之后，新 primary 上的统计信息可能不存在或过期——在 autoanalyze 重新收集完整之前，优化器可能在跑灾难性计划。
• 表膨胀：dead tuple 堆积使 seqscan 扫描的页面数从 1000 变成 5000，查询时间翻倍。

calls 频率异常

新增查询或查询频率突变也值得监控：

-- 检测在窗口内突然出现的高频新查询
SELECT s2.queryid,
       s2.calls - COALESCE(s1.calls, 0) AS calls_delta,
       round(s2.mean_exec_time::numeric, 2) AS avg_ms,
       left(s2.query, 120) AS query_preview
FROM stmt_snap_2 s2
LEFT JOIN stmt_snap_1 s1 USING (queryid)
WHERE COALESCE(s1.calls, 0) = 0
  AND s2.calls > 100
ORDER BY calls_delta DESC;
```text

这个 SQL 找到了在统计窗口内第一次出现且执行了 100 次以上的查询——可能是新上线的业务功能、或者是应用层 bug 导致的查询循环。

---

## 六、锁与等待：granted=false 的堆叠效应

### 内核数据来源

`pg_locks` 视图的数据来自共享内存中的 lock hash table（`LockMethodLockHash`）。当进程调用 `LockAcquire()` 请求一个 heavyweight lock 时，它在 lock hash table 中查找或创建 lock entry，并为该进程创建一个 proclock entry。如果请求的锁模式与已持有锁模式冲突，该 proclock entry 的 `granted` 字段设为 `false`，进程被放入该锁的等待队列并开始等待（通过信号量 `PGSemaphoreLock()` 休眠）。

有三层锁体系（参见[锁管理器](../06-lock-manager/06-lock-manager.html)）：SpinLock（自旋锁，几十个 CPU 周期）、LWLock（轻量级锁，自旋+N 次后进内核休眠）、Heavyweight Lock（重量级锁，支持死锁检测和等待队列）。`pg_locks` 只记录 Heavyweight Lock。LWLock 等待通过 `wait_event` 暴露。

### 锁等待的全局视图

```sql
SELECT locktype, mode, granted, count(*)
FROM pg_locks
WHERE NOT granted
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

granted = false 的锁为 0 不代表系统没有锁等待——短暂的行级锁等待（如两个并发 UPDATE 同一行）是正常现象，持续时间和等待时间都很短。需要关注的是：

• granted = false 的 relation 锁中 mode = AccessExclusiveLock：DDL（ALTER TABLE、DROP TABLE、CREATE INDEX CONCURRENTLY 的最终阶段）在排队等锁。这意味着有人在持表级锁未释放——几乎总是 idle in transaction 的会话。
• locktype = transactionid 且 granted = false：在等某个事务完成——通常是等该事务释放行锁（SELECT ... FOR UPDATE 或 UPDATE 持有的 RowExclusiveLock）。
• 同一种 mode 下大量 granted = false：锁排队队列在堆积，锁持有者是瓶颈。

追踪等待链

SELECT blocked.pid AS blocked_pid,
       blocked.usename AS blocked_user,
       blocked.query AS blocked_query,
       blocked.wait_event_type,
       blocked.wait_event,
       blocking.pid AS blocking_pid,
       blocking.usename AS blocking_user,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state,
       age(now(), blocking.xact_start) AS blocking_txn_age,
       age(now(), blocking.state_change) AS blocking_state_age
FROM pg_stat_activity blocked
CROSS JOIN LATERAL pg_blocking_pids(blocked.pid) AS blk(pid)
JOIN pg_stat_activity blocking ON blk.pid = blocking.pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocking_txn_age DESC;
```text

`blocking_txn_age` 是阻塞者事务启动以来的时间。如果超过几分钟，且 `blocking_state = 'idle in transaction'`，这就是那个忘了 COMMIT 的会话。`blocking_state_age` 可以帮助区分：阻塞者是正在执行一个长查询（`state = 'active'`），还是已经闲置了。

### 锁等待占比监控

```sql
WITH activity_stats AS (
    SELECT count(*) FILTER (WHERE state = 'active') AS total_active,
           count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_on_lock
    FROM pg_stat_activity
)
SELECT total_active, waiting_on_lock,
       round(100.0 * waiting_on_lock / NULLIF(total_active, 0), 2) AS lock_wait_pct
FROM activity_stats;

告警策略： - lock_wait_pct > 10%：Warning。锁等待已经开始影响系统的并行度和吞吐。 - lock_wait_pct > 30%：Critical。大量连接在排队等锁，系统吞吐可能腰斩。需要立即查找阻塞者并决定是否 pg_terminate_backend()。 - lock_wait_pct 持续 > 5% 数小时：可能需要审视应用层的并发访问模式（是否有不必要的排他锁、是否需要 advisory lock 替代行锁等）。

LWLock 争用：pg_locks 看不到的等待

LWLock 等待不在 pg_locks 中，而在 pg_stat_activity.wait_event 中。高并发的 LWLock 争用是一个关键的性能告警：

SELECT wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
GROUP BY 1
ORDER BY 2 DESC;
```text

高 `WALWriteLock` 争用 → WAL 写入是瓶颈（WAL 磁盘慢或 `full_page_writes` 产生大量 WAL）。高 `ProcArrayLock` 争用 → 连接数太多，快照获取扫描 ProcArray 成本太高。高 `BufferMapping` 争用 → `shared_buffers` 的分区太少（PG 16 增加了分区数，但在极高并发下仍可能成为瓶颈）。高 `lock_manager` 争用 → 大量并发 DML 获取和释放表级锁——这通常是应用设计问题。

---

## 七、系统资源：命中率骗局、checkpoint I/O 与临时文件

### shared_buffers 命中率不是 99% 就够了

几乎所有 PG 监控面板的第一个指标就是 shared_buffers 命中率：

```sql
SELECT round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS hit_ratio
FROM pg_stat_database;

这个指标的两个陷阱：

陷阱一：聚合掩盖了冷热表差异。 大量 shared_buffers 命中可能集中在少数热表（用户会话表、最近聊天记录表等）。大表的 seqscan 每次都从磁盘读取页面，但这被热表的高命中率淹没了。必须分表查看：

SELECT relid::regclass AS table_name,
       heap_blks_hit, heap_blks_read,
       round(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 30;
```text

`heap_blks_read` 高 + `hit_ratio` 低 = 该表的 seqscan 或大范围索引扫描一直在读磁盘。你可能需要增加该表在 shared_buffers 中的 residency（通过调大 `shared_buffers`），或者改变对该表的访问模式（避免不必要的全表扫描）。

**陷阱二：高命中率不意味 buffer 被有效利用。** 如果 `shared_buffers` 远小于活跃工作集，buffer 页面频繁被驱逐和重新读入（buffer churn）。虽然命中率可能仍有 95% 以上（每次读入后在短时间内被使用），但驱逐-重读循环增加了 buffer mapping 的锁争用和 CPU 开销。监控 `BufferMapping` 等待事件：

```sql
SELECT wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event = 'BufferMapping'
GROUP BY 1;

BufferMapping 等待表示进程在等 buffer mapping hash table 的分区 LWLock——这个等待在 buffer churn 场景下会出现。

Checkpoint I/O 监控

Checkpoint 是 PG 最大的 I/O 写峰值源之一。当 checkpoint 触发时（由 checkpoint_timeout 或 max_wal_size 触发），Checkpointer 进程扫描所有 dirty buffer 并写入磁盘。监控 checkpoint 频率和 I/O 量：

SELECT checkpoints_timed, checkpoints_req,
       buffers_checkpoint, buffers_clean, buffers_backend,
       buffers_clean / NULLIF(checkpoints_timed + checkpoints_req, 0) AS avg_bgwriter_cleaned,
       buffers_checkpoint / NULLIF(checkpoints_timed + checkpoints_req, 0) AS avg_checkpoint_buffers
FROM pg_stat_bgwriter;
```text

- `checkpoints_req`（requested checkpoints，由 WAL 大小触发）如果频繁出现（每小时超过 4-5 次），说明 `max_wal_size` 太小——WAL 写完即触发 checkpoint，导致频繁的全量 dirty buffer 冲刷。
- `buffers_backend`（由 Backend 进程写入的 buffer）如果远大于 0，说明 Backend 在不得不亲自写脏页——`bgwriter` 跟不上脏页产生速度。这可能意味着 `bgwriter_lru_maxpages` 太小。
- `buffers_checkpoint` 除以 checkpoint 次数是每次 checkpoint 的平均写缓冲量。在 SSD 上几百 MB 是正常的；在旋转磁盘上超过 100MB 可能导致明显的 I/O 峰值。

### 临时文件的生成速率

当查询中的 sort 或 hash 操作需要的内存超过 `work_mem` 时，PG 将溢出数据写入临时文件。临时文件默认放在 `base/pgsql_tmp/` 下（或 `temp_tablespaces` 指定的表空间）：

```sql
SELECT datname, temp_files, temp_bytes,
       pg_size_pretty(temp_bytes) AS temp_size_total,
       round(temp_bytes::numeric / NULLIF(temp_files, 0), 2) AS avg_bytes_per_file
FROM pg_stat_database
WHERE temp_files > 0
ORDER BY temp_bytes DESC;

temp_files 的生成速率比累积值更重要：

SELECT datname,
       round(temp_files / extract(epoch FROM now() - stats_reset)) AS temp_files_per_sec,
       temp_bytes / extract(epoch FROM now() - stats_reset) AS temp_bytes_per_sec_approx
FROM pg_stat_database
WHERE datname = current_database();
```text

如果 `temp_bytes_per_sec` 持续高于几百 KB/s，有查询的 `work_mem` 不足——它们在执行 disk-based sort 或 hash。这不是故障（临时文件机制是合法的），但持续的 disk-based sort 会消耗 I/O 带宽并拖慢查询。如果 `avg_bytes_per_file` 很大（如单个临时文件超过几百 MB），说明个别查询的排序数据集很大——需要为这些特定查询的 session 调高 `work_mem`（通过 `SET work_mem = '256MB'`），而不是全局调高。

临时文件的关键特性：它们不写入 WAL（使用 `temp_buffers`），不参与 checkpoint。所以大量临时文件不会增加 WAL 压力和 checkpoint I/O，但会消耗磁盘空间和 I/O 带宽。

### 监控维度的交叉验证

上一节提到六个监控维度不是独立存在的。真正的监控价值在于交叉验证。几个关键交叉点：

**连接慢 + 膨胀高 + wait_event=Lock**：典型的 idle in transaction 持锁链——一个忘了 COMMIT 的事务阻塞了 DDL，同时它的 `xmin` 阻止 VACUUM 回收，死元组堆积导致查询 seqscan 更多页面。修复路径不是调大 `shared_buffers`，而是 `pg_terminate_backend()` 那个 idle in transaction。

**查询突然变慢 + stddev_exec_time 飙升 + autovacuum 未触发**：统计信息漂移导致优化器选择了差计划。`pg_stat_user_tables.n_mod_since_analyze` 可以验证——如果这个值很大但 `last_autoanalyze` 是几小时前，手动 `ANALYZE` 该表可能就是修复路径。

**WAL 写入速率飙升 + checkpoints_req 频率增加 + IO 等待事件增加**：`max_wal_size` 太小的典型信号——WAL 写满触发频繁 checkpoint，checkpoint 写脏页产生 FPI 进一步增加 WAL 体积，形成正反馈循环。修复路径是调大 `max_wal_size`（从默认 1GB 到 4-16GB，取决于磁盘空间和 I/O 能力）。

**复制延迟上升 + pg_wal 文件数增长 + pg_replication_slots 有 inactive slot**：replication slot 溢出危机的标准前兆。修复路径：确认 standby 是否可恢复——如果可以恢复，`pg_stat_replication.reply_time` 应该能刷新；如果 standby 永久丢失，删除 slot（`pg_drop_replication_slot()`）以释放 WAL 段空间。

---

## 八、告警阈值的内核依据

### autovacuum_freeze_max_age 的 2 亿为什么不是安全线

PG 的 `autovacuum_freeze_max_age` 默认值 200,000,000（2 亿）。当 `age(pg_class.relfrozenxid)` 超过这个值时，autovacuum 触发 anti-wraparound VACUUM。在这种 VACUUM 中，`heap_vacuum_rel()` 扫描表的所有页面，对每个 tuple 检查 `xmin` 年龄。如果年龄超过 `vacuum_freeze_min_age`（默认 5000 万），则冻结该 tuple 的 `xmin`——将 `t_infomask` 中的 `HEAP_XMIN_FROZEN` 位置位，使得该 tuple 对所有事务可见，不再需要 XID 比较。

**2 亿是触发点，不是安全线。** 从触发到完成的窗口期存在倒计时风险：

1. Autovacuum Worker 被唤醒有一定延迟（受 `autovacuum_naptime` 控制，默认 1 分钟）。
2. Anti-wraparound VACUUM 全表扫描。对于 500GB+ 的表，这可能是 2-8 小时。
3. 在 VACUUM 扫描期间，系统的事务 ID 仍在被消耗。
4. 如果 XID 消耗速度快于 VACUUM 推进速度，年龄持续增长。
5. 当年龄达到约 20 亿时，数据库拒绝新事务，最终 shutdown。

所以告警阈值必须给 VACUUM 留足时间余量：

| 阈值 | 级别 | 动作 | 内核原因 |
|------|------|------|---------|
| `age(datfrozenxid)` > 1 亿 | Warning | 确认 autovacuum 配置足够；检查 XID 消耗速率趋势 | 给手动干预留足约 1 亿 XID（~数天到数周，取决于写入负载）的缓冲 |
| `age(datfrozenxid)` > 1.5 亿 | Critical | 手动 `VACUUM FREEZE` 冻结关键大表；考虑调低 `autovacuum_freeze_max_age` | Anti-wraparound autovacuum 已经开始但可能不够快；手动 VACUUM 不经过 cost-based delay |
| `age(datfrozenxid)` > 1.8 亿 | Emergency | 立即对所有高危表执行手动 `VACUUM FREEZE`；暂停非必要的写入 | 距离 trigger shutdown 只剩约 7000 万 XID——在高写入负载下可能就是几个小时 |

对于每天消耗 5000 万以上 XID 的高写入系统，告警阈值要更低（如 8000 万起）。计算 XID 消耗速率：

```sql
-- 需要两个时间点的快照
SELECT txid_current() AS current_xid, now() AS snapshot_time;
-- 1 小时后
SELECT txid_current() AS current_xid, now() AS snapshot_time;
-- XID 消耗速率 = (xid2 - xid1) / (time2 - time1)

autovacuum_vacuum_cost_delay 的平衡

autovacuum_vacuum_cost_delay（默认 2ms）控制 autovacuum 在执行 I/O 操作后的休眠时间。它的目的不是让 VACUUM 慢，而是防止 VACUUM 的 I/O 冲击影响业务查询——每次 vacuum_cost_page_hit（默认 1）、vacuum_cost_page_miss（默认 10）、vacuum_cost_page_dirty（默认 20）累积到 autovacuum_vacuum_cost_limit（默认 200）后，VACUUM 休眠 vacuum_cost_delay 毫秒。

这个机制的陷阱：如果你的系统 I/O 充足，2ms 的 delay 会显著拖慢 VACUUM。在高 UPDATE 负载下，VACUUM 可能追不上 dead tuple 的产生速度。监控 n_dead_tup 增长趋势 — 如果 last_autovacuum 的间隔越来越长但 dead tuple 仍在增长，说明 VACUUM 被限速拖住了。此时应该降低 autovacuum_vacuum_cost_delay（如改为 0 或者 1ms）或者提高 autovacuum_vacuum_cost_limit（如改为 2000）。

n_dead_tup / n_live_tup 的告警分层

需要区分表类型：append-only 表（只 INSERT）的 n_dead_tup 应接近 0——任何非零值都是异常（可能是事务回滚导致的 dead tuple）。高频 UPDATE 的小表（会话表、计数器表）的 n_dead_tup 可能在两次 VACUUM 之间跳跃，但只要 VACUUM 的频率足够（last_autovacuum 间隔短），不算膨胀。

九、监控数据源本身的坑

pg_stat_statements 的 queryid 陷阱

pg_stat_statements 使用 queryid（uint64 哈希）标识查询。两个问题：

并非真正参数化。 标准化过程（pgss_normalize_query()）保留常量值。所以以下两条 SQL 有不同的 queryid：

SELECT * FROM t WHERE id = 1;  -- queryid A
SELECT * FROM t WHERE id = 2;  -- queryid B
```text

只有当应用使用 prepared statement（`PREPARE` / Extended Query Protocol 的 Parse 阶段）时，`$1` 占位符才会让不同参数值共享同一 queryid。如果你在 `pg_stat_statements` 中看到大量 `calls = 1` 的条目，说明应用没有使用 prepared statement——每次查询都是新 queryid，统计条目数可能膨胀到数万条，把共享内存的 `pgss_hash` 撑满，导致旧条目被淘汰。

**哈希碰撞。** `queryid` 是 64 位非密码学哈希（基于 `hash_any()`）。虽然碰撞概率极低，但在数亿条不同查询的高规模环境中理论上有风险。PG 14 引入了 `compute_query_id` 基础设施，允许不同的扩展使用统一的 `queryid` 机制，但不解决碰撞问题本身。

缓解：使用 `pg_stat_statements.track = all`（默认 `top` 只跟踪最外层查询）会增加捕获量；如果你不需要嵌套查询的统计，保持默认 `top` 即可。对于 queryid 因为无 prepared statement 导致的爆炸，可以从应用层解决（使用 prepared statement 或 ORM 的参数化查询），或者定期调用 `pg_stat_statements_reset()` 来清理累积的噪声。

### track_io_timing 的性能开销

`track_io_timing = on` 启用后，每次 buffer read（`ReadBuffer()` 中的物理 I/O）都会在 I/O 开始前和结束后各调用一次高精度时间函数（`INSTR_TIME_SET_CURRENT()`，底层是 `clock_gettime(CLOCK_MONOTONIC)`）。在 Linux 上，`clock_gettime()` 通过 vDSO 实现，开销约 20-30ns。对于纯内存查询（`shared_buffers` 命中率接近 100%），这个开销几乎为零。

但对于 I/O 密集型负载——例如一张大表上的 seqscan 每秒读数千个页面——每个 `blks_read` 都伴随两次 `clock_gettime()` 调用。在高 QPS（如 10000+ TPS）的 OLTP 场景下，累积的系统调用开销可能相当于 5-10% 的额外 CPU 时间——这不是可以忽略的。

建议：在生产环境中默认关闭 `track_io_timing`。只在诊断 I/O 瓶颈时临时开启（通过 `ALTER SYSTEM SET track_io_timing = on; SELECT pg_reload_conf();`），诊断完就关闭。如果确实需要持续跟踪 I/O 延迟，确认你的 QPS 小于 1000/s，或磁盘 I/O 本身就是瓶颈（此时 `clock_gettime()` 相对于 I/O 延迟可以忽略）。

### pg_stat_activity 在高并发时的自身代价

查询 `pg_stat_activity` 需要获取 `ProcArrayLock` 的共享锁。在 2000+ 连接的环境下，`ProcArray` 是一个大数组。查询遍历整个数组来填充视图的每一行。同时，`GetSnapshotData()` 在每次查询开始时也需要获取相同的 `ProcArrayLock` 共享锁来扫描活跃事务列表——构建快照是 PG 中最频繁的操作之一（每个事务至少一次）。

当并发足够高（如 500+ 活跃连接同时执行查询）时，`ProcArrayLock` 共享锁上的竞争就会显现——`pg_stat_activity` 的查询和快照获取互相阻塞。症状是 `wait_event = 'ProcArrayLock'` 的等待事件。

缓解方法：
- 监控工具不要每 5 秒查询一次 `pg_stat_activity`。间隔设为 30 秒或更长。
- 如果只需要连接数，用 `pg_stat_database.numbackends` 而非扫描 `pg_stat_activity`。numbackends 是一个简单的原子计数器，不需要遍历 ProcArray。
- 如果需要 wait_event 分布，考虑 `pg_wait_sampling` 扩展（基于采样的等待事件统计，开销远低于持续扫描）。
- PG 14+ 的 snapshot scalability 优化（xmin/xid 的 per-backend 原子更新）减少了 `ProcArrayLock` 的持锁时间，但不能完全消除遍历开销。

### log_min_duration_statement 的日志洪水

`log_min_duration_statement` 记录超过指定毫秒数的所有查询。设得太低（如 `log_min_duration_statement = 0`，记录所有查询）会导致日志文件爆炸——在高 QPS 的 OLTP 系统中，每秒可能产生数千条日志行。日志写入本身消耗 I/O，而且在 Postgres 的 `syslogger` 进程中序列化——大量日志会阻塞其他日志消息。

建议：设为 `log_min_duration_statement = 100`（100ms），只记录慢查询。配合 `auto_explain` 扩展，在日志中自动附加这些慢查询的 `EXPLAIN` 输出：

```conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 100
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on

注意 auto_explain.log_analyze = on 会让 PG 实际执行查询后再输出 EXPLAIN ANALYZE——这意味着慢查询的额外开销来自 EXPLAIN ANALYZE 的执行（大约是查询本身的 5-10%）。如果系统已经在 I/O 或 CPU 极限，这可能是压垮骆驼的最后一根稻草。

十、工具集成：四层互补架构

PG 内核的统计视图是基础，但不同场景需要不同工具。四层分工如下：

第一层：pg_stat_* 视图 —— 即时精确状态

适用于：临时排查、手动诊断、一次性的状态查询。 不适用于：长期趋势分析、自动告警、历史回溯。

所有统计视图的计数器都是自 stats reset（或进程启动）以来的累积值。它们提供此刻的精确快照，但不保存时间序列历史。你在中午 12:00 查 pg_stat_user_tables 只能看到从上次 stats reset 到现在的累积死元组数，看不到上午 10:00 该表有多少死元组。

第二层：Prometheus + pg_exporter —— 时间序列与趋势告警

pg_exporter 是 Prometheus 的 PG exporter。它通过执行预定义的 SQL 将统计视图转换为 Prometheus 时间序列指标。核心价值是趋势——age(datfrozenxid) 的时间序列曲线比单个时间点的值重要得多，因为你可以计算斜率来预测何时达到危险值。

应该以 Prometheus 指标采集的：

• 连接：各状态的计数（idle、idle in transaction、active、wait_event 分布）。
• 膨胀：top-N 表的 n_dead_tup、n_live_tup、dead_ratio。
• XID 年龄：age(datfrozenxid) 趋势——最关键的时序指标。斜率决定了告警窗口。
• 复制延迟：write_lag、flush_lag、replay_lag 以及 pg_wal_lsn_diff(sent_lsn, replay_lsn)。
• WAL：wal_bytes_per_sec 写入速率、pg_ls_waldir() 的磁盘占用。
• 查询性能：top-N 查询的 mean_exec_time 和 calls 速率（通过 snapshot 差分为 rate 指标）。
• 锁：pg_locks 中 granted=false 的总数。
• 系统：pg_stat_database 的 blks_hit/blks_read、temp_files/temp_bytes、deadlocks。

不需要以 Prometheus 采集的：

• 查询文本本身——label cardinality 会爆炸，而且日志由 pgBadger 处理更好。
• 单次锁等待链——告警触发后用 pg_blocking_pids() 现场排查。
• 全量表级统计——表数量太多（几百到几千），每个表产生一组 Prometheus 时间序列，label cardinality 会压垮 Prometheus。只采集 top-N（如 top 20 死元组最多的表）。

告警规则示例（PromQL）：

# idle in transaction 超过 600 秒
pg_stat_activity_max_tx_duration{state="idle in transaction"} > 600

# XID 年龄超过 1 亿
pg_database_xid_age > 100000000

# 复制延迟超过 30 秒
pg_stat_replication_replay_lag > 30

# granted=false 的锁超过 10
count(pg_locks_not_granted) > 10

# WAL 目录超过 80% 的磁盘
pg_ls_waldir_size_bytes / pg_ls_waldir_disk_total_bytes > 0.8
```bash

### 第三层：pgBadger —— 日志离线分析

[pgBadger](https://github.com/darold/pgbadger) 解析 PG CSV 日志生成 HTML 报告。它补充了 `pg_stat_statements` 的缺口：

- **完整查询文本**：`pg_stat_statements` 截断查询到 `track_activity_query_size`（默认 1024 字节）。长 ETL 查询在 `pg_stat_statements` 中不可见。pgBadger 从日志中解析完整查询。
- **查询执行计划**：配合 `auto_explain`，捕获慢查询的 EXPLAIN 输出——这是 `pg_stat_statements` 完全没有的信息。
- **延迟分布直方图**：`pg_stat_statements` 只有 `mean`/`stddev`/`min`/`max`。pgBadger 显示持续时间直方图，揭示 P50/P95/P99 延迟分层和长尾特征。
- **Autovacuum 历史**：`log_autovacuum_min_duration = 0` 记录每次 autovacuum 操作的详情，pgBadger 可展示 autovacuum 的持续时间、处理死元组数、I/O 统计等。

pgBadger 需要的 PG 日志配置：

```conf
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_min_duration_statement = 100
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

第四层：pgCenter —— 实时现场诊断

pgCenter 是终端中的实时 PG 监控（类 top 界面）。它的最佳场景：

• 快速 ssh 到数据库主机看”此时此刻在发生什么”——连接数、活跃查询、锁等待、I/O、CPU。
• 临时诊断：看哪个查询在执行、哪个查询在等锁、I/O 在哪个表。
• 事后报告：pgcenter report 从 pg_stat_statements 和系统统计生成离线报告。

pgCenter 不替代 Prometheus/Grafana——Prometheus 给你时间序列趋势和自动告警，pgCenter 给你现场的即时视图。两者配合：Prometheus 告警通知你”XID 年龄超过 1 亿”，你 ssh 上去用 pgCenter 看”哪个表 age 最高、autovacuum 是否在跑、死元组多少”。

监控接入路径：从零到有

如果你接手了一个没有监控的 PG 集群，建议的接入顺序：

1. 先接 Prometheus + pg_exporter（第 1 天）。把连接数、idle in transaction 计数、XID 年龄、复制延迟、WAL 大小这些”生存指标”接入。这些指标的异常意味着数据库可能在几小时到几天内不可用——优先级最高。不需要调任何 PG 参数，pg_exporter 的默认采集 SQL 已经覆盖了这些。

2. 启用 pg_stat_statements（第 1 天）。在 shared_preload_libraries 中添加 pg_stat_statements，重启 PG。这是理解”哪些查询在消耗资源”的唯一窗口。不需要立即建告警，先把数据积累起来。

3. 配置慢查询日志和 auto_explain（第 2 天）。设置 log_min_duration_statement = 100，加载 auto_explain，启用 CSV 日志。确保你有查询文本和 plan 的历史记录。

4. 构建查询性能突变告警（第 1 周）。基于 snapshot 差分的 mean_exec_time 翻倍检测——这是最实用的”查询突然变慢”告警。

5. 引入 pgBadger 做离线分析（第 1 周）。不需要实时运行，每天跑一次日志分析报告——用于第二天早上回顾昨天的慢查询和 autovacuum 行为。

6. 配置膨胀和锁等待告警（第 2 周）。n_dead_tup/n_live_tup 比率和 granted=false 计数。这两类告警的紧急程度低于生存指标，但提供了更深层的系统健康信号。

7. 构建监控面板（持续迭代）。不是在第一天就照抄 80 个 Grafana panel——而是在排查了几次问题之后，把”每次都要查的东西”固化成面板。

四层分工总结

十一、关键要点

1. 监控的起点是内核故障模式，不是 Grafana 模板列表。 每个指标的内核数据结构决定了它的语义、更新频率和精度边界。不理解 n_dead_tup 只在 VACUUM 后更新，就会把其间增长的数据误判为”死元组突然消失”。

2. 连接数监控的核心不是百分比，是 idle in transaction 的持续时间。 一个 15 分钟的 idle in transaction 持锁、钉住 GetOldestXmin()、阻止 VACUUM 回收——它的破坏性比 100 个 idle 连接大得多。

3. 膨胀的根因是 VACUUM 跟不上 dead tuple 产生速度。 n_dead_tup/n_live_tup 是结果，n_tup_del + n_tup_upd 的速率和 last_autovacuum 的间隔才是需要调节的变量。对 append-only 表，任何非零 n_dead_tup 都是异常。

4. autovacuum_freeze_max_age（2 亿）是触发点不是安全线。 XID 年龄告警应设 1 亿至 1.5 亿，预留 anti-wraparound VACUUM 的全表扫描时间。高写入负载下阈值需按 XID 消耗速率动态调整。

5. 复制延迟的三层指标对应不同的瓶颈——网络、磁盘写入、WAL application。延迟为 0 时检查 reply_time：如果 reply_time 停留在几分钟前，standby 已不可用，延迟数字无意义。

6. shared_buffers 命中率 99% 不意味 I/O 没问题。 聚合命中率掩盖了冷热表差异和 buffer churn 的 CPU 开销。分表查看 pg_statio_user_tables 是必须的。

7. pg_stat_statements 需要 snapshot 差值做突变检测，不能只看累积值。 stddev_exec_time 区分”一直慢”和”偶尔慢”。mean_exec_time 相对于上一个窗口翻倍是计划突变的最早信号。

8. 监控工具本身有代价。 track_io_timing 增加系统调用开销（5-10% CPU in I/O heavy workloads），pg_stat_activity 的高频查询竞争 ProcArrayLock，auto_explain.log_analyze = on 在慢查询上叠加 EXPLAIN ANALYZE 的额外执行开销。

9. 四层工具有侧重地互补。 视图做即时诊断，Prometheus 做时序趋势和告警，pgBadger 从日志补充 plan 分析和完整查询文本，pgCenter 做实时现场勘查。不是”用一个工具替代所有”，而是”根据场景选正确的工具组合”。

上一章：扩展系统与 FDW 下一章：经典故障模式与排查手册，将用本章建立的监控指标体系逐层拆解五种致命故障模式——连接风暴、wraparound 危机、replication slot 溢出、OOM 连锁 kill、长事务隐性破坏——每种配故障时间线、内核根因和排查 SQL。

参考资料

源码（PG 17）

• src/backend/postmaster/pgstat.c：统计信息收集基础设施，pgstat_report_activity() 和各类计数器更新
• src/backend/storage/ipc/procarray.c：ProcArray 扫描、GetSnapshotData()、GetOldestXmin() — pg_stat_activity 的数据来源
• src/backend/storage/lmgr/lock.c：LockAcquire()、lock hash table — pg_locks 和 pg_blocking_pids() 的数据来源
• src/backend/storage/lmgr/lwlock.c：LWLock 实现和等待事件上报（pgstat_report_wait_start/end）
• src/backend/access/transam/xlog.c：XLogInsert()、XLogFlush() — pg_stat_wal 的计数器更新
• src/backend/replication/walsender.c：WalSndLoop() — pg_stat_replication LSN 和延迟的推进
• src/backend/access/heap/vacuumlazy.c：heap_vacuum_rel() — n_dead_tup/n_live_tup 的更新
• src/backend/access/heap/heapam.c：heap_update() — HOT UPDATE 判断和 n_tup_hot_upd 计数
• contrib/pg_stat_statements/pg_stat_statements.c：queryid 计算（pgss_hash_string()）和统计聚合（pgss_store()）
• src/backend/utils/adt/lockfuncs.c：pg_blocking_pids() 和 pg_locks 视图的 SQL 函数实现
• src/backend/postmaster/autovacuum.c：Autovacuum 调度和 autovacuum_freeze_max_age 触发逻辑

官方文档

• PostgreSQL Documentation, Chapter 27: Monitoring Database Activity（pg_stat_activity、pg_locks、pg_stat_replication、pg_stat_bgwriter）
• PostgreSQL Documentation, Chapter 28: Monitoring Disk Usage（pg_stat_user_tables、pg_statio_user_tables、pg_stat_database）
• PostgreSQL Documentation, Chapter 51: pg_stat_statements（queryid 计算、统计字段说明）
• PostgreSQL Documentation, Section 19.10: Automatic Vacuuming（autovacuum_freeze_max_age、vacuum_freeze_min_age、cost-based delay 机制）
• PostgreSQL Documentation, Section 30.5: WAL Configuration（WAL 写入、wal_sync_method、full_page_writes）
• PostgreSQL Documentation, Chapter 26: Backup and Restore（pg_stat_archiver、WAL 归档监控）

工具

• Vonng/pg_exporter: Prometheus exporter for PostgreSQL — https://github.com/Vonng/pg_exporter
• darold/pgBadger: PostgreSQL log analyzer — https://github.com/darold/pgbadger
• lesovsky/pgCenter: PostgreSQL command-line admin tool — https://github.com/lesovsky/pgcenter