PostgreSQL / 系统课时
排查长事务导致的表膨胀
排查长事务导致的表膨胀
这类故障不能只看“事务运行很久”或“表变大了”其中一项。目标是先确认哪个会话正在压低 VACUUM 的可回收边界,再评估终止事务的业务风险,最后把死元组清理与物理容量回收拆成两阶段执行。
学习目标
- 用
pg_stat_activity找出真正可能拖住清理边界的长事务。 - 把长事务证据与高死元组、高增长表关联起来,避免误杀无关会话。
- 在可回滚前提下结束阻塞,并选择普通
VACUUM或后续物理重写方案恢复容量。
从“事务很长”确认到“清理被挡住”
长事务会让旧快照持续存在,使一部分已被 UPDATE 或 DELETE 淘汰的行版本仍可能被该事务看见。此时 VACUUM 可以扫描表,却不能越过安全边界删除这些版本。排查时不要只按 xact_start 排序,还要看 backend_xid、backend_xmin、事务状态和当前或最近执行的 SQL。
下面的查询可先列出所有仍处在事务中的后端,并把最早开始的事务放在前面。
SELECT
pid,
usename,
datname,
state,
now() - xact_start AS xact_age,
age(backend_xid) AS xid_age,
age(backend_xmin) AS xmin_age,
wait_event_type,
wait_event,
left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND pid <> pg_backend_pid()
ORDER BY xact_start;预期结果
pid | usename | datname | state | xact_age | xid_age | xmin_age | wait_event_type | wait_event | query_sample
-------+---------+---------+---------------------+----------+---------+----------+-----------------+------------+-----------------------------
<PID> | app | appdb | idle in transaction | <TIME> | <VALUE> | <VALUE> | Client | ClientRead | UPDATE orders SET ...<PID>、<TIME> 和 <VALUE> 会随现场变化。xact_age 很大只是线索;当 backend_xid 或 backend_xmin 的年龄也明显偏大时,才更接近“这个会话正在扩大保留范围”的证据。

把阻塞事务与膨胀表关联起来
确认有老事务后,再看哪些表积累了大量死元组。n_dead_tup 是统计估算值,不等于精确的“膨胀字节数”,但它与表大小、最近 VACUUM 时间结合后,足以快速确定优先排查对象。
SELECT
relid::regclass AS table_name,
n_live_tup,
n_dead_tup,
round(
100.0 * n_dead_tup
/ GREATEST(n_live_tup + n_dead_tup, 1),
2
) AS dead_pct,
last_vacuum,
last_autovacuum,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;预期结果
table_name | n_live_tup | n_dead_tup | dead_pct | last_vacuum | last_autovacuum | total_size
---------------+------------+------------+----------+-------------+-----------------+-----------
public.orders | <VALUE> | <VALUE> | <VALUE> | <TIME> | <TIME> | <VALUE>如果某张高写入表的 n_dead_tup 持续上涨,同时长事务一直存在,而 VACUUM 执行后死元组仍不能明显下降,就形成了完整证据链:旧事务保留可见性 → 旧版本无法回收 → 表内可复用空间不足 → 文件继续增长。反过来,如果只有表很大但死元组不多,应继续检查索引膨胀、正常数据增长或大字段 TOAST,而不是直接终止长事务。
终止前先评估回滚半径
结束后端会让它当前未提交事务整体回滚,因此不能把 pg_terminate_backend 当成“清理按钮”。先确认会话属于哪个应用、是否正在执行批处理、是否有大量未提交写入、客户端能否安全重试,以及回滚本身是否会带来额外 I/O。
pg_cancel_backend 只取消正在执行的语句;如果会话随后仍停留在打开的事务中,清理边界不会因此可靠释放。真正需要解除长事务时,优先让业务侧主动 COMMIT 或 ROLLBACK;无法协调时再终止后端。
下面是操作模板。它依赖前一步已经确认的实际 PID,因此必须先把 <PID> 替换为目标后端编号。
SELECT pg_terminate_backend(<PID>);预期结果
pg_terminate_backend
----------------------
t返回 t 表示终止信号已成功发送;若权限不足、PID 已消失或目标受保护,实际结果会不同。
清理死元组并决定是否回收磁盘
阻塞事务结束后,先用普通 VACUUM 恢复页内可复用空间和统计信息。普通 VACUUM 通常可与业务读写并行,适合作为故障后的第一步。
VACUUM (ANALYZE) public.orders;预期结果
VACUUM随后重新执行 pg_stat_user_tables 查询,确认 n_dead_tup 下降、last_vacuum 或 last_autovacuum 已更新,并观察后续增长速度是否恢复正常。
普通 VACUUM 的主要价值是让表文件内部空间可再次利用,它不保证把已经扩大的关系文件立即缩回原尺寸。若磁盘容量必须真实归还给操作系统,应另开维护动作:能接受 ACCESS EXCLUSIVE 锁和重写成本时才考虑 VACUUM FULL;索引本身明显膨胀时可评估 REINDEX CONCURRENTLY;需要更低阻塞的表重写则应使用经过验证的在线重写方案。磁盘已经逼近满载时,不要贸然启动会额外消耗空间的大型重写。
最终验收不是“事务杀掉了”,而是长事务边界已经消失、死元组能够被持续回收、表增长斜率恢复正常,并且容量回收动作与锁窗口、额外磁盘需求都已经单独评估。