PostgreSQL高性能更新数百万非连续ID记录的最佳方案

针对2.5亿行规模的entries表，不要直接拼接超长WHERE IN列表执行更新——这种写法在ID量级到十万以上时，会出现SQL解析慢、执行计划劣化、长事务锁表等问题，实测性能比下面的方案差10倍以上。

核心思路 #

用临时表承接CSV中的目标ID，通过主键关联做更新，配合小批量提交控制事务大小，把单次大操作拆成多个短事务，既避免锁表影响业务，也能把IO压力控制在合理范围。

落地步骤 #

• 第一步：高速导入CSV到临时表
  用PostgreSQL原生的COPY命令导入数据，比应用层批量INSERT快一个数量级，全程不需要把ID读到应用内存：

  -- 创建会话级临时表，会话结束自动清理，不占用持久化存储
  CREATE TEMP TABLE tmp_target_ids (
    id BIGINT PRIMARY KEY -- 建主键，后续关联时走索引匹配，性能提升数倍
  ) ON COMMIT DROP;
  
  -- 直接从服务器本地路径加载CSV，示例CSV每行末尾带逗号，用csv格式自动识别字段
  COPY tmp_target_ids(id)
  FROM '/your/path/2.csv'
  WITH (
    FORMAT csv,
    FREEZE ON -- PostgreSQL 14+支持，导入后直接冻结数据页，省掉后续vacuum开销
  );
  

  注意：CSV中带前导零的ID（比如示例里的0899436814）会被BIGINT类型自动做数值转换，不需要额外做数据清洗，只要文件中没有非数字的非法字符即可。

• 第二步：分批关联更新
  不要一次性更新所有匹配行，小批量循环提交，每批更新1万~5万行（根据实例配置调整，内存够可以开到5万），避免长事务持锁、WAL暴涨：

  -- 会话级调大内存参数，加快关联和排序速度，不影响全局配置
  SET work_mem = '1GB';
  SET maintenance_work_mem = '2GB';
  
  DO $$
  DECLARE
    batch_size CONSTANT INT := 10000;
    updated_rows INT;
  BEGIN
    LOOP
      WITH matched_batch AS (
        SELECT t.id
        FROM tmp_target_ids t
        INNER JOIN entries e ON e.id = t.id
        -- 跳过已经打过标签2的行，避免重复追加产生无效更新
        WHERE NOT (2 = ANY(e.tags))
        LIMIT batch_size
        FOR UPDATE SKIP LOCKED -- 碰到被其他事务锁定的行直接跳过，不阻塞等待
      )
      UPDATE entries e
      SET tags = array_append(e.tags, 2)
      FROM matched_batch
      WHERE e.id = matched_batch.id;
  
      GET DIAGNOSTICS updated_rows = ROW_COUNT;
      COMMIT; -- 每批提交一次，立刻释放持有的行锁，截断WAL日志
      EXIT WHEN updated_rows = 0;
    END LOOP;
  END $$;
  

额外优化建议 #

• 任务尽量选业务低峰期执行，虽然分批更新对业务的锁影响极小，但批量IO还是会占用一定磁盘带宽。
• 如果表上有针对tags字段的自定义索引、或者关联的非必要触发器，更新前可以临时关闭，更新完成后重建，能减少40%以上的更新开销。
• 如果CSV文件在你本地电脑而不是数据库服务器上，可以用psql的\copy命令代替服务端COPY，不需要把文件传到服务器，导入性能差距很小。

为什么不推荐直接用长IN列表 #

• 百万级参数的IN列表，SQL解析阶段就要消耗几十秒时间，还很容易触发数据库的最大参数长度限制直接报错。
• 超长IN列表会让查询优化器难以生成最优执行计划，大概率会放弃主键索引走全表扫描，2.5亿行全表扫描通常要几十分钟，直接打满磁盘IO。
• 一次性更新百万行的长事务会持有行锁长达几十分钟甚至数小时，所有访问这些行的业务请求都会被阻塞，很容易引发业务雪崩。

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