1. 为什么需要一个趁手的 PostgreSQL 性能分析工具数据库跑得慢业务方催得急登录服务器一看 CPU 飙到 90%这时候你手里如果没有一套趁手的监控手段基本就是盲人摸象。pg_stat_statements、pg_stat_activity、auto_explain这些内置视图和扩展官方给的能力其实已经相当扎实但问题在于它们分散在不同视图里字段多、含义绕真到排查现场一条条 SQL 去拼、去算效率极低。我见过太多团队明明数据库里已经记录了慢查询的完整信息却因为没有一个统一的采集和展示层硬生生把十分钟能定位的问题拖成了半天的救火。所谓PostgreSQL 性能分析插件本质上就是一层“采集 聚合 可视化”的封装。它把数据库自身暴露出来的统计信息等待事件、执行计划、锁、IO、缓冲区命中率等定时抓取下来做二次加工最后以图表或者排行榜的形式呈现。pg 监控工具则是这类插件的统称可以是纯 SQL 脚本、可以是 Python/Go 写的小服务也可以是基于 Prometheus Grafana 的完整链路。它解决的核心问题只有一个让性能问题从“猜”变成“看”。这篇文章适合谁看如果你是会写 SQL 但没系统做过数据库性能排查的后端开发看完能自己搭一套最小可用的监控如果你是 DBA 或者运维能从中拿到一些采集频率、指标取舍、告警阈值的实战经验如果你只是好奇 PostgreSQL 到底能暴露多少信息也能对这套体系有个整体认知。下面我按“设计思路 → 核心指标 → 落地实现 → 排障实录”的顺序把我在几个项目里踩过的坑和总结出来的做法完整讲一遍。2. 整体设计思路与方案选型拆解2.1 先想清楚监控和分析是两件事很多人一上来就把“监控”和“分析”混为一谈结果做出来的东西四不像。我的经验是必须把这两层拆开。监控层关注的是“现在有没有问题”追求的是低开销、高频次、指标稳定。它采集的是聚合后的数字比如每秒事务数、当前活跃连接数、平均查询延迟。这一层的数据量小可以每 5 秒甚至每 1 秒采一次长期保留。分析层关注的是“问题到底出在哪”追求的是信息完整、可下钻。它需要的是单条 SQL 的完整文本、执行计划、等待事件分布。这一层数据量大采集频率要低比如每分钟一次保留时间也要短比如 7 天。把这两层分开设计最大的好处是避免“为了分析而高频采集导致数据库本身被拖垮”这种本末倒置的情况。我见过一个反面案例某团队用 1 秒一次的频率去全量抓pg_stat_activity结果监控进程自己成了数据库最大的 CPU 消耗者这就很尴尬了。2.2 采集方式的三条路线落地的时候采集方式基本就三条路各有取舍。采集方式实现手段优点缺点适用场景纯 SQL 轮询定时任务执行查询结果写本地表零依赖部署简单无历史趋势展示弱临时排查、小库扩展内采集用pg_stat_statements等扩展 自定义函数数据最准开销可控需要装扩展权限要求高生产库长期监控外部 Agent独立进程连库拉取推送到时序库与业务库解耦扩展性强架构复杂需维护组件多实例、大规模我个人的建议是单实例、中小规模优先走“扩展 定时 SQL 落表”这条路成本最低效果也够用。只有当实例数量上到几十个、需要统一大盘的时候才值得上外部 Agent 时序数据库那套。别为了“看起来专业”而过早引入复杂架构维护成本会教你做人。2.3 指标取舍不是越多越好PostgreSQL 暴露的统计字段有几百个全采一遍既没必要也不现实。我的取舍原则是围绕“资源、等待、吞吐、错误”四个维度来选。资源维度看 CPU、内存、IO、连接数等待维度看锁等待、IO 等待、LWLock 等待吞吐维度看 TPS、QPS、缓冲区命中率错误维度看死锁次数、回滚率、连接失败数。这四个维度基本能覆盖 90% 的性能问题定位场景。剩下的细枝末节等真出问题了再针对性去查原始视图不必预先全量采集。提示采集频率和保留周期一定要提前规划。我的经验值是监控类指标保留 30 天、分析类明细保留 7 天磁盘占用和查询效率能取得比较好的平衡。3. 核心指标解析与采集实操要点3.1 pg_stat_statements慢查询分析的主力pg_stat_statements是整套体系里最重要的一个扩展没有之一。它把归一化之后的 SQL把常量替换成占位符聚合起来记录调用次数、总耗时、平均耗时、行数、缓冲区读写等。要启用它先在配置文件里加上# postgresql.conf shared_preload_libraries pg_stat_statements pg_stat_statements.max 10000 pg_stat_statements.track all改完重启数据库再执行CREATE EXTENSION pg_stat_statements;就生效了。这里有几个参数值得说道说道。pg_stat_statements.max决定了能跟踪多少条不同的归一化 SQL默认 5000。如果你的业务 SQL 种类特别多超过这个数之后新的 SQL 就进不来了老的会被淘汰。我一般设成 10000 到 20000内存开销可以接受。pg_stat_statements.track有三个值top只跟踪顶层 SQLall连函数内部执行的 SQL 也跟踪none关闭。排查存储过程性能问题时必须用all但开销也更大按需选择。采集的时候最核心的一条查询长这样SELECT queryid, LEFT(query, 100) AS query_sample, calls, ROUND(total_exec_time::numeric, 2) AS total_ms, ROUND(mean_exec_time::numeric, 2) AS mean_ms, ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct, rows, shared_blks_hit, shared_blks_read FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;这条查询按总耗时排序能直接告诉你“谁最吃时间”。注意total_exec_time和mean_exec_time这两个字段名在 PostgreSQL 13 之前叫total_time和mean_time升级的时候脚本要跟着改这是个很常见的坑。3.2 pg_stat_activity实时会话与等待事件pg_stat_activity是排查“现在正在发生什么”的第一现场。它每一行是一个后端进程记录了当前执行的 SQL、状态、等待事件类型和具体等待事件。SELECT pid, usename, state, wait_event_type, wait_event, NOW() - query_start AS duration, LEFT(query, 80) AS query_sample FROM pg_stat_activity WHERE state idle AND pid pg_backend_pid() ORDER BY duration DESC;这里的关键是wait_event_type和wait_event这一对字段。wait_event_type告诉你进程在等什么大类比如Lock、IO、LWLock、Clientwait_event是具体的事件名。比如你看到大量Locktransactionid基本就是锁等待八成有长事务没提交看到IODataFileRead说明在等磁盘读可能是缓冲区不够或者查询没走索引。注意state idle in transaction是个危险信号。它表示事务开着但没在执行 SQL这种连接会一直持有锁是很多“莫名其妙的锁等待”的元凶。监控里一定要单独统计这类连接的数量。3.3 锁与阻塞链找到真正的源头锁等待最烦人的地方在于你看到的“受害者”往往不是“肇事者”。A 等 BB 等 CC 才是那个没提交事务的罪魁祸首。PostgreSQL 9.6 之后提供了pg_blocking_pids()函数可以直接查出阻塞链。SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, LEFT(query, 60) AS query_sample FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;cardinality(pg_blocking_pids(pid)) 0这个条件筛出来的就是被阻塞的进程blocked_by数组里就是阻塞它的进程号。顺着这个数组一层层往上找就能定位到根因。这个查询我建议直接做成监控面板上的一个固定项出锁问题时一键就能看。3.4 缓冲区命中率与 IO 指标缓冲区命中率反映的是“有多少数据是从内存里拿到的”这个值越低说明磁盘 IO 压力越大。计算方式SELECT SUM(blks_hit) * 100.0 / NULLIF(SUM(blks_hit) SUM(blks_read), 0) AS hit_ratio FROM pg_stat_database;健康的生产库这个值一般要在 99% 以上。如果掉到 95% 以下要么是shared_buffers配小了要么是有大查询在扫全表。注意用NULLIF防止除零这个细节在刚启动、还没有任何读操作的实例上会触发。配合pg_statio_user_tables还能看到具体哪张表的 IO 最重SELECT relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables ORDER BY heap_blks_read idx_blks_read DESC LIMIT 10;这张表能帮你快速锁定“IO 大户”通常优化这几张表的索引就能解决大部分 IO 问题。4. 从零搭建一套可用的监控采集链路4.1 建表把采集结果落下来要让监控有历史趋势必须把采集结果存下来。我一般建三张表一张存快照指标一张存慢查询明细一张存锁等待记录。CREATE TABLE monitor_snapshot ( id BIGSERIAL PRIMARY KEY, collected_at TIMESTAMPTZ DEFAULT NOW(), active_conns INT, idle_in_txn INT, tps NUMERIC(10,2), buffer_hit_ratio NUMERIC(5,2), lock_waits INT, deadlocks BIGINT ); CREATE TABLE monitor_slow_query ( id BIGSERIAL PRIMARY KEY, collected_at TIMESTAMPTZ DEFAULT NOW(), queryid BIGINT, query_sample TEXT, calls BIGINT, total_ms NUMERIC(14,2), mean_ms NUMERIC(14,2), pct NUMERIC(5,2) ); CREATE TABLE monitor_lock_wait ( id BIGSERIAL PRIMARY KEY, collected_at TIMESTAMPTZ DEFAULT NOW(), pid INT, blocked_by INT[], wait_event TEXT, query_sample TEXT );字段类型的选择有讲究。时间统一用TIMESTAMPTZ避免时区问题耗时用NUMERIC而不是FLOAT因为浮点数在累加时会有精度误差做报表统计时对不上账很头疼。4.2 采集脚本一个函数搞定一轮采集把采集逻辑封装成一个 PL/pgSQL 函数调用一次就完成一轮采集这样无论是用pg_cron还是外部定时任务都只需要调这一个函数。CREATE OR REPLACE FUNCTION collect_pg_metrics() RETURNS void AS $$ BEGIN INSERT INTO monitor_snapshot (active_conns, idle_in_txn, tps, buffer_hit_ratio, lock_waits, deadlocks) SELECT (SELECT COUNT(*) FROM pg_stat_activity WHERE state active), (SELECT COUNT(*) FROM pg_stat_activity WHERE state idle in transaction), (SELECT SUM(xact_commit xact_rollback) FROM pg_stat_database), (SELECT ROUND(SUM(blks_hit) * 100.0 / NULLIF(SUM(blks_hit) SUM(blks_read), 0), 2) FROM pg_stat_database), (SELECT COUNT(*) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0), (SELECT SUM(deadlocks) FROM pg_stat_database); INSERT INTO monitor_slow_query (queryid, query_sample, calls, total_ms, mean_ms, pct) SELECT queryid, LEFT(query, 200), calls, ROUND(total_exec_time::numeric, 2), ROUND(mean_exec_time::numeric, 2), ROUND((100 * total_exec_time / NULLIF(SUM(total_exec_time) OVER (), 0))::numeric, 2) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; INSERT INTO monitor_lock_wait (pid, blocked_by, wait_event, query_sample) SELECT pid, pg_blocking_pids(pid), wait_event, LEFT(query, 200) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0; END; $$ LANGUAGE plpgsql;这个函数里有个细节tps我直接存的是累计值真正的“每秒事务数”需要在查询时用相邻两次采样的差值除以时间间隔算出来。存累计值的好处是不会因为采集间隔抖动而失真坏处是查询时要多一步计算。两种做法都行看你的展示层怎么处理。4.3 调度pg_cron 还是外部定时如果数据库本身允许装扩展pg_cron是最省事的选择CREATE EXTENSION pg_cron; SELECT cron.schedule(pg-metrics, */1 * * * *, SELECT collect_pg_metrics(););这表示每分钟执行一次采集。pg_cron的好处是调度和数据库在同一个进程体系里不用额外维护定时任务坏处是它本身也会占用一点资源而且如果采集函数卡住了可能影响后续调度。外部定时任务比如系统的cron或者调度框架则更灵活可以设置超时、重试、失败告警。我的建议是采集频率高1 分钟以内用 pg_cron频率低或者需要复杂调度逻辑用外部任务。4.4 清理别让监控表撑爆磁盘监控数据是只增不减的必须配清理策略。最简单的是在采集函数末尾加一段删除逻辑DELETE FROM monitor_snapshot WHERE collected_at NOW() - INTERVAL 30 days; DELETE FROM monitor_slow_query WHERE collected_at NOW() - INTERVAL 7 days; DELETE FROM monitor_lock_wait WHERE collected_at NOW() - INTERVAL 7 days;如果数据量大直接DELETE会产生大量死元组建议配合分区表使用按天分区直接DROP掉过期分区效率高得多。分区表在 PostgreSQL 10 之后原生支持建起来也不复杂。提示清理操作最好放在采集频率最低的时段执行避免和业务高峰抢 IO。我一般把清理逻辑单独拆出来每天凌晨跑一次而不是每轮采集都删。5. 常见问题与排查技巧实录5.1 采集本身把数据库拖慢了怎么办这是最典型的“监控反噬”。症状是上了监控之后数据库整体负载反而升高了。排查思路是先看采集语句本身的耗时。pg_stat_statements里按query_sample搜你的采集 SQL看它的mean_exec_time。如果单次采集超过 100ms就要优化了。常见的优化点给监控表的时间字段建索引、限制LIMIT的数量、避免在采集里做复杂的JOIN。另一个隐蔽的坑是pg_stat_activity的查询。这个视图在连接数很多的时候查询本身就不便宜。如果实例有几千个连接每秒钟查一次pg_stat_activity全表开销相当可观。解决办法是降低频率或者只查state idle的行。5.2 慢查询排行榜里全是“自己人”有时候你会发现慢查询排行榜前几名全是监控采集自己的 SQL。这是因为采集 SQL 也被pg_stat_statements统计进去了。解决办法有两个一是把采集 SQL 用pg_stat_statements的过滤功能排除掉二是采集时加个标记展示时过滤。PostgreSQL 14 之后可以用pg_stat_statements的queryid排除或者干脆在采集 SQL 里加个特殊注释展示层按注释过滤。我一般用后者简单直接。5.3 时间字段对不上时区和精度问题NOW()返回的是带时区的时间但如果你用LOCALTIMESTAMP或者CURRENT_TIMESTAMP混用很容易出现“采集时间和展示时间差 8 小时”这种问题。统一用NOW()配合TIMESTAMPTZ类型展示层再按用户时区转换这是最稳妥的做法。精度方面NOW()在同一事务里返回的是事务开始时间如果你在一个长事务里多次调用拿到的是同一个值。采集场景下一般没这个问题但如果你在函数里依赖NOW()做时间差计算要留意这一点。5.4 常见问题速查表现象可能原因排查方向解决手段采集后数据库变慢采集 SQL 太重查采集 SQL 的 mean_exec_time降频、加索引、减字段慢查询榜全是监控自己采集 SQL 被统计看 query_sample 内容加注释过滤时间显示差几小时时区类型混用检查字段类型统一 TIMESTAMPTZ锁等待查不到源头阻塞链没展开用 pg_blocking_pids 递归逐层向上找根因监控表磁盘暴涨没配清理策略看表大小增长加清理或分区缓冲区命中率异常低shared_buffers 太小看配置和 IO 指标调大内存或优化查询5.5 几个我踩过的坑第一个坑是pg_stat_statements的queryid不稳定。数据库重启、扩展重载、甚至某些版本升级之后queryid可能会变。所以不要把queryid当作跨时间的唯一标识来做长期趋势分析它只适合在单次会话周期内做关联。长期分析要用归一化后的 SQL 文本做哈希。第二个坑是pg_stat_activity的query字段可能被截断。默认track_activity_query_size是 1024 字节超长的 SQL 会被截断排查时看不到完整语句。如果业务里有大 SQL把这个参数调到 4096 甚至更大重启生效。第三个坑是采集频率和pg_stat_statements的统计周期不匹配。pg_stat_statements里的calls是累计值如果你每分钟采集一次直接存calls得到的是累计调用数不是“这一分钟的调用数”。要做增量分析得用相邻两次的差值。我一开始没注意画出来的曲线是单调递增的完全没法看趋势。6. 展示层与告警的落地建议6.1 用 Grafana 还是自建页面如果团队已经有 Grafana直接接 PostgreSQL 数据源是最快的。把监控表配成数据源写几条 SQL 就能出图。Grafana 的优点是现成、好看、支持告警缺点是对于“下钻分析”这种交互配置起来比较繁琐。自建页面适合有前端资源的团队可以做得更贴合业务。比如点击某个慢查询直接展开它的执行计划、历史趋势、相关表信息。这种体验是通用工具给不了的。我的建议是监控大盘用 Grafana深度分析用自建页面两者互补。6.2 告警阈值怎么定告警阈值最忌讳拍脑袋。我的做法是先采集一到两周看指标的分布取 P95 或 P99 作为基线再往上留 20% 的余量作为告警线。比如缓冲区命中率平时都在 99.5% 以上那告警线可以设在 99%活跃连接数平时峰值 200告警线设在 300。告警要分级。P1 是“现在就有问题”比如死锁激增、连接数打满P2 是“趋势不对”比如命中率持续下降、慢查询数量缓慢上升。P1 立即通知P2 汇总成日报。别把所有告警都设成即时推送否则很快就会被忽略。6.3 一个容易被忽略的指标连接池等待如果你的应用用了连接池数据库层面的监控只能看到“已经建立的连接”看不到“在池子里排队等连接的请求”。这部分等待时间往往才是用户感知到的延迟大头。所以监控要分两层数据库层看连接状态应用层看连接池的等待队列长度和获取连接耗时。两层结合起来才能完整还原一次请求的延迟构成。7. 我在实际项目中的几点体会这套东西我在几个不同规模的库上都落地过最大的感受是监控的价值不在于采集了多少指标而在于出问题时能不能快速给出方向。一个只有 10 个指标但每个都清晰可用的面板胜过一个有 200 个指标但没人看得懂的大盘。另外采集脚本一定要版本化管理。我见过太多团队监控 SQL 是某个人在服务器上直接改的改完没记录过几个月谁也不知道某个指标是怎么算出来的。把采集函数、建表语句、调度配置都放进代码仓库改动用迁移脚本管理这个习惯能省掉后面无数的扯皮。最后分享一个小技巧在采集函数里加一个“采集耗时”的自监控字段记录每轮采集本身花了多少毫秒。这个数字如果突然变大往往意味着数据库整体在变慢是个很好的早期信号。我自己就是靠这个字段提前发现过一次磁盘 IO 性能下降的问题比业务报警早了大半天。