如何定位RDS PostgreSQL 14.4实例存储空间膨胀的缺口来源？

问题背景 #

使用RDS PostgreSQL 14.4实例，迁移完成时数据库总大小为46GB，一周内增至81GB。已完成以下排查：

• 死行总数：执行SELECT SUM(n_dead_tup) FROM pg_stat_all_tables WHERE n_dead_tup > 0;结果为114836，占存储空间比例不显著
• 数据库总大小：SELECT pg_size_pretty(pg_database_size('db_production'));返回81GB
• 表数据大小总和：SELECT pg_size_pretty(SUM(pg_relation_size(oid))) FROM pg_class;返回46GB
• 表+索引+TOAST大小总和：SELECT pg_size_pretty(SUM(pg_total_relation_size(oid))) FROM pg_class;返回70GB，仍有11GB存储空间缺口
  无法执行全库VACUUM FULL，需手动定位缺口来源并解决。

排查方向 #

1. 检查WAL日志占用 #

PostgreSQL预写日志（WAL）若未及时归档或保留过多，会占用大量空间：

• 查看当前WAL文件总大小：

  SELECT pg_size_pretty(SUM(size)) FROM pg_ls_waldir();
  

• 检查归档状态，确认是否存在归档失败：

  SELECT * FROM pg_stat_archiver;
  

• 查看RDS参数组中的wal_keep_size（保留WAL文件大小上限）和archive_mode（是否开启归档）配置。

2. 排查临时文件 #

大查询（如排序、哈希连接）会生成临时文件，若查询长时间运行，临时文件会持续占用空间：

• 通过CloudWatch监控FreeStorageSpace指标，观察空间是否随查询波动
• 识别生成临时文件的活跃会话：

  SELECT pid, query, temp_files, temp_bytes FROM pg_stat_activity WHERE temp_bytes > 0;
  

• 检查参数temp_file_limit是否限制了单进程临时文件大小。

3. 核查数据库日志文件 #

RDS的PostgreSQL日志若保留时间过长，会累积占用空间：

• 在RDS控制台查看log_retention_period参数（日志保留天数）
• 若日志占用过多，可缩短保留周期，RDS会自动清理超出保留期的日志。

4. 检查表空间与孤儿文件 #

• 查看各表空间的占用情况，确认是否有异常表空间：

  SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) FROM pg_tablespace;
  

• 若业务允许短时间锁表，可针对单个大表执行VACUUM FULL；或使用pg_repack在线整理表空间（需先在RDS参数组开启shared_preload_libraries = 'pg_repack'，再创建扩展CREATE EXTENSION pg_repack;），排查未被统计的孤儿文件。

5. 单独核查TOAST表 #

虽然pg_total_relation_size已包含TOAST表，仍可单独确认其占用：

SELECT relname AS toast_table, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 't'
ORDER BY pg_total_relation_size(oid) DESC;

解决建议 #

• WAL堆积：若归档失败，修复归档配置（如S3权限、归档命令）；调整wal_keep_size至合理值，避免保留过多不必要的WAL。
• 临时文件：优化大查询（添加索引、拆分查询），调整work_mem参数减少临时文件生成；终止长时间运行的查询释放临时空间。
• 日志占用：缩短日志保留周期，或开启日志自动轮转。
• 孤儿文件/表膨胀：使用pg_repack在线整理表空间替代VACUUM FULL；针对单个膨胀表执行VACUUM FULL（需评估业务影响）。

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