PostgreSQL数据库大小超出预期3倍，VACUUM FULL无法释放空间咨询

根因定位 #

你遇到的是PostgreSQL典型的孤立物理文件残留问题，该问题和VACUUM机制无关，因此执行VACUUM FULL不会有任何效果，常见触发原因如下：

• 长事务/2PC预备事务残留：执行TRUNCATE、DROP表操作时，PostgreSQL需要等所有持有该表快照的事务结束后，才会删除旧的物理文件。如果存在运行数天甚至更久的未提交长事务，或者异常残留的2PC预备事务，会导致旧文件永远不会被系统自动清理，最终变成pg_class中无对应记录的孤立文件。
• 临时表异常残留：业务使用的临时表在会话断开后，元数据会从pg_class中移除，如果会话断开时发生实例崩溃、IO异常等问题，对应的物理文件可能不会被同步删除，长期积累就会形成大量孤立文件。
• 操作过程中实例异常中断：执行TRUNCATE/DROP操作时，如果PostgreSQL进程被OOM杀掉、服务器宕机、磁盘IO故障，可能出现元数据已经从pg_class删除，但物理文件删除操作未执行的情况，重启后系统不会重新扫描清理这类文件，就会永久残留。

排查步骤 #

1. 首先排查残留长事务与预备事务：
   查询运行时间最长的10个事务：
   SELECT pid, datname, usename, state, now() - xact_start AS xact_duration FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_duration DESC LIMIT 10;
   查询残留的2PC预备事务：
   SELECT gid, prepared, owner, database FROM pg_prepared_xacts;
   若存在超过24小时的无用长事务或预备事务，kill对应进程或回滚预备事务后，观察系统是否会自动清理部分孤立文件。
2. 确认孤立文件总大小：
   进入data/base/[comb库对应的OID]目录，执行如下命令统计所有孤立文件的总大小，确认是否和600G左右的异常膨胀空间匹配：

   find . -type f -regex '.*/[0-9]+$' -printf "%f %s\n" | awk '
   NR==FNR { arr[$1] = 1; next }
   !arr[$1] { sum += $2 }
   END { print "孤立文件总大小：" sum/1024/1024/1024 " GB" }
   ' <(psql -d comb -t -c "SELECT relfilenode FROM pg_class WHERE relfilenode != 0") -
   

解决建议 #

短期快速释放空间方案 #

• 可停机场景：停止业务写入后做全量逻辑备份，确认备份可用后删除原有数据库实例，重建实例导入备份，即可完全清理所有孤立文件，空间恢复到300G左右的预期大小。
• 不可停机场景：搭建新的PostgreSQL实例，通过逻辑复制同步当前库的全量+增量数据，同步完成后切换业务流量到新实例，下线旧实例即可。

长期规避方案 #

• 配置长事务监控告警，对运行超过1小时的事务及时告警处理，避免长事务长期持有快照
• 每次执行批量TRUNCATE操作前，先确认没有未结束的事务访问待操作的表
• 每季度执行一次孤立文件排查，提前清理小体积的残留文件，避免积累到数百G的规模

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