PostgreSQL / 系统课时

排查长事务导致的表膨胀

排查长事务导致的表膨胀

这类故障不能只看“事务运行很久”或“表变大了”其中一项。目标是先确认哪个会话正在压低 VACUUM 的可回收边界,再评估终止事务的业务风险,最后把死元组清理与物理容量回收拆成两阶段执行。

学习目标

  • 用 pg_stat_activity 找出真正可能拖住清理边界的长事务。
  • 把长事务证据与高死元组、高增长表关联起来,避免误杀无关会话。
  • 在可回滚前提下结束阻塞,并选择普通 VACUUM 或后续物理重写方案恢复容量。

从“事务很长”确认到“清理被挡住”

长事务会让旧快照持续存在,使一部分已被 UPDATE 或 DELETE 淘汰的行版本仍可能被该事务看见。此时 VACUUM 可以扫描表,却不能越过安全边界删除这些版本。排查时不要只按 xact_start 排序,还要看 backend_xid、backend_xmin、事务状态和当前或最近执行的 SQL。

下面的查询可先列出所有仍处在事务中的后端,并把最早开始的事务放在前面。

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;

预期结果

text
 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 的年龄也明显偏大时,才更接近“这个会话正在扩大保留范围”的证据。

长事务把 VACUUM 清理边界压在旧快照之后

把阻塞事务与膨胀表关联起来

确认有老事务后,再看哪些表积累了大量死元组。n_dead_tup 是统计估算值,不等于精确的“膨胀字节数”,但它与表大小、最近 VACUUM 时间结合后,足以快速确定优先排查对象。

sql
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;

预期结果

text
 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> 替换为目标后端编号。

sql
SELECT pg_terminate_backend(<PID>);

预期结果

text
 pg_terminate_backend
----------------------
 t

返回 t 表示终止信号已成功发送;若权限不足、PID 已消失或目标受保护,实际结果会不同。

清理死元组并决定是否回收磁盘

阻塞事务结束后,先用普通 VACUUM 恢复页内可复用空间和统计信息。普通 VACUUM 通常可与业务读写并行,适合作为故障后的第一步。

sql
VACUUM (ANALYZE) public.orders;

预期结果

text
VACUUM

随后重新执行 pg_stat_user_tables 查询,确认 n_dead_tup 下降、last_vacuum 或 last_autovacuum 已更新,并观察后续增长速度是否恢复正常。

普通 VACUUM 的主要价值是让表文件内部空间可再次利用,它不保证把已经扩大的关系文件立即缩回原尺寸。若磁盘容量必须真实归还给操作系统,应另开维护动作:能接受 ACCESS EXCLUSIVE 锁和重写成本时才考虑 VACUUM FULL;索引本身明显膨胀时可评估 REINDEX CONCURRENTLY;需要更低阻塞的表重写则应使用经过验证的在线重写方案。磁盘已经逼近满载时,不要贸然启动会额外消耗空间的大型重写。

最终验收不是“事务杀掉了”,而是长事务边界已经消失、死元组能够被持续回收、表增长斜率恢复正常,并且容量回收动作与锁窗口、额外磁盘需求都已经单独评估。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

排查长事务导致的表膨胀

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通