PostgreSQL事务提交率：从统计口径到监控告警实战

刚接手一套 PostgreSQL 生产环境的时候，大多数人习惯先看 CPU、内存、连接数，再看慢查询。其实还有一个被低估的指标值得你每天都扫一眼：事务提交率（commit ratio）。它的数据来源很简单，就是pg_stat_database视图里xact_commit和xact_rollback两个计数器，但这两个数字组合出来的比例，能很直观地告诉你应用层是不是在大量制造失败事务。

这个指标能帮你回答几个很实际的问题：业务代码里的异常到底多不多？死锁、唯一键冲突、语句超时是不是在悄悄拖垮系统？连接池和 ORM 有没有在“假回滚”？对于 DBA、后端开发、架构师，以及那些一个人扛着数据库运维的兼职运维来说，学会正确读取和计算 commit ratio，等于多了一双能提前发现问题的眼睛。这篇文章我会把统计口径、正确查询方式、根因定位思路和监控告警建议一次性讲透，并把我实际踩过的坑一并交代清楚。

1. 事务提交率到底是什么，为什么值得天天看

1.1 先弄懂 xact_commit 与 xact_rollback 的源头

pg_stat_database是 PostgreSQL 自带的数据库级统计视图，每一行代表一个数据库。其中xact_commit表示自统计重置以来成功提交的事务总数，xact_rollback表示回滚的事务总数。这两个计数器的更新时间点很关键：事务结束时才更新，而不是事务开始或执行中间。

注意这里的事务是 PostgreSQL 内部的顶层事务概念。一条简单的INSERT在自动提交模式下也算一个事务，它成功就是一次xact_commit；执行中报错被回滚，就是一次xact_rollback。你显式写BEGIN; ... COMMIT;包住的多条语句，整体只算一个事务。反过来，如果这个事务块中途失败并且整体回滚，xact_rollback只加 1，而不是按里面失败的语句条数累加。这就是为什么回滚次数永远小于等于出错语句数量，排查时不能简单用回滚数去猜失败 SQL 有多密集。

还有一个容易忽略的细节：SAVEPOINT和 PL/pgSQL 里的EXCEPTION子块会把失败隔离在子事务里。子事务回滚后，只要顶层事务最终提交，xact_rollback不会增加。这个行为背后有很实际的优化含义：代码里多用保存点或异常子块，能把“局部失败”从“整体回滚”中拯救出来，指标上也会更健康。

1.2 commit ratio 能反映哪些问题，不能反映哪些问题

先给出公式，后面所有讨论都围绕它展开：

commit_ratio = xact_commit / (xact_commit + xact_rollback) * 100%

这个比例越接近 100%，说明事务失败率越低；回滚率就是它的补集，完全可以用100 - commit_ratio表达。高回滚率背后往往藏着这些典型问题：应用层没做好唯一键冲突兜底、死锁导致无辜事务被牺牲、语句超时或客户端断连、ORM 只读事务被连接池超时回收，以及 ETL 脚本“失败就整批重试”。

但它也有明显的边界。它只告诉你“失败发生得多不多”，不告诉你“是哪条 SQL 失败、为什么失败、失败事务写了多少脏数据”。两个事务，一个是一行插入失败，一个是百万行批量导入失败，在计数器上完全等价。所以这个指标更适合当健康度风向标，不适合当根因定位器。定位根因要靠写进快照的增量采样、pg_stat_statements和错误日志。

2. 基础查询与最容易踩的三个坑

2.1 最常出现在教程里的那一句 SQL

网上搜 PostgreSQL commit ratio，十有八九会给出下面这句：

SELECT datname, xact_commit, xact_rollback, round(100.0 * xact_commit / NULLIF(xact_commit + xact_rollback, 0), 2) AS commit_ratio FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY xact_commit DESC;

这句不是不能用，但你要非常清楚一件事：它算出来的是“从统计重置以来”的累计提交率，不是当前健康度。对于刚初始化完、数据量不大的库，它还算有参考价值；对于已经跑了几个月的生产库，xact_commit 可能已经是千万级，而最近一个小时的异常在累计值里会被稀释到几乎看不见。

举个例子：某库累计提交 100 万、回滚 1000，累计提交率 99.9%。某十分钟窗口内新增提交 100、回滚 900。窗口内的提交率是 100 / (100 + 900) = 10%，这是非常吓人的恶化程度。但如果你只看累计率，会变成 (1000100) / (1001000) ≈ 99.8%，看起来只是从 99.9% 掉了 0.1 个百分点。这种钝感会让你错过真正的故障窗口。累计值适合看长期趋势，不适合做实时判断。

2.2 坑一：把累计值当成当前健康度

