运维 服务运维

一次 PostgreSQL 长事务导致表膨胀与 autovacuum 失效、查询性能断崖式下跌的排查实录

核心报表表查询从毫秒级劣化到几十秒，根因是 idle-in-transaction 长事务持有旧快照，阻断 autovacuum 回收死元组导致表膨胀。

一、问题背景

公司内部的工单与结算报表系统后端跑在 PostgreSQL 13（一主两从）上，核心业务表 orders（订单表）日增约 30 万行。该系统平时表现稳定，单条按主键查询稳定在 2~5ms，分页报表查询也在 200ms 以内。

事情发生在一次大促复盘期间。BI 同事反馈，最近两周报表导出的等待时间越来越长，原本跑 10 秒的日报 SQL 现在经常卡到 40 秒以上，且主库磁盘使用率从 55% 悄悄涨到了 81%。由于是从“慢慢变慢”而非“突然宕机”，前几次都被当作临时抖动忽略了，直到某天早高峰一个核心查询超时把接口拖垮，才正式立项排查。

二、故障现象

现场能观察到的现象有五点，且都指向同一张表：

  1. 查询延迟持续劣化：SELECT ... FROM orders WHERE create_time BETWEEN ... 这类带时间范围扫描的 SQL，执行计划从 Index Scan 退化成 Bitmap Heap Scan 后进一步退化成全表 Seq Scan，耗时从亚秒级爬升到 30~50 秒，且几乎不随业务低峰回落。
  2. 表体积异常膨胀：orders 表逻辑行数约 2100 万，但 pg_relation_size 显示其堆文件已超过 28GB（按行宽估算正常应约 9GB），物理大小是逻辑数据量的 3 倍多。
  3. autovacuum 形同虚设：pg_stat_user_tables 里 last_autovacuum 字段经常停留在几天前，n_dead_tup（死元组数量）长期维持在千万级且不下降。
  4. 磁盘只增不减：主库数据目录每天净增长约 4GB，但业务写入量并没有成比例放大。
  5. 只读从库复制延迟偶发抖动：由于主库频繁做重量级清理/检查点，WAL 产生突增，从库 replay_lag 间歇升高。

最关键的是：这些现象是渐进式的，单看某一时刻的监控都不算“故障”，组合起来才暴露出性能断崖。

三、排查过程

1. 先确认是不是“死元组”堆积

PostgreSQL 的 MVCC 机制下，UPDATE/DELETE 不会立刻物理删除旧版本，而是把它们标记为死元组（dead tuple），依赖 autovacuum 回收。先直接看统计：

1
2
3
4
5
6
7
8

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       round(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 10;

结果 orders 表的 n_dead_tup 高达 4100 万，dead_pct 接近 195%——也就是死元组比活元组还多一倍。表已经严重膨胀，这与磁盘只增不减的现象完全吻合。

2. 为什么 autovacuum 不干活

继续看 autovacuum 的触发条件。pg_stat_user_tables 里 last_autovacuum 长期不更新，说明要么没触发，要么触发了但被阻塞。查一个关键指标——最老事务的年龄：

1
2
3

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

主库 xid_age 已超过 2 亿，逼近 autovacuum_freeze_max_age（默认 2 亿）。再查是否有长事务卡住：

1
2
3
4
5
6
7
8

SELECT pid, datname, usename, application_name,
       state, now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       wait_event_type, wait_event, left(query, 80) AS query
FROM pg_stat_activity
WHERE state IS DISTINCT FROM 'idle'
  AND (now() - xact_start) > interval '5 minutes'
ORDER BY xact_age DESC;

这一查就露馅了：有一个来自 BI 报表服务的连接，state='idle in transaction'，xact_age 已经 26 小时，对应的 query 是一条 BEGIN 后没提交也没回滚、只做了一次 SELECT 的会话。

3. 根因链条闭环

关键点在于：PostgreSQL 的 vacuum 不能回收“被任意活跃事务快照之后产生的死元组”。只要有一个事务（哪怕是 idle in transaction）还持有旧快照，它之前版本的所有行对它是“可见”的，vacuum 为了不破坏它的可重复读视图，就只能跳过这些死元组的回收。

换句话说：一个 26 小时没提交的 idle-in-transaction 会话，相当于把整个数据库的死元组回收冻结了 26 小时。期间 orders 表持续被 UPDATE/DELETE，n_dead_tup 只增不减，autovacuum 即使运行也收效甚微。表越长越大，索引也同步膨胀（index bloat），执行计划因代价估算失真逐渐偏向全表扫描，查询自然断崖式变慢。

验证索引膨胀可用 pgstattuple 扩展：

1
2
3

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('orders_pkey');
-- dead_tuple_percent 高达 60%+ 即明显膨胀

4. 确认阻塞来源

用锁视图确认 vacuum 进程是否因该长事务被挡：

1
2
3
4
5
6

SELECT a.pid, a.usename, a.state, l.locktype, l.mode, l.granted,
       b.query AS blocking_query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_locks bl ON bl.locktype = l.locktype AND bl.objid = l.objid AND NOT bl.granted
WHERE l.granted = false;

结果印证：autovacuum worker 在 orders 上请求 ShareUpdateExclusiveLock 处于 granted=false 等待状态，阻塞者正是那个 26 小时的长事务会话。链条完全闭合。

四、解决方案

1. 立即释放“冻结”的长事务

先与 BI 团队确认该会话已无业务用途，强制终止以解冻 vacuum：

1
2

-- 先尝试优雅通知（应用侧回滚），无效则：
SELECT pg_terminate_backend(12345);  -- 12345 为 above 查到的 pid

会话断开后，立马手动触发一次对 orders 的 vacuum：

1

VACUUM (VERBOSE, ANALYZE) orders;

⚠️ 注意：不要用 VACUUM FULL 作为首选。VACUUM FULL 会持排他锁重写整张表，在大表上会长时间锁表影响业务。真正需要回收物理空间时，更优解是使用 pg_repack（在线重组，只短暂加锁）。

2. 在线重组回收膨胀空间

安装并使用 pg_repack 对 orders 做在线瘦身：

1

pg_repack -d bizdb -t public.orders --no-order

执行后 orders 堆文件从 28GB 回落到约 9.5GB，索引 dead_tuple_percent 从 60% 降到 2%，报表查询耗时回到 1 秒以内。

3. 紧急调优 autovacuum 阈值

对写入频繁的大表降低触发门槛，让 vacuum 更积极：

1
2
3
4
5

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 5000,
  autovacuum_analyze_scale_factor = 0.05
);

