如何在PostgreSQL中高效删除800万条数据？求助优化方案

针对你要从1000万行表中删除800万行的场景，核心问题在于UUIDv4主键的离散性——每次删除都会触发大量索引页的随机IO，加上索引维护的开销，导致小批量删除也负载极高。以下是几个经过验证的解决思路：

方案一：创建新表保留数据（最高效，推荐优先使用） #

直接跳过删除操作，把需要保留的200万行数据迁移到新表，再替换原表，完全规避删除带来的索引维护成本。

操作步骤：

1. 创建新表并插入保留数据

   -- 复制原表结构（含约束、索引），也可以直接插入数据
   CREATE TABLE new_table (LIKE old_table INCLUDING ALL);
   -- 插入需要保留的行，替换成你的保留条件
   INSERT INTO new_table SELECT * FROM old_table WHERE 你的保留条件;
   

   若不需要保留所有索引，可先只复制结构，插入数据后再重建必要索引，速度更快。

2. 替换原表

   -- 先备份原表
   ALTER TABLE old_table RENAME TO old_table_backup;
   -- 把新表重命名为原表名
   ALTER TABLE new_table RENAME TO old_table;
   

   这一步锁表时间极短，几乎不影响业务（若业务可短暂停服）。

3. 验证数据后清理备份
   确认新表数据无误后，删除备份表：DROP TABLE old_table_backup;

优点：速度比删除快几个数量级，避免了删除操作的IO和索引开销。
注意：需预留至少等于原表大小的磁盘空间，操作前务必做好全量备份。

方案二：优化分批删除（适合无法停服的场景） #

如果不能停服，必须用删除操作，要解决UUID离散性的问题，同时降低索引维护成本：

1. 添加有序辅助列
   UUID是无序的，无法按主键范围分批，先给表加一个自增列用于分批：

   ALTER TABLE old_table ADD COLUMN batch_id SERIAL;
   

   该列会自动给现有行分配连续ID，后续可按此列范围分批删除。

2. 临时删除非必要索引
   删除时维护多个索引会极大增加负载，先删掉除主键外的所有索引：

   DROP INDEX idx_old_table_column1;
   DROP INDEX idx_old_table_column2;
   -- 其他索引同理
   

   删除完成后再重建这些索引：

   CREATE INDEX idx_old_table_column1 ON old_table (column1);
   CREATE INDEX idx_old_table_column2 ON old_table (column2);
   

3. 按有序列分批删除
   用循环按batch_id范围批量删除，每次处理1-5万行，间隔几秒降低负载：

   DO $$
   DECLARE
       batch_size INT := 50000;
       max_id INT;
       current_id INT := 1;
   BEGIN
       SELECT MAX(batch_id) INTO max_id FROM old_table WHERE 你的删除条件;
       WHILE current_id <= max_id LOOP
           DELETE FROM old_table 
           WHERE 你的删除条件 AND batch_id BETWEEN current_id AND current_id + batch_size - 1;
           COMMIT;
           PERFORM pg_sleep(1); -- 间隔1秒，避免数据库压力骤增
           current_id := current_id + batch_size;
       END LOOP;
   END $$;
   

4. 清理辅助列
   删除完成后，删掉添加的batch_id列：

   ALTER TABLE old_table DROP COLUMN batch_id;
   

优化参数：临时调高maintenance_work_mem（比如设为64MB或128MB），让索引重建和删除操作的内存更充足，事后记得改回原值。

方案三：游标批量删除（适合关联条件的删除） #

如果删除条件需要关联其他表，用游标批量获取要删除的主键，再批量删除：

DO $$
DECLARE
    cur CURSOR FOR SELECT uuid_col FROM old_table WHERE 你的删除条件 LIMIT 10000;
    id_batch UUID[];
BEGIN
    LOOP
        FETCH cur INTO id_batch;
        EXIT WHEN id_batch IS NULL;
        DELETE FROM old_table WHERE uuid_col = ANY(id_batch);
        COMMIT;
        PERFORM pg_sleep(0.5); -- 调整间隔时间
    END LOOP;
END $$;

注意：游标使用事务快照，若有并发写入可能会漏删，适合低并发或只读场景。

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