PostgreSQL批量删除大表优化方案

一、背景说明

在 PostgreSQL 中，如果需要对大表进行批量删除，尤其是该表存在逻辑复制、订阅同步、主从同步等场景时，不建议一次性执行大事务删除。

一次性删除大量数据可能会带来以下问题：

1. 单个事务过大，执行时间长；
2. 产生大量 WAL 日志；
3. 逻辑复制订阅端回放压力变大；
4. 复制槽 WAL 堆积；
5. checkpoint 变得频繁；
6. 磁盘 IO 压力增大；
7. 可能影响线上业务；
8. 一旦失败，整个大事务回滚，代价较高。

因此，大数据量删除建议采用：

先准备待清理临时表，再通过存储过程分批删除，每批独立提交，并在批次之间适当暂停。

本次执行方式示例：

CALL cleanup_main_data_table(5000, 180);

含义：

• 每批处理 5000 个分组 key；
• 每批提交一次事务；
• 每批之间暂停 180 秒；
• 自动循环执行，直到待清理表为空。

二、业务需求抽象

为了避免暴露真实业务信息，以下示例中的表名和字段名均已脱敏。

假设主数据表为：

main_data_table

主要字段如下：

idgroup_key

字段说明：

字段名	说明
id	主键或唯一标识
group_key	分组字段，例如某类业务标识

需求为：

针对指定的一批 group_key，每个 group_key 只保留 id 最小的前 5 条记录，其余记录删除。

待处理的 group_key 会先放入一张待清理表：

tmp_cleanup_keys

后续存储过程会从这张表中分批取数据进行处理。

三、整体处理思路

整体流程如下：

1. 创建待清理表 tmp_cleanup_keys；
2. 将需要清理的 group_key 写入待清理表；
3. 对待清理表进行去重；
4. 给主表和待清理表创建必要索引；
5. 创建批量删除存储过程；
6. 存储过程每次从待清理表中取一批 group_key；
7. 查询主表中这些 group_key 对应的数据；
8. 使用窗口函数 ROW_NUMBER() 对每组数据排序；
9. 每个 group_key 保留前 5 条；
10. 删除排名大于 5 的数据；
11. 删除本批已经处理过的 group_key；
12. 提交事务；
13. 暂停一段时间；
14. 继续处理下一批，直到待清理表为空。

四、为什么不建议一次性删除？

如果直接写一个大 SQL 一次性删除所有数据，例如：

DELETE FROM main_data_tableWHERE ...;

在数据量较小时问题不大，但如果涉及几十万、几百万甚至更多数据，会形成一个大事务。

大事务可能带来以下风险。

1. WAL 日志暴增

PostgreSQL 的删除操作会产生 WAL。

删除的数据越多，产生的 WAL 越多。

如果存在逻辑复制，WAL 还需要被订阅端消费，如果订阅端消费速度跟不上，就会出现 WAL 堆积和复制延迟。

2. 复制延迟增大

逻辑复制场景下，发布端提交删除事务后，订阅端需要应用这些删除操作。

如果单个事务太大，订阅端可能会集中处理大量变更，导致同步延迟上升。

3. checkpoint 频繁

大批量删除会造成大量数据页和 WAL 写入，从而可能引起 checkpoint 频繁发生。

日志中可能出现类似提示：

checkpoints are occurring too frequentlyHINT: Consider increasing the configuration parameter "max_wal_size".

这表示数据库写入压力较大，需要降低批处理速度，或者适当调整 WAL/checkpoint 相关参数。

4. 失败回滚成本高

如果一次性删除执行了很久，最后因为锁、连接、磁盘、网络等问题失败，整个事务都需要回滚。

分批提交可以降低这种风险。

五、创建待清理表

在正式批量删除之前，需要先创建一张待清理表，用来保存本次需要处理的 group_key。

表名示例：

tmp_cleanup_keys

字段名示例：

group_key

1. 创建普通待清理表

如果待清理的 key 来源于主表，可以使用如下 SQL 创建：

DROP TABLE IF EXISTS tmp_cleanup_keys;CREATE TABLE tmp_cleanup_keys ASSELECT DISTINCT group_keyFROM main_data_tableWHERE group_key IS NOT NULL;

说明：

• DROP TABLE IF EXISTS：如果之前已经存在同名表，先删除；
• CREATE TABLE AS：根据查询结果创建待清理表；
• SELECT DISTINCT：对 group_key 去重，避免重复处理；
• WHERE group_key IS NOT NULL：过滤空值。

2. 按条件创建待清理表

如果只想清理部分数据，可以加上业务筛选条件。

