如何在PostgreSQL中高效删除3000万行数据不压垮服务器

我使用AWS RDS PostgreSQL数据库（实例类型db.t4g.medium），维护一张记录客户商品每日库存的stocks表，当前含30亿行数据，表字段如下：

• supplier_id：关联客户表的外键
• retailer_id：关联客户表的外键
• ean：varchar(13)，无索引
• datetime：已建立索引
• quantity：integer，无索引

需要删除某供应商的所有数据（约3000万行），直接执行DELETE FROM stocks WHERE supplier_id = 200会导致数据库至少1小时无响应，因此终止了查询。后续改用按天批量删除的方式：

DELETE FROM STOCKS 
WHERE supplier_id=200
AND datetime >= '2022-08-19T00:00:00+00:00'::timestamptz 
AND datetime < '2022-08-20T00:00:00+00:00'::timestamptz 

该方法对数据量较小（100万行）的供应商有效，但针对3000万行的供应商，单天删除仍耗时超1小时且资源占用过高。

现需解决：如何在不耗尽数据库实例资源的前提下完成稳定删除？是否有更合理的拆分方式或其他技术方案？（不介意操作耗时，只要稳定即可）

附加信息 #

删除指定供应商所有数据的查询计划 #

执行：

EXPLAIN
DELETE FROM stocks
WHERE supplier_id = 158;

结果：

Delete on stocks (cost=0.00..80052412.00 rows=0 width=0)
  ->  Seq Scan on sa_inventory_stocktake  (cost=0.00..80052412.00 rows=36725650 width=6)
        Filter: (supplier_id = 158)

按天删除指定供应商数据的查询计划 #

执行：

EXPLAIN
DELETE FROM stocks
WHERE supplier_id = 158
AND datetime >= '2022-08-19T00:00:00+00:00'::timestamptz 
AND datetime < '2022-08-20T00:00:00+00:00'::timestamptz;

结果：

Delete on stocks  (cost=92677221.21..92898902.60 rows=0 width=0)
  ->  Bitmap Heap Scan on stocks  (cost=92677221.21..92898902.60 rows=56759 width=6)
        Recheck Cond: ((supplier_id = 158) AND (datetime >= '2022-08-19 00:00:00+00'::timestamp with time zone) AND (datetime < '2022-08-20 00:00:00+00'::timestamp with time zone))
        ->  BitmapAnd  (cost=92677221.21..92677221.21 rows=56759 width=0)
              ->  Bitmap Index Scan on stocks_supplier_id_c50e0b94  (cost=0.00..892175.08 rows=36725650 width=0)
                    Index Cond: (supplier_id = 158)
              ->  Bitmap Index Scan on stocks_lookup  (cost=0.00..91785017.50 rows=4614593 width=0)
                    Index Cond: ((datetime >= '2022-08-19 00:00:00+00'::timestamp with time zone) AND (datetime < '2022-08-20 00:00:00+00'::timestamp with time zone))

当前表已建立的索引 #

• supplier_id上的BTREE索引，非唯一
• retailer_id上的BTREE索引，非唯一
• retailer_id、datetime联合BTREE索引，非唯一

1. 进一步缩小删除批量 #

按天删除仍有压力，可拆分到按小时甚至15分钟的时间粒度，每次删除更小批次的数据。同时在每次删除后添加10-30秒的休眠，给数据库留出资源回收和事务提交的时间。

示例SQL（按小时拆分）：

DELETE FROM stocks
WHERE supplier_id = 200
AND datetime >= '2022-08-19T00:00:00+00:00'::timestamptz 
AND datetime < '2022-08-19T01:00:00+00:00'::timestamptz;

2. 使用DELETE ... LIMIT控制单批次行数 #

不依赖时间维度，直接限制每次删除的行数（比如每次删1万行），循环执行直到删除完成。这种方式能精准控制单批次的资源消耗。

示例原生SQL：

DELETE FROM stocks
WHERE supplier_id = 200
LIMIT 10000;

可在Python/Django中实现循环逻辑：

import time
from django.db import transaction
from your_app.models import Stock

while True:
    with transaction.atomic():
        deleted_count, _ = Stock.objects.filter(supplier_id=200).delete(limit=10000)
    if deleted_count == 0:
        break
    time.sleep(10)

如果需要有序删除，可结合ORDER BY datetime，但会增加少量开销。

3. 优化索引提升删除效率 #

当前按天删除的查询计划显示用了BitmapAnd组合两个索引，开销较高。可以创建**supplier_id + datetime的联合BTREE索引**，让数据库直接通过单个索引定位目标行，避免Bitmap合并的开销：

CREATE INDEX idx_stocks_supplier_datetime ON stocks(supplier_id, datetime);

建议在业务低峰期创建索引，完成后按时间段删除的查询会直接使用该联合索引，减少IO和CPU消耗。

4. 使用CREATE TABLE AS SELECT+交换表的方式（适合超大批量删除） #

如果3000万行占表比例较高，直接删除会产生大量WAL日志和碎片，可采用“保留需要的行，替换原表”的方式：

1. 创建新表，保留除目标供应商外的所有数据：

CREATE TABLE stocks_new AS
SELECT * FROM stocks WHERE supplier_id != 200;

2. 给新表创建和原表一致的索引、约束、外键：

CREATE INDEX idx_stocks_new_supplier_id ON stocks_new(supplier_id);
CREATE INDEX idx_stocks_new_retailer_id ON stocks_new(retailer_id);
CREATE INDEX idx_stocks_new_retailer_datetime ON stocks_new(retailer_id, datetime);
-- 按需添加其他约束和外键

3. 切换表（需短时间锁表，建议低峰期操作）：

BEGIN;
ALTER TABLE stocks RENAME TO stocks_old;
ALTER TABLE stocks_new RENAME TO stocks;
COMMIT;

4. 验证数据无误后，删除旧表：

DROP TABLE stocks_old;

这种方式避免了大量DELETE操作产生的WAL，资源消耗更平稳，但需要额外的存储空间（至少等于原表非目标数据的大小）。

5. 调整数据库参数控制资源 #

针对AWS RDS PostgreSQL，可临时调整以下参数降低删除操作的资源占用：

• 降低maintenance_work_mem：减少Bitmap索引扫描的内存使用，避免内存耗尽
• 降低work_mem：限制排序、Hash操作的内存分配
• 开启log_statement和log_min_duration_statement监控删除语句的执行情况，及时调整批量大小

注意：参数调整需在RDS控制台的参数组中进行，部分参数需要重启实例，建议提前备份参数组。

6. 利用分区表特性（长期优化方案） #

如果后续还有大量批量删除需求，可将stocks表改为按datetime分区的分区表（比如按天或按月分区）。这样删除指定供应商的历史数据时，直接对分区表执行批量删除，或者如果要删除整个分区的所有数据，直接DROP TABLE对应分区即可，效率极高。

分区表创建示例（按天分区）：

-- 创建父表
CREATE TABLE stocks (
    supplier_id INT,
    retailer_id INT,
    ean VARCHAR(13),
    datetime TIMESTAMPTZ,
    quantity INT
) PARTITION BY RANGE (datetime);

-- 创建天级分区表
CREATE TABLE stocks_20220819 PARTITION OF stocks FOR VALUES FROM ('2022-08-19') TO ('2022-08-20');
CREATE TABLE stocks_20220820 PARTITION OF stocks FOR VALUES FROM ('2022-08-20') TO ('2022-08-21');
-- 按需创建其他分区

内容的提问来源于stack exchange，提问作者physicalattraction