PostgreSQL 9.6：循环批量更新仍遇内存不足，如何实现真正的批量更新？

你的问题核心在于两个关键失误：

1. 初始的COUNT(1)会全表扫描一次，且循环内的子查询每次都要重新扫描符合条件的记录，加上OFFSET随着偏移量增大，性能会急剧下降，还可能引发重复/漏更的问题；
2. 整个DO块在单个大事务中执行，所有更新的undo日志都会被持续保留，这才是内存耗尽的主要原因——500万条记录的修改在一个事务里，内存根本无法承载。

下面给出两种高效的批量更新方案，都能拆分事务、避免全表扫描，彻底解决内存问题：

方案一：基于主键范围的批量更新（推荐，性能最优） #

利用order_id（UUID类型）的可比较特性，每次只更新一批范围内的记录，每批提交一次事务释放内存：

CREATE OR REPLACE PROCEDURE batch_update_placed_orders()
LANGUAGE plpgsql
AS $$
DECLARE
  batch_size INT := 1000; -- 可根据内存情况调整，比如2000/5000
  last_uuid UUID;
  updated_rows INT := 1;
BEGIN
  -- 初始化：获取第一批待更新记录的最后一个UUID
  SELECT order_id INTO last_uuid
  FROM placed_orders
  WHERE submitted = 'Y' AND source != 'B'
  ORDER BY order_id ASC
  LIMIT batch_size
  OFFSET batch_size - 1;

  -- 循环更新直到没有可修改的记录
  WHILE updated_rows > 0 LOOP
    -- 更新当前批次的记录
    UPDATE placed_orders
    SET source = 'B'
    WHERE submitted = 'Y' AND source != 'B'
      AND order_id <= last_uuid;

    -- 获取本次更新的行数
    GET DIAGNOSTICS updated_rows = ROW_COUNT;

    -- 如果还有未更新的记录，获取下一批的最后一个UUID
    IF updated_rows > 0 THEN
      SELECT order_id INTO last_uuid
      FROM placed_orders
      WHERE submitted = 'Y' AND source != 'B'
      ORDER BY order_id ASC
      LIMIT batch_size
      OFFSET batch_size - 1;
    END IF;

    -- 提交当前批次的修改，释放事务日志占用的内存
    COMMIT;
  END LOOP;
END $$;

调用方式：

CALL batch_update_placed_orders();

这个方案的优势：

• 基于主键范围筛选，避免了OFFSET带来的性能损耗；
• 每批提交一次事务，及时释放内存；
• 无需预先统计总条数，循环自动终止，避免数据变化导致的循环次数错误。

方案二：使用游标逐批处理 #

如果主键范围筛选不适合你的场景（比如条件更复杂），可以用游标批量获取待更新的ID，再执行更新：

CREATE OR REPLACE PROCEDURE cursor_batch_update()
LANGUAGE plpgsql
AS $$
DECLARE
  batch_size INT := 1000;
  order_ids UUID[];
  -- 定义游标，只获取需要更新的order_id
  cur CURSOR FOR
    SELECT order_id
    FROM placed_orders
    WHERE submitted = 'Y' AND source != 'B'
    ORDER BY order_id ASC;
BEGIN
  OPEN cur;
  LOOP
    -- 批量获取一批order_id
    FETCH NEXT batch_size FROM cur INTO order_ids;
    -- 没有更多记录则退出循环
    EXIT WHEN order_ids IS NULL OR array_length(order_ids, 1) = 0;

    -- 更新这批记录
    UPDATE placed_orders
    SET source = 'B'
    WHERE order_id = ANY(order_ids);

    -- 提交当前批次，释放内存
    COMMIT;
  END LOOP;
  CLOSE cur;
END $$;

调用方式：

CALL cursor_batch_update();

额外注意事项 #

• 调整batch_size：根据你的服务器内存情况调整，太大可能还是会占用过多内存，太小则会增加事务提交的开销；
• 锁与并发：批量更新会持有行锁，建议在业务低峰期执行，或者调小batch_size减少锁的持有时间；
• 避免并发修改：如果更新过程中有其他程序修改submitted或source字段，可能导致漏更，必要时可以加表锁（LOCK TABLE placed_orders IN EXCLUSIVE MODE;）或在更新条件中增加更严格的校验。

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