示例：

DROP TABLE IF EXISTS tmp_cleanup_keys;CREATE TABLE tmp_cleanup_keys ASSELECT DISTINCT group_keyFROM main_data_tableWHERE group_key IS NOT NULL  AND create_time < DATE '2024-01-01';

这里的 create_time 也是脱敏字段，仅作为示例。

如果实际表中没有该字段，可以替换为自己的筛选条件。

3. 从外部名单导入待清理 key

如果待清理的 group_key 来自外部名单，也可以先创建空表：

DROP TABLE IF EXISTS tmp_cleanup_keys;CREATE TABLE tmp_cleanup_keys (    group_key text NOT NULL);

然后通过 INSERT 写入：

INSERT INTO tmp_cleanup_keys(group_key)VALUES    ('key_001'),    ('key_002'),    ('key_003');

如果是从其他中间表导入：

INSERT INTO tmp_cleanup_keys(group_key)SELECT DISTINCT group_keyFROM source_key_tableWHERE group_key IS NOT NULL;

4. 检查待清理数量

创建完成后，可以先查看本次需要处理多少个 key：

SELECT COUNT(*) AS total_cleanup_keysFROM tmp_cleanup_keys;

5. 检查是否存在重复 key

如果创建表时没有使用 DISTINCT，建议检查是否存在重复值：

SELECT group_key, COUNT(*) AS cntFROM tmp_cleanup_keysGROUP BY group_keyHAVING COUNT(*) > 1ORDER BY cnt DESCLIMIT 20;

如果存在重复数据，建议去重。

6. 对待清理表去重

可以使用如下方式重新生成去重后的待清理表：

CREATE TABLE tmp_cleanup_keys_new ASSELECT DISTINCT group_keyFROM tmp_cleanup_keysWHERE group_key IS NOT NULL;DROP TABLE tmp_cleanup_keys;ALTER TABLE tmp_cleanup_keys_new RENAME TO tmp_cleanup_keys;

六、创建索引

为了保证批量删除效率，建议提前创建必要索引。

1. 主表索引

主表建议创建组合索引：

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_data_table_group_key_idON main_data_table(group_key, id);

该索引用于加速：

1. 根据 group_key 查找数据；
2. 每组按照 id 排序；
3. 窗口函数计算；
4. 后续删除匹配。

如果 id 已经是主键，也仍然建议保留 (group_key, id) 组合索引。

注意：

CREATE INDEX CONCURRENTLY

不能在事务块中执行，也就是说不要这样写：

BEGIN;CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_data_table_group_key_idON main_data_table(group_key, id);COMMIT;

应该直接单独执行。

2. 待清理表索引

待清理表建议创建索引：

CREATE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_keyON tmp_cleanup_keys(group_key);

如果确认 group_key 不应该重复，也可以创建唯一索引：

CREATE UNIQUE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_key_uniqON tmp_cleanup_keys(group_key);

如果创建唯一索引失败，说明待清理表中还存在重复 group_key，需要先去重。

七、单批删除 SQL 示例

在封装存储过程之前，可以先理解单批删除 SQL。

BEGIN;WITH batch AS MATERIALIZED (    SELECT group_key    FROM tmp_cleanup_keys    ORDER BY group_key    LIMIT 5000),ranked AS (    SELECT        t.id,        ROW_NUMBER() OVER (            PARTITION BY t.group_key            ORDER BY t.id ASC        ) AS rn    FROM main_data_table t    JOIN batch b ON t.group_key = b.group_key),deleted AS (    DELETE FROM main_data_table t    USING ranked r    WHERE t.id = r.id      AND r.rn > 5    RETURNING t.id),removed AS (    DELETE FROM tmp_cleanup_keys d    USING batch b    WHERE d.group_key = b.group_key    RETURNING d.group_key)SELECT    (SELECT COUNT(*) FROM deleted) AS deleted_rows,    (SELECT COUNT(*) FROM removed) AS processed_keys;COMMIT;

说明：

CTE	作用
batch	从待清理表中取一批 group_key
ranked	对每个 group_key 下的数据排序编号
deleted	删除每组中排名大于 5 的数据
removed	删除本批已经处理过的 group_key

八、创建存储过程

单批 SQL 可以手动执行，但如果待清理数据很多，手动执行不现实。

可以将其封装成存储过程，让 PostgreSQL 自动循环处理。

