PostgreSQL 内置统计视图

PostgreSQL提供了丰富的内置统计视图，用于监控数据库的运行状态和性能。这些视图存储在pg_catalog schema中，提供了关于数据库活动、资源使用和性能指标的详细信息。本文将介绍常用的内置统计视图及其使用方法。

统计收集配置

启用统计收集

确保在postgresql.conf中启用了统计收集：

# 启用基本统计收集
stats_collection_target = 100
# 启用详细的查询统计
track_activities = on
track_counts = on
track_io_timing = on  # 收集I/O计时信息
track_functions = all  # 收集函数执行统计

查看统计收集状态

sql

-- 查看统计收集参数
SHOW track_activities;
SHOW track_counts;
SHOW track_io_timing;

常用统计视图

连接与会话统计

pg_stat_activity

显示当前所有连接的详细信息：

sql

-- 查看所有活动连接
SELECT * FROM pg_stat_activity;
-- 查看活跃连接数
SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active';
-- 查看每个用户的连接数
SELECT usename, COUNT(*) FROM pg_stat_activity GROUP BY usename;
-- 查看长时间运行的查询（超过5分钟）
SELECT pid, usename, query_start, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

pg_stat_replication

显示复制连接的状态：

sql

-- 查看所有复制连接
SELECT * FROM pg_stat_replication;
-- 查看复制延迟
SELECT client_addr, application_name, state, sync_state, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag,
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag,
       pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag,
       pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;

数据库与表统计

pg_stat_database

显示每个数据库的统计信息：

sql

-- 查看数据库统计
SELECT datname, numbackends, xact_commit, xact_rollback, 
       blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, 
       tup_updated, tup_deleted 
FROM pg_stat_database;
-- 计算缓存命中率
SELECT datname, 
       blks_hit * 100.0 / (blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database;

pg_stat_user_tables

显示用户表的统计信息：

sql

-- 查看表统计
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
       n_live_tup, n_dead_tup, last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- 查看需要vacuum的表
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / (n_live_tup + 1), 2) AS dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000 OR round(n_dead_tup * 100.0 / (n_live_tup + 1), 2) > 5
ORDER BY dead_tup_ratio DESC;

pg_stat_user_indexes

显示用户索引的使用统计：

sql

-- 查看索引使用情况
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- 查看未使用的索引
SELECT schemaname, relname, indexrelname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

索引与约束统计

pg_stat_all_indexes

显示所有索引（包括系统索引）的统计信息：

sql

-- 查看所有索引使用情况
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_all_indexes
ORDER BY idx_scan DESC;

pg_constraint_stats

显示约束的使用统计：

sql

-- 查看约束使用情况
SELECT conname, conrelid::regclass, connamespace::regnamespace,
       n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
FROM pg_constraint_stats;

查询与执行统计

pg_stat_statements

这是一个扩展模块，提供了查询执行的详细统计。需要先启用该扩展：

sql

-- 安装扩展
CREATE EXTENSION pg_stat_statements;

配置postgresql.conf：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

使用示例：

sql

-- 查看最耗时的查询
SELECT queryid, query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- 查看执行次数最多的查询
SELECT queryid, query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

I/O 统计

pg_statio_user_tables

显示表的I/O统计：

sql

-- 查看表的I/O统计
SELECT schemaname, relname, 
       heap_blks_read, heap_blks_hit, 
       idx_blks_read, idx_blks_hit,
       toast_blks_read, toast_blks_hit,
       tidx_blks_read, tidx_blks_hit
FROM pg_statio_user_tables
ORDER BY (heap_blks_read + idx_blks_read) DESC;
-- 计算表的缓存命中率
SELECT schemaname, relname,
       round(heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read), 2) AS heap_cache_hit,
       round(idx_blks_hit * 100.0 / (idx_blks_hit + idx_blks_read), 2) AS idx_cache_hit
FROM pg_statio_user_tables;

pg_statio_user_indexes

显示索引的I/O统计：

sql

-- 查看索引的I/O统计
SELECT schemaname, relname, indexrelname, 
       idx_blks_read, idx_blks_hit
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC;

函数与触发器统计

pg_stat_user_functions

显示用户定义函数的执行统计：

sql

-- 查看函数执行统计
SELECT schemaname, funcname, calls, total_time, mean_time, self_time, mean_self_time
FROM pg_stat_user_functions
ORDER BY total_time DESC;

pg_stat_user_triggers

显示用户定义触发器的执行统计：

sql

-- 查看触发器执行统计
SELECT schemaname, relname, trigger_name, calls, total_time, mean_time
FROM pg_stat_user_triggers;

WAL 与 checkpoint 统计

pg_stat_wal

显示WAL生成和写入统计：

sql

-- 查看WAL统计
SELECT wal_records, wal_fpi, wal_bytes,
       stats_reset
FROM pg_stat_wal;

pg_stat_bgwriter

显示后台写入器和checkpoint的统计：

sql

-- 查看后台写入器和checkpoint统计
SELECT checkpoints_timed, checkpoints_req, 
       checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, buffers_backend, 
       buffers_backend_fsync, buffers_alloc,
       stats_reset
