PostgreSQL / 技术文章

PostgreSQL DELETE 后磁盘为何不变小

批量清理历史数据后,SQL 查询已经看不到这些行,监控中的磁盘占用却可能纹丝不动。这种现象常让 DBA、SRE 和后端工程师误判 DELETE 没有生效。真正需要区分的是“数据是否可见”“表内空间是否可复用”和“文件是否归还操作系统”三个问题;分清这三层后,就能决定应该等待 VACUUM、处理索引膨胀,还是安排代价更高的文件重写。

DELETE 删除的是可见性,不是立刻缩短文件

PostgreSQL 使用 MVCC 保存并发事务所需的行版本。执行 DELETE 时,目标行不会立即从数据页中物理擦除,而是被标记为对后续事务不可见。只要仍有旧快照可能需要看到这些版本,它们就不能被直接回收。

因此,大批量 DELETE 完成后通常会同时出现两个事实:业务查询已经查不到被删除的数据,但堆表文件仍然保留原有的数据页。操作系统看到的是关系文件占用了多少块,而不是其中还有多少业务上可见的行。

可以先同时观察活跃行、死元组估算值和关系大小:

sql
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname = 'event_history';

预期结果是 DELETE 后 n_live_tup 下降,而 n_dead_tup 可能上升;table_size 和 total_size 通常不会按删除的数据量同步下降。统计视图中的行数是估算值,必要时应结合 ANALYZE、表大小变化和实际业务行数判断。

VACUUM 回收的是表内可复用空间

当旧版本不再可能被任何事务访问时,VACUUM 可以清理 dead tuple,并把相应空间重新交给这个表使用。后续 INSERT 或 UPDATE 可以复用这些空闲位置,因此即使操作系统层面的文件大小没有变化,数据库内部仍可能已经完成了有效的空间回收。

sql
VACUUM (ANALYZE, VERBOSE) event_history;

执行后可以再次查看统计信息:

sql
SELECT
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'event_history';

如果 VACUUM 已经成功处理可回收的死元组,n_dead_tup 通常会下降。此时最重要的判断不是“文件有没有立刻变小”,而是“这些页能否被后续写入重新利用”。普通 VACUUM 的主要目标正是恢复可复用空间,而不是压缩整个关系文件。

普通 VACUUM 在满足条件时可以截断关系文件尾部连续的空页,因此文件偶尔也会缩小,但这要求可释放页面集中在文件末尾,不能把它当作批量 DELETE 后必然发生的行为。大量空洞散布在文件中间时,文件尺寸通常不会明显变化。

长事务可能让 VACUUM 暂时无能为力

如果数据库中存在长时间持有旧快照的事务,刚删除的旧行版本仍可能被这些事务访问,VACUUM 就不能安全移除它们。此时连续执行 VACUUM 也未必能解决问题,反而应该先确认是否存在异常长事务。

sql
SELECT
    pid,
    usename,
    state,
    xact_start,
    now() - xact_start AS xact_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

预期结果中,如果发现持续时间远超业务正常事务窗口的会话,需要先判断它是否持有业务必需的事务,再按照故障处置流程决定提交、回滚或终止。不要为了释放空间直接终止未知会话,因为长事务可能正在执行重要写入,回滚本身也可能具有较高成本。

除了普通业务事务,还需要关注复制槽、备库反馈等可能影响旧版本清理边界的因素。判断重点始终是:VACUUM 是否因为可见性边界无法清理,而不是单纯看它是否执行过。

表变小了还不够,索引和 TOAST 也要单独看

pg_relation_size() 只观察指定关系本身,而 pg_total_relation_size() 还包含索引和 TOAST 等相关存储。大规模 DELETE 会同时让表、索引以及可能存在的 TOAST 数据产生可回收空间,因此只盯着主表文件容易漏掉真正占用磁盘的对象。

sql
SELECT
    pg_size_pretty(pg_relation_size('event_history')) AS heap,
    pg_size_pretty(pg_indexes_size('event_history')) AS indexes,
    pg_size_pretty(pg_total_relation_size('event_history')) AS total;

如果 indexes 占比很高,即使堆表内部已经出现大量可复用空间,总磁盘占用仍可能主要来自索引。普通 VACUUM 可以清理失效索引项并改善后续复用条件,但不会像重建索引那样把所有空洞重新压紧成一个更小的索引文件。

需要真正压缩索引时,可以评估 REINDEX。线上环境若要降低阻塞,应根据 PostgreSQL 版本、索引类型和剩余磁盘评估 REINDEX CONCURRENTLY,因为并发重建需要额外空间,执行期间旧索引也不能立即消失。

真正归还操作系统磁盘需要重写或移除文件

如果目标不是让 PostgreSQL 将空间留给未来写入,而是必须降低文件系统占用,就需要选择能够重写或直接移除关系文件的操作。最典型的是 VACUUM FULL:它把仍然存活的数据重新写入紧凑的新文件,从而消除散布在旧文件中的空洞。

sql
VACUUM (FULL, ANALYZE) event_history;

完成后再次检查:

sql
SELECT
    pg_size_pretty(pg_relation_size('event_history')) AS heap,
    pg_size_pretty(pg_total_relation_size('event_history')) AS total;

预期结果是在删除比例足够高且存在可压缩空间时,关系尺寸明显下降。但 VACUUM FULL 需要对目标表取得 ACCESS EXCLUSIVE 锁,并执行整表重写;大表上会带来明显的锁窗口、I/O 和额外磁盘空间需求,因此不应仅因为 DELETE 后文件没变小就立即在线执行。

如果过期数据天然按时间分区隔离,DROP TABLE、删除旧分区,或在生命周期流程中先 DETACH PARTITION 再受控删除独立分区,通常比对一个超大普通表反复 DELETE 后再重写更适合周期性治理。它们处理的是整个关系文件,因此空间释放路径与逐行 DELETE 完全不同。

用三个问题决定下一步动作

面对“DELETE 已完成但磁盘没下降”,可以按三个层次判断。第一,删除行是否还因为旧快照而无法清理;如果是,应先处理长事务等可见性障碍,再让 VACUUM 工作。第二,释放出来的空间是否会很快被同一张表重新使用;如果会,普通 VACUUM 后保留文件通常反而能避免未来再次扩展文件。第三,是否存在明确的操作系统磁盘回收目标;只有答案为是时,才值得评估 VACUUM FULL、索引重建、表重建或分区级删除。

因此,“DELETE 后磁盘不变小”本身通常不是故障。DELETE 负责让旧行退出可见数据集,VACUUM 负责在安全时把空间转为数据库内部可复用,关系重写或文件级删除才负责把大量空间真正归还操作系统。把这三个阶段分别监控,才能避免一看到磁盘没下降就执行高风险重写,也能在容量确实逼近上限时准确找到应该处理的层级。

目录与阅读设置

记录一个想法

PostgreSQL DELETE 后磁盘为何不变小

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通