1. 问题背景与核心需求

当PostgreSQL数据库出现性能瓶颈时，DBA最常遇到的场景就是：某些SQL查询突然变慢，甚至被阻塞无法执行。上周我处理的生产环境故障就是个典型案例——业务系统突然出现大面积超时，但监控面板上的CPU、内存指标都显示正常。这种时候，快速定位哪些查询正在执行、哪些被阻塞，就成了解决问题的关键。

PostgreSQL提供了多种系统视图和函数来追踪活动查询，但不同版本之间存在差异，而且官方文档对实际应用场景的说明比较分散。本文将基于PostgreSQL 12+版本，分享一套经过生产验证的查询分析方法，包含以下实用技巧：

• 实时查看所有活动会话和查询
• 识别被阻塞的查询及其阻塞源
• 分析锁等待关系链
• 关键字段的解释和实际应用建议

2. 活动查询监控实战

2.1 基础查询：pg_stat_activity视图

所有活动会话信息都存储在pg_stat_activity系统视图中。这个视图包含20多个字段，但日常诊断只需要关注这几个关键列：

SELECT pid, usename, application_name, client_addr, state, query, query_start, state_change, wait_event_type, wait_event FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start;

字段解读与使用技巧：

• pid：会话进程ID，后续kill操作需要这个值
• state：重点关注active(正在执行)和idle in transaction(事务开启但无操作)
• wait_event_type：显示等待的资源类型，如Lock/IO/LWLock等
• 在生产环境建议添加backend_type = 'client backend'条件过滤系统进程

重要提示：query字段默认只显示前1024字节。要查看完整SQL需要设置track_activity_query_size参数（需重启），或者通过pg_stat_statements扩展获取历史查询。

2.2 高级过滤：识别问题会话

结合多个条件可以快速定位问题会话。以下是几个实用过滤示例：

长时间运行的查询：

SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes' ORDER BY duration DESC;

空闲事务（可能导致锁堆积）：

SELECT pid, now() - state_change AS idle_time, query FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes';

特定客户端的查询：

SELECT * FROM pg_stat_activity WHERE application_name = 'YourAppName' AND state != 'idle';

3. 阻塞查询分析与处理

3.1 锁等待关系查询

PostgreSQL的锁系统比较复杂，但通过以下查询可以可视化阻塞关系：

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

结果解读方法：

1. 从blocked_pid找到被阻塞的会话
2. 对应的blocking_pid就是阻塞源
3. 比较两者的query字段分析阻塞原因

3.2 锁类型分析与处理建议

PostgreSQL有多种锁类型，常见问题通常与这些锁相关：

当识别到阻塞后，可以通过以下命令终止会话（谨慎使用）：

SELECT pg_terminate_backend(pid); -- 强制终止 SELECT pg_cancel_backend(pid); -- 尝试取消查询

4. 系统扩展与高级技巧

4.1 安装pg_stat_statements扩展

这个官方扩展能记录所有查询的历史统计信息，安装方法：

CREATE EXTENSION pg_stat_statements;

然后在postgresql.conf中添加：

shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all

重启后即可使用这些有价值的视图：

-- 最耗时的查询 SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- 最高IO消耗的查询 SELECT query, shared_blks_read, shared_blks_written FROM pg_stat_statements ORDER BY shared_blks_read + shared_blks_written DESC LIMIT 10;

4.2 自动化监控方案

对于需要长期监控的环境，建议配置以下自动化机制：

1. 日志分析：设置log_min_duration_statement记录慢查询
2. Prometheus监控：使用postgres_exporter采集关键指标
3. 自定义报警：针对以下条件设置报警：
   • 存在超过5分钟的阻塞
   • 空闲事务超过10分钟
   • 锁等待队列长度异常

示例监控查询：

-- 阻塞会话计数报警 SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND now() - state_change > interval '1 minute';

5. 常见问题排查指南

5.1 为什么查询显示为"idle in transaction"？

这表示会话处于事务中但没有活动查询。常见原因包括：

• 应用开启了事务但未及时提交/回滚
• 连接池配置不当，连接被返还但事务未结束
• 应用代码异常导致事务未正常关闭

解决方案：

-- 查找所有空闲事务及其持续时间 SELECT pid, now() - xact_start AS duration FROM pg_stat_activity WHERE state = 'idle in transaction';

5.2 如何分析查询突然变慢？

当某个平时很快的查询突然变慢时，按以下步骤排查：

1. 检查是否被阻塞（使用第3节的锁查询）
2. 确认执行计划是否变化：

   EXPLAIN ANALYZE your_query;

3. 检查表统计信息是否过期：

   ANALYZE your_table; -- 更新统计信息

4. 查看是否有并行查询资源竞争

5.3 生产环境注意事项

1. 谨慎使用pg_terminate_backend：强制终止会话可能导致数据不一致
2. 避免频繁查询pg_stat_activity：这些视图查询本身会有性能开销
3. 长事务处理：对于无法终止的长事务，可以先设置锁超时：

   SET LOCAL lock_timeout = '5s'; -- 在新会话中设置

4. 连接池配置：确保连接池正确管理事务状态

我在实际运维中发现，90%的阻塞问题都与应用层的事务管理有关。建议开发团队遵循这些最佳实践：

• 事务尽可���短小
• 避免在事务中进行用户交互
• 为所有事务添加超时控制
• 使用重试机制处理死锁