HDUO Lab

深入 PostgreSQL 陷阱排查：Idle in Transaction 引发的 MVCC 巨量膨胀与 IOPS 耗尽实战

作者：

ningniu

在

排查过程中，某核心业务数据库突然遭遇性能断崖：QPS 从日常的 8000 跌至不足 500，99线延迟从 5ms 飙升至 3.5s，主机 Load Average 直接突破 120，IOPS 被瞬间打满。 最终结论：这是一个典型由“防御性配置缺失”加上“烂SQL”引发的血案。某个后台数据同步 Python 脚本由于异常终止，遗留了一个长达 72 小时的 idle in transaction 僵尸连接。这个僵尸连接死死拖住了全局的 xmin 水位，导致 Autovacuum 进程虽然疯狂运转，却无法清理这三天内产生的几千万条 Dead Tuples。核心表体积膨胀了 40 倍，原本走内存缓存的 Index Scan 退化成了疯狂刷盘的物理 I/O，最终引发整个集群雪崩。

解法很直接：Kill 掉僵尸 PID，利用 pg_repack 在线重建表空间，并在全局配置文件中强制开启 idle_in_transaction_session_timeout。

案发现场与指标确认

当时接警后，第一反应是看系统监控。iostat -x 1 显示磁盘 %util 持续 100%，读写带宽却并不高，典型的随机小 I/O 耗尽了存储性能。 切到数据库层，执行基础探查：

SELECT datname, usename, state, query, extract(epoch FROM now() - xact_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle' ORDER BY duration DESC LIMIT 5;

输出结果令人窒息，排名第一的连接 state 为 idle in transaction，duration 竟然高达 259200 秒（整整三天）。

紧接着看一眼核心交易表的膨胀情况：

SELECT relname, n_live_tup, n_dead_tup, 
       round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup + 1), 2) AS bloat_ratio
FROM pg_stat_user_tables 
WHERE relname = 'orders_hot';

结果显示 n_live_tup 为 120 万，而 n_dead_tup 高达 4800 万，膨胀率超 97%。表物理大小从正常的 500MB 飙到了将近 20GB。

底层原理解析：为什么一个 Idle 连接能搞死整个集群？

要理解这个致命陷阱，必须搞懂 PostgreSQL 的 MVCC（多版本并发控制）底层实现。 与 MySQL InnoDB 将旧版本数据塞进独立 Undo Log 的做法不同，PostgreSQL 采用了“原地追加”的设计。当你执行 UPDATE 时，PG 其实是做了一个 DELETE + INSERT，旧行（Dead Tuple）和新行（Live Tuple）都存在同一个数据页（Page）里。

为了清理这些旧行，PG 依赖后台的 Autovacuum 进程。但 Autovacuum 回收旧行的核心前提是：系统中没有任何活跃事务可能还会读取到这行数据。 判断标准就是事务 ID（XID）。如果旧行的 xmax（即删除该行的事务 ID）小于当前系统中所有活跃事务的最小事务 ID（全局 xmin），这行数据才能被安全回收。

灾难的逻辑链条就此闭环：

  1. 那个挂掉的 Python 脚本开启了一个事务（BEGIN），执行了一条简单的 SELECT，然后由于网络问题断开或进程 OOM 被杀，但它使用的连接池没有发送 COMMIT 或 ROLLBACK。

  2. 数据库层面，这个连接就卡在了 idle in transaction 状态，它的事务 ID 被永久保留。

  3. 核心表 orders_hot 每天有极高频的并发 UPDATE，产生海量 Dead Tuples。

  4. Autovacuum 定期唤醒扫描这块表，但一比对 XID，发现那个长事务的 xmin 比这些 Dead Tuples 的 xmax 还要老。

  5. Autovacuum 只能叹口气，原封不动地放过这些旧数据。

  6. 数据页被迅速塞满，新数据只能申请新的 Page，导致表物理文件无限膨胀。缓存命中率（Hit Ratio）断崖式下跌，每次查询都需要扫过几百个全是废数据的 Page，I/O 直接被打爆。

在开发眼里，似乎只是“一个空闲连接没关”，而在 PG 内核引擎里，这等同于“冻结了整个数据库的时间线”。

解决与止血方案

不要试图在业务高峰期直接执行 VACUUM FULL，这会申请 AccessExclusiveLock 排他锁，直接导致全表读写阻塞，引发更惨烈的级联故障。

第一步：物理超度僵尸连接

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' AND extract(epoch FROM now() - xact_start) > 300;

注：干掉之后，Autovacuum 终于能够正常回收之后的数据页，但这无法缩减已经膨胀的物理文件大小。

第二步：使用 pg_repack 无锁重建 借助 pg_repack 插件，在不影响业务读写的情况下重新组织表物理文件。

pg_repack -h localhost -U postgres -d mydb -t orders_hot -j 4

原理：它会建一张结构相同的新表，触发器同步增量数据，老表数据导完后交换表名，全程只需要极短的锁时间。

第三步：配置层面的防御性编程 永远不要相信客户端侧的规范，所有的保障必须在数据库内核层面兜底。在 postgresql.conf 中强制注入以下参数：

# 斩断 idle in transaction 狗皮膏药，超时 60 秒直接 Kill（PG 9.6+ 支持）
idle_in_transaction_session_timeout = 60000 

# 设置普通语句执行超时，防止烂 SQL 拖死 CPU
statement_timeout = 30000 

# 如果是 PG 14+，可以考虑开启 idle_session_timeout
idle_session_timeout = 300000

避坑总结

技术团队过度依赖 ORM 框架和连接池默认配置，往往容易忽略数据库原生会话生命周期的管理。PG 的 MVCC 架构在带来极高并发性能的同时，也把垃圾回收的压力交给了运维层面。 在架构设计时，如果你的业务属于“高频 Update/Delete”场景，监控体系里如果没有对 pg_stat_user_tables.n_dead_tup 和长事务时间做强力告警，那就是在抱着定时炸弹裸奔。

排查清单：PostgreSQL MVCC 膨胀与长事务速查

  1. 一键定位长事务与慢查询 sql SELECT pid, datname, usename, state, backend_xid, backend_xmin, extract(epoch FROM now() - xact_start) AS duration, query FROM pg_stat_activity WHERE state != 'idle' AND xact_start IS NOT NULL ORDER BY duration DESC LIMIT 10;
  2. 侦测表空间与索引膨胀率 重点关注 n_dead_tup 与 n_live_tup 比例。比例超过 20% 且表极大的，需要介入处理。 sql SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
  3. 检查 Autovacuum 运行状态 如果系统 I/O 正常但膨胀严重，检查 Autovacuum 进程是否在干活，或者是否被长事务卡住。 sql SELECT * FROM pg_stat_activity WHERE query LIKE 'autovacuum%';
  4. 验证防御性配置是否生效 执行 SHOW idle_in_transaction_session_timeout;，确保该值不为 0。

更多文章