云数据库 PostgreSQL 性能监控插件 pg_stat_monitor

一、概述

pg_stat_monitor 是一个 PostgreSQL 查询性能监控插件。该插件基于 pg_stat_statements 的统计思路扩展实现，提供按时间窗口聚合的 SQL 执行统计、计划统计、错误信息、访问对象、客户端信息、直方图分布等观测能力，便于定位慢 SQL、分析负载变化和排查性能问题。

二、支持版本

v17.10_r1.19、v18.4_r1.10及以上版本。

三、使用限制

·pg_stat_monitor 需要通过 shared_preload_libraries 在数据库启动阶段加载。未预加载时可以创建扩展对象，但核心统计功能不会生效，调用统计函数时可能提示插件未通过 shared_preload_libraries 加载。

·修改 pg_stat_monitor.pgsm_max、pg_stat_monitor.pgsm_query_max_len、pg_stat_monitor.pgsm_max_buckets、pg_stat_monitor.pgsm_bucket_time、pg_stat_monitor.pgsm_histogram_*、pg_stat_monitor.pgsm_query_shared_buffer、pg_stat_monitor.pgsm_enable_overflow 等 postmaster 级参数后，需要重启实例生效。

·插件会在全局执行链路中注册解析、计划、执行、Utility、错误日志等 Hook。建议在生产环境开启前进行压测，评估 CPU、内存和共享内存开销。

·pg_stat_monitor 视图默认授予 PUBLIC 查询权限，但跨用户查询文本、执行计划和客户端 IP 会按权限进行遮蔽。对于安全要求较高的实例，建议收敛视图权限，仅授权给 DBA 或监控账号。

·插件当前代码声明运行依赖 PostgreSQL 14 及以上版本。当前 C 代码版本号为2.4.0，可通过 pg_stat_monitor_version() 查询。

·如同时启用 pg_stat_statements，建议在 shared_preload_libraries 中同时配置两个插件，并关注两者对 Utility 语句统计 Hook 的影响。

五、配置预加载参数

检查 shared_preload_libraries 是否已包含 pg_stat_monitor：

SHOW shared_preload_libraries;

六、创建扩展

重启完成后，在需要查看统计信息的数据库中创建扩展：

CREATE EXTENSION pg_stat_monitor;

检查插件是否创建成功：

SELECT extname, extversionFROM pg_extensionWHERE extname = 'pg_stat_monitor';

查看插件运行版本：

SELECT pg_stat_monitor_version();

注意：说明：

七、参数说明

你可以通过 pg_settings 查看插件参数：

SELECT name, setting, unit, context, vartype, source, min_val, max_val, enumvalsFROM pg_settingsWHERE name LIKE 'pg_stat_monitor.%'ORDER BY name;

八、共享内存和时间窗口参数

注意：说明：

九、采集行为参数

pgsm_track 参数取值说明：

示例：

SET pg_stat_monitor.pgsm_track = 'all';SET pg_stat_monitor.pgsm_normalized_query = on;SET pg_stat_monitor.pgsm_track_planning = on;

十、直方图参数

注意：说明：

十一、查看 SQL 统计

插件创建后，你可以通过 pg_stat_monitor 视图查看采集到的 SQL 统计信息。

十二、查看最近 SQL

SELECT bucket, bucket_start_time, datname, username, application_name, queryid, pgsm_query_id, calls, rows, mean_exec_time, queryFROM pg_stat_monitorORDER BY bucket_start_time DESC, calls DESCLIMIT 20;

十三、查看耗时最高的 SQL

SELECT datname, username, calls, total_exec_time, mean_exec_time, max_exec_time, rows, queryFROM pg_stat_monitorORDER BY total_exec_time DESCLIMIT 20;

十四、查看平均耗时最高的 SQL

SELECT datname, username, calls, mean_exec_time, min_exec_time, max_exec_time, stddev_exec_time, queryFROM pg_stat_monitorWHERE calls > 0ORDER BY mean_exec_time DESCLIMIT 20;

十五、查看指定对象相关 SQL

