极客工场

Appearance

共 1,615 字预计阅读约 5 分钟

PostgreSQL VACUUM 与表膨胀治理：死元组、膨胀估算与在线回收 ​

更新时间：2026-09-18。本篇是 PostgreSQL 架构与 MVCC 的直接续篇。MVCC「插新留旧」带来的死元组若不回收，表和索引会持续膨胀、顺序扫描变贵、缓存命中率下降。本篇回答：膨胀怎么产生、怎么量化、怎么在线回收且不锁表。想快速理解「为什么要 vacuum」可先看 FAQ：PostgreSQL 为什么要 vacuum。

本文要回答的问题 ​

  • VACUUM 到底「回收」了什么？磁盘文件为什么不会自动缩小？
  • 普通 VACUUM、VACUUM FULL、VACUUM ANALYZE 三者差别？
  • 怎么量化一张表/索引的膨胀率？
  • autovacuum 为什么会「跟不上」？长事务为什么会让清理完全停摆？
  • 想把空间真正还给操作系统又不想长时间锁表，怎么办？

一、膨胀是怎么来的：从一次 UPDATE 看空间去向 ​

bash
更新前 8KB 页：                 更新后（VACUUM 前）：
+--------------------+          +--------------------+
| tuple v1 (活)       |          | tuple v1 (死,xmax) | ← 死元组，占着位置
| tuple (活)          |          | tuple v2 (活)      | ← 新插的版本
+--------------------+          +--------------------+
页里位置紧凑                     页里有“空洞”，但物理文件没缩小

关键事实：普通 VACUUM 只把死元组的位置标记为可复用，并不会把堆文件 truncate 缩小（仅末尾恰好全空时才归还少量空间）。因此：

  • 反复更新的表，物理文件会涨到「历史峰值大小」，然后趋于平稳（空间在内部循环复用）。
  • 若 VACUUM 长期跟不上，文件持续变大、索引同步变胖，缓存能装下的有效数据变少。
  • 删除一大批数据后，表文件通常不会缩小，需要额外手段。

二、三种 VACUUM 的本质区别 ​

命令是否锁表空间是否归还 OS是否更新统计信息用途
VACUUM不锁（可并发读写）否，仅标记可复用否（除非加 ANALYZE）日常清理死元组
VACUUM ANALYZE不锁否是清理 + 刷新优化器统计
VACUUM FULLACCESS EXCLUSIVE 强锁是，重建缩文件是彻底回收，但停机窗口
pg_repack / pg_squeeze几乎不锁（短时锁）是重建后 ANALYZE线上首选回收
sql
-- 看一张表的死元组/活元组计数（来自统计视图，轻量）
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/NULLIF(n_live_tup,0),1) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
WHERE relname = 'big_orders';

-- 精确膨胀：pgstattuple 走共享缓冲区/磁盘做全表采样，较贵，别在高峰跑
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('big_orders');
-- table_len / tuple_count / tuple_len / dead_tuple_count / dead_tuple_percent / free_space

第一个坑：n_dead_tup 是统计估算、且 VACUUM 后被清零，不能只看瞬时值。 评估膨胀应结合 pgstattuple 的 dead_tuple_percent + free_space，或用 pg_stat_user_tables 的 n_tup_hot_upd（HOT 低也会推高索引膨胀）。判断索引膨胀用 pgstatindex：

sql
SELECT * FROM pgstatindex('idx_big_orders_user_id');
-- 关注 leaf_fragmentation、avg_leaf_density；碎片高/密度低即膨胀

三、autovacuum 为什么会「跟不上」 ​

autovacuum 在「死元组数 > 阈值 + 比例 × 表行数」时触发：

bash
trigger = autovacuum_vacuum_threshold  (默认 50)
        + autovacuum_vacuum_scale_factor (默认 0.2) × n_live_tup

默认 0.2 意味着大表要积 20% 死元组才清，对大表过粗。除此之外还有四个常见「清不动」根因：

  1. 长事务/闲置在事务中的会话卡住 OldestXmin。 只要存在一个很老的活跃快照，那些只对它可能可见的死元组就不能被清（DEAD → 可回收的前提是对所有活跃事务都不可见）。
  2. 复制槽/备库延迟（hot_standby_feedback、xmin horizon）把全局最老 xmin 往后拽。
  3. cost-based vacuum 限速太保守：vacuum_cost_limit 太小，清理速度跑不赢写入。
  4. autovacuum worker 数不够（autovacuum_max_workers），多张大表同时到期排队。
sql
-- 头号排查：谁在拖住“最老事务”xmin（含复制槽、prepared xact）
SELECT pid, state, xact_start, now()-xact_start AS xact_age,
       backend_xmin, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

-- 复制槽是否卡住 xmin / catalog_xmin
SELECT slot_name, active, xmin, catalog_xmin,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

-- 准备事务（两阶段提交）残留也会卡住 horizon
SELECT * FROM pg_prepared_xacts;

