如何在PostgreSQL中分批删除重复数据？

原代码存在的问题 #

• OFFSET分页效率极低：随着i增大，数据库需要扫描前i行才能定位偏移位置，数据量越大速度越慢，甚至会超时。
• 重复全表计算开销大：每次循环都要对全表执行GROUP BY查找重复组，百万行数据下重复计算完全是资源浪费。
• 数据变动导致偏移失效：删除操作会改变表的行数，后续OFFSET会因前面数据被删除出现偏移错误，可能漏掉或重复处理重复组。
• 大事务风险高：所有删除操作放在一个事务中，一旦出错回滚会消耗大量资源，甚至导致数据库崩溃。

改进方案 #

方案1：用临时表预存重复组，分批删除 #

临时表仅存储重复组的关键信息（占用内存极小），避免重复计算：

-- 第一步：创建临时表存储所有重复组的标记信息
CREATE TEMP TABLE duplicate_groups AS
SELECT MIN(id) as min_id, column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

-- 第二步：分批删除重复数据，控制单次删除量
BEGIN;
LOOP
  DELETE FROM table_name t
  USING duplicate_groups dg
  WHERE t.column1 = dg.column1 
    AND t.column2 = dg.column2 
    AND ...
    AND t.id > dg.min_id
  LIMIT 10000; -- 单次删除10000条，可根据服务器性能调整

  IF NOT FOUND THEN
    EXIT;
  END IF;

  -- 每批删除后提交，避免大事务占用资源
  COMMIT;
  BEGIN;
END LOOP;
COMMIT;

-- 清理临时表
DROP TABLE duplicate_groups;

方案2：用游标遍历重复组，分批删除 #

无需临时表，通过游标逐个处理重复组：

BEGIN;
-- 声明游标，获取所有重复组的核心信息
DECLARE duplicate_cursor CURSOR FOR
SELECT MIN(id) as min_id, column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

-- 声明变量存储游标取出的数据（替换为实际列类型）
DECLARE min_id INT;
DECLARE col1 VARCHAR;
DECLARE col2 INT;
-- ... 声明对应重复列的变量

-- 遍历游标，逐个处理重复组内的重复数据
FETCH NEXT FROM duplicate_cursor INTO min_id, col1, col2, ...;
WHILE FOUND LOOP
  DELETE FROM table_name
  WHERE column1 = col1 
    AND column2 = col2 
    AND ...
    AND id > min_id
  LIMIT 10000; -- 每个组内分批删除，避免单次操作量过大

  FETCH NEXT FROM duplicate_cursor INTO min_id, col1, col2, ...;

  -- 可选：每处理100个组提交一次，控制事务大小
  -- IF MOD(cursor_position, 100) = 0 THEN
  --   COMMIT;
  --   BEGIN;
  -- END IF;
END LOOP;
COMMIT;
CLOSE duplicate_cursor;

原代码的修复建议（保留原有逻辑的优化） #

如果一定要用循环分页的方式，必须解决OFFSET的效率问题：

# 以Python操作PostgreSQL为例，其他数据库逻辑类似
import psycopg2

conn = psycopg2.connect("dbname=your_db user=your_user")
cur = conn.cursor()

last_min_id = 0
while True:
    # 基于上次处理的min_id分页，避免OFFSET的低效问题
    cur.execute("""
        SELECT MIN(id) as min_id, column1, column2, ...
        FROM table_name
        GROUP BY column1, column2, ...
        HAVING COUNT(*) > 1 AND MIN(id) > %s
        LIMIT 10000
    """, (last_min_id,))
    dups = cur.fetchall()
    if not dups:
        break

    # 更新下一批分页的起始标记
    last_min_id = max(d[0] for d in dups)

    # 批量删除当前批次的重复数据
    for dup in dups:
        min_id, col1, col2 = dup[0], dup[1], dup[2]
        cur.execute("""
            DELETE FROM table_name t
            WHERE t.column1 = %s 
              AND t.column2 = %s 
              AND ...
              AND t.id > %s
        """, (col1, col2, min_id))

    # 每批操作后提交，释放资源
    conn.commit()

cur.close()
conn.close()

关键注意事项 #

• 添加联合索引：在column1, column2, ..., id上创建联合索引，能大幅提升分组和删除操作的效率。
• 低峰期操作：删除操作会占用数据库资源并可能锁表，尽量在业务低峰期执行。
• 备份数据：操作前务必备份表数据，避免误删导致数据丢失。

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