PostgreSQL 故障排除的五大陷阱及规避方法

大多数 PostgreSQL 事件并非始于某种异常情况。它们通常始于一个缓慢的端点、一个阻塞会话队列、一张增长过快的表，或者在最糟糕的时刻来自 WAL 分区的磁盘警报。难点不在于知道 PostgreSQL 有索引、自动清理、内存设置、锁和 WAL。难点在于知道当前哪个因素最关键，并避免那些会让下一个事件变得更糟的修复措施。

下面列出的 PostgreSQL 故障排除陷阱是我在实际运维工作中最常见的。它们不仅仅是“调整这个参数”的建议。每个陷阱都包含了症状、陷阱本身，以及在修改生产环境之前更安全地推理问题的方法。

陷阱 1：索引不足与误用

导致 PostgreSQL 性能缓慢的最常见原因之一是索引不佳。许多 DBA 仅依赖自动创建的主键索引，未能考虑特定的查询模式，导致频繁且昂贵的顺序扫描，而非高效的索引扫描。

诊断：顺序扫描

当查询性能不佳时，从执行计划开始分析。如果查询会修改数据或可能运行很长时间，请先使用普通的 EXPLAIN。当你可以安全执行查询并需要真实计时和 I/O 行为时，再使用 EXPLAIN (ANALYZE, BUFFERS)。

EXPLAIN ANALYZE
SELECT * FROM user_data WHERE last_login > '2023-10-01' AND status = 'active';

规避陷阱：复合索引和部分索引

如果查询在 WHERE 子句中使用了多个列，复合索引可能会有所帮助，但列的顺序取决于查询的形状。等值过滤器通常应放在范围过滤器之前。对于像 WHERE status = 'active' AND last_login > ... 这样的查询，在 (status, last_login) 上建立索引通常比在 (last_login, status) 上更有用，因为 PostgreSQL 可以先缩小到一个状态，然后扫描日期范围。对于 ORDER BY last_login DESC LIMIT 50，最佳索引可能不同。

此外，考虑使用部分索引来仅对满足特定条件的列进行索引。这可以减小索引大小，并加快索引创建和维护的速度。

-- 为上述示例查询创建复合索引
CREATE INDEX idx_user_login_status ON user_data (status, last_login);

-- 仅为活跃用户创建部分索引
CREATE INDEX idx_active_users_email ON user_data (email) WHERE status = 'active';

不要仅仅因为今天 idx_scan 为零就删除索引。统计信息会在重启和手动重置后重置，并且某些索引是为罕见但重要的任务而存在的。更安全的审查方式如下：

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_size_pretty(pg_relation_size(indexrelid)) DESC;

如果一个索引很大，在整个业务周期内未被使用，并且不支撑约束，那么它就是删除的候选对象。在繁忙的系统中，使用 DROP INDEX CONCURRENTLY 以便在操作期间不阻塞正常的读写。

陷阱 2：忽视自动清理守护进程

PostgreSQL 使用多版本并发控制（MVCC）。更新和删除操作会留下旧的行版本，直到清理进程清理它们。自动清理不是可选的维护任务；它是正常数据库操作的一部分。它移除死元组，通过自动分析更新规划器统计信息，并防止事务 ID 回卷。

诊断：过度膨胀

忽视自动清理会导致表膨胀，文件系统会持有未使用的空间，从而显著减慢顺序扫描的速度。如果自动清理无法跟上高写入流量，XID 消耗会加速。

常见症状： 高 I/O 等待、表文件不断增长、行估计过时，以及即使活跃行数基本稳定，表大小仍在持续攀升。

有用的初步检查：

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

规避陷阱：调整自动清理

许多团队保留默认设置，然后发现默认值对于一两个高变更率的表来说不够激进。直接调整这些表，而不是让整个集群变得嘈杂。

首先需要理解的设置是：

autovacuum_vacuum_scale_factor：触发清理前必须变更的表比例。大表通常需要较低的值。
autovacuum_vacuum_threshold：添加到比例因子计算中的固定行阈值。
autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit：节流控制。加快自动清理可能会增加 I/O 压力，因此在更改后要监控系统。

在 postgresql.conf 中全局调整这些参数，或者使用存储参数按表调整，确保自动清理运行得足够激进以管理高变更率的表。

ALTER TABLE high_churn_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000
);

陷阱是在出现性能问题时禁用自动清理。如果自动清理持续可见，这通常意味着它正在努力赶上写入变更。应将其视为容量和调整信号，而不是自动清理导致问题的证据。

陷阱 3：`shared_buffers` 和 `work_mem` 的难题

错误配置内存分配是一个常见陷阱，直接影响数据库 I/O 性能。两个参数主导了这一领域：shared_buffers（缓存数据块）和 work_mem（用于会话内排序和哈希操作的内存）。

诊断：高磁盘 I/O 和溢出

如果 shared_buffers 对于工作负载来说太小，PostgreSQL 会更依赖操作系统缓存和存储。如果 work_mem 太小，排序和哈希操作会将临时文件溢出到磁盘。如果全局 work_mem 太大，并发查询的突发可能会耗尽内存。

要检查磁盘溢出，请使用 EXPLAIN ANALYZE。查找指示以下内容的行：

Sort Method: external merge Disk: 1234kB

