Appearance
PostgreSQL 运行时统计信息配置指南
PostgreSQL 提供了丰富的运行时统计信息收集功能，帮助数据库管理员监控和优化数据库性能。本文将详细介绍相关配置参数的作用、使用场景和最佳实践。
📊 统计信息系统概述
PostgreSQL 的统计信息系统分为两个主要部分：
统计信息系统解决的核心问题：
- 性能监控：实时了解数据库运行状态
- 问题诊断：快速定位性能瓶颈和异常
- 容量规划：基于历史数据进行资源规划
- 优化决策：为 SQL 优化和索引设计提供数据支持 :::
🔍 累积查询和索引统计信息
track_activities - 会话活动跟踪
功能说明
track_activities 参数控制是否收集每个会话当前执行命令的信息，这是监控数据库活动的基础。
业务场景应用
场景 1：生产环境故障排查
当生产数据库出现性能问题时，需要快速识别正在执行的慢查询：
sql
-- 启用会话活动跟踪（通常默认启用）
-- 在 postgresql.conf 中设置
-- track_activities = on
-- 查看当前活动的会话和查询
SELECT
pid, -- 进程ID
state, -- 会话状态
query_start, -- 查询开始时间
now() - query_start AS duration, -- 查询执行时长
query -- 当前执行的SQL
FROM pg_stat_activity
WHERE state = 'active' -- 只显示活跃会话
AND query NOT LIKE '%pg_stat_activity%' -- 排除本查询
ORDER BY duration DESC; -- 按执行时间降序排列示例输出：
pid | state | query_start | duration | query
-------+--------+-----------------------------+------------+---------------------------------
12345 | active | 2024-01-15 10:30:25.123456 | 00:02:35.5 | SELECT * FROM large_table WHERE...
12346 | active | 2024-01-15 10:32:45.789012 | 00:00:15.2 | UPDATE user_profile SET last_...分析过程：
- 问题识别：通过
duration字段快速识别长时间运行的查询 - 资源定位：
pid可用于进一步的系统级分析 - SQL 分析：
query字段显示具体的 SQL 语句，便于优化
track_activity_query_size - 查询文本存储大小
配置优化示例
sql
-- 当前配置查看
SHOW track_activity_query_size;
-- 在复杂应用环境中，可能需要增加存储大小
-- 在 postgresql.conf 中设置
-- track_activity_query_size = 4096 # 默认1024字节，增加到4KB注意事项增加 track_activity_query_size 会消耗更多内存，需要根据实际需求平衡：
- 小值（1024 字节）：适用于简单查询场景
- 大值（4096 字节+）：适用于复杂 ORM 生成的长 SQL :::
track_counts - 数据库活动统计
业务价值分析
track_counts 是自动清理（AUTOVACUUM）的基础，对数据库健康运行至关重要：
sql
-- 查看表级别的统计信息
SELECT
schemaname,
tablename,
n_tup_ins, -- 插入行数
n_tup_upd, -- 更新行数
n_tup_del, -- 删除行数
n_live_tup, -- 活跃行数
n_dead_tup, -- 死亡行数
last_vacuum, -- 最后手动清理时间
last_autovacuum -- 最后自动清理时间
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000 -- 关注有较多死亡行的表
ORDER BY n_dead_tup DESC;业务场景：电商订单系统监控
sql
-- 监控订单表的写入活动和清理状态
SELECT
tablename,
n_tup_ins AS daily_orders, -- 今日新订单
n_tup_upd AS order_updates, -- 订单更新次数
n_dead_tup, -- 需清理的死亡行
CASE
WHEN n_dead_tup > n_live_tup * 0.2
THEN '需要手动清理'
ELSE '正常'
END AS cleanup_status
FROM pg_stat_user_tables
WHERE tablename LIKE '%order%';track_io_timing - I/O 性能计时
性能诊断实战
启用 I/O 计时可以精确诊断存储性能问题：
sql
-- 启用I/O计时（需要重启或reload配置）
-- track_io_timing = on
-- 查看数据库级别的I/O性能
SELECT
datname,
blk_read_time / blks_read AS avg_read_time_ms, -- 平均读取时间(毫秒)
blk_write_time / blks_hit AS avg_write_time_ms, -- 平均写入时间(毫秒)
blks_read, -- 读取块数
blks_hit -- 缓存命中块数
FROM pg_stat_database
WHERE blks_read > 0
ORDER BY avg_read_time_ms DESC;EXPLAIN 分析示例：
sql
-- 使用BUFFERS选项查看查询的I/O详情
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';示例输出：
Nested Loop (cost=0.86..1234.56 rows=100 width=68)
(actual time=0.123..45.678 rows=95 loops=1)
Buffers: shared hit=234 read=56 written=12
I/O Timings: read=123.456 write=45.789
-> Index Scan on orders_date_idx (cost=0.43..123.45 rows=100 width=16)
(actual time=0.056..12.345 rows=95 loops=1)
Buffers: shared hit=12 read=3
I/O Timings: read=23.456分析要点：
shared hit：缓存命中，性能最好read：需要从磁盘读取，关注 I/O 时间I/O Timings：具体的读写耗时，帮助定位存储瓶颈
track_functions - 函数性能跟踪
存储过程性能优化
sql
-- 启用函数跟踪
-- track_functions = 'all' # 跟踪所有函数（SQL、C、PL/pgSQL）
-- 创建示例业务函数
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INTEGER)
RETURNS DECIMAL(10,2) AS $$
DECLARE
total DECIMAL(10,2);
BEGIN
-- 计算订单总金额，包含税费和折扣
SELECT
SUM(oi.quantity * oi.unit_price) * (1 + o.tax_rate) * (1 - o.discount_rate)
INTO total
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id = $1;
RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;
-- 调用函数进行测试
SELECT calculate_order_total(12345);
-- 查看函数性能统计
SELECT
funcname,
calls, -- 调用次数
total_time, -- 总执行时间
mean_time, -- 平均执行时间
self_time -- 函数自身时间（不包括调用的其他函数）
FROM pg_stat_user_functions
WHERE funcname = 'calculate_order_total';性能分析表格：
| 函数名 | 调用次数 | 总时间(ms) | 平均时间(ms) | 自身时间(ms) | 性能评级 |
|---|---|---|---|---|---|
| calculate_order_total | 1,000 | 2,500 | 2.5 | 2.1 | 良好 |
| process_payment | 500 | 15,000 | 30.0 | 28.5 | 需优化 |
优化建议当平均执行时间超过 100ms 时，建议：
- 检查函数内的 SQL 查询是否有合适的索引
- 考虑将复杂计算分解为多个简单函数
- 使用 EXPLAIN 分析函数内的查询计划 :::
📈 统计信息监控
compute_query_id - 查询标识符
查询性能追踪
查询标识符为相同的 SQL 语句（即使参数不同）生成唯一 ID，便于性能追踪：
sql
-- 启用查询ID计算
-- compute_query_id = 'on'
-- 安装pg_stat_statements扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 查看查询性能统计（需要pg_stat_statements扩展）
SELECT
query,
calls, -- 执行次数
total_exec_time, -- 总执行时间
mean_exec_time, -- 平均执行时间
rows, -- 返回行数
100.0 * shared_blks_hit / -- 缓存命中率
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;查询性能排行榜示例：
sql
-- Top 5 最耗时的查询类型
WITH query_stats AS (
SELECT
substr(query, 1, 60) || '...' AS query_preview,
calls,
total_exec_time,
mean_exec_time,
rows / NULLIF(calls, 0) AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 10 -- 只看执行次数较多的查询
)
SELECT
ROW_NUMBER() OVER (ORDER BY total_exec_time DESC) AS rank,
query_preview,
calls,
ROUND(total_exec_time::numeric, 2) AS total_time_ms,
ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
ROUND(avg_rows_per_call::numeric, 0) AS avg_rows
FROM query_stats
LIMIT 5;性能日志配置
详细的性能分析设置
sql
-- 在postgresql.conf中配置性能日志
-- log_statement_stats = off # 总体统计（与下面的选项互斥）
-- log_parser_stats = on # 解析器统计
-- log_planner_stats = on # 规划器统计
-- log_executor_stats = on # 执行器统计
-- 示例：分析复杂查询的各个阶段耗时日志输出示例：
LOG: PARSER STATS
DETAIL: ! system usage stats:
! 0.001000 s user, 0.000000 s system, 0.001234 s elapsed
! [0.001234 s user, 0.000000 s system total]
LOG: PLANNER STATS
DETAIL: ! system usage stats:
! 0.005000 s user, 0.001000 s system, 0.006789 s elapsed
! [0.006234 s user, 0.001000 s system total]
LOG: EXECUTOR STATS
DETAIL: ! system usage stats:
! 0.050000 s user, 0.010000 s system, 0.067890 s elapsed
! [0.056234 s user, 0.011000 s system total]🛠️ 最佳实践配置
生产环境推荐配置
sql
-- postgresql.conf 生产环境配置示例
# 基础统计信息（默认启用，保持开启）
track_activities = on
track_counts = on
# 查询文本大小（根据应用复杂度调整）
track_activity_query_size = 2048 # 对于复杂ORM，可设置为4096
# I/O计时（性能影响较小，建议启用）
track_io_timing = on
track_wal_io_timing = on
# 函数跟踪（根据需要启用）
track_functions = 'pl' # 只跟踪存储过程，降低开销
# 查询标识符（配合pg_stat_statements使用）
compute_query_id = 'auto'
# 统计信息一致性（推荐cache模式）
stats_fetch_consistency = 'cache'
# 性能日志（通常在测试环境启用）
log_statement_stats = off # 生产环境建议关闭
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off监控脚本示例
sql
-- 数据库健康检查脚本
CREATE OR REPLACE FUNCTION db_health_check()
RETURNS TABLE (
check_name TEXT,
status TEXT,
value TEXT,
recommendation TEXT
) AS $$
BEGIN
-- 检查长时间运行的查询
RETURN QUERY
SELECT
'Long Running Queries'::TEXT,
CASE WHEN COUNT(*) > 0 THEN 'WARNING' ELSE 'OK' END,
COUNT(*)::TEXT,
CASE WHEN COUNT(*) > 0 THEN 'Review and optimize slow queries' ELSE 'No issues found' END
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes';
-- 检查需要清理的表
RETURN QUERY
SELECT
'Tables Need Vacuum'::TEXT,
CASE WHEN COUNT(*) > 0 THEN 'WARNING' ELSE 'OK' END,
COUNT(*)::TEXT,
CASE WHEN COUNT(*) > 0 THEN 'Schedule vacuum for high dead tuple tables' ELSE 'Table maintenance up to date' END
FROM pg_stat_user_tables
WHERE n_dead_tup > n_live_tup * 0.1;
-- 检查缓存命中率
RETURN QUERY
SELECT
'Cache Hit Ratio'::TEXT,
CASE WHEN hit_ratio < 95 THEN 'WARNING' ELSE 'OK' END,
ROUND(hit_ratio, 2)::TEXT || '%',
CASE WHEN hit_ratio < 95 THEN 'Consider increasing shared_buffers' ELSE 'Cache performance is good' END
FROM (
SELECT
100.0 * sum(blks_hit) / NULLIF(sum(blks_hit + blks_read), 0) AS hit_ratio
FROM pg_stat_database
) AS cache_stats;
END;
$$ LANGUAGE plpgsql;
-- 执行健康检查
SELECT * FROM db_health_check();🔧 故障排查流程
故障排查检查清单 步骤 1：基础信息收集
- 确认统计信息收集是否启用
- 检查当前活跃会话数量
- 查看系统资源使用情况
步骤 2：性能瓶颈定位
- 识别慢查询和长时间运行的事务
- 分析 I/O 性能统计
- 检查锁等待情况
步骤 3：优化措施
- 优化 SQL 查询和索引
- 调整配置参数
- 考虑硬件升级 :::
通过合理配置 PostgreSQL 的运行时统计信息参数，可以建立完善的数据库监控体系，及时发现和解决性能问题，确保数据库系统的稳定高效运行。这些统计信息不仅帮助解决当前问题，更为长期的容量规划和架构优化提供了宝贵的数据支持。