数据库基础体系 · 第 102/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL 监控与诊断：pg_stat、锁、膨胀、慢查询和容量

在 PostgreSQL 中，“监控”不是定期查看几个数值，而是从数据库内部状态推导出因果链：

请求
  -> 会话与事务
  -> SQL 执行
  -> 访问表、索引、WAL 和缓存
  -> 行级、表级或事务级等待
  -> Vacuum、检查点、复制和文件增长
  -> 延迟、吞吐、容量与恢复风险

pg_stat_* 视图提供的是这条链路上的观测点。诊断的关键不在于某个指标是否“高”，而在于判断：

1. 哪个对象或会话处于异常状态；
2. 异常是资源消耗、等待，还是状态长期不推进；
3. 当前观测与其他观测之间是否能相互验证；
4. 采取操作后，状态是否按预期恢复。

本文围绕 PostgreSQL 官方公开语义讨论以下主题：

• pg_stat 统计信息的来源、范围、刷新和权限；
• 会话、事务和等待事件；
• 锁、阻塞链和死锁；
• MVCC、Vacuum、膨胀和冻结；
• 慢查询的发现、归因与验证；
• 表、索引、WAL、复制和文件系统容量；
• 一套从症状到根因的诊断流程。

示例默认适用于常见的 PostgreSQL 部署；具体视图、列和参数应以目标服务器版本的官方文档为准。需要扩展的示例会明确说明前置条件。

---

一、先建立观测模型：你看到的数值是什么

1. 统计视图不是实时审计日志

PostgreSQL 的许多统计信息由服务器进程累积，并通过统计系统暴露给 SQL。典型对象包括：

• pg_stat_activity：当前服务器进程和会话状态；
• pg_stat_database：数据库级事务、会话、缓存命中等累计指标；
• pg_stat_user_tables、pg_stat_all_tables：表访问、插入、更新、删除和 Vacuum；
• pg_stat_user_indexes：索引扫描和索引返回元组；
• pg_stat_bgwriter：后台写进程和检查点相关统计；
• pg_stat_wal：WAL 产生与写出统计，具体列随版本变化；
• pg_stat_replication、pg_stat_replication_slots：复制发送端状态；
• pg_locks：当前锁与等待锁，不是累计统计；
• pg_stat_statements：扩展提供的按归一化 SQL 聚合的执行统计。

应区分三类信息：

1. 当前状态

例如：

SELECT pid, usename, state, wait_event_type, wait_event,
       xact_start, query_start, state_change, query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

这些列描述当前后端进程看到的状态。一个会话可能从执行 SQL 变成等待锁，也可能刚好在查询结束后变成 idle。

2. 累计计数器

例如某表的 n_tup_upd、某数据库的 xact_commit。它们通常从统计重置点开始累积，而不是“最近一分钟”的值。要计算速率，必须两次采样：

$$rate = \frac{counter_2-counter_1}{t_2-t_1}$$

其中 counter 是累计值，t 是采样时间。只有在同一统计周期、没有发生重置，并正确处理计数器回绕时，这个速率才有意义。

3. 当前锁和当前文件大小

pg_locks 是当前锁表的视图；pg_relation_size() 是查询时的大小。它们不是累计值，不能用累计差分方式解释。

2. 统计时间、观察快照与权限

统计信息通常不是每个 SQL 都立即同步到所有读取者。某些统计数据在后端进程本地收集，再由统计系统提供；一个会话在读取统计视图时可能在一段时间内看到相对稳定的结果。排查问题时应记录：

• 查询开始和结束时间；
• 服务器版本；
• 统计是否曾被重置；
• 采样者的权限；
• 是否在同一个事务中多次读取视图。

如果一个长事务反复读取诊断结果，事务快照和统计视图的读取语义可能使“当前状态”看起来没有变化。采样脚本通常应使用短事务，并给每一批样本加上采样时间。

查看版本：

SELECT version(), current_setting('server_version_num');

从其他数据库查询目录和统计视图时还要注意：集群级对象、数据库级对象和当前数据库对象的范围并不相同。pg_stat_activity 可以看到整个实例上的活动，但普通用户通常只能看到自己的会话，超级用户或具备相应监控权限的角色才能观察其他会话的完整信息。

---

二、pg_stat 的核心视图：从会话到对象

1. pg_stat_activity：先判断会话处于什么状态

常用列的含义如下：

• pid：服务器后端进程 ID；
• datname、usename：数据库和用户；
• application_name、client_addr：连接来源；
• state：如 active、idle、idle in transaction、idle in transaction (aborted)；
• query_start：当前查询开始时间；对非活动会话，它表示最近一次查询开始时间；
• xact_start：当前事务开始时间；
• wait_event_type、wait_event：当前等待类型和具体等待事件；
• backend_xid、backend_xmin：与事务可见性和旧版本保留有关的事务标识，可能为空；
• query：当前或最近的查询文本，显示长度受相关设置影响。