规避陷阱：战略性内存分配

1. `shared_buffers`

shared_buffers 的一个常见起点是系统 RAM 的 25% 左右，但这并非通用规则。较小的实例、容器内存限制、混合工作负载和托管数据库平台都可能改变正确的值。PostgreSQL 也受益于操作系统页面缓存，因此将所有内存分配给 shared_buffers 通常是一个错误。

2. `work_mem`

此参数是会话特定的。一个常见陷阱是设置较高的全局 work_mem，当乘以数百个并发连接时，会迅速耗尽系统 RAM，导致交换和崩溃。相反，应设置一个保守的全局默认值，并使用 SET work_mem 为运行复杂报告或批处理作业的特定会话增加该值。

# postgresql.conf 示例
shared_buffers = 12GB   # 假设总 RAM 为 48GB
work_mem = 4MB          # 保守的全局默认值

对于报告作业，仅针对该会话或事务进行设置：

BEGIN;
SET LOCAL work_mem = '128MB';
-- 运行报告查询
COMMIT;

请记住，单个查询可能多次使用 work_mem。一个具有多个排序或哈希节点的并行查询可能会使实际使用的内存成倍增加。

陷阱 4：忽视长时间运行的查询和锁

不受约束、编写不佳的查询或应用程序错误可能导致连接持续活跃数小时，消耗资源，更糟糕的是，持有事务锁，从而阻塞其他进程。未能监控和管理这些查询是一个重大的稳定性风险。

诊断：监控活动会话

使用 pg_stat_activity 视图快速识别长时间运行的查询、它们正在执行的特定 SQL 以及当前状态（例如，等待锁、活跃）。

SELECT pid, usename, client_addr, backend_start, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

对于锁等待，包括阻塞 PID：

SELECT a.pid,
       a.usename,
       a.state,
       now() - a.query_start AS age,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY age DESC;

规避陷阱：超时和终止

实施会话和语句超时，以在失控进程造成重大损害之前自动终止它们。

statement_timeout：单个语句在被取消之前可以运行的最长时间。应在全局或按应用程序连接设置。
lock_timeout：语句在放弃尝试之前等待锁的最长时间。

对于立即缓解，你可以使用在 pg_stat_activity 中识别的进程 ID（PID）来终止有问题的进程：

-- 设置全局语句超时为 10 分钟（600000 毫秒）
ALTER SYSTEM SET statement_timeout = '600s';

-- 使用其 PID 终止特定查询
SELECT pg_terminate_backend(12345);

当查询仅仅是昂贵时，首选 pg_cancel_backend(pid)。它会取消当前语句，但保持会话存活。当会话处于事务空闲状态、持有锁或对取消无响应时，使用 pg_terminate_backend(pid)。终止错误的 backend 可能会回滚应用程序仍期望完成的工作，因此在操作之前，请捕获查询、用户、客户端地址和阻塞关系。

陷阱 5：糟糕的 WAL 管理和磁盘容量规划

PostgreSQL 依赖预写日志（WAL）来实现持久性和复制。在大量写入流量期间，WAL 段会迅速累积。一个常见的运维陷阱是未能监控与 WAL 归档相关的磁盘空间使用情况，或在没有充分存储规划的情况下设置激进的 WAL 参数。

诊断：数据库停止

糟糕的 WAL 管理最严重的症状是数据库在保存 pg_wal 的分区上空间耗尽。这通常发生在归档失败、备用服务器宕机或复制槽为不再存在的消费者保留 WAL 时。

规避陷阱：大小规划和归档

1. 控制 WAL 大小

max_wal_size 参数是一个检查点目标，而不是硬磁盘配额。当必须为归档、复制或恢复保留 WAL 时，PostgreSQL 可能会超过它。设置得太低会导致频繁的检查点和额外的 I/O。设置得更高可以平滑检查点压力，但你仍然需要磁盘监控和归档监控。

# postgresql.conf 示例
# 增加以减少高负载下的检查点频率
max_wal_size = 4GB 
min_wal_size = 512MB

2. 归档策略

如果启用了 WAL 归档（archive_mode = on）用于时间点恢复（PITR）或复制，则归档进程必须可靠。如果归档目标（例如，网络存储）变得不可访问，PostgreSQL 将继续持有这些段，最终填满本地磁盘。确保监控到位，以便在 archive_command 失败持续存在时提醒 DBA。

同时检查复制槽：

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

一个不活跃且保留 WAL 不断增长的复制槽是填满主库的最快方式之一。

实用的故障排除顺序

当面临压力时，使用固定的顺序，这样你就不会随机追逐症状：

检查磁盘空间，特别是数据目录、pg_wal 和临时文件位置。
检查 pg_stat_activity 中的活动会话和阻塞者。
使用 EXPLAIN (ANALYZE, BUFFERS) 检查慢查询计划是否确实如你所想。
检查表变更率、死元组和自动清理历史。
检查 WAL 归档、复制延迟和槽保留。
一次只更改一件事，并保留更改前后的证据。

最大的 PostgreSQL 故障排除错误是将每个事件都视为调整问题。有时正确的修复是索引。有时是缺少超时。有时是卡住的复制槽。数据库通常会给你足够的证据；关键在于在调整参数之前阅读这些证据。