PostgreSQL VACUUM 与表膨胀治理：MVCC 死元组、Autovacuum 调优与膨胀诊断

UPDATE users SET email = 'new@example.com' WHERE id = 1;

页面示意（一行数据）：
  Tuple v1: email='old@example.com'  xmin=100, xmax=200  ← 死元组
  Tuple v2: email='new@example.com'  xmin=200, xmax=0    ← 活元组

-- 观察当前事务状态对死元组回收的影响
SELECT pid, state, xact_start, backend_xid, backend_xmin
FROM pg_stat_activity;
-- backend_xmin 是当前会话认为最早可见的事务，VACUUM 不能清理比它更新的死元组

-- 手动对单表执行 VACUUM
VACUUM (VERBOSE, ANALYZE) users;

-- 查看某表的死元组与最近 vacuum 时间
SELECT relname, n_dead_tup, n_live_tup,
       last_vacuum, last_autovacuum, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'users';

-- 检查表的冻结年限（age）
SELECT relname, age(relfrozenxid) AS freeze_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC LIMIT 20;
-- 接近 2 亿（autovacuum_freeze_max_age 默认值）时需立即处理

触发阈值 = autovacuum_vacuum_threshold
           + (autovacuum_vacuum_scale_factor × 表行数)

默认：50 + 0.2 × reltuples

-- 查看当前 autovacuum 配置
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_cost_delay;

-- 高频更新的热点表：更激进
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);

-- 超大型归档表：放宽阈值减少扫描频率
ALTER TABLE archive_logs SET (autovacuum_vacuum_scale_factor = 0.4);

-- 查看表级参数
SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 统计表：死元组比例、空余空间
SELECT * FROM pgstattuple('orders');

-- 只取关键字段
SELECT table_len,
       dead_tuple_count,
       dead_tuple_percent,
       free_percent
FROM pgstattuple('orders');

-- 全库死元组最多的前 20 张表
SELECT
  schemaname, relname,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / GREATEST(n_live_tup, 1), 3) AS dead_ratio,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 判断表是否"真膨胀"：对比实际大小与活元组理论大小
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS actual_size,
  pg_size_pretty(n_live_tup * 300) AS approx_live_size
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY (pg_total_relation_size(relid) / GREATEST(n_live_tup, 1)) DESC
LIMIT 10;

-- pgstatindex 查看索引的空余率
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_uid');

-- 关键字段
SELECT index_size,
       dead_tuples,
       free_space,
       free_percent
FROM pgstatindex('idx_orders_uid');
-- free_percent 超过 20~30% 说明索引明显膨胀

-- 在线重建（PG 12+，不阻塞读写）
REINDEX INDEX CONCURRENTLY idx_orders_uid;
REINDEX TABLE CONCURRENTLY orders;

-- 非生产环境可直接重建
REINDEX TABLE orders;

-- 全量重写表，压缩到最小体积
VACUUM FULL orders;

-- 带统计更新
VACUUM (FULL, ANALYZE) orders;

-- 按指定索引的键序重排表（同时重建全部索引）
CLUSTER orders USING idx_orders_created;

-- 不加索引名：使用表上次 CLUSTER 的索引
CLUSTER orders;

-- 重排后再次按序扫描性能大幅提升
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-09-01';

-- 为热点表预留 20% 页内空间，提升 HOT 命中率
ALTER TABLE orders SET (fillfactor = 80);

-- 观察 HOT 更新比例
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / GREATEST(n_tup_upd, 1), 1) AS hot_percent
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY hot_percent ASC
LIMIT 20;
-- HOT 比例过低 → 检查是否有 UPDATE 索引列、fillfactor 是否过高

-- 反模式：高频更新大文本字段（每次都是全行重写）
UPDATE sessions SET payload = payload || '...' WHERE session_id = ?;

-- 改进：把频繁变化的字段抽到子表，或使用 JSONB 局部更新
-- JSONB 局部更新（对 jsonb 列使用 || 只重写受影响部分）
UPDATE sessions
SET metadata = jsonb_set(metadata, '{last_seen}', to_jsonb(NOW()))
WHERE session_id = ?;

-- 找出"最需要 VACUUM"的表
SELECT
  schemaname, relname,
  n_live_tup, n_dead_tup,
  CASE WHEN n_live_tup > 0
       THEN round(100.0 * n_dead_tup / n_live_tup, 1)
       ELSE 0 END AS dead_pct,
  last_autovacuum,
  pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC
LIMIT 20;

# cron 每天执行，输出膨胀前 10 表
psql "$DATABASE_URL" <<'SQL'
\pset format unaligned
\pset footer off
SELECT '膨胀预警: ' || relname || ' dead_pct=' ||
       round(100.0 * n_dead_tup / n_live_tup, 1) || '%'
FROM pg_stat_user_tables
WHERE n_live_tup > 0
  AND 100.0 * n_dead_tup / n_live_tup > 20
ORDER BY n_dead_tup DESC LIMIT 10;
SQL