在 Postgres 里，大规模删除的唯一可扩展方案是 DROP TABLE

2026-06-11 34 预计阅读时间: 1 分钟
来源: planetscale.com AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：10 分钟

一条 DELETE FROM logs WHERE created_at < '2024-01-01' 看起来很干净——删掉旧数据，回收空间，一切恢复正常。但在 Postgres 里，这条语句做的事情和你想的完全相反：它不是在回收资源，而是在制造更多工作量。当删除量达到百万甚至亿级行时，后果会从"慢"升级到"不可用"。

理解这个问题，并重新组织表结构让删除变成 DROP TABLE 或 TRUNCATE，是处理大规模数据生命周期的前提。

DELETE 的真实代价

Postgres 的 MVCC 机制决定了 DELETE 不是物理移除行，而是把行标记为"dead tuple"——旧版本仍然留在磁盘上，直到 VACUUM 来清理。这意味着：

  • 写入放大：每一行被删除都要写一次 WAL 记录，还要更新页内的状态位。删一亿行，WAL 的写入量接近插入一亿行。
  • 索引膨胀：索引页里的 dead tuple 不会自动移除。B-tree 索引会变得越来越"稀疏"——逻辑上只剩 10% 的行，物理上仍占 90% 的页，查询要扫描大量空位。
  • VACUUM 压力：大量删除后，autovacuum 必须扫过整张表来回收 dead tuple。如果表本身就有几十 GB，这个全表扫描本身就是一次沉重的 I/O 操作。而且 VACUUM 只回收空间给同一张表内部使用，不会把磁盘空间还给操作系统（除非碰巧是表末尾的页且 vacuum_truncate 生效）。
  • 并发阻塞：长事务持有快照时，dead tuple 不能被回收，膨胀持续累积。删除期间表上的行锁也会和正常查询冲突。

一句话：DELETE 把"清理"变成了"先污染再治理"，而且治理本身也很贵。

TRUNCATE 和 DROP TABLE 为什么完全不同

TRUNCATE TABLE 和 DROP TABLE 走的是完全不同的物理路径：

  • TRUNCATE 直接释放与表关联的所有磁盘文件页，一次性把空间还给操作系统。它写一条 WAL 记录表示"这张表被清空了"，而不是逐行写 WAL。速度接近瞬间完成。
  • DROP TABLE 更彻底——连表定义、索引、所有磁盘文件一起删除。同样是一条 WAL，而不是 N 条。

两者都不产生 dead tuple，不需要 VACUUM，不会导致索引膨胀。代价从 O(被删除行数) 降到 O(1)。

但代价是：它们是 DDL 操作，会拿 ACCESS EXCLUSIVE 锁，期间所有查询都不能访问这张表。对于 TRUNCATE，锁在语句结束后立即释放；对于 DROP TABLE，表已经不存在了。所以关键问题变成了：你能不能让业务逻辑容忍"整张表瞬间消失"？

让删除变成 DROP TABLE 的三种实践模式

答案是重新组织数据结构，让"删除一批旧数据"等价于"丢弃一张旧表"。

模式一：按时间分区，DROP 旧分区

这是最标准的做法。Postgres 的声明式分区（PG 10+）原生支持按范围分区，删除旧数据就是 DROP TABLE 一个分区：