CREATE OR REPLACE PROCEDURE cleanup_main_data_table(    p_batch_size integer DEFAULT 5000,    p_sleep_seconds numeric DEFAULT 180)LANGUAGE plpgsqlAS $$DECLARE    v_deleted_rows bigint;    v_processed_keys bigint;    v_batch_no bigint := 0;BEGIN    LOOP        WITH batch AS MATERIALIZED (            SELECT group_key            FROM tmp_cleanup_keys            ORDER BY group_key            LIMIT p_batch_size        ),        ranked AS (            SELECT                t.id,                ROW_NUMBER() OVER (                    PARTITION BY t.group_key                    ORDER BY t.id ASC                ) AS rn            FROM main_data_table t            JOIN batch b ON t.group_key = b.group_key        ),        deleted AS (            DELETE FROM main_data_table t            USING ranked r            WHERE t.id = r.id              AND r.rn > 5            RETURNING t.id        ),        removed AS (            DELETE FROM tmp_cleanup_keys d            USING batch b            WHERE d.group_key = b.group_key            RETURNING d.group_key        )        SELECT            (SELECT COUNT(*) FROM deleted),            (SELECT COUNT(*) FROM removed)        INTO            v_deleted_rows,            v_processed_keys;        v_batch_no := v_batch_no + 1;        RAISE NOTICE 'batch %, deleted_rows=%, processed_keys=%',            v_batch_no, v_deleted_rows, v_processed_keys;        COMMIT;        EXIT WHEN v_processed_keys = 0;        IF p_sleep_seconds > 0 THEN            PERFORM pg_sleep(p_sleep_seconds);        END IF;    END LOOP;END;$$;

九、执行存储过程

创建完成后，直接执行：

CALL cleanup_main_data_table(5000, 180);

参数说明：

参数	含义
5000	每批处理 5000 个 group_key
180	每批处理完后暂停 180 秒

执行过程中会输出类似日志：

NOTICE: batch 1, deleted_rows=123456, processed_keys=5000NOTICE: batch 2, deleted_rows=98765, processed_keys=5000NOTICE: batch 3, deleted_rows=54321, processed_keys=5000

当待清理表为空时，过程结束。

十、重要注意事项

1. 调用存储过程时不要手动开启事务

因为存储过程内部已经执行了：

COMMIT;

所以调用时应直接执行：

CALL cleanup_main_data_table(5000, 180);

不要这样执行：

BEGIN;CALL cleanup_main_data_table(5000, 180);COMMIT;

否则可能出现事务控制相关错误。

2. LIMIT 限制的是 key 数量，不是删除行数

存储过程中的：

LIMIT p_batch_size

限制的是每批处理多少个 group_key，不是删除多少行数据。

例如：

CALL cleanup_main_data_table(5000, 180);

表示每批取 5000 个 group_key。

如果每个 group_key 下有很多行数据，那么实际每批删除的行数可能远大于 5000。

3. 每批之间暂停的作用

本方案中设置：

p_sleep_seconds = 180

表示每批提交后暂停 180 秒。

这样做的目的包括：

1. 给逻辑复制订阅端留出追赶时间；
2. 降低 WAL 瞬时堆积；
3. 减少 checkpoint 过于频繁的问题；
4. 降低磁盘 IO 峰值；
5. 避免对线上业务造成明显影响。

如果数据库压力较小，可以缩短暂停时间：

CALL cleanup_main_data_table(5000, 60);

如果数据库压力较大，可以降低批量并加大暂停时间：

CALL cleanup_main_data_table(3000, 300);

十一、执行前检查

正式执行前，建议先做以下检查。

1. 检查待清理 key 数量

SELECT COUNT(*) AS total_cleanup_keysFROM tmp_cleanup_keys;

2. 检查主表中涉及的数据量

SELECT COUNT(*) AS matched_rowsFROM main_data_table tJOIN tmp_cleanup_keys k ON t.group_key = k.group_key;

3. 预估会删除的数据量

可以先用窗口函数估算要删除多少行：

WITH ranked AS (    SELECT        t.id,        ROW_NUMBER() OVER (            PARTITION BY t.group_key            ORDER BY t.id ASC        ) AS rn    FROM main_data_table t    JOIN tmp_cleanup_keys k ON t.group_key = k.group_key)SELECT COUNT(*) AS estimated_delete_rowsFROM rankedWHERE rn > 5;

这个 SQL 只统计，不删除。

4. 抽样查看每组保留情况

WITH ranked AS (    SELECT        t.id,        t.group_key,        ROW_NUMBER() OVER (            PARTITION BY t.group_key            ORDER BY t.id ASC        ) AS rn    FROM main_data_table t    JOIN tmp_cleanup_keys k ON t.group_key = k.group_key)SELECT *FROM rankedWHERE group_key IN (    SELECT group_key    FROM tmp_cleanup_keys    ORDER BY group_key    LIMIT 10)ORDER BY group_key, rn;

