PostgreSQL批量删除内存耗尽问题排查与解决方案咨询

首先，咱们从你提供的执行计划和错误信息来拆解问题：

内存耗尽的核心原因 #

1. Hash Join的内存开销
   你的DELETE语句里的子查询用到了Hash Semi Join，PostgreSQL会把连接的表数据加载到内存中的哈希表来匹配符合条件的行。看执行计划里的数字：table1有5700多万行，table2有3600多万行，这么大的数据量构建哈希表，会直接吃掉大量内存。如果你的work_mem设置不够大，PostgreSQL会尝试把哈希表写到临时文件里，但如果服务器内存（包括交换分区）已经被占满，连临时文件的写入操作都无法完成，就会抛出Out of memory错误。

2. 大事务的内存累积
   你把所有DELETE操作放在一个大事务里，PostgreSQL需要为事务中所有被删除的行保留undo日志（用于事务回滚），同时这些被删除的行在事务提交前不会被真正清理，都会占用内存和磁盘空间。即使你减少了单次删除的数据量，只要还是在同一个大事务里，内存开销会不断累积，最终还是会耗尽资源。

解决方法 #

针对你的场景，这里有几个切实可行的优化方案：

1. 优化子查询的执行计划，避免大Hash Join #

从执行计划看，table3的查询用到了索引，但table1和table2都是全表扫描（Seq Scan）。你可以：

• 给table1.table2_id和table2.table3_id建立B-tree索引，这样查询优化器可能会选择Nested Loop代替Hash Semi Join，大幅降低内存开销。
• 把IN子查询改成DELETE ... USING语法，比如：

  DELETE FROM table1
  USING table2, table3
  WHERE table1.table2_id = table2.id
    AND table2.table3_id = table3.id
    AND table3.some_id = 44265;
  

  这种写法有时候会让优化器生成更高效的执行计划，减少内存占用。

2. 分批次小事务删除，不要用大事务 #

把大操作拆分成多个独立的小事务，每删除一小批数据就提交一次，这样可以及时释放undo日志占用的内存。比如：

-- 每次删除10000行，直到没有符合条件的数据
WHILE EXISTS (
  SELECT 1 FROM table1
  JOIN table2 ON table1.table2_id = table2.id
  JOIN table3 ON table2.table3_id = table3.id
  WHERE table3.some_id = 44265
) LOOP
  DELETE FROM table1
  USING table2, table3
  WHERE table1.table2_id = table2.id
    AND table2.table3_id = table3.id
    AND table3.some_id = 44265
  LIMIT 10000;
  COMMIT; -- 每批提交，释放内存
END LOOP;

注意：PostgreSQL 9.6支持DELETE搭配LIMIT使用，但要确保WHERE条件能稳定匹配到数据，避免漏删。

3. 调整PostgreSQL配置参数 #

• 临时调整work_mem：针对当前会话提高work_mem（比如SET work_mem = '64MB';），让哈希表能在内存中容纳更多数据，减少临时文件的写入。但不要全局调得太高，避免其他查询抢占内存。
• 检查临时文件存储路径：确保临时文件存在于有足够磁盘空间的分区，避免因磁盘不足加剧内存压力。

4. 预生成待删除ID到临时表 #

先把需要删除的ID查询出来，存入带索引的临时表，再用这个临时表来关联DELETE，避免重复执行复杂的关联查询：

-- 创建临时表并插入待删除的table1 ID
CREATE TEMP TABLE to_delete_table1 AS
SELECT table1.id
FROM table1
JOIN table2 ON table1.table2_id = table2.id
JOIN table3 ON table2.table3_id = table3.id
WHERE table3.some_id = 44265;
-- 给临时表加索引加速匹配
CREATE INDEX idx_to_delete_table1_id ON to_delete_table1(id);

-- 分批次删除
WHILE EXISTS (SELECT 1 FROM to_delete_table1) LOOP
  DELETE FROM table1
  WHERE id IN (SELECT id FROM to_delete_table1 LIMIT 10000);
  -- 从临时表移除已删除的ID
  DELETE FROM to_delete_table1 WHERE id IN (SELECT id FROM table1 WHERE id IN (SELECT id FROM to_delete_table1 LIMIT 10000));
  COMMIT;
END LOOP;

关于删除顺序的疑问 #

你当前先删叶子表再逐层向上的策略是完全正确的！因为如果父表有外键关联到叶子表，先删父表会触发外键约束错误。保持这个顺序，结合上面的分批次提交方案，就能有效解决内存问题。

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