第二个坑：idle in transaction 是膨胀隐形推手。 应用开了事务不提交（比如拿到连接后做了慢 RPC），它的 backend_xmin 一直停在老位置，导致全库 VACUUM 无法回收。监控要把「最长事务年龄」当成一等公民报警，而不只是看连接数。

四、在线回收：把空间真正还给 OS ​

VACUUM FULL 重建表能彻底收缩，但持 ACCESS EXCLUSIVE 锁，期间完全不能读写，大表等于停机。线上用 pg_repack（触发器版）或 pg_squeeze（逻辑解码版）在线重建：

bash
# pg_repack：先建影子表+触发器追增量，再追平后瞬间切换，最后只在切换瞬间短锁
pg_repack -d app_db -t public.big_orders -j 2 -h /var/run/postgresql

# 只重建膨胀的索引（比整表轻）
pg_repack -d app_db -t public.big_orders -i idx_big_orders_user_id
方案锁影响需要额外空间原理风险
VACUUM FULL全程强锁≈1 倍表重写到新文件停机
pg_repack仅切换瞬间强锁≈1 倍表影子表 + 触发器触发器有少量写放大
pg_squeeze切换瞬间短锁≈1 倍表逻辑解码追增量需逻辑复制配置
REINDEX CONCURRENTLY不阻塞读写索引大小新旧索引并存切换耗 IO

第三个坑：在线重建需要约一倍的剩余空间，且产生大量 WAL/IO。 务必先看磁盘水位（df -h、WAL 目录），在低峰执行，并确认没有长事务/失效复制槽，否则重建过程追不齐增量会拖很久。

五、预防：让膨胀根本长不起来 ​

治理不如预防，四件事最有效：

  1. 大表单独调小 autovacuum 比例，并提高 cost limit 让它跑得快：
sql
ALTER TABLE big_orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 2
);
  1. 热点更新表设 fillfactor，配合 HOT 减少索引版本（详见 架构与 MVCC 第四节）：
sql
ALTER TABLE hot_counter SET (fillfactor = 85);
-- 随后用 pg_repack 在线重建使 fillfactor 生效
  1. 批量删除分批化，避免一次制造海量死元组撑爆单轮 VACUUM：
sql
-- 分批删除：每批 5 万行，应用层循环直到 affected 为 0，批间可让出 IO
DELETE FROM log
WHERE ctid IN (
  SELECT ctid FROM log
  WHERE created_at < now() - interval '30 days'
  LIMIT 50000
);
VACUUM (VERBOSE, ANALYZE) log;   -- 大删除后手动触发一次清理

更彻底的方案是按时间声明式分区：过期数据 DROP TABLE old_partition 直接归还空间，完全绕过死元组。日志/流水类大表应优先分区，而不是靠 DELETE + VACUUM 长期硬扛。

  1. 盯死「最老事务年龄」与表的 age(relfrozenxid)，防止长事务/复制槽停摆清理与冻结。

六、一套可直接落地的巡检 SQL ​

sql
-- 1) 死元组 Top：死元组比例高且最近没成功 autovacuum 的表
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/NULLIF(n_live_tup,0),1) AS dead_pct,
       age(relfrozenxid) AS xid_age,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

-- 2) 长事务（> 5min）——膨胀与冻结停滞的根因
SELECT pid, state, now()-xact_start AS age, backend_xmin, left(query,80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now()-xact_start > interval '5 minutes'
ORDER BY xact_start;

-- 3) autovacuum 当前进度（PG12+）
SELECT datname, relid::regclass AS tbl, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
       round(100.0*heap_blks_scanned/NULLIF(heap_blks_total,0),1) AS pct
FROM pg_stat_progress_vacuum;

phase 很有用：长期停在 scanning heap 说明表大；vacuuming indexes 反复多轮说明 maintenance_work_mem 太小、死元组一次装不下导致索引被多次扫描，可调大该参数。

七、相关与延伸 ​

机制来源见 PostgreSQL 架构与 MVCC（xmin/xmax、HOT、冻结）；WAL 堆积与 checkpoint 的关系见 WAL 与 checkpoint 调优；统计信息陈旧会误导执行计划，见 EXPLAIN (ANALYZE, BUFFERS)。MySQL 侧对应概念（undo purge）可对照 MySQL 锁与事务。短问答见 FAQ PostgreSQL 为什么要 vacuum。

总结 ​

PostgreSQL VACUUM 与表膨胀：MVCC 插新留旧产生死元组，普通 VACUUM 只标记位置可复用、不缩文件，VACUUM FULL 能缩但全程强锁表；线上用 pgstattuple/pgstatindex 量化膨胀、用 pg_repack/REINDEX CONCURRENTLY 在线回收；治理根因是「消灭拖住 Oldestxmin 的长事务与失效复制槽 + 给大表调小 autovacuum scale factor、提高 cost limit、热点表设 fillfactor、流水表改分区直接 DROP」，并把最老事务年龄纳入监控。