十二、执行过程中查看进度

1. 查看剩余待处理 key 数量

SELECT COUNT(*) AS remaining_keysFROM tmp_cleanup_keys;

2. 查看当前执行状态

SELECT    pid,    state,    now() - query_start AS running_time,    wait_event_type,    wait_event,    queryFROM pg_stat_activityWHERE query ILIKE '%cleanup_main_data_table%'   OR query ILIKE '%main_data_table%'ORDER BY query_start;

3. 查看是否存在锁等待

SELECT    pid,    locktype,    relation::regclass AS relation_name,    mode,    grantedFROM pg_locksWHERE relation IN (    'main_data_table'::regclass,    'tmp_cleanup_keys'::regclass)ORDER BY granted, pid;

十三、逻辑复制场景监控

如果数据库存在逻辑复制，执行期间建议重点关注发布端和订阅端状态。

1. 发布端查看复制槽

SELECT    slot_name,    active,    restart_lsn,    confirmed_flush_lsn,    wal_status,    safe_wal_sizeFROM pg_replication_slots;

重点关注：

• active 是否为 true；
• wal_status 是否正常；
• safe_wal_size 是否持续下降；
• 是否出现 WAL 堆积。

2. 订阅端查看订阅状态

SELECT    subname,    pid,    received_lsn,    latest_end_lsn,    pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS receive_lag,    now() - latest_end_time AS time_lagFROM pg_stat_subscription;

重点关注：

• pid 是否有值；
• receive_lag 是否持续增大；
• time_lag 是否持续变大；
• 订阅 worker 是否频繁重启。

十四、异常中断后如何恢复

该方案的优点是每批都会独立提交。

流程为：

1. 删除本批主表中的多余数据；
2. 删除本批待清理 key；
3. 提交事务。

如果执行过程中连接中断、会话断开或人为取消：

• 已提交批次不会回滚；
• 当前未提交批次会自动回滚；
• 未处理完成的 group_key 仍然保留在 tmp_cleanup_keys 中；
• 后续可以重新执行存储过程继续处理。

恢复执行方式：

CALL cleanup_main_data_table(5000, 180);

十五、批量大小如何选择

批量大小没有固定标准，需要根据数据库压力、WAL 增长速度、复制延迟和磁盘 IO 情况进行调整。

参考如下：

场景	建议批量	暂停时间
数据库压力较小	10000	30 秒到 60 秒
普通线上环境	5000	60 秒到 180 秒
复制延迟明显	3000	180 秒到 300 秒
IO 压力较高	1000	300 秒以上

本次示例使用：

CALL cleanup_main_data_table(5000, 180);

属于相对稳妥的方案。

十六、可选优化：调整 WAL 和 checkpoint 参数

如果执行过程中频繁出现 checkpoint 提示，例如：

checkpoints are occurring too frequentlyHINT: Consider increasing the configuration parameter "max_wal_size".

可以考虑适当调整 PostgreSQL 配置。

示例：

max_wal_size = 16GBmin_wal_size = 4GBcheckpoint_timeout = 15mincheckpoint_completion_target = 0.9

如果数据量更大，可以进一步评估：

max_wal_size = 32GBmin_wal_size = 8GB

注意：

这些参数需要结合磁盘容量、业务写入量、备份策略、复制延迟综合评估，不建议盲目修改。

十七、完整执行流程汇总

下面给出一份从创建待清理表到执行清理的完整 SQL 流程。

1. 创建待清理表

DROP TABLE IF EXISTS tmp_cleanup_keys;CREATE TABLE tmp_cleanup_keys ASSELECT DISTINCT group_keyFROM main_data_tableWHERE group_key IS NOT NULL;

如果只清理部分数据，可以加条件：

DROP TABLE IF EXISTS tmp_cleanup_keys;CREATE TABLE tmp_cleanup_keys ASSELECT DISTINCT group_keyFROM main_data_tableWHERE group_key IS NOT NULL  AND create_time < DATE '2024-01-01';

2. 检查待清理数量

SELECT COUNT(*) AS total_cleanup_keysFROM tmp_cleanup_keys;

3. 创建索引

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_data_table_group_key_idON main_data_table(group_key, id);

CREATE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_keyON tmp_cleanup_keys(group_key);

如果确认不允许重复，可以创建唯一索引：

CREATE UNIQUE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_key_uniqON tmp_cleanup_keys(group_key);

