PG 表膨胀排查实录：从 82GB 到 12GB

问题背景：表文件持续变大，查询却越来越慢

一次例行巡检中，业务反馈订单历史表的按时间范围查询从 200ms 上升到 3s。慢查询本身不复杂，但执行计划中的 Seq Scan 实际扫描行数明显偏高，缓存命中率也下降。进一步查看表大小发现，真实有效数据约 12GB，表和索引却占了 82GB。

这是典型的 PostgreSQL 表膨胀。PostgreSQL 的 MVCC 机制在更新、删除行时不会立刻覆盖旧版本，而是留下死元组，之后由 autovacuum 清理。正常情况下，死元组占用的空间会进入空闲空间映射，供后续写入复用；但如果 autovacuum 跟不上长事务、大批量更新、空闲空间映射不足或膨胀已经发生，表文件就会越来越大。

膨胀的直接危害有三类：

顺序扫描需要读取更多页面，缓冲区缓存被无效页面挤占。
索引层级变深、索引体积增大，回表与索引扫描成本上升。
备份、WAL、存储和备库重放压力同步增加。

本文记录一次完整处理过程：先用系统视图估算，再用 pgstattuple 精确诊断，最后使用 pg_repack 在线重建，把表从 82GB 缩到 12GB。

方案对比：VACUUM、VACUUM FULL 与 pg_repack

处理膨胀前，要先区分“清理死元组”和“归还操作系统空间”。普通 VACUUM 会清理死元组并更新可见性映射，但通常不会缩小表文件；VACUUM FULL 可以重写整表并归还空间，但会持有强锁，业务运行期间基本不可用；pg_repack 通过触发器捕获增量变化，在线创建新表和新索引，再短暂加锁切换，更适合生产窗口有限的场景。

需要强调，pg_repack 不是零成本工具。重建期间需要额外约一倍表和索引空间，会产生写入放大，并且初始扫描仍有 I/O 成本；切换瞬间也需要短暂元数据锁。因此，先量化膨胀、避开业务高峰、准备取消方案，比直接执行命令更重要。

步骤一：用系统视图快速圈定可疑表

如果还没有安装扩展，可以先通过 pg_stat_user_tables 观察死元组、最后 vacuum 时间和分析情况。下面 SQL 可直接运行，用于找出更新删除频繁、autovacuum 长期未处理的表。

sql

-- 查看用户表的死元组、活跃度和 autovacuum 状态
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(
        100.0 * n_dead_tup /
        NULLIF(n_live_tup + n_dead_tup, 0), 2
    ) AS dead_ratio_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

再结合表物理大小判断是否真的需要瘦身：

sql

