作为DBA，咱们都经历过这样的场景：凌晨三点被报警惊醒，数据库突然响应变慢，业务方连环call催问原因。此时若连pg_stat_activity都没用过，就像被蒙着眼睛在迷宫里找出口。PostgreSQL提供的系统视图和动态统计信息，就是DBA在性能调优战场上的夜视仪和定位雷达。今天我们就手把手探索这些宝藏工具，帮你把被动救火变成主动防御。

一、系统视图：数据库运行的透视镜

1.1 会话监控视图实战

系统视图就像数据库的X光片，把运行状态直接暴露在我们眼前。来看这个经典的会话监控案例：

-- 查询当前活跃会话的技术栈：PostgreSQL 14
SELECT 
    pid AS 进程ID,
    usename AS 用户名,
    application_name AS 应用名称,
    client_addr AS 客户端IP,
    state AS 状态,
    now() - query_start AS 已执行时间,
    query AS SQL片段
FROM pg_stat_activity 
WHERE state = 'active'
ORDER BY 已执行时间 DESC;

执行结果可能显示：

 进程ID | 用户名 | 应用名称  | 客户端IP   | 状态   | 已执行时间    | SQL片段
--------+--------+-----------+------------+--------+---------------+----------------------------------
 19876 | appuser| web-api   | 192.168.1.5| active | 00:05:23      | UPDATE orders SET status=$1 WHERE...
 20543 | batch  | data-sync | 10.0.0.12  | active | 00:12:17      | VACUUM ANALYZE large_table

关键字段解析：

  • 状态：active表示正在执行，idle in transaction是长事务
  • 已执行时间：快速定位慢查询
  • SQL片段：展示前100字符，方便快速识别问题语句

1.2 锁监控的攻防战

锁等待是生产环境的高频问题，这时需要pg_locks和pg_stat_activity的联合作战：

-- 阻塞链分析技术栈：PostgreSQL 14
SELECT 
    blocked.pid AS 被阻塞进程,
    blocked.query AS 被阻塞SQL,
    blocking.pid AS 阻塞进程,
    blocking.query AS 阻塞SQL,
    age(now(), blocking.query_start) AS 阻塞持续时间
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked ON blocked_locks.pid = blocked.pid
JOIN 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_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

典型结果：

被阻塞进程 | 被阻塞SQL           | 阻塞进程 | 阻塞SQL         | 阻塞持续时间
----------+-----------------------+----------+-----------------+--------------
 22456    | UPDATE accounts SET.. | 23109    | SELECT * FROM...| 00:03:45

这个查询清晰展示了锁等待的上下游关系，结合pg_terminate_backend能快速解除阻塞。

二、动态统计信息：数据库的体检报告

2.1 表级统计的微观世界

pg_stat_user_tables记录着每张表的访问密码，来看这个索引优化的经典案例：

-- 索引使用分析技术栈：PostgreSQL 14
SELECT 
    schemaname AS 模式名,
    relname AS 表名,
    seq_scan AS 全表扫描次数,
    idx_scan AS 索引扫描次数,
    n_live_tup AS 有效元组数,
    n_dead_tup AS 失效元组数,
    round(100 * idx_scan / (seq_scan + idx_scan + 1),2) AS 索引使用率
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 0
ORDER BY 索引使用率 ASC;

结果可能显示：

模式名 | 表名       | 全表扫描 | 索引扫描 | 有效元组 | 失效元组 | 索引使用率
-------+------------+----------+----------+----------+----------+-----------
 public| order_items| 1423     | 8        | 1,234,567| 45,678   | 0.56

解读技巧：

  • 索引使用率<5%的表需要检查索引有效性
  • 高dead tuple提示需要更频繁的autovacuum
  • 全表扫描次数与表大小的比值高说明缺少合适索引

2.2 后台进程的秘密日志

pg_stat_bgwriter是数据库IO的健康晴雨表：

-- 后台写入统计技术栈：PostgreSQL 14
SELECT 
    checkpoints_timed AS 计划检查点,
    checkpoints_req AS 强制检查点,
    buffers_checkpoint AS 检查点写入量,
    buffers_clean AS 后台清理,
    buffers_backend AS 后端写入,
    maxwritten_clean AS 最大写入批次
FROM pg_stat_bgwriter;

关键指标解读：

  • 强制检查点超过总检查点10%：说明checkpoint_segments设置过小
  • buffers_backend占比高：说明shared_buffers可能不足
  • maxwritten_clean数值激增：考虑调整bgwriter_lru_maxpages

三、关联技术的组合拳

3.1 扩展统计的核武器

pg_stat_statements模块是SQL优化的显微镜：

-- 查询TOP10慢SQL技术栈：PostgreSQL 14+pg_stat_statements
SELECT 
    queryid,
    query AS SQL语句,
    calls AS 执行次数,
    total_exec_time AS 总耗时,
    mean_exec_time AS 平均耗时,
    rows AS 返回行数
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

样例输出：

queryid | SQL语句                     | 执行次数 | 总耗时 | 平均耗时 | 返回行数
--------+------------------------------+----------+--------+----------+----------
 329a1b | UPDATE products SET stock=$1| 14234    | 325.6s | 22.8ms   | 0

配合EXPLAIN ANALYZE能精准定位执行计划问题，同时要注意定期重置统计信息避免数据稀释。

四、应用场景与实战兵法

4.1 经典故障排查流程

  1. 突发性能下降：通过pg_stat_activity定位慢SQL，用pg_locks检查锁冲突
  2. 磁盘IO激增：查看pg_stat_bgwriter的检查点频率，分析pg_stat_database的临时文件
  3. 内存溢出告警：结合pg_stat_user_tables的索引使用率调整缓存策略

4.2 技术优劣分析

优势矩阵：

  • 零成本：无需安装额外工具
  • 实时性：统计信息秒级刷新
  • 可追溯：长期监控数据可存储分析

局限点：

  • 统计重置：重启后部分计数清零
  • 存储限制：历史数据需要自行归档
  • 维度局限：缺乏跨节点的全局视角

五、注意事项与踩坑指南

  1. 数据保鲜度：统计信息非实时更新，紧急时刻查询后等待1秒再验证
  2. 权限控制：监控视图需要授予特定权限，禁止滥用超级用户
  3. 采样误差：高并发时的瞬态数据可能具有欺骗性
  4. 版本差异：v13新增的wal使用统计，旧版本需采用替代方案

六、总结与展望

系统视图如同数据库的神经系统，动态统计则是流动的血液。掌握这些工具后，DBA的视角将从"盲人摸象"升级为"CT扫描"。随着PG版本演进，统计信息会愈发精细，但核心思路不变——通过数据驱动决策，让每个优化动作都有迹可循。