分批删除与空间回收
PostgreSQL 没有在删除语句里限定行数的语法，要分批得自己切片：用一个子查询先取出这一批要删的行位置，再据此删除。更要紧的是删除之后的账——删除只是把行标记为死元组，空间不会立刻还给操作系统，回收由自动清理任务负责。大批量删除之后如果不关注这一点，表会持续膨胀，查询与写入都会变慢。
基本信息
| 适用平台 | PostgreSQL 12 及以上 |
|---|---|
| 切片方式 | 用行位置子查询限定每批行数 |
| 删除代价 | 标记死元组，空间回收依赖自动清理 |
| 膨胀观察 | 查看死元组数量与表实际占用大小的比例 |
| 更省的做法 | 按时间分区的表直接摘除或删除整个分区 |
典型使用场景
- 按时间清理过期日志：每天删除超出保留期的数据
- 分批清理历史数据：避免一次性长事务持锁
- 观察删除后的空间回收情况，评估是否需要人工清理
- 分区表的历史数据下线
- 迁移前的数据瘦身
分批删除写法
分批删除写法
-- 每批最多删除 5000 行，用行位置子查询切片
DELETE FROM logs l
USING (
SELECT ctid FROM logs
WHERE created_at < now() - interval '90 days'
ORDER BY id
LIMIT 5000
) b
WHERE l.ctid = b.ctid;
-- 循环执行直到影响行数小于批次上限，说明已清理完
-- 观察死元组与表大小，判断是否需要额外清理
SELECT relname, n_live_tup, n_dead_tup, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;
-- 分区表：直接摘除整段历史，代价远低于逐行删除
-- ALTER TABLE logs DETACH PARTITION logs_2023_01;
-- DROP TABLE logs_2023_01;删除后的关注点
| 关注点 | 表现 | 处理方向 |
|---|---|---|
| 死元组堆积 | 死元组数量持续增长 | 检查自动清理是否跟上 |
| 表膨胀 | 表占用远大于有效数据 | 评估整表重建或分区改造 |
| 长事务阻塞 | 清理被长事务挡住无法回收 | 缩短事务，避免长连接空闲事务 |
| 索引膨胀 | 索引体积同步增长 | 重建索引或调整清理频率 |
| 分区表 | 摘除分区几乎零成本 | 优先用分区下线替代逐行删除 |
每批太大就回到长事务问题，太小则事务与索引维护开销占比过高。先用单批耗时与锁等待数据测出合适区间，再固定到脚本里。
行位置在行被更新后会变化，因此每批都要重新取一次位置，不要把位置缓存下来跨批使用。并发写入频繁表上优先考虑主键区间切片。