PostgreSQL常规VACUUM未释放开销占用磁盘空间问题排查

真实原因定位 #

你遇到的定时VACUUM导致磁盘临时膨胀、仅VACUUM FULL可回收空间的问题，核心源于以下两点：

1. 大规模索引死条目清理的临时空间开销
   关闭自动VACUUM后，每日积累了海量索引死条目（每秒75次更新持续24小时，仅B树/哈希索引的死条目数量就非常可观）。手动执行VACUUM时，PostgreSQL需要对两类索引进行死条目清理：
   • B树索引清理时会尝试页面合并，但若死条目分布零散，合并过程中需临时分配新页面存放整理后的索引数据，这些新页面会占用额外磁盘空间，且普通VACUUM不会将旧的空闲页面返还给操作系统。
   • 哈希索引的清理逻辑会扫描所有哈希桶标记死条目，处理大规模死条目时可能触发桶的重新组织，导致分配新磁盘块，进一步推高磁盘占用。
2. 表空间碎片化的“锁定效应”
   日常更新时，死元组的空间被即时复用，无需分配新块；但手动VACUUM一次性标记所有死元组为空闲后，PostgreSQL的表空间管理会暂时保留这些空闲块（避免频繁分配/释放的开销），而你的随机更新模式无法快速填满这些空闲块，导致磁盘占用看起来持续增加。只有VACUUM FULL会彻底重构表和索引，将未使用的磁盘块返还给操作系统。

磁盘占用稳定的解决方案 #

针对你行数和data数组长度恒定的场景，可采用以下方案：

1. 恢复自动VACUUM并优化参数 #

关闭自动VACUUM是死元组和索引死条目大量积累的根源，恢复后针对profile表设置精细化参数，降低单次清理压力：

ALTER TABLE profile SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0,
  autovacuum_analyze_threshold = 20000
);

通过固定阈值触发自动VACUUM（死元组达10000条时启动），避免积累大量数据后一次性清理的磁盘开销。同时调整全局参数提升清理频率：

SET autovacuum_max_workers = 3;
SET autovacuum_naptime = 60;

2. 替换哈希索引为B树索引 #

PostgreSQL哈希索引在高频更新场景下的清理效率、空间复用性远不如B树索引，而user_id作为主键本身具备唯一性，完全可以用B树索引替代：

DROP INDEX idx_profile_user_id_hash;
CREATE UNIQUE INDEX idx_profile_user_id_btree ON profile(user_id);

B树索引的死条目清理机制更成熟，能显著减少清理过程中的临时空间占用。

3. 改用VACUUM ANALYZE替代单纯VACUUM #

手动清理时执行VACUUM ANALYZE，在清理死元组的同时更新统计信息，且其清理逻辑更温和，临时空间开销更低：

VACUUM ANALYZE profile;

添加VERBOSE参数可查看清理细节，确认死元组和索引死条目的处理情况：

VACUUM VERBOSE ANALYZE profile;

4. 拆分定时清理任务 #

若必须保留手动定时清理，将每日一次的全量VACUUM改为每2-4小时一次的轻量清理，减少单次处理的数据量：
用pg_cron设置定时任务：

SELECT cron.schedule('every-3-hours-vacuum-profile', '0 */3 * * *', 'VACUUM ANALYZE profile;');

5. 限制VACUUM FULL的使用 #

VACUUM FULL会锁表影响业务性能，仅在磁盘空间紧张时作为应急手段。日常通过自动VACUUM或定时轻量清理，即可维持空间复用的平衡，无需频繁使用。

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