PostgreSQL RDS磁盘数据库大小与实际表大小差异过大问题排查

执行以下查询得到数据库总大小为459GB：

SELECT
    pg_size_pretty(pg_database_size('MY_DB')) AS total_database_size;

但执行表大小统计语句后，结果总和不足10GB：

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC;

已执行vacuum analyse无效果，补充环境信息：

• 使用DBT（会创建临时表，当前未运行）；
• 使用Fivetran导入数据。

可能原因及排查步骤 #

1. 统计范围不完整 #

当前查询仅计算了表本身的大小（pg_relation_size），但数据库总大小包含索引、TOAST大字段表、序列、物化视图等所有对象，以及WAL日志、临时文件。先修改查询统计完整用户对象大小：

SELECT 
    nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND relkind IN ('r', 'i', 'm') -- 覆盖表、索引、物化视图
ORDER BY pg_total_relation_size(C.oid) DESC;

若总和仍远小于459GB，继续排查以下点。

2. WAL日志未及时归档 #

PostgreSQL的pg_wal目录存储事务日志，若归档配置失效或同步异常，WAL文件会持续堆积占用空间。

• 检查WAL目录大小：

cd $(psql -d MY_DB -t -c "SHOW data_directory;")
du -sh pg_wal/

• 验证归档配置：

SHOW archive_mode;
SHOW archive_command;

确保archive_mode为on且archive_command可正常执行，修复后用pg_archivecleanup工具清理过期WAL（禁止手动删除文件）。

3. 残留临时对象或长事务阻塞回收 #

• DBT或Fivetran异常终止时，可能残留未清理的临时表；

• 长事务持有快照，会导致DROP后的对象无法被VACUUM回收。

• 查询当前临时对象：

SELECT nspname || '.' || relname AS temp_relation,
       pg_size_pretty(pg_total_relation_size(C.oid)) AS size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname LIKE 'pg_temp%';

若存在大量临时对象，重启PostgreSQL可清空所有临时对象。

• 查询长事务：

SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;

终止长时间idle的事务后，在业务低峰执行VACUUM FULL（注意该操作会锁表）。

4. Fivetran同步日志或中间表占用 #

Fivetran通常会创建fivetran_log等专属schema，存储同步日志、错误记录或中间表，这些可能占用大量空间：

SELECT 
    nspname AS schema_name,
    pg_size_pretty(sum(pg_total_relation_size(C.oid))) AS schema_total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname LIKE 'fivetran%'
GROUP BY nspname;

若日志表过大，可按Fivetran文档调整日志保留策略或清理旧记录。

5. 数据目录其他异常文件 #

扫描数据库数据目录下所有子目录的大小，定位异常占用：

cd $(psql -d MY_DB -t -c "SHOW data_directory;")
du -sh */

重点检查pg_blobs（大对象存储）、pg_stat_tmp等目录是否异常。

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