PostgreSQL 18.6 存储运维实战（第 6 篇）：DELETE 清空九成数据，磁盘为什么几乎没变

历史表删掉九成数据count(*)已下降磁盘告警却没有解除。普通VACUUM跑完文件仍很大于是团队准备在高峰期执行VACUUM FULL。DELETE、空间可复用、文件缩小是三个不同结果。先判断容量究竟被 heap、索引还是 TOAST 占用再决定要复用、重写还是从数据生命周期上避免逐行删除。先给“空间释放”定义口径同一句“释放了空间”在 PostgreSQL 中可能指四件事口径观察对象DELETE 后普通 VACUUM 后VACUUM FULL 后行对新查询不可见SQL 结果是是是dead tuple 可清理MVCC 版本等待安全 horizon可被清理重写时消失relation 内可复用FSM / 页内空闲不一定通常增加紧凑后剩余较少文件归还文件系统relation 文件尺寸通常否通常否仅可能截尾通常是普通VACUUM的首要工作是回收 dead tuple 占用使空间可被同一 relation 后续复用而不是把每个空洞搬到文件尾。它没有重写整张表因此中间页面的空闲不会自动变成可截掉的连续尾部。三阶段实验行数、内部空间和文件尺寸不是同步变化前提与控制变量在独立测试库运行预留至少数百 MB 空间。测试规模可按机器能力调整但三个阶段必须使用同一张表。不要在生产大表上为了复现实验执行VACUUM FULL。DROPTABLEIFEXISTSevent_log;CREATETABLEevent_log(idbigintPRIMARYKEY,created_at timestamptzNOTNULL,payloadtextNOTNULL);INSERTINTOevent_logSELECTg,timestamptz2026-08-30 00:0008-g*interval1 second,repeat(md5(g::text),8)FROMgenerate_series(1,1000000)ASg;VACUUM(ANALYZE)event_log;统一用下面的查询记录尺寸。pg_table_size包含表主 fork、FSM、VM 和 TOASTpg_indexes_size是索引总量pg_total_relation_size是二者合计。SELECTpg_size_pretty(pg_relation_size(event_log))ASmain_fork,pg_size_pretty(pg_table_size(event_log))AStable_total,pg_size_pretty(pg_indexes_size(event_log))ASindexes_total,pg_size_pretty(pg_total_relation_size(event_log))ASrelation_total;阶段一逐行删除九成数据DELETEFROMevent_logWHEREid900000;SELECTcount(*)FROMevent_log;SELECTpg_size_pretty(pg_relation_size(event_log))ASmain_fork,pg_size_pretty(pg_indexes_size(event_log))ASindexes_total,pg_size_pretty(pg_total_relation_size(event_log))ASrelation_total;预期count(*)只剩十万但 relation 尺寸变化很小。DELETE 写入新 MVCC 状态使旧 tuple 对之后快照不可见它不会逐行把物理文件压紧还会产生 WAL、更新索引清理状态并增加 vacuum 债务。阶段二普通 VACUUMVACUUM(VERBOSE,ANALYZE)event_log;SELECTpg_size_pretty(pg_relation_size(event_log))ASmain_fork,pg_size_pretty(pg_indexes_size(event_log))ASindexes_total,pg_size_pretty(pg_total_relation_size(event_log))ASrelation_total;没有旧快照阻挡时VACUUM 能移除 dead tuple使页面空闲信息进入 Free Space Map。FSM 为 heap 和绝大多数索引记录 relation 内可用空间后续插入可以优先复用这些页面而不必继续扩展文件。文件仍可能不缩小。这不是 VACUUM 失败只有 relation 尾部形成连续空页并且截尾阶段能取得所需锁时普通 VACUUM 才可能把末尾部分归还操作系统。本文删除的是低id物理空洞主要位于旧页面不保证位于文件尾。这一步证明空间能够从“不可复用的 dead tuple”转为“relation 内可复用”仅凭文件尺寸不能证明复用量反过来n_dead_tup降低也不等于文件系统得到容量。阶段三VACUUM FULL先确认当前空闲磁盘、锁窗口、WAL 和副本承受能力再只在测试环境执行VACUUM(FULL,ANALYZE)event_log;SELECTpg_size_pretty(pg_relation_size(event_log))ASmain_fork,pg_size_pretty(pg_indexes_size(event_log))ASindexes_total,pg_size_pretty(pg_total_relation_size(event_log))ASrelation_total;VACUUM FULL把仍存活的内容写入新的紧凑文件通常能显著缩小 relation代价是重写、额外磁盘空间和表级ACCESS EXCLUSIVE锁。它与可以并发读写的普通 VACUUM 不是“强力版”和“普通版”的简单关系而是不同维护操作。实验的实际字节数会因页面布局、元组宽度、版本和文件系统变化。应验证方向不应把某个压缩比当作容量承诺。为什么“删了低 id”与“删了高 id”可能不同如果插入大致按id递增较新的高id更靠近 relation 尾部。删除连续的高id后普通 VACUUM 更有机会截掉尾部空页删除低id则往往留下大量位于文件中间的空洞。但物理位置不是 SQL 契约并发写入、HOT 链、页面复用、fillfactor、聚簇历史都会改变分布。不能根据主键大小直接断言哪些 block 可截断必须用实际尺寸和页面证据验证。普通 VACUUM 的截尾还可能短暂需要更强的锁TRUNCATE false可关闭该行为以避免相关锁影响但也放弃尾部归还。是否调整应基于锁证据和容量目标而不是套用固定参数。先回答空间到底在 heap、索引还是 TOAST总尺寸大不等于 heap 膨胀。排障先拆分 relationSELECTc.oid::regclassASrelation,pg_size_pretty(pg_relation_size(c.oid))ASmain_fork,pg_size_pretty(pg_table_size(c.oid))AStable_with_toast,pg_size_pretty(pg_indexes_size(c.oid))ASindexes,pg_size_pretty(pg_total_relation_size(c.oid))AStotalFROMpg_classAScWHEREc.oidevent_log::regclass;再看统计趋势SELECTn_live_tup,n_dead_tup,n_tup_ins,n_tup_upd,n_tup_del,last_autovacuum,autovacuum_countFROMpg_stat_user_tablesWHERErelidevent_log::regclass;这些是估算或累计统计并非即时精确事实。要判断真实 bloat需结合采样扩展、时间序列和维护窗口不要用n_dead_tup / n_live_tup单一比例直接决定执行重写。索引也需要单独判断。即使 heap 页面可复用索引可能仍有待清理项或结构性空闲反过来索引尺寸大也可能只是业务需要的多组宽索引并非异常膨胀。TOAST 则可能因为大字段版本产生独立体积。长事务会让普通 VACUUM 连“内部复用”都做不到VACUUM 只能移除对所有相关快照都不再需要的 tuple。一个长期持有旧快照的事务可能让已删除行仍必须保留。先做只读检查SELECTpid,usename,application_name,state,xact_start,backend_xmin,wait_event_type,wait_eventFROMpg_stat_activityWHERExact_startISNOTNULLORDERBYxact_start;复制槽的xmin或catalog_xmin也可能保持清理 horizonSELECTslot_name,slot_type,active,xmin,catalog_xmin,restart_lsn,wal_status,safe_wal_sizeFROMpg_replication_slots;发现旧事务或槽后先确认所有者、恢复用途、下游追赶能力与数据保留承诺。直接终止会话或删除复制槽具有业务和恢复风险不属于普通空间排障的默认动作。四条路径不是同一把锤子的不同力度路径主要结果在线代价适用判断DELETE行逻辑不可见产生 dead tuple行锁、WAL、索引与 vacuum 债务零散、需触发器/外键语义的删除DELETE 普通VACUUM清理并让 relation 内空间复用可与读写并发但有 I/O 且受 horizon 限制表会继续以相似规模写入VACUUM FULL重写紧凑文件并归还空间ACCESS EXCLUSIVE、临时空间、WAL/复制压力必须立即回收文件且有维护窗口DROP/DETACH PARTITION整块数据快速退出活动表元数据锁粒度必须提前设计按时间或离散边界整批淘汰如果空闲空间将在未来一周被同表重新写满付出强锁和重写成本把它还给操作系统随后 relation 又扩展回来往往没有业务收益。容量决策应比较“可复用速度”和“再次增长速度”而不是追求最小文件截图。周期淘汰数据分区是在设计删除成本若规则是“只保留 90 天按天整体淘汰”时间范围分区可以把逐行 DML 变成分区生命周期操作CREATETABLEevent_log_p(idbigintNOTNULL,created_at timestamptzNOTNULL,payloadtextNOTNULL)PARTITIONBYRANGE(created_at);CREATETABLEevent_log_p_202608PARTITIONOFevent_log_pFORVALUESFROM(2026-08-01 00:0008)TO(2026-09-01 00:0008);到期时可先摘除而不是立刻销毁ALTERTABLEevent_log_p DETACHPARTITIONevent_log_p_202608 CONCURRENTLY;并发摘除降低父表锁级别但有事务块、默认分区等限制必须按官方语义验证。摘除后先核对保留边界、归档、审计和查询依赖再在另一个受控变更中删除独立表。直接DROP TABLE更快但父表需要ACCESS EXCLUSIVE且误删恢复依赖备份。分区不是事故发生后的无成本补丁。把普通大表迁移为分区表需要新结构、数据搬迁或分批切换唯一约束通常必须包含分区键分区过多还会带来规划和元数据开销。应让分区粒度同时匹配保留周期、查询裁剪和运维批次。生产处置先止住磁盘风险再修生成机制1. 只读确认文件系统剩余容量与增长速度heap、索引、TOAST 各自尺寸dead tuple 生成与清理趋势长事务、复制槽、autovacuum 进度删除的数据是否会被近期写入替代业务是否真的要求立即归还操作系统。2. 最小遏制若磁盘逼近阈值先停止非必要批量写入/删除限制事故继续放大确认备份与副本健康扩容或迁移低风险文件以换取决策时间。不要在剩余空间不足时直接启动需要额外副本空间的重写。3. 根因修复调整 per-table autovacuum 阈值与资源使清理吞吐跟上 dead tuple 生成修复长事务、连接池idle in transaction和失效复制槽治理降低不必要 UPDATE合理设置 fillfactor 提高 HOT 机会将周期淘汰改为匹配保留规则的分区操作仅对确认需要归还文件空间的 relation 选择受控重写。4. 双重验收技术验收包括磁盘水位停止恶化、dead tuple 趋势受控、VACUUM 周期稳定、锁等待和复制延迟在阈值内。业务验收还要确认保留窗口、查询结果、归档可用性与恢复演练通过。VACUUM FULL 的执行护栏必须重写时至少准备可验证的备份或快照以及足以容纳重写与 WAL 峰值的空间明确到单表的目标禁止把数据库级命令当作顺手清理维护窗口与依赖方确认设置合理的lock_timeout避免无限等待后突然抢到锁从较小、低风险 relation 灰度持续观察pg_stat_progress_cluster、磁盘、WAL 和副本达到阻塞会话、复制延迟、磁盘余量或业务错误率阈值时停止后续表。中止重写可以阻止继续扩大影响但已消耗的 I/O、WAL 和缓存扰动不能撤销完成后的新文件也不能靠一条“反向 SQL”恢复原物理布局。回滚的目标应是恢复业务可用性与数据正确性而不是恢复原来的膨胀尺寸。这个实验能证明什么证据能证明不能证明DELETE 后 count 下降新快照看到的业务行减少文件空间已经回收VACUUM 后尺寸不变没有大量尾部文件被截掉VACUUM 没清理任何 dead tuple后续 INSERT 不再增长relation 内空间得到复用所有索引和 TOAST 都无膨胀VACUUM FULL 后文件变小重写能压紧该时刻的存活数据根因已修复、以后不会再涨分区 DROP 很快整分区淘汰避免逐行 DELETE任意删除条件都适合分区面试时怎么讲可以这样回答DELETE 只改变 tuple 的 MVCC 可见性普通 VACUUM 在安全 horizon 后清理 dead tuple并通过 FSM 让空间供 relation 内部复用通常不会移动存活行来缩文件。VACUUM FULL通过 relation rewrite 归还操作系统空间但需要ACCESS EXCLUSIVE锁和额外容量。周期性整批淘汰应在模型阶段按时间分区用 DETACH/DROP 改变删除成本排障时还要先区分 heap、索引、TOAST并处理长事务和 vacuum 吞吐根因。实验清理DROPTABLEIFEXISTSevent_log;DROPTABLEIFEXISTSevent_log_p;删除分区父表会同时删除仍挂载的示例分区只应在确认对象为本实验创建后执行。若分区已经被摘除成为独立表先核对名称和数据归属不要使用CASCADE扩大清理范围。官方资料PostgreSQL 18VACUUMPostgreSQL 18Routine VacuumingPostgreSQL 18Free Space MapPostgreSQL 18Table PartitioningPostgreSQL 18Database Object Size FunctionsPostgreSQL 18Progress ReportingPostgreSQL 18.6 源码标签 REL_18_6