页边剪报

剪报:PostgreSQL 15 MERGE 与 INSERT ... ON CONFLICT 的并发语义差异

· 来源备忘:PostgreSQL 15 Release Notes & Cybertec 博客摘录 #postgresql#sql

PG 15 终于加上了标准 SQL 的 MERGE 语句,第一反应是以后是不是不用写 ON CONFLICT DO UPDATE 了。读完 Cybertec 的测试文章才发现两者的并发保证根本不是一回事:

MERGE does not guarantee atomicity against concurrent INSERTs under READ COMMITTED: if two sessions run MERGE for the same missing key simultaneously, both see NOT MATCHED and one can still fail with unique_violation!
— cybertec-postgresql.com, MERGE in PostgreSQL 15

简单记结论:

  • 单条或小批量高频并发幂等写入(“有则更新,无则插入”),老老实实继续用 INSERT ... ON CONFLICT,它有底层 speculative insertion 锁兜底,绝不会抛唯一键冲突。
  • 跑批同步整张临时表(既有更新、又有新增、还要顺带把源表消失的行 DELETE 掉),这时候才是 MERGE 的主场。