为何Aurora Postgres全表2000万行更新耗时远超插入？如何优化？

核心原因 #

1. MVCC机制的额外开销
   PostgreSQL（包括Aurora）基于MVCC（多版本并发控制）实现事务，更新操作并非直接修改原数据行，而是标记旧行失效后写入新行版本。这会生成两倍于插入的WAL（预写日志）数据——既要记录旧行快照，也要记录新行内容。而插入仅需写入新行的WAL，日志量和IO开销低得多。

2. 索引同步成本
   如果表上存在索引（包括my_column自身的索引或其他关联索引），更新操作需要同步修改所有涉及的索引条目。每更新一行，都要删除旧索引条目并插入新条目，这个过程的IO和CPU开销远大于插入时的单次索引写入。

3. 并行执行能力差异
   你用多连接并行脚本插入数据，能充分利用多核和多连接资源；但默认单语句UPDATE是单进程执行（PostgreSQL对DML的并行支持有限，全表更新的并行度远不如批量插入），无法发挥多核优势。

4. 存储访问模式不同
   插入通常是顺序写入新数据块，Aurora分布式存储对顺序写入优化更好；而更新需要随机定位到每个现有数据块，修改后同步到存储节点，随机IO的延迟远高于顺序IO。

5. 版本膨胀与垃圾回收压力
   更新过程中会产生大量临时旧行版本，这些版本需保留到所有引用事务结束，临时占用更多存储的同时，还会触发后台垃圾回收（VACUUM）的额外开销，进一步拖慢更新速度。

优化方法 #

• 分批并行更新
  将全表拆分为多个小批次，通过多连接并行执行：

  -- 按主键分批次，例如每批次10万条
  UPDATE my_table SET my_column = 'some_value' WHERE id BETWEEN 1 AND 100000;
  UPDATE my_table SET my_column = 'some_value' WHERE id BETWEEN 100001 AND 200000;
  -- 用脚本自动生成批次语句并多连接执行
  

  既能利用多核资源，也能减少单事务的锁范围和WAL写入压力。

• 临时移除非必要索引
  如果表上有非核心业务的索引，先删除索引，完成更新后再重建：

  DROP INDEX idx_my_table_my_column; -- 假设该索引非必需
  UPDATE my_table SET my_column = 'some_value';
  CREATE INDEX idx_my_table_my_column ON my_table(my_column);
  

  重建索引的效率远高于逐行更新索引，因为重建是批量扫描数据并写入索引，而更新是逐行修改。

• 用INSERT替代UPDATE（适合无实时写入场景）
  如果允许短时间的表替换窗口，可以创建新表并批量插入数据，再切换表名：

  -- 创建新表并写入数据，同时设置目标列值
  CREATE TABLE new_my_table AS SELECT *, 'some_value' AS my_column FROM my_table;
  -- 复制原表的约束、索引（如果需要）
  ALTER TABLE new_my_table ADD PRIMARY KEY (id);
  -- 原子性切换表名
  BEGIN;
  DROP TABLE my_table;
  ALTER TABLE new_my_table RENAME TO my_table;
  COMMIT;
  

  完全利用插入的高效性，避免MVCC的更新开销。

• 调整WAL与并行参数（需结合Aurora配置）

  • 增大wal_buffers，减少WAL频繁刷盘的次数（Aurora部分参数由AWS管理，需确认可调整范围）；
  • 调整max_parallel_workers_per_gather，提升查询并行度，让更新语句能利用更多核心；
  • 适当延长checkpoint_timeout，减少检查点触发的IO峰值，但需注意会增加数据库恢复时间。

• 使用分区表
  如果表已按某个键（如id、时间）分区，可以针对每个分区并行执行更新，每个分区的更新操作独立，大幅提升并行效率。

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