4. 创建存储过程

CREATE OR REPLACE PROCEDURE cleanup_main_data_table(    p_batch_size integer DEFAULT 5000,    p_sleep_seconds numeric DEFAULT 180)LANGUAGE plpgsqlAS $$DECLARE    v_deleted_rows bigint;    v_processed_keys bigint;    v_batch_no bigint := 0;BEGIN    LOOP        WITH batch AS MATERIALIZED (            SELECT group_key            FROM tmp_cleanup_keys            ORDER BY group_key            LIMIT p_batch_size        ),        ranked AS (            SELECT                t.id,                ROW_NUMBER() OVER (                    PARTITION BY t.group_key                    ORDER BY t.id ASC                ) AS rn            FROM main_data_table t            JOIN batch b ON t.group_key = b.group_key        ),        deleted AS (            DELETE FROM main_data_table t            USING ranked r            WHERE t.id = r.id              AND r.rn > 5            RETURNING t.id        ),        removed AS (            DELETE FROM tmp_cleanup_keys d            USING batch b            WHERE d.group_key = b.group_key            RETURNING d.group_key        )        SELECT            (SELECT COUNT(*) FROM deleted),            (SELECT COUNT(*) FROM removed)        INTO            v_deleted_rows,            v_processed_keys;        v_batch_no := v_batch_no + 1;        RAISE NOTICE 'batch %, deleted_rows=%, processed_keys=%',            v_batch_no, v_deleted_rows, v_processed_keys;        COMMIT;        EXIT WHEN v_processed_keys = 0;        IF p_sleep_seconds > 0 THEN            PERFORM pg_sleep(p_sleep_seconds);        END IF;    END LOOP;END;$$;

5. 执行批量清理

CALL cleanup_main_data_table(5000, 180);

6. 查看剩余进度

SELECT COUNT(*) AS remaining_keysFROM tmp_cleanup_keys;

7. 清理完成后确认结果

检查是否还有待处理 key：

SELECT COUNT(*) AS remaining_keysFROM tmp_cleanup_keys;

如果结果为 0，说明全部处理完成。

可以进一步确认主表中每个 group_key 是否最多只保留 5 条：

SELECT group_key, COUNT(*) AS cntFROM main_data_tableGROUP BY group_keyHAVING COUNT(*) > 5ORDER BY cnt DESCLIMIT 20;

注意：

如果主表中还有很多不在本次清理范围内的 group_key，上述 SQL 会检查全表。

如果只想检查本次处理范围，建议在清理前备份一份待清理 key 表，例如：

CREATE TABLE tmp_cleanup_keys_backup ASSELECT *FROM tmp_cleanup_keys;

然后清理完成后检查：

SELECT t.group_key, COUNT(*) AS cntFROM main_data_table tJOIN tmp_cleanup_keys_backup b ON t.group_key = b.group_keyGROUP BY t.group_keyHAVING COUNT(*) > 5ORDER BY cnt DESCLIMIT 20;

十八、临时表是否需要删除？

如果确认清理完成，并且不再需要保留本次清理记录，可以删除临时清理表：

DROP TABLE IF EXISTS tmp_cleanup_keys;

如果创建了备份表，也可以在确认无误后删除：

DROP TABLE IF EXISTS tmp_cleanup_keys_backup;

不过生产环境中，建议至少保留一段时间，方便后续排查。

十九、总结

对于 PostgreSQL 大表批量删除，尤其是存在逻辑复制或线上业务压力的场景，不建议一次性执行大事务删除。

更稳妥的方案是：

创建待清理表，分批处理，小事务提交，批次间暂停，并持续监控数据库和复制状态。

本方案的核心执行命令为：

CALL cleanup_main_data_table(5000, 180);

核心优点：

1. 避免超大事务；
2. 降低 WAL 瞬时压力；
3. 降低逻辑复制延迟风险；
4. 支持中断后继续执行；
5. 不需要人工重复执行；
6. 对线上业务影响更可控；
7. 处理进度可以通过待清理表直观看到。

实际生产环境中，建议根据数据库负载、磁盘 IO、WAL 增长速度和复制延迟情况，动态调整批量大小和暂停时间。

以上就是PostgreSQL大表批量删除优化方案的详细内容，更多关于PostgreSQL大表批量删除优化的资料请关注本站其它相关文章！

您可能感兴趣的文章:

• PostgreSQL 删除表的具体使用小结
• postgresql如何找到表中重复数据的行并删除
• Postgresql删除数据库表中重复数据的几种方法详解
• postgresql 实现多表关联删除