如何避免Aurora Postgres运行大insert into (select ...)查询时内存溢出

核心问题1解答 #

Postgres默认不会对INSERT ... SELECT做内部分页，核心原因是单SQL语句的原子性约束：默认情况下单条SQL是一个独立事务，需要保证要么全部执行成功，要么全部失败回滚。如果数据库内部自动拆分成分批执行，中途出现故障的话会产生部分写入的脏数据，违反ACID的原子性要求，所以Postgres不会默认做这个操作。

你观察到的全量读取SELECT结果到内存的情况，大概率是你的SELECT查询的执行计划中存在需要缓存全量结果的节点：比如查询包含排序（ORDER BY）、哈希聚合、哈希连接、或者非物化CTE等逻辑，这些操作需要先拿到全量结果才能继续执行，会占用大量私有内存，这部分内存不受shared_buffers限制，所以你调整shared_buffers完全没用。

核心问题2解答 #

Postgres本身的内存限制参数是生效的，但你调整的参数不对：

• shared_buffers是全局共享的页面缓存，用来缓存表和索引的磁盘页，和你本次查询占用的私有执行内存无关
• work_mem是单会话单排序/哈希操作的内存上限，但是如果你的查询有多个排序/哈希节点，或者开启了并行查询，多个worker进程都会各自占用work_mem，总和很容易超出实例内存上限，触发OOM Killer。

如果要让Postgres尽可能避免OOM，可以做两个会话级调整：

1. 临时关闭当前会话的并行查询：SET max_parallel_workers_per_gather = 0;
2. 调低当前会话的work_mem，同时如果涉及临时表的话调低temp_buffers。

无需客户端修改的内置解决方案 #

你不需要在客户端写分页逻辑，直接在数据库内用匿名PL/pgSQL块就能完成分批插入，性能比客户端分页高很多，示例如下：

-- 推荐用主键分页，性能远高于OFFSET
DO $$
DECLARE
  batch_size INT := 10000; -- 可根据实际情况调整批次大小
  last_id INT := 0; -- 换成你源表的有序唯一键的初始最小值
  max_id INT;
BEGIN
  SELECT MAX(id) INTO max_id FROM my_other_table WHERE (condition);
  WHILE last_id < max_id LOOP
    INSERT INTO my_table (a,b,c)
    SELECT a,b,c FROM my_other_table 
    WHERE (condition) AND id > last_id
    ORDER BY id LIMIT batch_size;
    last_id := last_id + batch_size;
    COMMIT; -- 每批次提交释放事务占用的内存
  END LOOP;
END $$;

如果你的场景必须保证全量插入的原子性，不能分批提交，可以先临时禁用目标表的索引、触发器、外键约束，关闭并行查询后再执行原生的INSERT ... SELECT，内存占用会大幅降低。

内容的提问来源于stack exchange，提问作者I was in the neighborhood