-- 创建按月分区的日志表
CREATE TABLE events (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    payload    JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 为 2024 年每个月创建分区
DO $$
BEGIN
    FOR i IN 1..12 LOOP
        EXECUTE format(
            'CREATE TABLE events_y2024m%2s PARTITION OF events
             FOR VALUES FROM (%L) TO (%L)',
            lpad(i::text, 2, '0'),
            make_date(2024, i, 1)::timestamptz,
            (make_date(2024, i, 1) + interval '1 month')::timestamptz
        );
    END LOOP;
END $$;

-- 未来分区可以提前批量创建，或用 pg_partman 自动管理

删除一整月的数据：

-- 瞬间完成，没有 dead tuple，没有 VACUUM
DROP TABLE events_y2024m01;

查询仍然走父表 events，Postgres 会自动做分区裁剪（partition pruning），只扫描符合 WHERE 条件的分区。业务代码不需要改。

注意：分区键（created_at）必须包含在所有 UNIQUE 索引中，这是 Postgres 的硬性限制。如果你的主键是 id，必须改成 (created_at, id) 复合主键。

模式二：旋转表——TRUNCATE 当前写入表

有些场景不是"删旧数据"，而是"定期清空当前批次"。比如每日 ETL 的中间暂存表、每轮测试的快照表。用旋转表模式：

-- 两张结构相同的轮换表
CREATE TABLE staging_active (LIKE staging_template INCLUDING ALL);
CREATE TABLE staging_standby (LIKE staging_template INCLUDING ALL);

-- 应用写入 staging_active
-- 每轮结束时，切换并清空
BEGIN;
ALTER TABLE staging_active  RENAME TO staging_standby;
ALTER TABLE staging_standby RENAME TO staging_active;
COMMIT;

-- 切换完成后，清空旧表（现在是 staging_standby）
TRUNCATE TABLE staging_standby;

ALTER TABLE RENAME 只改目录项，不搬数据，瞬间完成。TRUNCATE 同样瞬间完成。写入方在下一轮直接写新的 staging_active，读取方如果需要上一轮数据，可以短暂查询 staging_standby。

模式三：替换表——用新表替代旧表后 DROP

当你需要"删除大部分行但保留少量行"时，反过来做更高效：把要保留的行拷到新表，然后 DROP 旧表。

-- 旧表：big_table，需要保留最近 7 天的数据
-- 步骤 1：创建新表并拷贝要保留的行
CREATE TABLE big_table_new (LIKE big_table INCLUDING ALL);

INSERT INTO big_table_new
SELECT * FROM big_table
WHERE created_at >= now() - interval '7 days';

-- 步骤 2：原子切换
BEGIN;
ALTER TABLE big_table      RENAME TO big_table_old;
ALTER TABLE big_table_new  RENAME TO big_table;
COMMIT;

-- 步骤 3：确认无误后丢弃旧表
DROP TABLE big_table_old;

这个模式的关键是 INSERT INTO new SELECT 只拷贝少量保留行，代价是 O(保留行数)，而 DELETE 的代价是 O(删除行数)。当删除比例超过 50% 时，替换模式几乎总是更快。

风险点：切换期间依赖父表的视图和外键需要检查。LIKE ... INCLUDING ALL 会拷贝索引和约束，但外键指向的仍是旧表 OID，切换后需要重建：

-- 切换后重建外键
ALTER TABLE child_table
    DROP CONSTRAINT child_table_parent_id_fkey,
    ADD CONSTRAINT child_table_parent_id_fkey
        FOREIGN KEY (parent_id) REFERENCES big_table(id);

什么时候仍然需要 DELETE

不是所有场景都能用 DDL 替代 DELETE：

  • 删除量小（几千行以内）：DELETE 的开销可以忽略，autovacuum 很快就能跟上。
  • 删除条件不是分区键：比如"删除所有 status = 'expired' 的行"，如果表没有按 status 分区，你无法直接 DROP 一个分区。可以考虑按 status 做列表分区，但分区数会膨胀。
  • 不能拿 ACCESS EXCLUSIVE 锁：如果表必须 7×24 无间断查询，哪怕 1 秒的锁也不行。这时只能用 DELETE + VACUUM，或者用逻辑复制把删除操作转移到下游。

在这些场景下，可以减轻 DELETE 的伤害：

-- 分批删除，每批 10000 行，减少单次锁持有时间和 WAL 峰值
DO $$
DECLARE
    done BOOLEAN := FALSE;
BEGIN
    WHILE NOT done LOOP
        DELETE FROM big_table
        WHERE created_at < '2024-01-01'
        LIMIT 10000;

        GET DIAGNOSTICS done = ROW_COUNT;
        done := (done = 0);

        -- 每批之间让 autovacuum 有机会介入
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

注意：在 PL/pgSQL 的循环里用 COMMIT 需要 PG 11+ 的过程内事务控制。更简单的做法是用外部脚本循环执行带 LIMIT 的 DELETE。

决策清单

面对"需要删除大量数据"时，按这个顺序判断：

  1. 表是否已经按删除条件分区？ → 是：DROP TABLE partition_name，结束。
  2. 是否需要清空整张表？ → 是：TRUNCATE TABLE，结束。
  3. 删除比例是否超过 50%？ → 是：创建新表，拷贝保留行，RENAME 切换，DROP 旧表。
  4. 是否可以接受短暂（<1秒）的不可访问？ → 是：考虑模式三的替换表方案。
  5. 以上都不满足？ → 分批 DELETE + 主动 VACUUM，同时规划下一次重构为分区表。

核心原则只有一个：让数据的生命周期对齐表的边界。如果你知道数据会在某一天被删除，那它从一开始就应该住在一张会在那一天被 DROP 的表里。

# AI # MySQL # 数据库

相关推荐