一个更适合初步排查的查询：

SELECT pid,
       datname,
       usename,
       application_name,
       client_addr,
       state,
       clock_timestamp() - query_start AS query_age,
       clock_timestamp() - xact_start  AS xact_age,
       wait_event_type,
       wait_event,
       left(query, 300) AS query_sample
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;

active 不等于正在消耗 CPU

state = 'active' 表示后端正在执行查询，但它可能正在：

• 使用 CPU；
• 读取数据页；
• 等待锁；
• 等待客户端继续发送数据；
• 等待并行工作进程；
• 等待 WAL、I/O 或其他内部资源。

因此必须联合观察：

state, wait_event_type, wait_event

在 PostgreSQL 中，如果一个后端正在等待，通常仍可能显示为 active。active 与 wait_event 并不互斥。

idle in transaction 是一种危险状态

该状态表示事务已经开始，但当前没有执行查询。例如：

BEGIN;
SELECT * FROM orders WHERE id = 1;
-- 客户端长时间不发送 COMMIT 或 ROLLBACK

这个事务可能：

• 持有锁；
• 保持较早的事务快照；
• 阻止旧版本被清理；
• 使连接池中的连接长期不可用。

事务年龄的含义不同于查询年龄：

SELECT pid,
       now() - xact_start  AS transaction_age,
       now() - query_start AS query_age,
       state,
       wait_event_type,
       wait_event,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

这里的 xact_start 为空表示当前没有事务；query_start 较早但 state = 'idle'，只说明最近一次查询较早，不代表查询仍在运行。

2. 数据库级统计：总量和趋势

SELECT datname,
       numbackends,
       xact_commit,
       xact_rollback,
       blks_read,
       blks_hit,
       tup_returned,
       tup_fetched,
       temp_files,
       temp_bytes,
       deadlocks,
       stats_reset
FROM pg_stat_database
ORDER BY datname;

缓存命中率可以粗略计算为：

$$hit\_ratio = \frac{blks\_hit}{blks\_hit+blks\_read}$$

但它只表示通过 PostgreSQL 缓冲区访问时，页面在共享缓冲区中命中的比例。它不等价于：

• 查询一定很快；
• 操作系统页缓存命中率；
• 存储设备没有瓶颈；
• 某个具体查询没有大量 I/O。

例如一个只访问很小热表的系统可能有很高的全局命中率，同时某个大表范围扫描仍然很慢。相反，批量扫描导致的低命中率也未必是故障。

回滚数也不能直接解释为“数据库出错”。应用主动回滚、事务冲突、约束错误和死锁回滚都可能增加 xact_rollback。必须结合日志和会话状态定位原因。

3. 表和索引统计：访问量不等于成本

表统计：

SELECT schemaname,
       relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze,
       vacuum_count,
       autovacuum_count,
       analyze_count,
       autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC NULLS LAST;

这些字段通常是估计或累计统计：

• seq_scan：顺序扫描次数；
• seq_tup_read：顺序扫描读取的元组数；
• idx_scan：索引扫描次数；
• idx_tup_fetch：通过索引扫描取回的元组数；
• n_live_tup、n_dead_tup：对当前活元组、死元组的估计；
• last_autovacuum、last_autoanalyze：最近一次自动维护时间。

idx_scan 高不代表索引有效，seq_scan 高也不代表缺少索引。需要结合：

• 查询谓词；
• 返回行数；
• 表大小；
• 选择性；
• 统计信息；
• EXPLAIN (ANALYZE, BUFFERS) 的实际计划。

索引统计：

SELECT schemaname,
       relname,
       indexrelname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan NULLS FIRST;

一个索引扫描次数很低，可能是无用索引，也可能是低频但关键的查询索引；删除前必须检查约束、唯一性、外键和业务查询。某些索引还可能被优化器使用，但统计观察窗口内没有被访问。

4. pg_stat_io 与版本边界

较新 PostgreSQL 版本提供更细粒度的 I/O 统计视图，例如按后端类型、对象类型和上下文观察读取、写入、命中、扩展和刷新。它适合回答：

• I/O 是来自用户后端，还是后台进程；
• I/O 发生在表、索引、临时文件还是其他对象；
• 写入压力是否集中在某类上下文。

但 pg_stat_io 的列和可用范围具有版本敏感性。监控 SQL 不应假设所有 PostgreSQL 版本都存在同一列；部署多个版本时，应按 server_version_num 选择查询。

---

三、锁与阻塞：从等待者反推出持有者