这个坑在上面的例子里已经很直白了。正确做法是做增量计算：取两个时间点的快照，用差值算窗口内提交率。就像看汽车油耗，不能看总里程除以总加油量，而要看这段路程实际烧了多少油。增量计算能暴露短时间窗口内的剧烈波动，这才是告警和排查真正需要的信号。

2.3 坑二：datname 的 NULL 默认行与权限限制

pg_stat_database里有一个特殊行，datid 为 0，datname 为 NULL，它代表整个实例的汇总统计。很多人写查询时直接用WHERE datname IS NOT NULL把它过滤掉，这在“按库看明细”时没问题，但如果想快速看整个集群的总提交率，恰恰应该保留这一行。做快照对比表时，建议用datid作为关联键，而不是datname，因为 NULL 无法正常参与等值连接。

权限上也要留个心眼。新版本 PostgreSQL 对统计视图的可见性做了收紧，普通业务账号通常只能看到自己正在连接的库，甚至某些字段会被隐藏。如果监控程序想收集所有库的 commit/rollback，不要用普通业务账号，更不要图省事直接给超级用户。更合理的做法是创建专用监控账号，并授予pg_monitor或pg_read_all_stats角色。

2.4 坑三：counter reset 与数据库重启

pg_stat_reset()会把统计计数器清零，数据库重启同样会重置。两次采样如果中间发生了 reset，差值就会变成负数或者从 0 重新开始，算出来的比例完全失真。好在pg_stat_database里有stats_reset字段，记录最近一次统计重置的时间。做快照时把这个字段一起存下来，计算差值时增加一个过滤条件：

WHERE s2.stats_reset = s1.stats_reset

如果两次采样之间 stats_reset 发生了变化，就说明中间有过重置，这组差值要直接丢弃。

3. 亲手算一次“窗口内提交率”：快照对比法

3.1 临时表快照：5 分钟内的增量

如果你只是临时排查，不需要建正式监控表，用两个临时表就能完成一次五分钟窗口采样。第一次查询：

CREATE TEMP TABLE s1 AS SELECT datid, datname, xact_commit, xact_rollback, stats_reset FROM pg_stat_database WHERE datname IS NOT NULL;

等 5 分钟之后再执行一次同样的查询，落到s2表里：

CREATE TEMP TABLE s2 AS SELECT datid, datname, xact_commit, xact_rollback, stats_reset FROM pg_stat_database WHERE datname IS NOT NULL;

然后关联求差值：

SELECT s2.datname, (s2.xact_commit - s1.xact_commit) AS commit_delta, (s2.xact_rollback - s1.xact_rollback) AS rollback_delta, round(100.0 * (s2.xact_commit - s1.xact_commit) / NULLIF((s2.xact_commit - s1.xact_commit) + (s2.xact_rollback - s1.xact_rollback), 0), 2) AS commit_ratio_pct FROM s1 JOIN s2 USING (datid) WHERE s2.stats_reset = s1.stats_reset ORDER BY commit_delta DESC;

这个查询里我特意把差值项凑在一起计算，而不是直接取s2.xact_commit / (s2.xact_commit + s2.xact_rollback)，因为后者拿到的是两个累计值的比值，不是窗口增量。NULLIF 是为了防止“窗口内一个事务都没有”时出现除零错误，那段时间应该显示为无数据，而不是 0%。如果 commit_delta 和 rollback_delta 都是 0，说明该库这段时间基本没有事务活动，不需要关注。

3.2 做一张可持续采样的快照表

临时表适合一次性诊断，日常监控还是要落一张长期快照表。表结构可以这样设计：

CREATE TABLE pg_stat_snapshots ( snap_time timestamptz NOT NULL, datid oid NOT NULL, datname name, xact_commit bigint, xact_rollback bigint, stats_reset timestamptz, PRIMARY KEY (snap_time, datid) );

采集脚本用 psql 加 COPY 最高效，输出 CSV 直接追加。下面是一个可以放进 crontab 的示例脚本：

#!/usr/bin/env bash PSQL="/usr/bin/psql -X -A -t" DSN="host=/var/run/postgresql dbname=postgres user=pgmonitor" OUT_DIR="/var/lib/postgresql/monitor" $PSQL "$DSN" -c "copy ( select now(), datid, datname, xact_commit, xact_rollback, stats_reset from pg_stat_database where datname is not null ) to stdout with csv" >> "$OUT_DIR/pg_stat_snapshots_$(date +%Y%m%d).csv"

建议每 1 分钟采集一次，文件按天切分，至少保留 7 天。这个数据量非常小，一天 1440 次采样，每次几十行，连一张普通业务表都比它大得多。但它的价值很大：有了历史快照，你就能随时算“过去 10 分钟”“过去 1 小时”的提交率，不用等故障发生时才临时补采样。

3.3 用窗口函数看趋势变化

快照表里数据累积之后，可以用lag()窗口函数把相邻两次采样连起来算窗口增量：