FROM pg_stat_bgwriter;
-- 计算checkpoint相关指标
SELECT 
    checkpoints_timed,
    checkpoints_req,
    (checkpoints_req * 100.0 / (checkpoints_timed + checkpoints_req)) AS checkpoint_req_ratio,
    checkpoint_write_time / (checkpoints_timed + checkpoints_req) AS avg_write_time,
    checkpoint_sync_time / (checkpoints_timed + checkpoints_req) AS avg_sync_time
FROM pg_stat_bgwriter;

统计信息的重置

重置所有统计信息

sql

-- 重置所有统计信息
SELECT pg_stat_reset();

重置特定统计信息

sql

-- 重置数据库统计
SELECT pg_stat_reset_single_table_counters('table_oid');
-- 重置pg_stat_statements统计
SELECT pg_stat_statements_reset();
-- 重置后台写入器统计
SELECT pg_stat_reset_shared('bgwriter');

统计信息的使用场景

性能监控

sql

-- 实时监控连接数
SELECT COUNT(*) AS total_connections, 
       COUNT(*) FILTER (WHERE state = 'active') AS active_connections,
       COUNT(*) FILTER (WHERE state = 'idle') AS idle_connections
FROM pg_stat_activity;
-- 监控缓存命中率
SELECT 
    'index' AS cache_type,
    round(sum(idx_blks_hit) * 100.0 / sum(idx_blks_hit + idx_blks_read), 2) AS cache_hit_ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT 
    'table' AS cache_type,
    round(sum(heap_blks_hit) * 100.0 / sum(heap_blks_hit + heap_blks_read), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

问题诊断

sql

-- 查找锁等待
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;

优化建议

sql

-- 识别需要 vacuum 的表
SELECT 
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / (n_live_tup + 1), 2) AS dead_tup_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000 OR round(n_dead_tup * 100.0 / (n_live_tup + 1), 2) > 10
ORDER BY dead_tup_ratio DESC;
-- 识别未使用的索引
SELECT 
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, relname;

最佳实践

定期收集和分析统计信息

配置合适的统计收集参数
定期查看关键统计指标
建立基线，监控异常变化

结合其他监控工具

将内置统计视图与Prometheus、Grafana等工具结合使用
建立监控仪表盘，可视化关键指标
设置告警阈值，及时发现问题

注意统计信息的准确性

统计信息是采样收集的，可能存在一定误差
对于关键查询，可以使用EXPLAIN ANALYZE获取更准确的执行计划
定期重置统计信息，避免统计信息过期

保护统计信息的访问

限制对统计视图的访问权限
敏感统计信息（如查询内容）可能包含敏感数据
考虑使用只读角色访问统计信息

常见问题与解决方案

统计信息不准确

问题：统计信息与实际情况不符 解决方案：

手动收集统计信息：ANALYZE table_name;
增加统计收集目标：ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 1000;
重启数据库，重新加载统计信息

统计视图查询缓慢

问题：查询统计视图时性能较差 解决方案：

限制返回的列数和行数
使用合适的WHERE条件过滤
避免在高并发场景下频繁查询统计视图

缺少某些统计信息

问题：某些统计视图不存在或为空 解决方案：

检查是否启用了相应的统计收集参数
确保扩展已正确安装（如pg_stat_statements）
等待统计信息收集完成

结论

PostgreSQL的内置统计视图提供了丰富的数据库运行状态信息，是数据库监控和性能优化的重要工具。通过合理使用这些视图，可以实时监控数据库的运行状态，诊断性能问题，优化查询和资源使用。

在实际生产环境中，建议：

结合监控工具，建立可视化的监控仪表盘
定期分析统计信息，识别潜在问题
根据统计信息调整数据库配置和优化查询
建立告警机制，及时响应异常情况

通过充分利用PostgreSQL的内置统计视图，可以提高数据库的性能和可靠性，确保数据库系统的稳定运行。

PostgreSQL 内置统计视图 ​

统计收集配置 ​

启用统计收集 ​

查看统计收集状态 ​

常用统计视图 ​

连接与会话统计 ​

pg_stat_activity ​

pg_stat_replication ​

数据库与表统计 ​

pg_stat_database ​

pg_stat_user_tables ​

pg_stat_user_indexes ​

索引与约束统计 ​

pg_stat_all_indexes ​

pg_constraint_stats ​

查询与执行统计 ​

pg_stat_statements ​

I/O 统计 ​

pg_statio_user_tables ​

pg_statio_user_indexes ​

函数与触发器统计 ​

pg_stat_user_functions ​

pg_stat_user_triggers ​

WAL 与 checkpoint 统计 ​

pg_stat_wal ​

pg_stat_bgwriter ​

统计信息的重置 ​

重置所有统计信息 ​

重置特定统计信息 ​

统计信息的使用场景 ​

性能监控 ​

问题诊断 ​

优化建议 ​

最佳实践 ​

定期收集和分析统计信息 ​

结合其他监控工具 ​

注意统计信息的准确性 ​

保护统计信息的访问 ​

常见问题与解决方案 ​

统计信息不准确 ​

统计视图查询缓慢 ​

缺少某些统计信息 ​

结论 ​