Upsert与CASE WHEN实现PostgreSQL多行更新的性能与优劣对比

性能表现 #

CASE WHEN 写法的 UPDATE 执行速度更快，整体表现更优：

• Upsert（INSERT ... ON CONFLICT）本质是先执行插入逻辑，触发主键冲突后再转入更新流程——即便你明确所有目标ID都存在，PostgreSQL仍会完成插入前的约束检查、行锁定等额外步骤，带来不必要的开销。
• CASE WHEN 的 UPDATE 直接通过主键索引定位目标行（WHERE id IN (...)），仅对匹配行执行条件更新，逻辑更简洁，锁开销更小。高并发场景下，这种写法的锁竞争概率更低，不会因插入逻辑的额外锁导致等待。

核心优劣差异 #

语义准确性与安全性 #

• Upsert 的设计初衷是兼容「插入+更新」场景，若输入的ID不存在，会自动插入新行，和你“仅需更新”的需求不符，存在误插入数据的风险（比如ID输入错误时）。而 CASE WHEN 的 UPDATE 只会处理已存在的行，完全符合业务意图，更安全。
• 你的 Upsert 示例中，为满足 col1 的非空约束，不得不填写无意义的'dummy value'，这不仅冗余，还可能因疏忽引发错误（比如约束变更时，占位符可能导致插入失败）。而 UPDATE 只需修改目标列col2，无需处理其他列，代码更简洁。

锁机制差异 #

PostgreSQL 中两种写法的锁行为不同：

• Upsert 执行时会先获取**排他锁（ExclusiveLock）**用于冲突检测，锁范围更大，高并发下更容易引发锁等待。
• UPDATE 仅对匹配行获取行级排他锁（RowExclusiveLock），锁粒度更细，对其他事务的影响更小。

返回结果差异 #

使用 RETURNING 子句时：

• Upsert 会返回所有处理的行（包括插入和更新的），若你只关心更新行数，结果需要额外过滤。
• UPDATE 仅返回被成功更新的行，结果更直接符合需求。

适用场景 #

• 优先用 CASE WHEN UPDATE：当你确定所有目标行都存在，仅需批量更新不同行的指定列值时，这是最优选择，性能、安全性、可读性都更优。
• 用 Upsert：仅当你需要同时处理「存在则更新，不存在则插入」的场景时（比如批量同步外部数据），才适合使用。

参考代码 #

create table example (
  id integer NOT NULL,
  col1 varchar(255) NOT NULL,
  col2 varchar(255) NOT NULL,
  CONSTRAINT example_pkey PRIMARY KEY (id)
);

-- 目标：根据id更新col2

-- Upsert写法（适合插入+更新场景）
-- 注意：若id不存在会插入新行
INSERT INTO example
  (id, col1, col2)
  VALUES
  (5, 'dummy value', 'actual value to update 1'),
  (7, 'dummy value', 'actual value to update 2')
ON CONFLICT (id) DO UPDATE
  SET col2=EXCLUDED.col2;

-- CASE WHEN UPDATE写法（适合仅更新场景）
UPDATE example
  SET col2=(
        CASE 
          WHEN id=5 THEN 'actual value to update 1' 
          WHEN id=7 THEN 'actual value to update 2' 
        END
      )
WHERE
  id in (5, 7);

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