relations 字段保存语句访问的表或视图名称，可用于根据对象定位相关 SQL：

SELECT datname, username, calls, total_exec_time, relations, queryFROM pg_stat_monitorWHERE relations @> ARRAY['public.orders']ORDER BY total_exec_time DESC;

如果对象名中未带 schema，可先直接展开查看：

SELECT query, relationsFROM pg_stat_monitorWHERE relations IS NOT NULLORDER BY query;

十六、查看错误 SQL

插件会通过错误日志 Hook 记录 SQL 执行过程中的错误级别、SQLSTATE 和错误消息：

SELECT datname, username, query, decode_error_level(elevel) AS error_level, sqlcode, message, callsFROM pg_stat_monitorWHERE elevel IS NOT NULL AND elevel <> 0ORDER BY bucket_start_time DESC;

十七、查看 SQL 执行计划

默认不采集执行计划。如需临时排查，可在会话中开启：

SET pg_stat_monitor.pgsm_enable_query_plan = on;SELECT * FROM your_table WHERE id = 1;SELECT query, planid, query_planFROM pg_stat_monitorWHERE query_plan IS NOT NULLORDER BY bucket_start_time DESCLIMIT 10;

注意：说明：

十八、查看 SQL 注释标签

默认不提取 SQL 注释。如需基于注释传入业务标签，可开启：

SET pg_stat_monitor.pgsm_extract_comments = on;SELECT 1 /* { "application": "order-service", "trace_id": "demo" } */;SELECT query, commentsFROM pg_stat_monitorWHERE comments IS NOT NULLORDER BY bucket_start_time DESC;

注意：说明：

注意：SQL 注释可能包含业务标识或敏感信息，生产环境开启前请评估视图访问权限。

十九、查看响应时间直方图

查看直方图边界：

SELECT range();

查看某条 SQL 在指定 bucket 下的响应时间分布：

SELECT bucket, queryid, queryFROM pg_stat_monitorORDER BY calls DESCLIMIT 1;

将上一步得到的 bucket 和 queryid 传入 histogram：

SELECT *FROM histogram(<bucket>, <queryid>) AS h(range text, freq int, bar text);

返回字段说明：

20、视图字段说明

pg_stat_monitor 视图会根据 PostgreSQL 版本创建对应字段。当前代码中的最新视图主要包含以下字段类别。

21、基础维度

22、SQL 标识和文本

23、执行统计

24、计划统计

25、IO、WAL、JIT 和并行统计

26、错误信息

你可以使用 decode_error_level(elevel) 将错误级别转换为可读文本。

27、函数说明

注意：说明：

29、按时间窗口观察 SQL 负载

pg_stat_monitor 与 pg_stat_statements 的主要差异之一是引入 bucket。每个 bucket 代表一个时间窗口，可用于观察短时间内的负载变化。

SELECT bucket, bucket_start_time, count(*) AS sql_count, sum(calls) AS total_calls, sum(total_exec_time) AS total_exec_timeFROM pg_stat_monitorGROUP BY bucket, bucket_start_timeORDER BY bucket_start_time DESC;

30、开启规范化 SQL

开启后，常量会被替换为占位符，便于聚合相同形态 SQL：

SET pg_stat_monitor.pgsm_normalized_query = on;SELECT * FROM orders WHERE id = 1;SELECT * FROM orders WHERE id = 2;SELECT query, callsFROM pg_stat_monitorWHERE query LIKE '%orders%'ORDER BY calls DESC;

31、观察嵌套 SQL

默认 pgsm_track = 'top' 只跟踪顶层语句。如果需要观察函数或 DO 块内部 SQL，可设置为 all：

SET pg_stat_monitor.pgsm_track = 'all';

排查结束后建议恢复为 top：

SET pg_stat_monitor.pgsm_track = 'top';

32、查看计划阶段耗时

默认不采集计划阶段统计。需要排查优化器耗时时可开启：

SET pg_stat_monitor.pgsm_track_planning = on;SELECT query, plans, total_plan_time, mean_plan_time, calls, total_exec_time, mean_exec_timeFROM pg_stat_monitorORDER BY total_plan_time DESCLIMIT 20;