1. 锁的作用和两种状态

锁是并发控制机制，用于保护对象或事务状态。常见粒度包括：

• 表级锁；
• 行级锁；
• 页级锁；
• advisory lock；
• 事务 ID 相关锁；
• 虚拟事务相关锁。

pg_locks 展示后端当前请求或持有的锁。关键字段包括：

• pid：关联后端；
• locktype：锁类型；
• mode：锁模式；
• granted：是否已获得；
• relation：关联关系 OID；
• transactionid：事务 ID 锁；
• virtualxid：虚拟事务 ID 锁；
• waitstart：开始等待的时间，具体可用性依版本而定。

granted = false 表示等待锁；granted = true 表示已持有或已获得锁。

只看等待者不够，因为等待者的 SQL 不一定是根因。根因通常是某个持锁时间很长的事务。

2. 构造阻塞关系

较新的 PostgreSQL 提供 pg_blocking_pids(pid)，可直接获取阻塞某个进程的 PID。一个实用查询是：

SELECT blocked.pid AS blocked_pid,
       blocked.usename AS blocked_user,
       blocked.state AS blocked_state,
       blocked.wait_event_type,
       blocked.wait_event,
       clock_timestamp() - blocked.query_start AS blocked_for,
       left(blocked.query, 300) AS blocked_query,
       blocker.pid AS blocker_pid,
       blocker.usename AS blocker_user,
       blocker.state AS blocker_state,
       clock_timestamp() - blocker.xact_start AS blocker_xact_age,
       left(blocker.query, 300) AS blocker_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(pid)
  ON true
JOIN pg_stat_activity AS blocker
  ON blocker.pid = bp.pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocked.query_start;

其因果关系是：

blocked.pid
  -> pg_blocking_pids(blocked.pid)
  -> blocker.pid
  -> blocker 的事务年龄、状态和 SQL

如果阻塞者是 idle in transaction，最后一条 SQL 可能早已执行完；真正需要关注的是它没有结束事务。

3. 锁冲突不是所有锁都能互相阻塞

表锁模式之间是否冲突由 PostgreSQL 的锁矩阵决定。不能依据“锁模式名字看起来更强”来推断。诊断时应：

1. 找到 granted = false 的等待者；
2. 获取阻塞 PID；
3. 观察阻塞者持有的锁；
4. 确认对象、事务和 SQL；
5. 判断是正常串行化、长事务，还是异常阻塞。

例如 ALTER TABLE、某些 TRUNCATE、VACUUM FULL、REINDEX 等操作可能需要强表锁，并阻塞普通访问。相反，普通 DML 通常不会简单地阻塞所有读查询，不能把“有写事务”直接等同于“读被锁住”。

行锁还有一个容易误解的地方：行锁冲突可能通过事务 ID 锁表现出来，而不是在 pg_locks 中出现一个直观的“这行被某 PID 持有”的记录。此时应结合 pg_stat_activity 和 pg_blocking_pids() 分析事务依赖。

4. 死锁与锁等待的区别

锁等待是等待者最终可能获得锁；死锁则是等待关系形成环：

事务 A 持有锁 1，等待锁 2
事务 B 持有锁 2，等待锁 1

PostgreSQL 的死锁检测器会检查等待图。发现死锁后，会中止其中一个事务并报错，通常表现为：

ERROR: deadlock detected

这与普通锁等待不同：普通等待可能长期存在但不形成环；死锁通常会被检测并打破。生产诊断应保留死锁日志，因为 pg_stat_database.deadlocks 只能说明发生过多少次，不能重建完整 SQL 顺序。

降低死锁风险的根因措施是让事务以一致顺序访问对象或行，并缩短事务；提高 deadlock_timeout 只会改变检测时机，不会解决锁顺序问题。

5. 终止会话的风险

常用操作：

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

• pg_cancel_backend 请求取消当前查询，但保留连接和事务上下文；
• pg_terminate_backend 终止后端，数据库会回滚其未提交事务并释放资源。

终止阻塞者可能立即释放锁，但回滚本身也可能耗时，并继续持有部分资源。若终止的是关键业务事务，还可能引起应用重试风暴。执行前应确认：

• PID 是否仍是同一会话；
• 进程属于哪个应用；
• 是否处于复制、维护或 DDL 操作；
• 事务是否可能很大；
• 应用是否会自动重连并重复提交。

---

四、MVCC、Vacuum 与表膨胀

1. UPDATE 为什么可能增加文件占用

PostgreSQL 使用 MVCC（多版本并发控制）。更新一行时，通常不是直接覆盖旧版本，而是：