4. 设置事务超时兜底

在数据库级别给“空闲在事务中”的会话加超时，从机制上杜绝此类问题复发：

1

ALTER DATABASE bizdb SET idle_in_transaction_session_timeout = '5min';

同时在应用连接池（PgBouncer）层面设置 server_idle_timeout 和 query_timeout，双保险。

五、根因分析

本质是一条“不起眼”的 BI 会话泄漏：BEGIN 后查询完毕却忘记 COMMIT/ROLLBACK，连接被连接池保活成 idle in transaction。在 MVCC 下，这个未结束事务持有的旧快照，使 vacuum 无法回收该快照之后产生的任何死元组。叠加 orders 表高频 UPDATE/DELETE，死元组以千万级速度累积，表与索引持续膨胀，执行计划劣化，最终表现为查询性能断崖式下跌。问题不在 autovacuum 配置（默认配置对大多数表是够的），而在一个长事务把整个回收链路“冻住”了。

六、预防措施

  1. 集群级设置超时阈值：idle_in_transaction_session_timeout、statement_timeout、lock_timeout 三件套在实例/库级别统一配置，避免单个泄漏会话拖垮全局。
  2. 监控“长事务”而非只监控慢 SQL：把 pg_stat_activity 中 state='idle in transaction' 且 now()-xact_start > 5min 的会话接入告警，做到分钟级发现。
  3. 监控表膨胀率：定期跑 bloat 估算 SQL，对 dead_pct > 50% 的表自动派单处理；对大表常态化 pg_repack 维护窗口。
  4. 应用侧规范事务：禁止“查询后不提交”的写法，所有事务显式 COMMIT/ROLLBACK；BI/报表类长查询改用独立只读从库 + SET transaction_read_only，与核心写入库隔离。
  5. autovacuum 大表定制：对写入频繁的大表单独下调 scale_factor、上调 cost_limit，避免默认参数在大表上响应过慢。

七、总结

这次故障的典型之处在于：它没有报错、没有宕机、没有硬件异常，纯粹是“一个被遗忘的事务”通过 MVCC 机制缓慢冻结了整个回收链路，最终以“查询变慢”的形式爆发。给运维的启示是——监控不能只看 CPU/内存/慢日志，事务年龄（age(datfrozenxid)）和 idle in transaction 长会话同样是高优先级的“沉默杀手”。配好超时阈值 + 盯住长事务 + 定期治理表膨胀，基本能把这类问题挡在发生之前。

© 2020 - 2026 Zero Day Notes
使用 Hugo 构建
主题 Stack 由 Jimmy 设计