PostgreSQL大表批量更新提速咨询：2亿行数据优化方案

嘿，针对你这种2亿行大表、80%数据需要更新的场景，常规UPDATE确实容易因为WAL日志堆积、锁持有时间过长等问题拖慢速度，我给你几个实战验证过的提速方案，你可以按需选择：

当超过70%的数据需要更新时，重建表的效率通常远高于逐行UPDATE——UPDATE会产生大量死元组和WAL日志，而CTAS是批量写入新数据，几乎没有额外开销。具体步骤：

• 第一步：创建包含更新后数据的新表
  关联源表把新字段的值直接带入，注意保留原表所有字段：

  CREATE TABLE new_target_table AS
  SELECT 
    t.*, -- 原表所有已有字段
    -- 用源表的值填充新增字段，若源表无匹配则保留原表默认值（如果有的话）
    COALESCE(s.new_field1, t.new_field1) AS new_field1,
    COALESCE(s.new_field2, t.new_field2) AS new_field2
  FROM target_table t
  LEFT JOIN source_table s ON t.join_column = s.join_column;
  

• 第二步：重建原表的索引、主键和约束
  这一步要确保和原表的约束完全一致：

  -- 重建之前删除的复合主键
  ALTER TABLE new_target_table ADD CONSTRAINT pk_target_table PRIMARY KEY (col1, col2, col3, col4);
  -- 重建其他必要的索引
  CREATE INDEX idx_target_table_xxx ON new_target_table (xxx_column);
  

• 第三步：原子交换表（几乎无停机）
  用事务包裹重命名操作，确保切换过程中业务无感知：

  BEGIN;
  -- 重命名原表为备份表
  ALTER TABLE target_table RENAME TO target_table_old;
  -- 把新表重命名为原表名
  ALTER TABLE new_target_table RENAME TO target_table;
  COMMIT;
  

• 最后：验证数据无误后删除备份表
  确认业务正常、数据匹配后再清理旧表：

  DROP TABLE target_table_old;
  

如果不能重建表，就把大更新拆成小批量提交，避免长事务拖垮数据库。这里给你一个PL/pgSQL的循环脚本示例：

DO $$
DECLARE
  batch_size INT := 150000; -- 建议根据服务器内存调整，10万-50万之间测试
  updated_count INT;
BEGIN
  LOOP
    -- 只更新需要修改的行，避免重复操作
    UPDATE target_table t
    SET new_field1 = s.new_field1, new_field2 = s.new_field2
    FROM source_table s
    WHERE t.join_column = s.join_column
      -- 过滤出字段值不一致的行，减少无效更新
      AND (t.new_field1 IS DISTINCT FROM s.new_field1 OR t.new_field2 IS DISTINCT FROM s.new_field2)
    LIMIT batch_size;

    -- 获取本次更新的行数
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    -- 没有更新行就退出循环
    IF updated_count = 0 THEN
      EXIT;
    END IF;
    -- 每批提交，释放锁和事务日志
    COMMIT;
    -- 可选：加个短暂延迟，避免占满CPU
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

在更新前临时调整以下参数，能进一步提升速度（更新后记得改回原值）：

• 调高maintenance_work_mem：给索引创建、数据写入分配更多内存，比如服务器有32G内存的话，可设为4GB：

  SET maintenance_work_mem = '4GB';
  

• 临时关闭目标表的自动清理：避免autovacuum在更新过程中频繁清理死元组拖慢速度：

  ALTER TABLE target_table SET (autovacuum_enabled = false);
  -- 更新完成后改回
  ALTER TABLE target_table SET (autovacuum_enabled = true);
  

• 扩大WAL日志限制：减少checkpoint的触发频率，降低磁盘IO压力：

  SET max_wal_size = '16GB';
  

检查你的source_table的关联字段（也就是JOIN时用的字段）是否有索引——如果没有，每次关联都会全表扫描source_table，这会严重拖慢速度。赶紧补上：

CREATE INDEX idx_source_join_column ON source_table (join_column);

最后提醒一句：不管用哪个方案，一定要先在测试环境验证，避免影响生产数据！

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