WITH ranked AS ( SELECT snap_time, datname, xact_commit, xact_rollback, stats_reset, lag(xact_commit) OVER (PARTITION BY datid ORDER BY snap_time) AS prev_commit, lag(xact_rollback) OVER (PARTITION BY datid ORDER BY snap_time) AS prev_rollback, lag(stats_reset) OVER (PARTITION BY datid ORDER BY snap_time) AS prev_reset FROM pg_stat_snapshots ) SELECT snap_time, datname, (xact_commit - prev_commit) AS commit_delta, (xact_rollback - prev_rollback) AS rollback_delta, CASE WHEN (xact_commit - prev_commit) + (xact_rollback - prev_rollback) > 0 THEN round(100.0 * (xact_commit - prev_commit) / ((xact_commit - prev_commit) + (xact_rollback - prev_rollback)), 2) ELSE NULL END AS commit_ratio FROM ranked WHERE stats_reset = prev_reset ORDER BY snap_time DESC;

实际使用中你会发现一个常见干扰项：低流量时段的比例抖动。比如某个测试库五分钟内只有 5 个事务，其中 1 个回滚，窗口回滚率按比例算高达 20%；下一个五分钟恰好没有回滚，比例又瞬间归零。这种抖动不是真实故障信号，所以做趋势展示或告警查询时，建议加一个最小事务数过滤条件，比如要求commit_delta + rollback_delta >= 50，否则不计入异常判断。

4. 提交率走低之后，怎么定位根因

4.1 先缩小嫌疑范围：按库、按时间窗

提交率异常时，第一件事不是翻代码，而是确认“到底哪个库、哪个时间段出问题”。用快照表做一个按库汇总，能快速锁定目标：

SELECT datname, count(*) AS samples, sum(commit_delta) AS total_commits, sum(rollback_delta) AS total_rollbacks, round(100.0 * sum(rollback_delta) / NULLIF(sum(commit_delta) + sum(rollback_delta), 0), 2) AS rollback_ratio_pct FROM ( -- 上面窗口函数那个子查询 ) t WHERE snap_time >= now() - interval '1 hour' GROUP BY datname ORDER BY total_rollbacks DESC;

另外别忘了pg_stat_database里有一个deadlocks字段，专门统计该库检测到的死锁次数。如果回滚增量升高的同时，deadlocks增量也同步升高，那基本可以判定死锁是主要因素，后面优先查锁竞争和事务顺序。

4.2 用 pg_stat_statements.failed 找出失败语句

PostgreSQL 14 及以后版本，pg_stat_statements新增了failed字段，直接统计每条语句的失败执行次数。这是定位回滚来源最顺手的工具：

SELECT calls, failed, round(total_exec_time / calls, 2) AS avg_exec_ms, query FROM pg_stat_statements WHERE failed > 0 ORDER BY failed DESC LIMIT 20;

需要注意，这里统计的是语句失败的次数，不是事务回滚的次数。一个包含五条 SQL 的事务整体回滚，可能五条语句都显示失败一次。看到 failed 高的语句后，再配合它的文本去判断是哪一类错误：是幂等冲突、约束冲突，还是超时取消。

如果是 PostgreSQL 13 及更早版本，pg_stat_statements没有失败计数，就只能退回到错误日志分析。生产环境建议提前开启错误日志记录，把log_min_error_statement设为error，这样每条 ERROR 日志后面会带上出错的语句文本，排查效率会高很多。

4.3 日志关键字速查表

下面这张表是我排查回滚问题时最常用到的错误信号。看到日志里有这些关键字，原因基本就清晰了大半：

如果是死锁导致的高回滚，写日志里往往会出现两个进程互相等待的详细链。如果日志里大面积出现 duplicate key，那瓶颈通常在前端幂等键生成策略，而不是数据库本身。

4.4 一个回滚率异常排查的真实流程

说一个我印象很深的案例。某业务系统的订单库，某天下午监控面板显示回滚率从平时的 0.2% 突然涨到 18% 左右，持续了快四十分钟。当时第一反应不是去看慢查询，而是做了三件事：先看快照表确认受影响的是订单库；再看pg_stat_statements.failed，发现排名第一是一条针对业务幂等表的INSERT语句；最后翻错误日志，确认大量报错是duplicate key value violates unique constraint。

进一步追查才发现，问题出在最近一次发版。应用层为了做接口幂等，会在插入前先查一次是否已有数据，查不到就执行插入。但两个并发请求同时走到“查询不到”的分支，就会都尝试插入，后到的那一个触发唯一键冲突。而当时的应用框架把整个工作单元包在一个事务里，冲突一旦发生，连同前面已经执行的其他写操作一起回滚，于是失败被放大了。修复方式也很直接：把插入语句换成INSERT ... ON CONFLICT DO UPDATE，让数据库来做原子化的幂等判断，同时尽量缩短事务范围，不要在事务里放与业务无关的远程调用。修复之后回滚率很快回到 0.3% 以下，但这次排查的经验一直提醒我：高回滚率往往不是数据库的问题，而是应用层错误处理策略的问题。