1. 创建新版本；
2. 将旧版本标记为不可供未来事务使用；
3. 通过事务可见性判断不同事务应该看到哪个版本；
4. 等所有可能看到旧版本的事务结束后，Vacuum 才能回收旧版本占用的空间。

删除也类似：删除操作首先标记版本，之后由 Vacuum 清理。

因此表空间占用并不只由“当前逻辑行数”决定。更新频繁、删除频繁、长事务或自动维护不足时，物理文件可能明显大于当前有效数据量。

2. 可见性的形式化条件

对一个元组版本，可以抽象出：

• xmin：创建该版本的事务 ID；
• xmax：删除或替换该版本的事务 ID，可能为空；
• S：某个事务开始时的快照；
• committed(x)：事务 x 是否已提交；
• visible(x, S)：事务 x 是否对快照 S 可见。

简化地说，旧版本对快照 S 可见，需要满足：

$$visible\_insert(xmin,S) \land \neg visible\_delete(xmax,S)$$

其中：

• visible_insert 表示创建者已经提交，且不晚于快照可见边界；
• visible_delete 表示删除者已经提交，并且删除已对该快照可见；
• 如果 xmax 为空，则没有删除者；
• 如果相关事务仍未提交，其他事务可能不能把该版本视为已删除。

Vacuum 要安全移除旧版本，必须确认不存在任何仍然有效的快照可能看到它。这就是为什么“一个很久不结束的事务”能够阻止清理：即使它当前处于 idle in transaction，它的快照仍可能代表一个很早的可见性边界。

这不是“Vacuum 没有运行”，而是 Vacuum 运行后发现旧版本仍不能安全移除。

3. n_dead_tup 不等于精确膨胀字节数

pg_stat_user_tables.n_dead_tup 是估计的死元组数量。它不能直接乘以平均行大小就得到准确的膨胀量，因为还涉及：

• 行版本大小不同；
• 页面内空闲空间；
• HOT 更新；
• 页面布局；
• TOAST 表；
• 索引中的对应项；
• 已被标记为可复用但文件尚未缩小的空间。

一个表可能有较高 n_dead_tup，但后续插入能够复用这些页面，文件不会继续增长。反过来，死元组不多时，历史页布局、索引膨胀或 TOAST 仍可能造成较大的物理占用。

4. Vacuum 的三个不同目标

普通 VACUUM

普通 Vacuum 通常：

• 清理可回收的死元组；
• 更新页面可见性等维护信息；
• 使空间可供同一表后续使用；
• 不通常把表文件截断到任意更小尺寸；
• 不需要长时间持有阻塞普通读写的强表锁。

它的主要目标是“回收给本表复用”，不是把磁盘空间归还给操作系统。

VACUUM (FULL)

VACUUM FULL 会重写表，压缩物理布局，通常可以显著缩小文件，但它需要更强的锁，并且需要额外磁盘空间保存重写过程中的新副本。生产环境执行前必须计算：

所需峰值空间
≈ 原表及相关对象占用
  + 重写副本
  + 并发业务产生的其他增长

它可能在空间最紧张时反而无法执行，也可能因强锁造成业务中断。

ANALYZE

ANALYZE 不负责清理死元组。它采样数据，更新优化器统计信息，使选择性估计和执行计划更准确。表没有膨胀，不代表统计信息一定新；统计信息新，也不代表膨胀已解决。

5. Freeze 与事务 ID 回卷风险

事务 ID 空间有限。数据库不能无限期使用越来越老的事务 ID，否则会发生回卷风险。冻结（freeze）的目的，是把足够老的事务状态标记为对所有事务都可见，从而不再需要保留完整的历史事务 ID 语义。

应观察数据库级最老事务年龄：

SELECT datname,
       age(datfrozenxid) AS frozen_xid_age,
       mxid_age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY frozen_xid_age DESC;

还应观察会话中的长事务：

SELECT pid,
       datname,
       usename,
       state,
       age(backend_xid)  AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_age,
       query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL
   OR backend_xmin IS NOT NULL
ORDER BY xact_start NULLS LAST;

backend_xmin 反映后端可能要求保留的可见性边界；它不应被简单解释为“该后端正在修改这么多行”。诊断冻结问题时，要结合：

• 数据库的 datfrozenxid；
• 表的冻结年龄统计；
• 长事务；
• 复制槽；
• 逻辑复制或其他保留快照；
• Autovacuum 是否被禁用、延迟或频繁失败。

自动 Vacuum 的触发与表规模、更新删除量、相关阈值和成本设置有关，不能只以“某个固定分钟数没有运行”判断异常。对高更新表，应重点观察死元组增长速率、最近一次自动 Vacuum、事务年龄和维护是否经常被取消。

6. HOT 更新和索引膨胀

