PostgreSQL 主要监控命令 — pg_stat_activity 和 pg_stat_user_tables

不用 pg_stat_activity 和 pg_stat_user_tables 来监控数据库，就像只看体温来判断健康状况一样。你只看大概，根本搞不清楚问题在哪。这两个 PostgreSQL 的关键命令能让你不只是“看着”，还能主动分析你的数据库到底发生了啥。

啥是 pg_stat_activity？

pg_stat_activity 是 PostgreSQL 的系统视图，能显示所有连接到你数据库的信息。它能回答：谁连上数据库了？现在都在跑什么 SQL？哪些连接“卡住”了还在闲着？这是你分析服务器当前活动的利器。

来看看 pg_stat_activity 里主要的字段。datname 是客户端连的数据库名，usename 是连上来的用户名。application_name 是用这个连接的应用名，client_addr 是客户端的 IP 地址。backend_start 是客户端啥时候连上的，state 表示当前连接状态（active、idle、idle in transaction），query 里是正在执行或者最后执行的 SQL。

例子 1：查看所有活跃连接

想看现在有哪些活跃连接，可以跑这个 SQL：

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

注意 query 字段。它显示当前正在跑的 SQL。如果某个 SQL 跑太久，八成有点问题。

例子 2：分析事务状态

有时候连接会“卡”在 idle in transaction 状态。这说明事务开了但没提交，容易导致锁表啥的。

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

怎么解决？ 如果你发现有“卡住”的事务，可以用下面的命令把它干掉：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

有些开发者特别喜欢用这个命令。建议你先跟团队确认下能不能“杀”进程。呃，抱歉，是“断开”连接。

表使用监控：pg_stat_user_tables 视图

pg_stat_activity 主要看连接，pg_stat_user_tables 则能让你了解表的性能。你能知道：表被读写的频率，哪些表最常用，哪里可能有性能瓶颈。

下面这些 pg_stat_user_tables 字段很有用。relname 是表名，seq_scan 是全表扫描次数，idx_scan 是用索引扫描的次数。n_tup_ins 是插入的行数，n_tup_upd 是更新的行数，n_tup_del 是删除的行数。

例子 1：对比索引和全表扫描的使用

如果索引用得很少（idx_scan 接近 0），说明这张表的查询可以优化下。

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

结果示例：

比如你看到 orders 表全表扫描（seq_scan）次数很多，考虑加个索引。想象下 orders 有 3500 次全表扫描但只有 100 次索引扫描，而 employees 只有 50 次全表扫描却有 1000 次索引扫描——这就是明显的优化信号。

例子 2：分析表的操作数量

想知道表里的数据有多“活跃”，可以查下插入、更新和删除的行数：

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

你能发现啥？ 插入（n_tup_ins）和删除（n_tup_del）操作很多的表，可能是数据库里的“热点”。这些表的性能要特别关注。

性能分析实战：组合 pg_stat_activity 和 pg_stat_user_tables 的数据

分析数据库性能时，可以把这两个视图的数据结合起来。先用 pg_stat_activity 找出慢 SQL，再用 pg_stat_user_tables 看这些 SQL 都操作了哪些表。如果慢 SQL 都在 seq_scan 很高的表上，试试优化 SQL 或加索引。

查询示例：

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';