PostgreSQL大表批量删除优化方案_MsSqlserver

一、背景说明

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

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

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

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

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

本次执行方式示例：

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

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

三、整体处理思路

整体流程如下：

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

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

如果直接写一个大 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
having 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 秒。

这样做的目的包括：

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

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

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
having 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
having 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);

核心优点：

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

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

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

PostgreSQL大表批量删除优化方案