PostgreSQL / 系统课时
比较删除与分区删除成本
比较删除与分区删除成本
清理到期数据时,真正要比较的不是“哪条 SQL 最快”,而是每种操作会产生多少行级工作、WAL、锁影响以及后续空间回收成本。本课用同类过期数据对比 DELETE、TRUNCATE、DROP 与 DETACH PARTITION,并给出生产环境的选择边界。
学习目标
- 解释
DELETE、TRUNCATE、DROP与DETACH PARTITION在 MVCC、WAL 和锁影响上的核心差异 - 根据整表清空、整分区过期和零散行过期三类场景选择清理方式
- 判断数据被移除后,磁盘空间是否会立即归还给操作系统
先看四种操作的本质差异
DELETE 是逐行删除。被删除的行对后续快照变为不可见,并留下 dead tuple,之后需要 VACUUM 将相应空间标记为可复用。它最灵活,但删除行数越多,行级修改、索引维护、WAL、扫描成本和表膨胀风险通常越高。
TRUNCATE 面向整个关系,也可以截断指定分区表。它不会像 DELETE 那样逐行生成 dead tuple,但它并不是“几乎不写 WAL”:TRUNCATE 本身仍是 WAL 记录的事务性操作,会记录关系截断及相关元数据变化。其机制与逐行 DELETE 完全不同,不需要为每个被删除元组和每条相关索引项执行逐项删除,因此在清空大量数据时,WAL 量和执行成本通常明显更低,但不能把这种差异理解成没有 WAL。
DROP TABLE 会直接移除关系对象。若历史数据已经按时间分区且整个分区都到期,删除对应分区表可以避免逐行清理和后续垃圾回收,但对象本身也会消失。
DETACH PARTITION 只解除分区与父表的绑定,数据仍保留在独立表中。它适合“先移出在线查询路径,再导出、校验或延迟删除”的归档流程;真正删除数据文件,还需要后续执行 DROP TABLE。
| 操作 | 删除粒度 | MVCC / dead tuple | WAL 与索引维护 | 主要锁影响 | 空间回收 |
|---|---|---|---|---|---|
DELETE |
行 | 会留下 dead tuple | 逐行修改并维护相关索引,大批量时成本高 | 行级修改,同时持有表级 ROW EXCLUSIVE 锁 |
VACUUM 后主要供表内复用,通常不缩小文件 |
TRUNCATE |
整个关系 | 不逐行产生 dead tuple | 仍写 WAL,但记录的是截断等操作,不执行逐行元组与索引删除 | ACCESS EXCLUSIVE |
通常立即截断关系存储并释放对应空间 |
DROP TABLE |
整个对象 | 不保留待清理行 | 记录对象删除,不做逐行索引维护 | 需要强对象锁 | 删除对象并释放其关系文件 |
DETACH PARTITION |
整分区 | 数据仍存在 | 不逐行删除分区中的数据 | 需要分区结构相关锁 | 仅解绑,不释放该表的数据文件 |
用实验观察 DELETE 与 TRUNCATE
下面创建两张相同的小表,一张逐行删除,一张整表截断。这个示例不是性能压测,而是验证两种操作都能清空数据并保留表对象,为后续理解空间语义建立基线。
DROP TABLE IF EXISTS delete_demo;
DROP TABLE IF EXISTS truncate_demo;
CREATE TABLE delete_demo (id bigint PRIMARY KEY, payload text);
CREATE TABLE truncate_demo (LIKE delete_demo INCLUDING ALL);
INSERT INTO delete_demo
SELECT g, repeat('x', 200)
FROM generate_series(1, 10000) AS g;
INSERT INTO truncate_demo
SELECT * FROM delete_demo;
DELETE FROM delete_demo;
TRUNCATE truncate_demo;
SELECT
(SELECT count(*) FROM delete_demo) AS delete_rows,
(SELECT count(*) FROM truncate_demo) AS truncate_rows,
to_regclass('delete_demo') IS NOT NULL AS delete_table_exists,
to_regclass('truncate_demo') IS NOT NULL AS truncate_table_exists;预期结果
delete_rows | truncate_rows | delete_table_exists | truncate_table_exists
-------------+---------------+---------------------+----------------------
0 | 0 | t | t两种操作都让查询结果变成 0 行,而且两张表仍然存在。不同之处在于物理清理方式:DELETE 留下的不可见行版本要等待 VACUUM 回收为可复用空间;普通 VACUUM 通常不会把整个关系文件缩回操作系统。TRUNCATE 则直接截断关系存储,更适合确认整个关系的数据都可以清空的场景。
分区表为什么更适合生命周期治理
时间分区可以把“删除数亿行”转化为“处理一个已经到期的分区对象”。当保留策略与分区边界一致时,分区级操作能够避开逐行扫描、逐行索引删除以及海量 dead tuple,这也是大表生命周期治理优先按时间分区的重要原因。
下面的主示例先创建一个月份分区,再将它从父表解绑。解绑之后,从父表查询不到这批数据,但独立分区表仍然保留,可继续用于归档校验。
DROP TABLE IF EXISTS events CASCADE;
CREATE TABLE events (
id bigint,
occurred_at date NOT NULL,
payload jsonb
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
INSERT INTO events
VALUES (1, '2026-01-15', '{"type":"demo"}');
ALTER TABLE events DETACH PARTITION events_2026_01;
SELECT
(SELECT count(*) FROM events) AS parent_rows,
to_regclass('events_2026_01') AS detached_table,
(SELECT count(*) FROM events_2026_01) AS rows_kept;预期结果
parent_rows | detached_table | rows_kept
-------------+----------------+----------
0 | events_2026_01 | 1此时数据已经不再属于父表 events,但仍完整保存在 events_2026_01。完成归档和校验后再执行 DROP TABLE events_2026_01;,才会删除该对象并释放对应关系文件。

选择清理方式的判断边界
如果过期条件只能命中表中的部分行,DELETE 仍然是必要工具,但大批量清理应配合小批次提交、节流以及 VACUUM 和复制延迟监控。若整个临时表、阶段表或独立关系都可以清空,可以考虑 TRUNCATE,前提是业务能够接受其强锁和依赖限制。
如果数据天然按时间分区,且保留策略与分区边界一致,应优先让生命周期治理发生在分区级。需要保留归档缓冲期时,先 DETACH PARTITION 将旧分区移出在线查询路径,校验完成后再 DROP TABLE;如果无需保留中间状态,也可以直接删除到期分区对象。这样通常能把主要清理工作从“与删除行数成正比”转化为“对分区对象做元数据和文件级操作”。
最后必须区分“逻辑上已经没有这些行”和“磁盘已经归还给操作系统”。普通 DELETE 加 VACUUM 主要解决表内空间复用;要稳定、快速地回收整段历史数据占用的文件空间,更适合使用与保留周期对齐的分区淘汰、TRUNCATE,或其他会重写、截断或移除关系文件的维护方式。