在postgresql 15中,从大表中快速清理历史数据的最有效方法是使用 drop table 配合分区表(partitioning),或者对非分区表使用 delete 结合 vacuum full。对于拥有数十亿行且按时间范围分区的表,直接删除整个分区(即 drop table)是瞬间完成的,因为它只涉及元数据操作,不产生大量的wal日志和锁竞争。如果表未分区,则应使用带有 limit 的批量 delete 语句,以避免事务膨胀和锁超时,随后执行 vacuum full 以回收磁盘空间。

如何设计支持快速删除的分区策略?
要实现秒级删除,核心在于将数据按时间范围进行物理隔离。在PostgreSQL 15中,推荐使用声明式分区(Declarative Partitioning)。通过将主表定义为范围分区,并按 created_at 字段划分区间,旧的数据会被存储在独立的子表中。当需要清理一年前的数据时,只需找到对应的分区并执行 DROP TABLE。这种方法避免了全表扫描和逐行删除的性能开销,同时保持了主表的索引效率。

CREATE TABLE measurements (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
-- 创建当前分区
CREATE TABLE measurements_y2023m04 PARTITION OF measurements
FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');
-- 创建历史分区
CREATE TABLE measurements_y2022m04 PARTITION OF measurements
FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
-- 模拟插入数据
INSERT INTO measurements SELECT random()::int, '2022-04-01'::date + i, random()::int, random()::int
FROM generate_series(1, 1000000) AS s(i); -- 假设插入了大量数据
-- 快速清理:直接删除整个分区
DROP TABLE IF EXISTS measurements_y2022m04;
上述代码展示了如何创建一个按日期范围分区的表。首先定义主表结构并指定分区键。接着,为不同的月份创建具体的分区。在实际操作中,你可以编写一个脚本定期创建未来的分区,并删除过期的分区。当执行 DROP TABLE measurements_y2022m04 时,PostgreSQL 会立即释放该分区占用的所有磁盘块,无需等待垃圾回收机制。
非分区表的大规模数据删除方案是什么?
如果由于架构限制无法使用分区表,必须处理单一的大表,那么直接使用 DELETE FROM table WHERE condition 可能会导致严重的性能问题,包括长事务、锁持有时间过长以及事务日志(WAL)爆满。推荐的替代方案是采用“分批删除”策略。通过在一个循环中使用 DELETE ... LIMIT n,每次只删除少量数据并提交事务。这样可以减少单次事务的负载,允许其他查询并发执行。删除完成后,必须运行 VACUUM FULL 来真正回收空间,因为普通的 VACUUM 只是标记空间为可用,不会缩小表文件。

-- 假设我们要删除 created_at 早于 '2022-01-01' 的记录
-- 使用 PL/pgSQL 进行分批删除
DO $$
DECLARE
rows_deleted INTEGER;
BEGIN
LOOP
DELETE FROM measurements
WHERE created_at < '2022-01-01'
LIMIT 10000; -- 每批删除1万条
GET DIAGNOSTICS rows_deleted = ROW_COUNT;
EXIT WHEN rows_deleted = 0;
-- 可选:短暂休眠以避免CPU过载
PERFORM pg_sleep(0.1);
END LOOP;
END $$;
-- 最后回收空间
VACUUM FULL measurements;
这段代码演示了如何在非分区场景下安全地清理数据。关键在于 LIMIT 10000 的使用,它确保了每次 DELETE 操作都很快完成并立即提交。虽然这比一次性删除慢得多,但它将对生产环境的影响降到了最低。需要注意的是,VACUUM FULL 会获取排他锁,阻塞所有对该表的读写操作,因此建议在业务低峰期执行。此外,如果表上有外键约束或触发器,分批删除的逻辑可能需要调整以处理级联效应。
删除操作后为什么需要执行VACUUM FULL?
在PostgreSQL中,删除操作并不会立即从磁盘上移除数据页,而是将这些页面标记为“死元组”(dead tuples)。这是为了支持多版本并发控制(MVCC),确保正在读取旧数据的查询不会看到不一致的状态。然而,这些死元组会占用磁盘空间并导致索引膨胀。普通的 VACUUM 命令可以清理这些死元组并回收空间给表内部重用,但不会收缩表文件的大小。如果你希望彻底释放磁盘空间,使表文件变小,就必须使用 VACUUM FULL。它会重新构建整个表,将活数据紧凑排列,并将未使用的空间归还给操作系统。这也是为什么在大批量删除后,VACUUM FULL 是恢复磁盘容量的必要步骤。

















