欢迎体验这篇关于 PostgreSQL pg_stat_activity 视图的教学指南！如果你正在管理 PostgreSQL 数据库，并且希望实时掌握数据库的运行状态、排查性能瓶颈或优化查询，pg_stat_activity 是一个不可或缺的工具。这篇文章将以通俗易懂的方式，带你从零开始掌握 pg_stat_activity 的功能、字段含义、监控技巧，以及如何利用它进行性能调优。无论你是数据库新手还是希望提升管理能力的开发者，这篇指南都将为你提供实用的知识和实践方法。

1. 什么是 pg_stat_activity？

pg_stat_activity 是 PostgreSQL 的一个系统视图，提供了数据库中当前活动会话（连接）的实时快照。它记录了每个会话的状态、正在执行的查询、资源使用情况等信息，是监控数据库运行状态和排查问题的重要工具。通过 pg_stat_activity，你可以回答以下问题：

• 当前有哪些用户连接到数据库？
• 哪些查询正在运行，运行了多久？
• 是否有锁冲突或长时间运行的事务？
• 哪些会话可能导致性能问题？

pg_stat_activity 视图适用于以下场景：

• 实时监控：了解数据库的当前负载和活动。
• 问题排查：定位慢查询、锁等待或连接泄漏。
• 性能调优：优化查询或调整配置以提升效率。

在开始之前，确保你：

• 已安装 PostgreSQL（推荐最新稳定版本，如 17.x）。
• 能通过 psql 或 GUI 工具（如 pgAdmin）访问数据库。
• 具备查询系统视图的权限（普通用户可访问部分字段，超级用户可访问全部）。

接下来，我们将从 pg_stat_activity 的字段开始，逐步探索其监控与调优的应用。

2. pg_stat_activity 视图详解

pg_stat_activity 视图包含丰富的字段，涵盖了会话和查询的详细信息。以下是关键字段及其含义：

示例查询： 查看当前所有活动会话：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    pid,
    usename,
    application_name,
    state,
    query,
    date_trunc('second', now() - query_start) AS query_duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_duration DESC;

输出样例：

pid  | usename | application_name | state  | query                                   | query_duration
-----|---------|------------------|--------|-----------------------------------------|----------------
1234 | alice   | psql             | active | SELECT * FROM orders WHERE user_id = 1  | 00:00:10
5678 | bob     | pgAdmin          | active | UPDATE users SET name = 'Bob' WHERE id = 2 | 00:00:05

3. 配置与权限

3.1 确保视图可用

pg_stat_activity 默认随 PostgreSQL 安装，无需额外扩展。普通用户可以访问部分字段（如 pid、query），但某些字段（如 client_addr）需要超级用户权限。

检查权限：

1

SELECT * FROM pg_stat_activity LIMIT 1;

如果返回数据，说明你有访问权限。

3.2 启用详细监控

要获取完整的等待事件信息，确保以下配置启用：

• track_activities：默认启用，控制 pg_stat_activity 的数据收集。
• track_io_timing：启用 I/O 计时，记录块读写时间：

1
2

ALTER SYSTEM SET track_io_timing = on;
SELECT pg_reload_conf();

3.3 查询文本截断问题

如果 query 字段被截断（默认最大长度 1024 字节），增加 track_activity_query_size：

track_activity_query_size = 2048

修改后需重启数据库：

1

sudo systemctl restart postgresql

4. 使用 pg_stat_activity 进行监控

pg_stat_activity 的实时数据非常适合监控数据库的运行状态。以下是常见的监控场景及查询。

4.1 监控当前活动查询

找出正在运行的查询及其持续时间：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    pid,
    usename,
    application_name,
    state,
    query,
    date_trunc('second', now() - query_start) AS query_duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_duration DESC;

用途：

• 识别慢查询（运行时间长的查询）。
• 检查是否有意外的长查询影响性能。

4.2 检测空闲事务

空闲事务（idle in transaction）可能阻止 autovacuum 清理死元组，导致性能下降。查找此类会话：

1
2
3
4
5
6
7
8

SELECT
    pid,
    usename,
    query,
    date_trunc('second', now() - xact_start) AS transaction_duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY transaction_duration DESC;

解决方案：

• 终止长时间空闲的事务：

1
2
3
4

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > '5 minutes';

• 检查应用代码，确保事务及时提交或回滚。

4.3 检查锁等待

锁冲突是性能瓶颈的常见原因。以下查询找出等待锁的会话及其阻塞者：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database = blocking_locks.database
    AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE blocked.wait_event_type = 'Lock'
AND NOT blocked_locks.granted
AND blocking_locks.granted;

解决方案：

• 终止阻塞会话（谨慎操作）：

1

SELECT pg_terminate_backend(1234);

• 优化查询，减少锁冲突（如使用行级锁或优化事务）。

4.4 监控连接数

过多连接可能耗尽数据库资源。检查当前连接数：

1
2
3
4
5
6