33、清空统计信息

SELECT pg_stat_monitor_reset();

注意：说明：

注意：该函数会清空全局统计信息，默认未授予 PUBLIC。建议仅允许 DBA 或监控管理账号使用。

34、权限和安全建议

插件安装脚本中默认：

GRANT SELECT ON pg_stat_monitor TO PUBLIC;GRANT EXECUTE ON FUNCTION pg_stat_monitor_internal TO PUBLIC;REVOKE ALL ON FUNCTION pg_stat_monitor_reset FROM PUBLIC;

代码中对部分敏感字段做了权限控制：

·当前用户可以查看自己的 query、query_plan 和 client_ip。

·超级用户或 pg_read_all_stats 成员可以查看全部用户的 query、query_plan 和 client_ip。

·其他用户查看非本人记录时，query 和 query_plan 显示为 <insufficient privilege>，client_ip 显示为空。

生产环境建议：

REVOKE SELECT ON pg_stat_monitor FROM PUBLIC;REVOKE EXECUTE ON FUNCTION pg_stat_monitor_internal(boolean) FROM PUBLIC;GRANT SELECT ON pg_stat_monitor TO pg_read_all_stats;

注意：说明：

35、推荐配置

通用生产观测配置示例：

pg_stat_monitor.pgsm_max = 256MBpg_stat_monitor.pgsm_query_shared_buffer = 20MBpg_stat_monitor.pgsm_query_max_len = 2048pg_stat_monitor.pgsm_max_buckets = 10pg_stat_monitor.pgsm_bucket_time = 60pg_stat_monitor.pgsm_track = 'top'pg_stat_monitor.pgsm_track_utility = onpg_stat_monitor.pgsm_track_planning = offpg_stat_monitor.pgsm_normalized_query = onpg_stat_monitor.pgsm_enable_query_plan = offpg_stat_monitor.pgsm_extract_comments = off

高安全要求场景建议：

pg_stat_monitor.pgsm_enable_overflow = offpg_stat_monitor.pgsm_enable_query_plan = offpg_stat_monitor.pgsm_extract_comments = offpg_stat_monitor.pgsm_track = 'top'

诊断慢 SQL 时可在会话级临时开启：

SET pg_stat_monitor.pgsm_track_planning = on;SET pg_stat_monitor.pgsm_enable_query_plan = on;SET pg_stat_monitor.pgsm_track = 'all';

36、卸载插件

在数据库中删除扩展：

DROP EXTENSION pg_stat_monitor;

38、为什么创建扩展后没有统计数据？

请确认插件已通过 shared_preload_libraries 预加载，并且数据库实例已重启：

SHOW shared_preload_libraries;

如未包含 pg_stat_monitor，请配置后重启实例。

39、为什么看不到其他用户的 SQL 文本？

这是权限控制行为。非当前用户、非超级用户、非 pg_read_all_stats 成员查询其他用户记录时，query 和 query_plan 会显示为 <insufficient privilege>。

如监控账号需要查看全部 SQL，可授予：

GRANT pg_read_all_stats TO monitor_user;

40、为什么修改部分参数后不生效？

context = postmaster 的参数只能在启动时生效，修改后需要重启数据库。可通过如下命令查看参数级别：

SELECT name, context, pending_restartFROM pg_settingsWHERE name LIKE 'pg_stat_monitor.%'ORDER BY name;

41、为什么 query_plan 为空？

query_plan 默认不采集。需要参考如下命令开启：

SET pg_stat_monitor.pgsm_enable_query_plan = on;

同时，只有 SELECT 类查询才会采集执行计划。

42、为什么 comments 为空？

comments 默认不采集。需要参考如下命令开启：

SET pg_stat_monitor.pgsm_extract_comments = on;

43、为什么 bucket 中的统计会变化？

pg_stat_monitor 按时间窗口聚合统计。当前 bucket 仍在写入，bucket_done = false；历史 bucket 已结束，bucket_done = true。当 bucket 数超过 pgsm_max_buckets 后，旧 bucket 会被复用。