PostgreSQL 40TB数据库高回滚率：如何获取回滚会话及表详情？

1. 实时捕捉触发回滚的会话 #

通过pg_stat_activity视图直接查看当前会话的回滚统计，筛选出有回滚行为的会话：

SELECT pid, usename, datname, application_name, client_addr,
       xact_start, query, xact_rollback
FROM pg_stat_activity
WHERE xact_rollback > 0 
  AND (state = 'idle in transaction' OR state = 'active');

• xact_rollback：该会话已执行的回滚次数
• query：当前或最后执行的SQL语句，可直接定位触发回滚的操作
• pid：会话进程ID，可结合pg_terminate_backend(pid)临时终止异常会话（需谨慎操作）

2. 追踪回滚涉及的表 #

PostgreSQL没有原生记录回滚具体表的系统视图，可通过以下两种方式间接定位：

方式一：临时开启事务回滚日志 #

修改postgresql.conf临时开启相关日志配置（仅在高回滚时段启用，避免日志过载）：

log_statement = 'all'
log_min_duration_statement = 0
log_transaction_abort = on

重载配置后，回滚的事务会被记录到PostgreSQL日志文件中，日志内容包含回滚事务执行的所有SQL语句，从中可提取涉及的表名。监控结束后记得恢复原配置。

方式二：通过死元组变化定位 #

回滚会产生大量死元组，对比高回滚时段前后的表死元组数量，激增的表即为回滚涉及的表：

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) AS dead_tup_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

3. 长期监控高频回滚SQL #

启用pg_stat_statements扩展，统计触发回滚次数最多的SQL语句：

1. 先在postgresql.conf中添加配置并重启数据库：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

2. 创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

3. 查询高频回滚SQL：

SELECT queryid, query, calls, rollback_count,
       round(rollback_count::numeric / calls * 100, 2) AS rollback_rate
FROM pg_stat_statements
WHERE calls > 0 AND rollback_count > 0
ORDER BY rollback_count DESC;

• rollback_count：该SQL触发的回滚总次数
• rollback_rate：该SQL的回滚占比，可快速定位问题语句

4. 全局回滚率统计 #

查看数据库整体回滚情况，确认高回滚时段的趋势：

SELECT datname, xact_rollback, xact_commit,
       round(xact_rollback::numeric / (xact_commit + xact_rollback) * 100, 2) AS rollback_rate
FROM pg_stat_database
WHERE datname = '你的目标数据库名';

内容的提问来源于stack exchange，提问作者Kyosh Pietro