PostgreSQL VACUUM 与自动清理深度解析与可验证实践

本文面向 PostgreSQL 13–16，目标是“把 VACUUM 从概念变为可验证的运行保障”。围绕 Autovacuum 触发逻辑、冻结年龄与膨胀治理，给出逐步实验、观测指标与安全参数边界。

## 适用版本与前提

• 版本：PostgreSQL 13–16（验证命令在这些版本均可用）。
• 必须启用：`track_counts=on`（默认），以便统计触发 Autovacuum。
• 建议开启：`pg_stat_statements` 用于热点 SQL 画像（不影响 VACUUM 实验）。

## 关键概念速览

• 可见性地图（Visibility Map）：记录哪些页无需扫描即可安全移除死元组，VACUUM FULL 与普通 VACUUM 行为不同。
• 冻结年龄（Freeze Age）：避免事务 ID 回绕，受 `autovacuum_freeze_max_age` 影响；默认 2e9 附近的安全阈。
• 膨胀（Bloat）：频繁更新/删除导致表/索引空洞，需通过 `VACUUM`+`REINDEX` 或 `CLUSTER`/`pg_repack` 治理。

## 可复现实验：触发 Autovacuum

1. 创建实验表并设置较小 `fillfactor`，鼓励更新不写回原页：

CREATE TABLE t_vac (
  id BIGSERIAL PRIMARY KEY,
  k  BIGINT NOT NULL,
  v  TEXT    NOT NULL
) WITH (fillfactor=70);

INSERT INTO t_vac (k, v)
SELECT g, repeat('x', 200)
FROM generate_series(1, 200000) AS g;

2. 执行批量更新与删除，制造死元组：

UPDATE t_vac SET v = repeat('y', 200) WHERE k % 3 = 0;
DELETE FROM t_vac WHERE k % 5 = 0;

3. 观察 Autovacuum 队列与表统计：

SELECT relname, n_dead_tup, n_live_tup, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 't_vac';

-- 正在执行的 autovacuum 会在活动视图中出现
SELECT pid, datname, relid::regclass AS relation, query
FROM pg_stat_activity
WHERE query ILIKE '%autovacuum%';

4. 手动触发对比：

VACUUM t_vac;           -- 轻量，维护可见性地图与清理死元组
VACUUM (ANALYZE) t_vac; -- 同时更新统计

预期：`n_dead_tup` 降低，`autovacuum_count`/`vacuum_count` 增加。若死元组增长快于清理，需调优触发阈值。

## 触发参数与安全边界（均可在线验证）

• `autovacuum_naptime`：默认 1min，控制周期扫描。
• `autovacuum_vacuum_threshold`：默认 50，基础触发量。
• `autovacuum_vacuum_scale_factor`：默认 0.2（20%），比例触发；对大表常降至 0.01–0.05。
• `autovacuum_vacuum_cost_limit` / `autovacuum_vacuum_cost_delay`：抑制 IO 峰值，生产建议适度提高 limit、降低 delay 保证跟上写入。
• 冻结相关：`autovacuum_freeze_max_age`（默认 2e9）与 per-table `vacuum_freeze_table_age`。可通过 `pg_class.relfrozenxid` 监控冻结进度。

验证方法：

SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_scale_factor;

SELECT c.relname,
       age(c.relfrozenxid) AS relfrozen_age,
       s.n_live_tup, s.n_dead_tup
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relname = 't_vac';

## 膨胀诊断与治理

• 诊断：使用 `pg_stat_user_tables` 的 `n_dead_tup` 与索引大小对比；或借助 `pgstattuple` 扩展：

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('t_vac');

• 治理路径：
• 普通 `VACUUM (FULL)`：重写表，需要排他锁与更高 IO；适用于离线窗口。
• `REINDEX CONCURRENTLY`：在线重建索引，减少阻塞。
• `pg_repack`（外部工具）：在线重写表与索引，生产常用。

## 写法与建模建议（可直接验证）

• 降低更新密度：将高频更新列与主表分离（如事件日志表），减少主表死元组积累。
• 合理 `fillfactor`：对频繁更新的表设置 70–90 之间；通过上文更新实验验证页回写行为。
• 分区表：高写入/归档场景使用范围分区，结合 `ALTER TABLE ... DETACH PARTITION` 做冷数据归档，降低全表 VACUUM 压力。

## 监控与告警指标

• `pg_stat_user_tables.n_dead_tup` 增速 vs `autovacuum_count`。
• `age(pg_class.relfrozenxid)` 接近阈值告警。
• 活跃 autovacuum 数与阻塞：`pg_stat_activity` 中是否被长事务阻塞（注意长事务会阻止清理）。

## 注意事项

• 长事务与逻辑复制会延迟死元组回收；请避免在大表上长时间未提交的事务。
• `VACUUM FULL` 会需要排他锁与额外空间，请在离峰执行并预留足够磁盘。
• PostgreSQL 13+ 的可见性地图改进使 VACUUM 更高效，但对极端更新负载仍需调参与分区设计。

## 结语

以“可观测—可触发—可治理”的闭环，确保 VACUUM 既能跟上写入节奏，又能在冻结阈值前完成清理。通过上文的 SQL 与参数，运维与开发可在 13–16 版本稳定复现实验并落地生产策略。