SELECT
    state,
    count(*) AS connection_count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

解决方案：

• 调整 max_connections（默认 100）：

max_connections = 200

• 使用连接池（如 PgBouncer）管理连接。

5. 使用 pg_stat_activity 进行性能调优

pg_stat_activity 不仅用于监控，还能指导性能调优。以下是具体应用场景。

5.1 优化慢查询

场景：发现一个查询运行时间超过 10 秒：

1
2
3
4
5
6
7

SELECT
    pid,
    query,
    date_trunc('second', now() - query_start) AS query_duration
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > '10 seconds';

调优步骤：

1. 使用 EXPLAIN ANALYZE 分析查询计划：

1

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

2. 检查是否缺少索引，创建索引：

1

CREATE INDEX idx_orders_user_id ON orders(user_id);

3. 优化查询逻辑（如减少子查询或冗余计算）。

5.2 减少锁冲突

场景：发现多个会话因锁等待而卡住。 调优步骤：

• 缩短事务持续时间，确保快速提交或回滚。
• 使用 SELECT ... FOR UPDATE NOWAIT 避免长时间等待。
• 调整表结构（如分区表）以减少锁竞争。

5.3 管理资源密集型会话

找出消耗 I/O 或 CPU 的会话：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    pid,
    usename,
    query,
    wait_event_type,
    wait_event,
    date_trunc('second', now() - query_start) AS query_duration
FROM pg_stat_activity
WHERE wait_event_type IN ('IO', 'CPU')
AND state = 'active';

调优步骤：

• 增加 work_mem 或 maintenance_work_mem：

1

SET work_mem = '16MB';

• 检查 autovacuum 是否需要优化（见第 6.2 节）。

6. 高级技巧与优化

6.1 自动化监控

将 pg_stat_activity 数据集成到监控系统（如 Prometheus + Grafana）：

• 导出慢查询和锁等待数据：

1
2
3
4
5

SELECT
    count(*) AS active_queries,
    sum(CASE WHEN now() - query_start > '1 second' THEN 1 ELSE 0 END) AS slow_queries
FROM pg_stat_activity
WHERE state = 'active';

• 设置告警，当慢查询数量或锁等待超过阈值时通知。

6.2 结合其他视图

pg_stat_activity 与其他视图结合，提供更全面的分析：

• pg_stat_statements：分析慢查询的统计信息：

1
2
3
4
5
6
7
8

SELECT
    pss.query,
    pss.total_exec_time,
    psa.pid,
    psa.query AS current_query
FROM pg_stat_statements pss
JOIN pg_stat_activity psa ON pss.query = psa.query
WHERE psa.state = 'active';

• pg_stat_all_tables：检查死元组与查询的关系：

1
2
3
4
5
6
7
8

SELECT
    psa.pid,
    psa.query,
    pst.relname,
    pst.n_dead_tup
FROM pg_stat_activity psa
JOIN pg_stat_all_tables pst ON psa.query LIKE '%' || pst.relname || '%'
WHERE psa.state = 'active';

6.3 日志增强

启用慢查询日志，与 pg_stat_activity 结合分析：

log_min_duration_statement = 1000  # 记录执行时间超过 1 秒的查询

查看日志并与 pg_stat_activity 对比，找出问题查询。

6.4 定时清理空闲连接

编写脚本定期终止空闲连接：

1
2
3
4

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - backend_start > '1 hour';

7. 常见问题与解决方案

7.1 查询文本截断

症状：query 字段不完整。 解决方案：

• 增加 track_activity_query_size：

track_activity_query_size = 4096

• 重启数据库。

7.2 看不到等待事件

症状：wait_event_type 或 wait_event 为空。 解决方案：

• 确保 PostgreSQL 版本支持（9.6 及以上）。
• 检查 track_activities 是否启用（默认启用）。

7.3 连接数过多

症状：FATAL: sorry, too many clients already。 解决方案：

• 增加 max_connections 或使用连接池。
• 监控连接数，终止不必要的会话：

1
2
3
4

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND application_name != 'psql';

8. 总结与实践建议

通过本文，你深入了解了 pg_stat_activity 的字段、监控方法和调优技巧。以下是一些实践建议：

• 定期监控：每天检查慢查询和锁等待，优化问题查询。
• 自动化告警：使用监控工具跟踪连接数和空闲事务。
• 结合工具：将 pg_stat_activity 与 pg_stat_statements 和日志分析结合。
• 谨慎终止：终止会话前确认不会影响业务。

动手实践：

1. 查询当前活动会话，找出运行时间超过 5 秒的查询。
2. 检查是否有空闲事务，尝试终止一个长时间空闲的会话。
3. 分析锁等待，优化一个阻塞查询。
4. 配置慢查询日志，与 pg_stat_activity 对比分析。

希望这篇指南能帮助你利用 pg_stat_activity 提升 PostgreSQL 的监控与调优能力！如果有任何问题或需要进一步探讨，欢迎留言交流。