如何在AWS Aurora Serverless v2（PostgreSQL 15）中慢速更新大表？

针对2亿行、主键为随机UUID且无其他索引的PostgreSQL表，直接全表更新会耗尽资源影响生产，以下是几种可控的慢速更新方案，耗时几天或几周均可适配：

方案1：按UUID范围分批更新 #

利用UUID的字符串排序特性，将主键拆分为多个连续的字符范围，每次仅更新一个范围内的数据，通过控制批次大小和间隔时间降低负载。

• 操作步骤：
  1. 先估算UUID分段的数据量，确保每批数据均匀：

     SELECT COUNT(*) FROM your_table WHERE id >= '00000000-0000-0000-0000-000000000000' AND id < '22222222-2222-2222-2222-222222222222';
     

  2. 编写循环更新逻辑，每次处理一个分段，加入幂等条件避免重复更新：

     UPDATE your_table
     SET target_column = 'new_value'
     WHERE id >= 'start_uuid' 
       AND id < 'end_uuid'
       AND target_column != 'new_value';
     

  3. 每批更新后暂停一段时间，释放资源：

     SELECT pg_sleep(60); -- 暂停60秒，可根据负载调整
     

  4. 可以用PL/pgSQL存储过程或外部脚本（如Python）自动遍历所有分段，无需手动执行每一批。

方案2：游标逐批处理 #

通过PostgreSQL游标分批获取需要更新的行ID，逐批执行更新，严格控制每批处理的行数，适合对负载敏感的场景。

• 操作步骤：
  1. 开启事务并创建游标，仅获取未更新的行ID：

     BEGIN;
     DECLARE update_cursor CURSOR FOR
       SELECT id FROM your_table WHERE target_column != 'new_value';
     

  2. 循环获取批次数据并更新，每批提交一次事务：

     FETCH NEXT 10000 FROM update_cursor INTO temp_id;
     WHILE FOUND LOOP
       UPDATE your_table SET target_column = 'new_value' WHERE id = temp_id;
       FETCH NEXT 10000 FROM update_cursor INTO temp_id;
       COMMIT; -- 避免长事务占用资源
       BEGIN;
       SELECT pg_sleep(30); -- 每批后暂停30秒
     END LOOP;
     CLOSE update_cursor;
     COMMIT;
     

  注意：游标初始化时会触发全表扫描，可能有短暂IO负载，但后续分批处理的负载可控。

方案3：结合业务低峰时段调度 #

利用Aurora Serverless v2的自动扩缩容特性，在业务低峰时段加大更新批次，高峰时段缩小批次或暂停，平衡更新效率与生产影响。

• 操作步骤：
  1. 分析业务流量，确定低峰时段（如凌晨2:00-6:00）。
  2. 低峰时段设置较大批次（如20万行）、较短间隔（如10秒）；高峰时段设置小批次（如2万行）或暂停更新。
  3. 用AWS Lambda配合CloudWatch Events定时触发不同时段的更新逻辑，实现自动化调度。

方案4：新建表逐步迁移（最小生产影响） #

如果业务允许短暂停服或支持双写，通过新建表分批迁移数据并设置目标列值，最后切换表名，这种方式对生产的影响最小。

• 操作步骤：
  1. 创建与原表结构完全一致的新表：

     CREATE TABLE new_your_table (LIKE your_table INCLUDING ALL);
     

  2. 分批插入数据，同时设置目标列的新值：

     INSERT INTO new_your_table
     SELECT id, col1, col2, 'new_value' AS target_column, colN -- 列出所有列
     FROM your_table
     WHERE id >= 'start_uuid' AND id < 'end_uuid';
     

  3. 所有数据迁移完成后，切换表名（需暂停写入或开启双写保证数据一致性）：

     ALTER TABLE your_table RENAME TO old_your_table;
     ALTER TABLE new_your_table RENAME TO your_table;
     

  4. 验证数据无误后，删除旧表释放存储空间。

通用注意事项 #

• 操作前务必备份数据，防止操作失误导致数据丢失。
• 实时监控Aurora的CPU、内存、IOPS指标，根据实际负载动态调整批次大小和间隔。
• 所有更新语句加入target_column != 'new_value'条件，保证操作幂等，脚本中断后可直接重启。

内容的提问来源于stack exchange，提问作者Joey Yi Zhao