PostgreSQL表膨胀监控最佳实践：3TB库每周扫描方案咨询

针对你的场景（3TB数据量、30万张表，每周执行膨胀扫描），直接给出可落地的实践方案：

排除不可行方案 #

• 基于统计信息的Wiki查询：这类方法依赖pg_stat_user_tables中的n_live_tup、n_dead_tup等采样统计值，误差极大（尤其是大表、更新频繁的表），且统计信息更新不及时，早已不适合生产环境的精准监控，直接排除。
• 全量pgstattuple全表扫描：3TB全量扫描会带来巨大IO压力，30万张表循环执行总耗时极长，即使单表事务也会持续占用系统资源，严重影响业务，不可行。

最优方案：pgstattuple_approx 采样扫描 #

pgstattuple_approx是PostgreSQL 12+版本pgstattuple扩展提供的采样扫描函数，完美适配大规模库的膨胀监控需求：

• 低IO开销：默认采样1%的数据页面（可调整），无需全表扫描，速度比pgstattuple快数十倍
• 可接受的准确性：采样误差在生产监控的容忍范围内，核心表可通过调整采样页面数提升精度
• 易落地：单表调用即可，无需复杂逻辑

具体实施步骤 #

1. 先安装扩展：

   CREATE EXTENSION IF NOT EXISTS pgstattuple;
   

2. 编写脚本循环遍历表（按schema+表名或OID），单表执行：

   SELECT schemaname, relname, approx_tuple_percent AS live_tuple_rate, 
          100 - approx_tuple_percent AS bloat_rate
   FROM pgstattuple_approx('your_schema.your_table');
   

3. 优化执行策略：
   • 分批次/低峰期执行：将30万张表拆分到每周的不同低峰时段（比如凌晨），避免一次性打满IO
   • 按业务优先级扫描：核心业务表优先扫描，非核心表拉长扫描周期（比如每月一次）
   • 控制并发数：用脚本限制同时扫描的表数量（比如5-10个并发），避免系统资源过载
   • 调整采样精度：对核心表可指定采样页面数，比如pgstattuple_approx('core_table', 2000)（采样2000个页面），平衡速度与精度

补充优化建议 #

• 增量扫描替代全量：不要每周扫所有表，改为分4批，每周扫1/4，每月完成一轮全量扫描，进一步分散IO压力
• 结合表更新频率：通过pg_stat_user_tables的n_dead_tup、last_autovacuum等字段筛选出更新频繁的表，仅对这类表定期扫描；静态表（无更新）可每季度扫一次即可

内容的提问来源于stack exchange，提问作者Peter