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

下面创建两张相同的小表,一张逐行删除,一张整表截断。这个示例不是性能压测,而是验证两种操作都能清空数据并保留表对象,为后续理解空间语义建立基线。

sql
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;

预期结果

text
 delete_rows | truncate_rows | delete_table_exists | truncate_table_exists
-------------+---------------+---------------------+----------------------
           0 |             0 | t                   | t

两种操作都让查询结果变成 0 行,而且两张表仍然存在。不同之处在于物理清理方式:DELETE 留下的不可见行版本要等待 VACUUM 回收为可复用空间;普通 VACUUM 通常不会把整个关系文件缩回操作系统。TRUNCATE 则直接截断关系存储,更适合确认整个关系的数据都可以清空的场景。

分区表为什么更适合生命周期治理

时间分区可以把“删除数亿行”转化为“处理一个已经到期的分区对象”。当保留策略与分区边界一致时,分区级操作能够避开逐行扫描、逐行索引删除以及海量 dead tuple,这也是大表生命周期治理优先按时间分区的重要原因。

下面的主示例先创建一个月份分区,再将它从父表解绑。解绑之后,从父表查询不到这批数据,但独立分区表仍然保留,可继续用于归档校验。

sql
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;

预期结果

text
 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,或其他会重写、截断或移除关系文件的维护方式。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

比较删除与分区删除成本

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

登录哪都通