我清楚记得第一次在9.5的release notes里看见INSERT ... ON CONFLICT时的反应终于不用再靠规则触发器异常捕获那套歪门邪道来做UPSERT了。从那时起这套实现就一直是PostgreSQL并发写入场景里的顶梁柱。十年过去网上仍然有人问PostgreSQL下载哪个版本9.5是不是太老了其实版本号从来不是关键关键是理解内核里那套机制——从9.5的投机插入到后来各版本对分区表、约束、MERGE的演进理解透了你才能在18上写出不踩坑的业务逻辑。这篇文章我会从源码执行路径的角度把这套UPSERT机制拆开讲清楚。不是念文档而是带你走一遍一条INSERT ON CONFLICT DO UPDATE在9.5的解析器里怎么变成计划在执行器里怎么探测冲突怎么用speculative insertion做到不用回滚就能处理冲突以及后面9.5到18这十几年里哪些能力变了、哪些坑一直存在。适合正在用PostgreSQL做高并发写入、数据同步、报表累加的开发者也适合想开始读PG源码但不知道从哪下手的同学。1. 9.5之前UPSERT是一场灾难为什么ON CONFLICT是刚需1.1 传统做法的三宗罪在9.5之前你要是想在PostgreSQL里实现有则更新无则插入大概只有四条路先SELECT再INSERT/UPDATE、用CTE做UPDATE ... RETURNING当不存在时再insert、靠规则系统改写、或者干脆捕获唯一约束异常。这四条路看着都能跑生产环境里全部有硬伤。最典型的是先查再写两个并发请求同时读到记录不存在然后都去插入其中一个一定会被唯一约束炸回来。你得在外层再包一个EXCEPTION WHEN unique_violation THEN UPDATE这等于把并发正确性完全丢给异常处理。异常捕获的问题在于事务块会变成aborted子事务日志里全是错误信息性能也差——因为每次唯一约束冲突都要做一次完整的索引探测和错误构造。CTE方案稍微好看一点但它不是原子的。理论上你可以用一个WITH语句做到存在就跳过但两个并发事务仍然可能在同一个间隙里都判断为不存在然后一起插入。规则系统更不用说了规则重写只执行一遍遇到批量多行插入时会非常尴尬比如一行冲突、一行不冲突时整个语句的语义会变得极难维护。说到底这些方案都没办法在数据库内核层面回答一个问题同一时刻有两个会话都想插入同一个键到底谁赢、输了的那方该怎么办。1.2 冲突不是一个错误而是一个分支9.5给出的答案本质上是一次理念转变唯一键冲突不再是一个需要抛异常的事情而是插入操作里的一个正常分支。一个插入语句在探测到索引冲突之后可以选择什么都不做也可以选择转成一条更新语句去更新冲突的旧行。因为整个决策发生在执行器内部所以它是原子的——并发会话之间通过索引锁和行锁互相等待最后到手的结果是一致的。这个理念转变带来了一个几乎所有人都忽略的好处你不再需要为了UPSERT把业务拆成两条SQL。一条语句里可以同时包含插入的新值和如何基于新值更新旧值两套逻辑数据库自己负责协调。这也是后来逻辑复制、数据仓库增量同步等场景能大规模使用PostgreSQL的一个基础前提。2. 9.5语法与语义拆解DO NOTHING、DO UPDATE、conflict_target2.1 三种写法和它们的边界9.5的ON CONFLICT语法本身不长但语义边界很微妙。先看最常见的三种-- 方式一不做任何处理 INSERT INTO t (id, val) VALUES (1, a) ON CONFLICT DO NOTHING; -- 方式二指定冲突目标只针对这个唯一键冲突时忽略 INSERT INTO t (id, val) VALUES (1, a) ON CONFLICT (id) DO NOTHING; -- 方式三指定冲突目标冲突时更新 INSERT INTO t (id, val) VALUES (1, a) ON CONFLICT (id) DO UPDATE SET val EXCLUDED.val;注意第一行ON CONFLICT DO NOTHING后面没有冲突目标它表示任意唯一约束冲突都忽略。这种方式可以用于防重复但无法指定只在主键冲突时忽略、在另一个唯一键冲突时报错。如果你要精准控制必须给出conflict_target。conflict_target支持几种形式列名列表、ON CONSTRAINT constraint_name、或者一个唯一索引表达式。它本质上不是随便写两个列就能匹配的——PostgreSQL要求目标必须对应一个已经存在的唯一索引或排除约束。你写了一个没有对应唯一索引的列执行时会直接报no unique or exclusion constraint matching the ON CONFLICT specification哪怕这个列其实可以用来判断主键冲突数据库也不会帮你推断。2.2 conflict_target为什么必须踩着唯一索引走很多初学者不理解为什么要这个限制。直接说吧ON CONFLICT的冲突探测不是靠比较值而是靠索引探测。执行器插入元组之后要把元组插入所有相关索引。当插入到唯一索引时如果发现索引页里已经存在相同键值的另一个元组就说明冲突了。这样设计的好处是效率极高因为你不需要额外扫表也不需要用户提供一套如何进行冲突判断的逻辑坏处是没有唯一索引的列本身就没有资格作为冲突判断的仲裁者。另外conflict_target对应的唯一索引不能是部分索引。文档里明确说它必须是一个non-partial的唯一索引。如果你建的是CREATE UNIQUE INDEX ... WHERE statusactive这种部分索引用它来做ON CONFLICT目标会直接报错。原因是部分索引只能覆盖表的一部分行对于那些不满足索引谓词的行索引里根本没有对应条目于是冲突这个判断就失效了。2.3 EXCLUDED伪表和旧值语义DO UPDATE子句里有两个东西可以引用一个是旧行也就是原表里那个已经存在的冲突行另一个是EXCLUDED表示如果没有冲突将被插入的那一行也就是VALUES里提供的值。这个设计把旧值和新值的区分放到了SQL层面读起来非常直观INSERT INTO stats (user_id, total) VALUES (100, 1) ON CONFLICT (user_id) DO UPDATE SET total stats.total EXCLUDED.total, updated_at now();这里stats.total是旧值EXCLUDED.total是这次要累加的量。注意一点即使你使用了DO UPDATE不一定会更新每一列——你写的哪些列出现在SET里哪些列就被更新。这给后面避免无意义更新留下了操作空间。还有一个隐藏语义当冲突发生时旧行已经被当前事务以FOR UPDATE的语义锁住了。也就是说在有并发的情况下插入方会等待另一个事务提交或回滚后再拿到最新版本的旧行去做更新。这个等待过程极度依赖死锁检测热点键上如果处理不当死锁是家常便饭后面的实战章节我会专门讲。3. 源码级核心机制一条UPSERT语句在9.5里经历了什么3.1 从gram.y到InsertStmt冲突子句如何变成计划参数在PostgreSQL源码里任何SQL都要经过词法、语法、分析、重写、计划、执行这几关。ON CONFLICT也不例外。语法文件src/backend/parser/gram.y里为INSERT语句新增了opt_on_conflict规则它最终会填进InsertStmt结构体中的onConflictClause字段。这个字段的类型是OnConflictClause *里面保存了三个信息action是DO NOTHING还是DO UPDATEinfer冲突目标也就是前面说的唯一索引仲裁条件targetListDO UPDATE时要更新的列和表达式。走到计划阶段时优化器会把InsertStmt改造成一个ModifyTable计划节点这个节点上挂着onConflictAction、arbiterIndexes、updateColsLists等信息。arbiterIndexes是一个Oid数组里面是计划器根据conflict_target到系统目录里匹配出来的唯一索引的OID。计划阶段有一个重要动作它会用ShareLock锁住匹配到的索引对象防止在语句执行期间索引被并发DROP或重建。这个细节很少人注意但你回过头看很多UPSERT执行到一半索引没了的诡异错误其实在这里就已经把好后路断掉了。3.2 ExecInsert与arbiter索引探测冲突执行阶段的主场在src/backend/executor/nodeModifyTable.c。插入一条带ON CONFLICT的行时执行器不会先跑一遍SELECT去看有没有冲突而是按正常插入流程走先把元组放进堆页面然后挨个插入索引。这个流程和普通INSERT最大的差别在于插入唯一索引时如果检测到重复键普通INSERT会抛duplicate key value violates unique constraint错误而ON CONFLICT模式下这个错误不再是终点而是转到了一个内部处理分支。这个内部处理分支的实现在execIndexing.c里。它会把唯一索引冲突的关键信息——冲突的索引、冲突元组在堆里的tid——传给上层。nodeModifyTable.c拿到这个信息后会根据action决定DO NOTHING直接结束不插入、不更新返回一个空的插入结果DO UPDATE切换到更新逻辑先锁定冲突行再构造一条UPDATE元组执行。整个过程中你肉眼看到的表现是要么插入了要么更新了要么什么都没干。但如果你看性能监控会发现这种模式下不会产生像异常捕获那样的大量错误日志这是一个很实在的收益。3.3 speculative insertion避免无谓WAL回滚的钥匙9.5的实现里最核心的机制就是投机插入speculative insertion。这大概是整个ON CONFLICT最精妙的地方。普通INSERT流程中一个元组先插入堆再插入索引然后这条插入会被记入WAL。如果唯一索引冲突后面要回滚WAL里已经写下的插入记录就白白浪费了。9.5的做法是当语句带了ON CONFLICT时先用heapam.c里的heap_insert函数以特殊模式插入元组。这个模式下元组会被打上一个speculative token它在系统表里的可见性是被吊起来的——其他事务能看到这是一个未确认的元组但不把它当作正式数据。关键点是投机插入不会立即写WAL也不会立即终结整个事务。执行器先带着这个幽灵元组去尝试插入唯一索引。如果索引探测发现没有冲突就调用heap_finish_speculative把投机元组转为正式元组并补写WAL。如果发现冲突就调用heap_abort_speculative直接丢弃这个投机元组不走完整的回滚路径。因为投机元组的插入和清除都在内存和缓冲池层面完成没有产生WAL也没有把事务标记为aborted所以它的开销远低于插进去再回滚。听上去有点绕但你可以把它理解成先拿一支笔试探性地在纸上描了一下发现和旁边格子重叠了就直接把笔划擦掉而不是撕掉整页纸重新写。这个设计让高频UPSERT场景下的日志量、回滚段压力、索引维护成本都变得可控也是后来很多性能测试里UPSERT比先查后插快一个数量级的原因。3.4 并发更新EvalPlanQual与行锁重检查UPSERT最难的其实不是单条语句而是多个会话同时UPSERT同一个键。比如两个会话同时执行INSERT ... ON CONFLICT (id) DO UPDATE SET cnt cnt 1 WHERE id 1会发生什么第一个会话插入失败后要拿旧行去更新它需要给旧行加FOR UPDATE行锁。如果第二个会话也进入了冲突处理分支它同样想拿同一行的行锁于是它只能等待第一个会话提交。这是符合预期的——数据库会把并发更新串行化保证最后计数不会丢。但这里有个细节第二个会话等到锁之后它刚才看到的旧行版本可能已经过期了如果第一个会话做了更新。执行器不能直接拿过期的版本去计算cnt 1否则就丢了第一个会话的累加。为此PostgreSQL在9.5里引入了对冲突更新场景的EvalPlanQualEPQ检查。所谓EPQ就是SQL标准里READ COMMITTED下的更新如果行被并发修改则重新读取新行版本再判断。ON CONFLICT DO UPDATE用到的是同一套机制冲突分支拿到的旧行如果发现已经被别的事务更新就重新读取一次该行的最新提交版本然后再执行DO UPDATE的计算。这套机制保证了最后的cnt是两次cnt1之后的值而不是两次都基于初始值最后只加了一次。理解这一点很重要很多并发测试如果只在应用层做压力测试往往看不出问题但你一旦把两路请求同时发过去就会发现计数不符。那不是数据库bug是你没有意识到UPSERT的更新天然要带EPQ重读。3.5 源码阅读路径速查如果你真的想亲手打开源码验证我建议按这个顺序看会顺很多源文件关键点作用src/backend/parser/gram.yopt_on_conflict规则语法解析生成OnConflictClausesrc/include/nodes/parsenodes.hInsertStmt,OnConflictClause语法树结构定义src/backend/optimizer/plan/createplan.cModifyTable节点构造把ON CONFLICT信息挂到计划节点src/backend/executor/nodeModifyTable.cExecInsert执行插入主流程、冲突分支切换src/backend/executor/execIndexing.cExecInsertIndexTuples索引探测收集唯一索引冲突src/backend/access/heap/heapam.cheap_insert,heap_finish_speculative,heap_abort_speculative投机插入的底层实现这个顺序就是一条SQL从文本变成变更的完整链路。我读源码时习惯先在ExecInsert附近打断点因为它往上一层层是计划器和解析器往下一层层是索引和堆非常像一个总线节点。4. 从9.5到18这十年UPSERT发生了什么4.1 分区表与UPSERT的逐步解禁9.5发布时PostgreSQL还没有声明式分区的概念。那时候人们用继承表做分区而ON CONFLICT和继承表有一种别扭的关系理论上可以把ON CONFLICT写到继承表上但子表各自有独立索引跨子表做唯一约束在标准继承场景下就不受保护。10版本引入声明式分区后情况更复杂了。因为分区表本身没有物理存储所有数据都落在子表里唯一约束能不能跨子表取决于分区键和唯一键的关系。比如你按region分区但唯一键是user_id那同一个user_id可能被插入到两个region分区里各自的唯一索引都能通过这时ON CONFLICT就不会触发——因为冲突根本不在同一个索引页面中出现。这些年PostgreSQL一直在补这块短板。分区表上逐步支持了唯一索引、外键也允许把ON CONFLICT用在一级分区上。但直到最近几个版本跨分区级别的唯一冲突仍然很难像普通表那样丝滑。所以我给生产环境的建议一直是如果你要用分区表做UPSERT请把分区键作为唯一约束的一部分或者明确接受冲突判断只能发生在分区内这个约束。这是从9.5到18都在强调的一个边界。4.2 arbiter的扩展与约束匹配能力9.5的实现里conflict_target的匹配逻辑比较严格必须是完整覆盖目标列的唯一索引。后来版本逐步放宽了一些细节比如支持用ON CONSTRAINT指定系统目录里命名约束也允许对索引表达式做判定。这些变化让UPSERT不再只能针对简单的单列主键也能用在复合唯一键、排除约束exclusion constraint上。排除约束是个冷门但很有意思的能力。普通唯一索引只保证等值不重复排除约束可以做到时间区间不重叠二维坐标不重复这类复杂规则。在9.5里如果排除约束触发冲突ON CONFLICT分支也能捕获。这个能力在预订系统、库存占用等场景里非常有用。不过要注意排除约束冲突的处理逻辑比唯一索引更复杂因为判定冲突需要真的去读对比元组而不是简单比键值。这也是为什么很多业务宁可多建一个普通唯一索引也不用排除约束做UPSERT的仲裁目标。4.3 MERGE的登场从ON CONFLICT到SQL标准15版本引入了MERGE语句它可以在一条语句里混合INSERT、UPDATE、DELETE是SQL标准里真正的Upsert。很多人问有了MERGEON CONFLICT是不是该退役了我的看法是两者定位不同。ON CONFLICT专门为插入时撞唯一索引这种高频场景优化路径短、效率高、语义直白非常适合写入密集型业务MERGE更像一个通用的目标表源数据同步工具适合做ETL、数据对账它的语法更宽泛但性能上不一定比得上ON CONFLICT的窄路径。这一点在18版本里依然成立。社区偶尔有讨论要不要在MERGE和ON CONFLICT之间做更多融合但我个人在实际性能测试中简单的累加场景下ON CONFLICT仍然是最稳的选择。4.4 周边生态vacuum、逻辑复制与大规模写入ON CONFLICT DO UPDATE本质上是先失败再更新它会在目标表上产生更新版本。频繁UPSERT一个窄表会让表的dead tuples死元组膨胀速度非常快。所以9.5之后PostgreSQL在vacuum调度上做了很多配套改进包括自动vacuum的work item拆分、索引清理效率优化以及在13版本后对autovacuum参数在频繁更新小表场景下的默认行为调整。逻辑复制场景里UPSERT也扮演了重要角色。标准INSERT经过逻辑复制到订阅端后如果订阅端已经有同主键行会导致复制冲突。解决办法之一就是在订阅端用ON CONFLICT DO NOTHING或DO UPDATE来吞掉冲突。这在10版本逻辑复制刚出来时就是常见配置。后续版本又不断给逻辑复制加了冲突检测和跳过机制但ON CONFLICT仍然是订阅端最常见的兜底方案。从9.5到18语法本身几乎没有变化变的是它跑在什么表结构上普通表、分区表、逻辑复制订阅端、能匹配什么约束唯一索引、排除约束、以及周围基础设施对它的友好程度。内核机制已经相当稳定真正需要你关注的其实是自己的业务模型是否踩中了它天生的那些边界。5. 实战我在生产环境里见过的UPSERT翻车现场5.1 无意义更新导致xid爆炸和表膨胀这是最常见的一个坑。很多人写成这样INSERT INTO t (id, cnt) VALUES (100, 1) ON CONFLICT (id) DO UPDATE SET cnt EXCLUDED.cnt;这段SQL的意图可能是如果存在就把它设置成1。但问题是即使旧行的cnt已经是1DO UPDATE分支依然会执行更新生成一个新版本并且消耗一个新的事务IDxid。在高并发写入同一个热行时这一行会被反复更新成相同内容每秒钟产生几十上百个死元组表和索引一路膨胀vacuum根本追不上。正确做法是用WHERE做哨兵INSERT INTO t (id, cnt) VALUES (100, 1) ON CONFLICT (id) DO UPDATE SET cnt 1 WHERE t.cnt IS DISTINCT FROM 1;拿IS DISTINCT FROM而不是是为了处理NULL和相等比较时的边界。这样当旧值已经等于目标值时WHERE为假更新分支不执行也就不会制造死元组。你可以在执行计划里看到这时冲突分支会变成not updated热度下降了不止一个量级。5.2 热点唯一键上的并发更新死锁UPSERT在高并发下比普通UPDATE更容易触发死锁原因在于冲突处理时的逆向锁顺序。举个我实际遇到过的例子两个会话都执行同一段批量UPSERT会话A按id1,2的顺序处理会话B按id2,1的顺序处理。A先拿到1的行锁B先拿到2的行锁随后A去拿2、B去拿1互相等待PostgreSQL死锁检测器介入其中一方报错回滚。这个问题本质上可以通过所有会话都按同样的顺序处理key来规避。如果业务做不到全局排序至少要在UPSERT语句层面保证一组记录的处理顺序稳定。还有一个细节ON CONFLICT DO UPDATE的锁等待发生在冲突行的FOR UPDATE上如果同一行被连续密集更新lock_timeout设得短一点可以避免整个连接卡死。我这里给个通用建议线上环境把lock_timeout设置成比如5秒至少让问题暴露成一条带canceling statement due to user request的错误而不是无限期等锁。5.3 与序列、逻辑复制交互时的注意事项在高频UPSERT场景里使用serial或IDENTITY列时你会观察到序列值出现很多空洞因为九成的插入请求可能在冲突后被丢弃或转成更新但序列已经先取走了值。这是正常现象不是bug。如果你下游有ETL要求id连续那就不该用数据库序列而应该在应用层生成业务唯一键。逻辑复制的订阅端如果用ON CONFLICT DO NOTHING避免主键冲突要小心一个副作用它会把真正的数据差异也一起吞掉。比如发布端同一个主键更新了新值订阅端的冲突处理只做了DO NOTHING导致订阅端数据滞后且无感知。对需要强一致的数据正确做法是DO UPDATE并且把WHERE条件设计成只有真正需要更新时才更新这样才能既解决复制冲突又保证数据最终一致。5.4 升级版本时的行为差异检查清单从9.5一路升级到17、18这些版本时ON CONFLICT的SQL写法几乎没有破坏性变化真正的风险来自周边如果你的表从继承表改成了声明式分区ON CONFLICT的仲裁目标需要重新建立唯一索引如果启用了并行插入、并行查询注意EPQ重读在并行计划下的行为边界如果使用了本地临时表、跨数据库外部表某些组合下ON CONFLICT不可用或行为不一致EXCLUDED伪表在触发器、规则、CLUSTER等周边功能里的表现升级前最好跑一遍回归测试涉及lastval()和RETURNING时冲突分支的返回值可能不符合你预期。我遇到过最坑的升级问题不是ON CONFLICT本身而是旧版本里靠异常捕获UPDATE实现UPSERT的存储过程在升级后因为事务状态处理差异导致部分冲突被吞。这类东西只能通过全面的集成测试暴露。6. 现在还想研究源码应该从哪里下手6.1 从EXPLAIN看到计划层的行为差异没有读代码习惯的同学可以先从执行计划下手。在9.5及之后的版本里执行EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO t (id, val) VALUES (1, x) ON CONFLICT (id) DO UPDATE SET val EXCLUDED.val;你会发现INSERT走的是Insert on t的ModifyTable节点但输出里不会直接告诉你冲突仲裁索引是谁。想看计划器到底选中了哪个索引可以临时打开SET debug_print_plan on; SET client_min_messages debug;再执行UPSERT服务器日志/客户端会打出计划树的完整结构里面会包含onConflictActionONCONFLICT_UPDATE和arbiterIndexes的OID。看到OID后你在系统目录里反查SELECT relname FROM pg_class WHERE oid arbiter_index_oid::oid;就能确定计划器选择的仲裁索引。这个方法我在很多次为什么ON CONFLICT没触发的问题调查里用过非常快。6.2 核心函数阅读顺序建议如果你想看源码我建议按这个顺序读第一章看语法结构第二章看执行器第三章看堆管理基本就能扫完主干。先从gram.y找出所有带on_conflict的规则理解语法树里都有什么。然后跳到nodeModifyTable.c找到ExecInsert。这个函数很长你不用一次读完只看它怎么判断onConflictAction不为ONCONFLICT_NONE时的分支。接着去execIndexing.c找ExecInsertIndexTuples看它如何返回冲突路径。最后到heapam.c里找带speculative字样的函数理解什么情况下调用heap_finish_speculative、什么时候调用heap_abort_speculative。这样的阅读顺序不依赖IDE用grep和ctags就够了。源码版本建议直接拉master或者17/18的branch因为9.5的代码结构和现在已经有很大差异但核心的ExecInsert、heapam几个函数的骨架变化不大。6.3 用gdb断点验证投机插入想更直观地感受speculative insertion可以自己编译一个DEBUG模式的PostgreSQL然后用gdb挂在后端进程上在heap_finish_speculative和heap_abort_speculative分别打断点。先开一个正常表单独插入一行。另一个会话执行INSERT ... ON CONFLICT DO UPDATE命中已经存在的行。你会清楚看到断点首先落在heap_finish_speculative之后的某个地方但这里不会直接执行finish——因为插入流程要先探测索引。只有当唯一索引探测表明没有冲突时才会执行heap_finish_speculative一旦探测到冲突代码会走heap_abort_speculative。这个调试过程能让你直观理解为什么9.5设计时非要引入投机插入如果没有投机机制唯一索引冲突会导致整个堆插入被回滚而回滚和投机abort根本不是一个成本量级。亲手跑一次断点比看十遍文档都记得牢。PostgreSQL的ON CONFLICT从9.5一路走到18语法上几乎没有变化这说明它当初的设计边界是对的。真正需要你花时间理解的是那套藏在冲突分支背后的索引探测、投机插入、EPQ重读和锁等待机制。我在实际项目里用这套机制扛过每天几亿次累加写入也见过因为无意义更新把表撑到几百GB的翻车现场。把它当做一个精密的并发原语来尊重而不是一句有则更新无则插入的语法糖你就能少踩很多看不见的坑。