如果更新不修改索引涉及的列，并且页面上有足够空间，PostgreSQL 可能执行 HOT（Heap-Only Tuple）更新。新版本留在同一堆页面中，减少索引项新增；但 HOT 不是保证：

• 页面空间不足时无法使用；
• 修改索引列时通常不适用；
• 仍需要 Vacuum 清理旧版本链；
• 表和索引可能因不同访问模式产生不同程度的空间浪费。

因此“索引扫描很快”与“索引物理占用合理”是两个问题。

---

五、如何测量容量和膨胀

1. 从数据库、表、索引到 TOAST

数据库大小：

SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

表及其关联对象：

SELECT schemaname,
       relname,
       pg_size_pretty(pg_table_size(relid)) AS table_heap_toast,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

三个函数的区别：

• pg_table_size()：表本体及相关 TOAST 等存储，但不包含索引；
• pg_indexes_size()：该表所有索引占用；
• pg_total_relation_size()：表、TOAST 和索引的总占用。

如果只看“逻辑表数据”，却忽略 TOAST 和索引，会严重低估容量。大字段、JSON、数组和宽行可能把大量数据放入 TOAST 表。

查看表、索引、TOAST 的关系：

SELECT c.oid::regclass AS relation,
       c.relkind,
       pg_size_pretty(pg_relation_size(c.oid)) AS main_size,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
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 pg_total_relation_size(c.oid) DESC
LIMIT 50;

relkind 的具体编码应以系统目录文档为准；不要把所有 pg_class 行都当作普通表。

2. 容量是增长率问题，不只是当前大小

对表或数据库做定期采样：

t0: size0
t1: size1

增长率：

$$growth\_rate = \frac{size_1-size_0}{t_1-t_0}$$

预计达到可用空间上限的时间可粗略估计为：

$$time\_to\_full = \frac{usable\_free\_space}{growth\_rate}$$

这个估计只有在增长率相对稳定时才有意义。生产上至少要分离观察：

• 业务数据增长；
• 更新/删除导致的死版本；
• 索引增长；
• TOAST 增长；
• 临时文件；
• WAL；
• 归档目录；
• 复制槽保留的 WAL；
• 操作系统或云盘文件系统剩余空间。

WAL 和表数据增长不是同一件事。一次大批量更新可能产生大量 WAL，但表文件未必按相同字节数增长；启用归档、复制或逻辑槽时，WAL 还可能在下游未消费时持续保留。

3. 用文件系统和数据库视角互相验证

数据库视角：

SELECT pg_size_pretty(pg_database_size(current_database()));

操作系统视角：

