PostgreSQL数据库亿级行数据高效更新的最优实现方案

针对数周一次、单表1亿-1.2亿行规模的第三方数据定期更新场景，优先级最高的生产可用方案是「全量导入临时新表+原子切换」，其次是「临时表中转的批量原地更新」，逐条比对单条更新的方案直接淘汰，无落地价值。

• 逐条比对单条更新
  直接排除：1亿行量级下，单条交互的网络IO、事务开销、锁竞争会把更新周期拉长到数小时甚至数天，还会产生巨量WAL日志拖垮实例性能，完全不满足生产可用性要求。

• 全量导入临时新表+原子切换（首选，90%以上同场景生产环境的标准选择）
  落地步骤：

  1. 拿到第三方全量数据后，在同库下创建和生产表结构完全一致的临时中转表（比如生产表叫biz_data，中转表命名为biz_data_staging_2024xx，加日期后缀避免重名），初期不要建索引，先留空表。
  2. 用COPY命令批量把第三方全量数据导入中转表，绝对不要用逐行INSERT，COPY是PostgreSQL批量导入效率最高的原生方式，1.2亿行单表导入通常可以控制在10-30分钟区间（视字段长度、服务器配置浮动）。导入阶段可以临时关闭中转表的autovacuum，进一步提速。
  3. 数据导完后，给中转表补上和生产表完全一致的主键、索引、约束、字段默认值、访问权限，做完基础数据校验（行数核对、核心字段非空校验、枚举值合法性校验），避免脏数据上线。
  4. 用单事务包装表切换操作，保证原子性，切换全程毫秒级，业务无感知：

     BEGIN;
     -- 持有排他锁仅做元数据改名，无数据拷贝开销
     ALTER TABLE biz_data RENAME TO biz_data_old;
     ALTER TABLE biz_data_staging_2024xx RENAME TO biz_data;
     COMMIT;
     

  5. 观察10-20分钟，确认新表查询正常、业务读写无异常后，再异步删除旧表释放磁盘空间。
     优势：
  • 导入全程不碰生产表，不占用生产表锁，完全不影响线上业务正常访问
  • 切换操作是纯元数据修改，毫秒级完成，无业务中断窗口
  • 回滚成本极低，切换后如果发现数据问题，只要在事务里把旧表名改回去就能立刻恢复
  • 没有复杂的变更比对逻辑，人为出错概率极低
    劣势：
  • 导入阶段需要占用和原表相当的磁盘空间，按单条记录平均100字节估算，1.2亿行算上索引大概需要15-25G额外空间，绝大多数生产服务器都能满足。
  • 切换瞬间需要短暂持有表级排他锁，只要不在切换事务里塞入其他慢操作，完全不会造成业务卡顿。

• 临时表中转的批量原地更新（次选，仅适合磁盘空间不足以支撑双表、且每次同步数据变更比例低于20%的场景）
  不要写存储过程循环逐行更新，所有操作必须走集合级SQL，落地步骤：

  1. 同样先用COPY把全量第三方数据导入无索引、无约束的中转临时表，最大化导入速度。
  2. 给中转表仅创建主键字段的索引，为后续关联操作做准备。
  3. 分三步执行批量变更，单步操作如果影响行数超过1000万，建议按主键ID分段执行，避免长事务持锁过久、WAL日志爆量：
     • 第一步：删除生产表中、中转表不存在的失效数据

       DELETE FROM biz_data t
       WHERE NOT EXISTS (
           SELECT 1 FROM staging_data s WHERE s.id = t.id
       );
       

     • 第二步：批量插入中转表有、生产表没有的新增数据

       INSERT INTO biz_data (id, col1, col2, col3)
       SELECT s.id, s.col1, s.col2, s.col3
       FROM staging_data s
       LEFT JOIN biz_data t ON s.id = t.id
       WHERE t.id IS NULL;
       

     • 第三步：仅更新确实有字段变更的存量数据，不要全字段无脑更新，用IS DISTINCT FROM处理NULL值比对问题，减少无效写操作

       UPDATE biz_data t
       SET col1 = s.col1,
           col2 = s.col2,
           col3 = s.col3
       FROM staging_data s
       WHERE t.id = s.id
         AND (t.col1, t.col2, t.col3) IS DISTINCT FROM (s.col1, s.col2, s.col3);
       

  4. 全部更新完成后，建议执行pg_repack（不锁表）或者业务低峰期执行VACUUM FULL回收表碎片，避免长期更新后表膨胀影响查询性能。
     优势：不需要双份全量数据的磁盘空间
     劣势：
  • 更新过程中会产生大量行锁，高并发业务场景可能出现锁等待
  • 大量DELETE/UPDATE操作会产生表碎片，需要额外的空间回收操作
  • 当每次同步变更比例超过30%时，整体执行效率远低于新表切换方案。

• 不管选哪种方案，中转表导入阶段一定要用COPY，不要用多行INSERT，前者导入效率是后者的5-10倍
• 中转表导入、建索引阶段，可以临时调大当前会话的maintenance_work_mem参数（比如设为2G），大幅加快建索引、数据导入速度
• 数周一次的同步场景不要上逻辑复制、触发器之类的重方案，徒增维护成本，没有实际收益
• 如果对数据一致性要求极高，可以在切换前给原表做一次快照备份，进一步降低故障恢复成本

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