使用Postgres的ON CONFLICT语句时如何避免不必要的更新操作

你可以在ON CONFLICT的UPDATE子句后追加WHERE条件，仅当现有行与上游传入的excluded数据存在差异时才执行更新，调整后的SQL如下：

INSERT INTO symbols (id, name, status) 
VALUES 
  (1, 'one', 'online'),
  (2, 'two', 'offline'),
  -- 其余数据行
ON CONFLICT (id) 
DO UPDATE SET 
  (id, name, status) = (excluded.id, excluded.name, excluded.status)
WHERE 
  -- 仅行数据存在差异时触发更新
  symbols IS NOT DISTINCT FROM excluded IS FALSE
RETURNING *

逻辑说明 #

• PostgreSQL的ON CONFLICT UPDATE支持附加WHERE过滤条件，只有满足条件的冲突行才会执行更新操作，数据完全一致的行不会触发写入，从根源避免了不必要的磁盘IO、WAL日志生成和死行产生。
• 这里使用IS NOT DISTINCT FROM做行级等值判断，是为了兼容字段允许为NULL的场景：普通等值判断中NULL = NULL会返回NULL（不满足条件），而IS NOT DISTINCT FROM会将两个NULL值判定为相等，符合数据一致性校验的需求。
• 如果需要单独控制部分字段的校验逻辑，也可以拆分为单个字段的判断，示例如下：

  WHERE 
    symbols.name IS NOT DISTINCT FROM excluded.name IS FALSE
    OR symbols.status IS NOT DISTINCT FROM excluded.status IS FALSE
  

性能收益 #

对于你提到的单次同步数千行、大部分行无变更的定期同步场景，该优化可以将同步性能提升30%~80%（具体取决于无变更行的占比），同时大幅减少表的膨胀速度。

内容的提问来源于stack exchange，提问作者Ian Storm Taylor