df -h /var/lib/postgresql
du -sh /var/lib/postgresql/* 2>/dev/null

两者不一致并不立即说明统计错误，原因可能包括：

• 数据库目录下还有 WAL；
• 归档目录在另一个位置；
• 临时文件；
• 多个表空间；
• 日志或备份文件；
• 文件已被删除但仍被进程打开；
• du、文件系统稀疏文件和快照的统计方式不同。

容量故障的诊断必须明确“哪个路径满了”，而不是只看数据库总大小。

---

六、慢查询：先区分执行慢、等待慢和客户端慢

1. 一个查询为什么会慢

端到端延迟可拆成：

$$T_{total} = T_{queue} + T_{lock} + T_{plan} + T_{cpu} + T_{io} + T_{network} + T_{client}$$

其中：

• T_queue：连接池或应用排队；
• T_lock：等待锁；
• T_plan：解析、重写和生成计划；
• T_cpu：执行计算；
• T_io：读取或写入数据；
• T_network：结果传输；
• T_client：客户端接收或处理结果。

数据库日志中的执行时间与用户看到的总耗时不一定相同。一个查询计划执行很快，但客户端迟迟不读取结果，服务器可能出现发送相关等待；一个查询根本没开始执行，可能已经在连接池或锁上等待很久。

2. pg_stat_activity 只能发现当前慢查询

当前活动查询：

SELECT pid,
       now() - query_start AS duration,
       wait_event_type,
       wait_event,
       state,
       datname,
       usename,
       application_name,
       query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

这适合发现“现在正在发生”的问题，但查询结束后信息就会被覆盖。要发现历史上最消耗资源的 SQL，需要日志或 pg_stat_statements。

3. 使用 pg_stat_statements 聚合历史 SQL

前置条件通常包括：

1. 在 shared_preload_libraries 中加载 pg_stat_statements；
2. 重启服务器使预加载配置生效；
3. 在目标数据库执行：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

查询累计执行时间：

SELECT calls,
       total_exec_time,
       mean_exec_time,
       rows,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_written,
       wal_bytes,
       left(query, 500) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

常见含义：

• calls：调用次数；
• total_exec_time：累计执行时间；
• mean_exec_time：平均执行时间；
• rows：累计返回或影响的行数；
• shared_blks_hit、shared_blks_read：共享缓冲区命中和读取；
• temp_blks_written：临时块写入；
• wal_bytes：生成 WAL 的统计，具体可用性依版本和语句类型而定。

平均时间和总时间回答不同问题：

• mean_exec_time 高：单次调用慢；
• calls 很高而平均时间不高：可能是“低延迟但高总成本”的热点；
• total_exec_time 高：总体消耗大；
• temp_blks_written 高：可能发生排序、哈希或物化溢出；
• shared_blks_read 高：可能是扫描量大，也可能是缓存不热。

pg_stat_statements 按归一化后的查询聚合，字面量通常被参数化为占位符。它非常适合排名，但不能替代单次执行计划，因为不同参数可能触发不同计划。统计还可能在服务器重启、扩展重置或人工调用重置函数后归零，因此必须保存采样数据。

4. 日志是低频慢查询的事实来源

常用配置包括：

log_min_duration_statement = 1000
log_lock_waits = on
deadlock_timeout = 1s

含义：

• log_min_duration_statement = 1000：记录执行时间达到 1000 毫秒的语句；
• log_lock_waits = on：当锁等待超过 deadlock_timeout 时记录相关信息；
• deadlock_timeout：死锁检查和锁等待日志相关的等待阈值，不是“锁最多只能等这么久”。

这些设置会增加日志量。阈值应结合流量和日志接收能力。若需要执行计划，可考虑 auto_explain，但对所有查询启用 ANALYZE 可能增加开销，尤其是高并发系统。

5. 用 EXPLAIN 从估计到实测

先看不执行的计划：

EXPLAIN
SELECT o.id, o.created_at
FROM orders AS o
WHERE o.customer_id = 42
  AND o.created_at >= CURRENT_DATE - INTERVAL '7 days';

再在可控环境或确认副作用后使用：

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT o.id, o.created_at
FROM orders AS o
WHERE o.customer_id = 42
  AND o.created_at >= CURRENT_DATE - INTERVAL '7 days';

EXPLAIN ANALYZE 会真正执行语句：

• 对 SELECT 通常没有业务写入副作用；
• 对 INSERT、UPDATE、DELETE 会实际修改数据；
• 可能触发触发器、函数、WAL 和锁；
• 不能在生产上不加判断地执行。

关键比较是估计行数与实际行数：

estimated rows  vs  actual rows

若某节点估计 10 行但实际 1,000,000 行，优化器可能选择错误连接顺序或连接算法。常见原因有：

• 统计信息过旧；
• 数据分布高度倾斜；
• 多列相关性未被建模；
• 谓词表达式难以估计；
• 参数化查询的通用计划不适合所有参数。

BUFFERS 用于观察共享缓冲区命中、读取和临时块；WAL 可帮助识别写入代价。执行计划中出现顺序扫描本身不是错误，若查询需要返回表中大部分行，顺序扫描往往是合理选择。

6. 计划正常但仍慢的反例

下面的查询可能拥有合理计划，却仍然延迟很高：

SELECT * FROM large_table;

原因可能是：

• 返回数据量巨大；
• 网络传输慢；
• 客户端逐行处理；
• 客户端没有及时读取结果，服务器等待发送；
• 业务把大结果集作为单次请求返回。

因此不能仅凭“数据库 CPU 不高”或“执行计划用了索引”断言系统正常。需要比较数据库执行时间、等待事件、网络发送和客户端耗时。

---

七、连接、事务和资源耗尽

1. 连接数不是吞吐量

查看连接使用：

SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction')
           AS idle_in_transaction
FROM pg_stat_activity;

再与限制比较：

SELECT current_setting('max_connections')::int AS max_connections;

连接过多可能导致：

• 每个后端进程占用内存；
• 上下文切换增加；
• 锁竞争增加；
• 应用排队；
• 数据库达到连接上限，新连接失败。

连接池可以减少后端连接数，但连接池不能修复长事务、慢 SQL 或未提交事务。池中的客户端连接处于 idle 不一定异常；处于 idle in transaction 才通常需要优先调查。

2. 事务年龄比连接空闲时间更重要

以下查询找出长事务：

SELECT pid,
       datname,
       usename,
       application_name,
       client_addr,
       state,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       wait_event_type,
       wait_event,
       left(query, 300) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

一个会话可能只有一条很短的 SQL，但因为客户端忘记提交，事务持续数小时。这种状态往往同时解释：

• 锁一直不释放；
• n_dead_tup 持续增长；
• Vacuum 清理效果差；
• datfrozenxid 年龄升高；
• 复制或逻辑解码无法推进。

应用侧应保证每个事务都有明确的提交、回滚和超时路径。数据库侧可以用 idle_in_transaction_session_timeout 保护系统，但它是强制断开机制，必须评估应用是否能够正确处理连接终止。

---

八、复制、WAL 与容量的联动

1. 主库复制状态

物理流复制发送端常见查询：

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       sync_state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       write_lag,
       flush_lag,
       replay_lag
FROM pg_stat_replication;

LSN 是 WAL 位置。发送、写入、刷盘和重放位置之间的差异可以帮助判断延迟在哪个阶段：

主库产生 WAL
  -> 发送端 sent_lsn
  -> 备库接收并写入 write_lsn
  -> 备库刷盘 flush_lsn
  -> 备库重放 replay_lsn

不同版本对延迟列的计算和展示可能存在差异，不应把空值简单当成“零延迟”。应同时查看 LSN 差距、备库状态和网络情况。

2. 复制槽可能保留 WAL

复制槽的目的是防止主库过早移除下游仍需要的 WAL。若槽对应的消费者停止推进，WAL 会持续保留，最终填满磁盘。

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

其中可用列依版本而异。诊断逻辑是：

1. 找到增长最快的 WAL 或归档目录；
2. 检查复制槽是否长时间不活跃；
3. 计算当前 WAL 与槽保留位置之间的距离；
4. 确认槽是否仍被业务使用；
5. 不要未经确认直接删除槽。

删除仍有消费者依赖的槽可能导致下游无法继续，需要重新初始化或重新同步。容量告警不能只盯着表大小，复制槽和归档失败同样会造成磁盘耗尽。

---

九、从症状到根因的诊断流程

场景一：接口突然变慢

按以下顺序采样：

-- 1. 当前活动和等待
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS query_age,
       now() - xact_start AS xact_age,
       left(query, 300) AS query
FROM pg_stat_activity
WHERE state <> 'idle';

-- 2. 阻塞关系
SELECT pid, pg_blocking_pids(pid), query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 3. 慢 SQL 聚合
SELECT calls, total_exec_time, mean_exec_time,
       shared_blks_read, temp_blks_written, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

推导路径：

• 若大量会话等待同一阻塞者，先处理锁或长事务；
• 若没有锁等待但共享块读取和 CPU 很高，检查执行计划；
• 若数据库执行时间不高但接口仍慢，检查连接池、网络和客户端读取；
• 若大量临时块写入，检查排序、哈希、聚合和 work_mem 使用；
• 若问题只发生在特定参数，比较不同参数的实际计划，而非只看归一化 SQL 的平均值。

场景二：磁盘持续增长

先按对象排序：

SELECT n.nspname AS schema_name,
       c.relname,
       c.relkind,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 'i', 't')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 30;

再联结维护和事务信息：

SELECT s.relid::regclass AS table_name,
       s.n_live_tup,
       s.n_dead_tup,
       s.last_autovacuum,
       s.last_autoanalyze,
       a.pid,
       a.state,
       now() - a.xact_start AS xact_age
FROM pg_stat_user_tables AS s
LEFT JOIN pg_stat_activity AS a
  ON a.backend_xmin IS NOT NULL
ORDER BY s.n_dead_tup DESC NULLS LAST;

这里不能仅凭一条 SQL 得出结论。还应分别检查：

• 大表本体；
• 索引；
• TOAST；
• 临时文件；
• WAL；
• 复制槽；
• 归档目录；
• 文件系统路径。

若确认是死版本堆积，先查长事务和 Vacuum 是否能推进；若是索引或表的物理膨胀，再选择普通 Vacuum、在线重建、分区治理或计划内的重写操作。VACUUM FULL 不是通用“压缩按钮”。

场景三：死锁频发

需要同时保留：

• PostgreSQL 死锁日志；
• 应用请求 ID；
• 事务中的 SQL 顺序；
• 涉及表和行的业务键；
• deadlocks 的累计趋势。

仅查看当前 pg_locks 往往来不及，因为死锁检测后其中一个事务已经被回滚。日志中的“过程”比事后快照更重要。修复通常是统一访问顺序、缩短事务、减少事务内外部调用，并检查重试逻辑是否把冲突放大。

---

十、常见误解与边界

1. “命中率低，所以一定要加内存”

不成立。命中率是全局聚合指标，可能被批量扫描、一次性查询或统计重置影响。应优先定位具体查询的 BUFFERS、对象大小、工作集和存储延迟。

2. “有死元组，所以立刻执行 VACUUM FULL”

不成立。死元组可能被普通 Vacuum 清理并复用。先判断：

• 是否有长事务阻止清理；
• 死元组是否仍增长；
• 表文件是否真的需要归还操作系统；
• 是否有足够临时磁盘；
• 是否能承受强锁；
• 是否可以采用在线重建或分区替换。

3. “n_dead_tup 就是膨胀百分比”

不成立。它是估计的死元组数，不能直接代表可回收字节，也不包含所有索引和 TOAST 影响。

4. “查询很慢，所以 SQL 一定在执行”

不成立。它可能在连接池排队、等待锁、等待客户端读取，甚至在应用事务中尚未提交给数据库。端到端监控必须把应用、连接池和数据库时间关联起来。

5. “pg_stat_statements 的平均时间代表所有请求”

不成立。归一化 SQL 可能有不同参数、不同计划和不同数据分布。应结合百分位延迟、日志样本和具体参数的 EXPLAIN。

6. “重置统计后，系统性能变好了”

不成立。重置只改变观测基线，不改变查询、锁、表或 WAL 本身。重置统计前应记录旧值，否则会失去趋势证据。

---

十一、把监控指标组织成可解释的信号

一套有用的 PostgreSQL 监控不应只有孤立阈值，而应围绕状态变化建立关联：

会话与事务

• 当前连接数、活动数、空闲事务数；
• 最长事务年龄；
• 等待锁的会话数和最长等待；
• 按应用、用户、数据库分组。

执行与等待

• SQL 执行时间的平均值和高百分位；
• pg_stat_statements 的累计时间和调用次数；
• 锁等待、I/O 等待、客户端发送等待；
• 临时文件和临时块增长；
• WAL 生成速率。

表与维护

• n_dead_tup 及其增长速率；
• 自动 Vacuum、自动 Analyze 的最近时间；
• 表、索引、TOAST 大小；
• 最老事务年龄；
• 数据库冻结年龄和多事务年龄。

复制与容量

• 发送、刷盘、重放位置；
• 复制延迟和槽保留 WAL；
• 归档失败；
• 各表空间和文件系统可用空间；
• 数据、索引、WAL、日志、临时文件的增长率。

这些指标的价值来自关联。例如：

接口变慢
+ 大量 wait_event = Lock
+ 一个 idle in transaction 会话年龄很大
=> 优先判断长事务阻塞

磁盘增长
+ n_dead_tup 快速增加
+ autovacuum 未推进
+ backend_xmin 很老
=> 优先判断旧快照阻止版本回收

磁盘增长
+ 表和索引增长不大
+ replication slot 长时间不活跃
=> 优先检查 WAL 保留，而不是先重建表

---

十二、生产操作的验证闭环

任何修复操作都应有明确的前后验证。

取消或终止会话后

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age
FROM pg_stat_activity
WHERE pid = 12345;

确认：

• 会话是否消失或查询是否结束；
• 阻塞链是否断开；
• 回滚是否仍在进行；
• 应用是否开始重试。

调整维护后

观察一段时间：

SELECT relid::regclass,
       n_dead_tup,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
WHERE relid = 'public.orders'::regclass;

确认死元组增长是否下降、自动 Vacuum 是否成功、事务年龄是否允许清理推进。

调整 SQL 或索引后

不要只看一次请求。比较同一采样窗口内的：

• 实际计划；
• 执行时间分布；
• shared_blks_read；
• 临时块；
• WAL；
• 调用次数；
• 锁等待；
• 业务成功率。

处理容量后

确认数据库和操作系统两侧：

SELECT pg_size_pretty(pg_database_size(current_database()));

-- 操作系统
-- df -h <数据目录或表空间路径>

如果删除了归档文件、废弃了复制槽或完成了重写操作，还要验证：

• 文件系统空间是否真正释放；
• 下游复制是否仍健康；
• 备份和恢复链是否完整；
• 监控基线是否因统计重置而改变。

PostgreSQL 诊断的核心是把“当前等待”“累计统计”“物理大小”“事务可见性”和“业务延迟”放在同一条因果链上。pg_stat 告诉你发生了什么，pg_locks 告诉你谁在等待谁，MVCC 和 Vacuum 解释为什么旧版本仍然存在，执行计划解释 SQL 为什么消耗资源，容量与复制视图则说明这些状态何时会变成系统级故障。只有将它们联合起来，监控数据才会从数字变成可验证的诊断结论。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：PostgreSQL 备份恢复：pg_dump、Base Backup、WAL 归档和 PITR
• 下一篇：PostgreSQL 参数与性能：内存、WAL、Planner、连接和 Autovacuum
• 延伸：PostgreSQL MVCC 与 Vacuum：Tuple 可见性、膨胀、冻结和诊断
• 延伸：数据库可观测性：连接、事务、锁、执行计划、复制和 SLO

官方资料

• PostgreSQL Current Documentation
• PostgreSQL Internals

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。