5. 回滚率升高不等于故障：学会区分假阳性

5.1 低流量场景下的比例失真

前面已经提到低流量抖动。这里再给一个更具体的场景：某内部管理系统，非工作时间事务量很小，十分钟窗口可能只有 3 个事务。如果其中 1 个在凌晨定时脚本里因为连接被清理而回滚，比例瞬间就是 33%。如果告警规则只看比例不看事务量，你会收到一堆半夜垃圾告警。给告警加一个“窗口内事务总数大于等于 N”的条件，比调高比例阈值更有效。

5.2 连接池与 ORM 的“假回滚”

有一种回滚率升高不是业务故障，而是事务管理不当。典型的例子是 Hibernate / JPA 这类 ORM：如果开启了一个事务，但事务里只有几个 SELECT 查询，开发人员以为“只读不需要提交”，或者事务边界写得太宽，事务一直开着。等连接池的超时回收机制介入，后端会话被强制终止，这个事务就会以回滚收场。流量一大的时候，这类“只读假回滚”能把回滚率冲得很高。

排查它有个特点：错误日志里通常看不到 ERROR，回滚率升高但 failed 语句很少甚至没有。因为你看到的不是 SQL 报错，而是连接被回收。解决方式是规范事务边界，明确只读操作走 autocommit 或真正只读的事务，提交后立即释放连接。

5.3 迁移、批处理、测试脚本的预期回滚

还有一种情况更要淡定：数据迁移或批处理脚本，设计上就是“先导一批数据到临时表，校验不过就整批回滚，下次重跑”。这种业务逻辑本身依赖回滚，所以某个库长期保持高回滚率不等于异常。监控时要区分业务线：联机交易库和 ETL 库不能用同一套阈值，建议在监控维度上加上application_name或datname分组，为不同类型的库配置不同的告警策略。

6. 监控该有的姿势：阈值、权限与面板

6.1 经验阈值参考

我用下面这张表作为初步参考，但它绝不是铁律。真正合理的阈值要看业务对事务失败的容忍度：

所有阈值都必须在“窗口内事务量足够大”的前提下使用。最低事务量建议根据系统规模设定，生产核心库可以要求 5 分钟内至少 100 个事务才计算比例，小系统可以放宽到 20 个。

6.2 监控账号用 pg_monitor，别拿超级用户跑

给监控程序单独建账号：

CREATE ROLE pgmonitor LOGIN PASSWORD 'xxxx'; GRANT pg_monitor TO pgmonitor;

pg_monitor能读pg_stat_database、pg_stat_activity、pg_stat_replication等统计视图，足够监控用了，没必要给超级用户。少给权限不只是安全考虑，也是防止监控程序误操作把生产库搞出问题。

6.3 接进 Prometheus 与 Grafana 的思路

如果你已经有 Prometheus 和 Grafana，事务提交率这个指标接入成本很低。postgres_exporter默认会采集pg_stat_database相关指标，其中就包含pg_stat_database_xact_commit和pg_stat_database_xact_rollback。在 Grafana 里用速率函数算增量占比：

rate(pg_stat_database_xact_commit[5m]) / (rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m])) * 100

PromQL 里的rate()本身就是基于时间序列增量的计算，能自动处理计数器重置问题，这比手写快照表省事很多。唯一要留神的是指标里带datname标签，记得用查询过滤掉汇总行，或者在面板里按数据库分组展示。告警表达式加上最小事务量条件，避免低流量抖动触发误报。

实际使用中我还养成了一个习惯：快照表不要只留 commit 和 rollback，顺手把deadlocks、tup_inserted、tup_updated、tup_deleted也一起存下来。这样回滚率异常时，可以马上对比“回滚增量和死锁增量”是否同步，也可以看“回滚事务是否伴随大量写操作”，进一步判断回滚带来的真空清理压力。单看 commit ratio 永远只是冰山一角，把它和失败日志、锁等待、死锁、表膨胀趋势组合在一起看，才会有真正的判断力。

最后分享一个小技巧：同一套监控视图，不要只盯绝对值。找一个你已知“一直很健康”的库作为对照，当某个库的提交率相对自身历史基线下降两个百分点以上时，哪怕绝对数值还在 99%，也值得看一眼。因为我遇到过不少案例，问题不是突然爆炸，而是慢慢侵蚀，累计值掩盖了恶化趋势，等真正爆发时已经晚了。提交率这种指标，最适合的用法不是等告警响，而是定期翻一翻，用三分钟的时间确认系统还在它该在的轨道上。