-- 按包含索引的总占用空间排序
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_size_pretty(pg_table_size(c.oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

系统视图成本低，适合巡检，但它依赖统计信息，不能准确告诉你页面中有多少空闲空间。比如死元组已经被 vacuum 标记为可复用，n_dead_tup 可能很低，但表文件依旧巨大。这时就需要 pgstattuple。

步骤二：安装并使用 pgstattuple 精确定位膨胀

pgstattuple 是官方贡献扩展，提供页级统计。先在目标数据库安装：

sql

-- 在业务数据库中安装扩展，不是只装在 postgres 库
CREATE EXTENSION IF NOT EXISTS pgstattuple;

对订单历史表执行精确扫描：

sql

-- 全表扫描式统计，结果准确，但大表会产生明显 I/O
SELECT *
FROM pgstattuple('public.order_history');</n```

重点字段如下：

- `table_len`：表当前物理长度。
- `tuple_count`：有效元组数。
- `tuple_len`：有效元组总长度。
- `dead_tuple_count` 与 `dead_tuple_len`：死元组数量和占用空间。
- `free_space`：页面内空闲空间，包含可复用但未归还操作系统的空间。
- `free_percent`：空闲比例，是判断历史膨胀的重要指标。

本次问题表的结果中，`dead_tuple_percent` 只有 3%，但 `free_percent` 超过 78%。这解释了为什么普通 vacuum 无法让表变小：死元组已经基本清理，空间留在了表文件内部。

如果想减少扫描成本，可以使用 `pgstattuple_approx`，它通过可见性映射跳过已知全部可见的页面，速度更快，适合大表初筛：

```sql
-- 近似统计，适合先扫库筛选，再对重点表做精确确认
SELECT
    relname,
    pg_size_pretty(table_len) AS table_len,
    tuple_count,
    dead_tuple_count,
    round(dead_tuple_percent::numeric, 2) AS dead_pct,
    round(approx_free_percent::numeric, 2) AS free_pct,
    pg_size_pretty(approx_free_space) AS free_space
FROM pgstattuple_approx('public.order_history'::regclass);

还可以单独检查索引膨胀。索引膨胀往往比表更隐蔽，因为索引字段多、碎片页多，重建后范围扫描和嵌套循环连接都可能变快。

sql

-- 查看某个索引的页面占用与碎片情况
SELECT *
FROM pgstatindex('public.idx_order_history_created_at');

步骤三：用 pg_repack 在线瘦身

确认膨胀后，小表可以在维护窗口执行 VACUUM FULL public.order_history;，但这张 82GB 的表不能接受长时间锁表，因此选择 pg_repack。

服务器端需要安装与 PostgreSQL 大版本匹配的 pg_repack 软件包，并在数据库中创建扩展：

sql

-- pg_repack 服务端扩展
CREATE EXTENSION IF NOT EXISTS pg_repack;

命令行建议先使用 --dry-run 做条件检查：

bash

# 检查扩展、主键、权限和连接信息，不真正执行重建
pg_repack \
  --host=10.0.0.11 \
  --port=5432 \
  --username=postgres \
  --dbname=orders \
  --table=public.order_history \
  --dry-run

正式执行时限制单表并行度，避免把 I/O 打满。生产环境建议放在低峰期，并开启时间输出：

bash

# 在线重建单表，-j 控制建索引并发，-e 输出执行命令
pg_repack \
  --host=10.0.0.11 \
  --port=5432 \
  --username=postgres \
  --dbname=orders \
  --table=public.order_history \
  --jobs=2 \
  --wait-timeout=30 \
  --elevel=INFO

pg_repack 的核心流程是：创建日志表与触发器记录增量变化，创建新表并批量复制旧数据，创建索引并校验，然后在短事务中锁表、重放剩余日志、切换表名。流程如下图：

重建完成后，刷新统计信息并复查：

sql

-- 重建后收集统计信息，避免规划器仍使用旧成本
ANALYZE public.order_history;

-- 复查物理大小
SELECT
    pg_size_pretty(pg_table_size('public.order_history')) AS table_size,
    pg_size_pretty(pg_indexes_size('public.order_history')) AS index_size,
    pg_size_pretty(pg_total_relation_size('public.order_history')) AS total_size;

-- 再次确认空闲率
SELECT
    tuple_count,
    round(tuple_percent::numeric, 2) AS tuple_pct,
    round(dead_tuple_percent::numeric, 2) AS dead_pct,
    round(free_percent::numeric, 2) AS free_pct,
    pg_size_pretty(free_space) AS free_space
FROM pgstattuple('public.order_history');

本次重建后，表和索引总大小从 82GB 降到 12.6GB，缓冲区命中率恢复，典型时间范围查询回到 180ms 左右。

避坑总结：生产环境至少注意这 5 点

不要把 n_dead_tup 低误认为没有膨胀。 常见现象是 autovacuum 已经执行过，n_dead_tup 接近 0，但查询仍慢、表文件仍很大。原因是死元组空间变成了页内空闲空间，普通 vacuum 不会收缩文件。应使用 pgstattuple 或 pgstattuple_approx 查看 free_percent 和 free_space。
不要在业务高峰直接跑全量 pgstattuple。 该函数会读取表页面，大表可能造成明显 I/O 抖动。错误做法是对几十 GB 表逐个执行精确统计；正确做法是先用 pgstattuple_approx 或系统视图筛选，再对最可疑的表精确确认。
不要忽略磁盘剩余空间。 pg_repack 重建期间会创建新表和新索引，通常需要接近原表总大小的额外空间。若磁盘只剩 20%，重建可能在中途失败并留下临时对象。执行前应检查表空间所在磁盘，预留至少 表大小 + 索引大小 + 重建期间增量 的空间。
不要忽视长事务和锁等待。 如果有长查询、未提交事务或 DDL 锁占用目标表，pg_repack 最后切换可能超时。执行前应查询 pg_stat_activity 和 pg_locks，确认没有长时间闲置事务；必要时设置合理的 --wait-timeout，不要无限等待。
不要重建完就结束。 表结构切换后如果不执行 ANALYZE，规划器可能短时间内仍选择较差计划。重建后应立即收集统计信息，观察 EXPLAIN (ANALYZE, BUFFERS)、缓存命中率和慢查询，确认收益真正落地。

最后，瘦身只是止血，避免反复膨胀还要调整 autovacuum 参数。对高频更新表，可适当降低 autovacuum_vacuum_scale_factor，提高 autovacuum_vacuum_cost_limit，并定期监控膨胀率，才能把空间和查询性能长期稳定在合理范围。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

PG 表膨胀排查实录：从 82GB 到 12GB ​

问题背景：表文件持续变大，查询却越来越慢 ​

方案对比：VACUUM、VACUUM FULL 与 pg_repack ​

步骤一：用系统视图快速圈定可疑表 ​

步骤二：安装并使用 pgstattuple 精确定位膨胀 ​

步骤三：用 pg_repack 在线瘦身 ​

避坑总结：生产环境至少注意这 5 点 ​