PostgreSQL大表批量删除优化方案详解从原理到实践

一、背景说明

在PostgreSQL中，如果要对大表进行批量删除，尤其是当这张表还涉及逻辑复制、订阅同步、主从同步这些场景时，千万别想着一次性开个大事务全部删掉——那几乎是给自己挖坑。

一次性删除几百万行数据，会出现哪些问题呢？

1. 单个事务过大，执行时间拖得老长；
2. WAL日志量暴增，磁盘压力瞬间拉满；
3. 逻辑复制场景下，订阅端回放压力直线上升；
4. 复制槽的WAL可能堆积，搞不好把发布端都拖垮；
5. checkpoint变得极其频繁，系统日志里全是警告；
6. 磁盘IO也是一波猛攻，别的业务都得跟着吃亏；
7. 更关键的是——一旦这个事务执行到一半因为锁、连接、磁盘等问题失败了，整个大事务需要全部回滚，那个代价，想想都头大。

所以，大数据量删除的正确做法是：

先准备一张待清理的临时表，然后通过存储过程分批删除，每批都独立提交，批次之间还要适当暂停一下，给系统喘口气。

本次示例的执行方式是这样的：

CALL cleanup_main_data_table(5000, 180);

解释一下含义：

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

二、业务需求抽象

为了避免暴露真实业务信息，下面所有表名和字段名都做了脱敏处理。

假设主数据表叫：

main_data_table

核心字段就两个：

id, group_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_table WHERE ...;

数据量小的时候倒也没啥问题，但一旦涉及几十万、几百万甚至更多数据，那就会形成一个巨大的事务。背后的风险，一个个来说。

1. WAL日志暴增

PostgreSQL的删除操作会生成WAL日志——数据删得越多，WAL产生的量就越大。逻辑复制场景下，订阅端消费这些WAL需要时间，一旦赶不上，就会出现WAL堆积和复制延迟。

2. 复制延迟增大

发布端提交删除事务后，订阅端需要应用这些删除操作。如果单个事务太大，订阅端可能会集中处理大量变更，导致同步延迟直接飙升。

3. checkpoint频繁

大批量删除会造成大量数据页和WAL写入，进而引发checkpoint过于频繁。日志里可能就会看到类似这样的提示：

checkpoints are occurring too frequently
HINT: 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 AS
SELECT DISTINCT group_key
FROM main_data_table
WHERE 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 AS
SELECT DISTINCT group_key
FROM main_data_table
WHERE 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_key
FROM source_key_table
WHERE group_key IS NOT NULL;

4. 检查待清理数量

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

SELECT COUNT(*) AS total_cleanup_keys
FROM tmp_cleanup_keys;

5. 检查是否存在重复key

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

SELECT group_key, COUNT(*) AS cnt
FROM tmp_cleanup_keys
GROUP BY group_key
HA VING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 20;

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

6. 对待清理表去重

可以用下面这种方式重新生成一张去重后的待清理表：

CREATE TABLE tmp_cleanup_keys_new AS
SELECT DISTINCT group_key
FROM tmp_cleanup_keys
WHERE 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_id
ON main_data_table(group_key, id);

这个索引的作用是：加速根据 group_key 查找数据、每组按 id 排序、窗口函数计算、以及后续的删除匹配。

即使 id 已经是主键，也仍然建议保留 (group_key, id) 这个组合索引。

注意一点：

CREATE INDEX CONCURRENTLY

不能在事务块中执行，所以不要这样写：

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

应该直接单独执行。

2. 待清理表索引

待清理表建议创建一个索引：

CREATE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_key
ON tmp_cleanup_keys(group_key);

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

CREATE UNIQUE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_key_uniq
ON 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 plpgsql
AS $$
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=5000
NOTICE: batch 2, deleted_rows=98765, processed_keys=5000
NOTICE: 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_keys
FROM tmp_cleanup_keys;

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

SELECT COUNT(*) AS matched_rows
FROM main_data_table t
JOIN 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_rows
FROM ranked
WHERE 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 ranked
WHERE 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_keys
FROM tmp_cleanup_keys;

2. 查看当前执行状态

SELECT
    pid,
    state,
    now() - query_start AS running_time,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE 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,
    granted
FROM pg_locks
WHERE 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_size
FROM 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_lag
FROM pg_stat_subscription;

重点观察：

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

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

这个方案最大的优点之一就是：每批都会独立提交。流程是：先删除本批主表中的多余数据，再删除本批待清理key，然后提交事务。

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

• 已提交的批次不会回滚；
• 当前未提交的批次会自动回滚；
• 未处理完成的 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 frequently
HINT: Consider increasing the configuration parameter "max_wal_size".

那可以考虑适当调整PostgreSQL的配置参数。

示例：

max_wal_size = 16GB
min_wal_size = 4GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

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

max_wal_size = 32GB
min_wal_size = 8GB

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

十七、完整执行流程汇总

下面给出一份从创建待清理表到执行清理的完整SQL流程，可以直接拿来用。

1. 创建待清理表

DROP TABLE IF EXISTS tmp_cleanup_keys;
CREATE TABLE tmp_cleanup_keys AS
SELECT DISTINCT group_key
FROM main_data_table
WHERE group_key IS NOT NULL;

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

DROP TABLE IF EXISTS tmp_cleanup_keys;
CREATE TABLE tmp_cleanup_keys AS
SELECT DISTINCT group_key
FROM main_data_table
WHERE group_key IS NOT NULL
  AND create_time < DATE '2024-01-01';

2. 检查待清理数量

SELECT COUNT(*) AS total_cleanup_keys
FROM tmp_cleanup_keys;

3. 创建索引

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

CREATE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_key
ON tmp_cleanup_keys(group_key);

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

CREATE UNIQUE INDEX IF NOT EXISTS idx_tmp_cleanup_keys_group_key_uniq
ON 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 plpgsql
AS $$
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_keys
FROM tmp_cleanup_keys;

7. 清理完成后确认结果

检查是否还有待处理的key：

SELECT COUNT(*) AS remaining_keys
FROM tmp_cleanup_keys;

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

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

SELECT group_key, COUNT(*) AS cnt
FROM main_data_table
GROUP BY group_key
HA VING COUNT(*) > 5
ORDER BY cnt DESC
LIMIT 20;

不过需要注意：如果主表中还有很多不在本次清理范围内的 group_key，上面的SQL会检查全表。如果只想检查本次处理的范围，建议在清理前备份一份待清理key表，比如：

CREATE TABLE tmp_cleanup_keys_backup AS
SELECT *
FROM tmp_cleanup_keys;

然后清理完成后用这个备份表来检查：

SELECT t.group_key, COUNT(*) AS cnt
FROM main_data_table t
JOIN tmp_cleanup_keys_backup b ON t.group_key = b.group_key
GROUP BY t.group_key
HA VING COUNT(*) > 5
ORDER BY cnt DESC
LIMIT 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增长速度和复制延迟情况，动态调整批量大小和暂停时间，这样才能在清理效率和系统稳定性之间找到最合适的平衡点。