企业官网建设流程全解析

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 落表”这条路，成本最低，效果也够用。只有当实例数量上到几十个、需要统一大盘的时候，才值得上外部 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只跟踪顶层 SQL，all连函数内部执行的 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、Client；wait_event是具体的事件名。比如你看到大量Lock+transactionid，基本就是锁等待，八成有长事务没提交；看到IO+DataFileRead，说明在等磁盘读，可能是缓冲区不够或者查询没走索引。

注意：state = 'idle in transaction'是个危险信号。它表示事务开着但没在执行 SQL，这种连接会一直持有锁，是很多“莫名其妙的锁等待”的元凶。监控里一定要单独统计这类连接的数量。

3.3 锁与阻塞链：找到真正的源头

锁等待最烦人的地方在于，你看到的“受害者”往往不是“肇事者”。A 等 B，B 等 C，C 才是那个没提交事务的罪魁祸首。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 常见问题速查表

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 性能下降的问题，比业务报警早了大半天。