以前做用户行为统计的时候最头疼的一步不是写SQL而是怎么把“新用户要插入、老用户要累加”这个逻辑在一个事务里干完。早期方案是先查一次有了就更新、没有就插入这在单机低并发下勉强能用但流量一起来马上踩坑要么主键冲突报错要么更新重复数据。后来换了PostgreSQL的UPSERTINSERT ON CONFLICT一行SQL搞定不但把竞态问题解决了还顺带少写了几十行应用层代码。今天这篇就专门聊聊这个东西从语法细节到场景实操再到排队思路争取能帮正在被类似问题折腾的同行一次理清。1. UPSERT到底解决了什么痛点1.1 “先查再插”的并发陷阱最常见的朴素做法是应用层先SELECT一条针对某个唯一键判断记录是否存在然后走INSERT或者UPDATE分支。单线程下没问题但并发一起来两个请求同时查到“不存在”然后同时执行INSERT必然有一个撞唯一索引报错。很多人第一反应是捕获异常再UPDATE但这种方式有两个问题一是报错本身会引发连接层面的异常处理事务状态可能受影响二是多一次往返性能白白损耗。还有一种变体是写一个带锁的事务先SELECT FOR UPDATE再决定插入还是更新。这样确实能规避并发冲突但事务里要额外加锁处理不好容易死锁而且代码复杂度明显上升。说白了为了“一条记录是否存在”这个简单判断应用层做了太多事这个本该由数据库来保证的语义不应该让业务代码来跟紧。1.2 UPSERT为什么更省心UPSERT是Update和Insert的合成词在PostgreSQL里的实现就是INSERT ON CONFLICT。它把“尝试插入、冲突就处理”这个动作放到了一条SQL里由数据库内核保证原子性。更关键的是ON CONFLICT后面的处理逻辑可以是DO NOTHING也可以是DO UPDATE甚至能加WHERE条件来控制怎么更新、什么时候更新。这解决的不只是并发问题还有代码维护成本。你不再需要写一大块if-else逻辑也不再需要在应用层维护两种SQL的拼接。而且UPSERT在处理过程中不会像“先查再插”那样暴露中间状态给别的连接数据一致性由数据库的约束机制来保证这一点在多实例场景下尤其重要。1.3 适用场景与不适合的场景UPSERT适合三类典型场景第一种是累加统计类比如用户点击量、播放次数需要按天或按用户维度做递增计数第二种是数据导入去重类比如从外部文件或消息队列写入数据希望重复记录自动跳过第三种是配置同步类外部系统推送配置到本地表存在就覆盖不存在就插入。但有一点必须清醒UPSERT并不是为“高频更新同一条热点记录”设计的。如果你有多个并发事务都在更新同一行就算用了INSERT ON CONFLICT依然会有锁等待和性能瓶颈。这类场景得靠队列削峰、批量合并或者改成分区表不能指望一条UPSERT包治百病。2. ON CONFLICT语法核心细节拆解2.1 最基本的两种形态先看DO NOTHING它表示碰到唯一键冲突时直接跳过不报错、不更新。常用于“这个记录存在就拉倒别重复写”的场景INSERT INTO user_stats (user_id, stat_date, pv) VALUES (101, 2025-01-01, 1) ON CONFLICT (user_id, stat_date) DO NOTHING;这里(user_id, stat_date)必须是对应唯一索引或主键的列。如果表上压根没有这个唯一约束PostgreSQL会直接报错提示你缺失冲突判定的索引。DO UPDATE则是冲突时执行更新逻辑最典型的写法是这样INSERT INTO user_stats (user_id, stat_date, pv) VALUES (101, 2025-01-01, 1) ON CONFLICT (user_id, stat_date) DO UPDATE SET pv user_stats.pv EXCLUDED.pv;重点在EXCLUDED这个伪表它代表“本次想要插入但被挡住的那行数据”。你一定要理解这一步ON CONFLICT捕获到的不是新值而是被唯一约束挡住的待插入值通过EXCLUDED才能拿到它才能去做pv累加这样的操作。2.2 可选的冲突目标与判定表达式conflict_target不一定只能是普通列名也可以是表达式索引或部分索引。比如业务上只对“未删除”的数据做唯一约束就可以用带WHERE条件的部分唯一索引那么ON CONFLICT后面也可以跟着写WHEREINSERT INTO orders (order_no, biz_date, status) VALUES (SO-20250101-0001, 2025-01-01, OPEN) ON CONFLICT (order_no) WHERE status CANCELED DO UPDATE SET biz_date EXCLUDED.biz_date;这里必须注意ON CONFLICT后面跟的WHERE条件要和部分索引的WHERE条件完全一致数据库才认为冲突目标是可用的。否则PostgreSQL不知道你想按哪个唯一约束来判断冲突就会报“there is no unique or exclusion constraint matching the ON CONFLICT specification”。2.3 DO UPDATE的更新范围控制DO UPDATE里的SET子句本质上是“额外执行一次UPDATE”不只是处理冲突还可以更新任意列。你可以用表名引用原行的值也可以用EXCLUDED引用新值INSERT INTO product_sku (sku_code, stock, updated_at) VALUES (SKU-10086, 50, now()) ON CONFLICT (sku_code) DO UPDATE SET stock GREATEST(product_sku.stock, EXCLUDED.stock), updated_at now();这个写法支持表达式、子查询、窗口函数灵活性很高。但切忌把DO UPDATE当成万能的“upsert后门”一旦你在SET里做了复杂的聚合计算执行计划就可能变得很重影响写入性能。3. 从零到一UPSERT实战操作全过程3.1 场景建模统计表如何设计唯一键我直接拿一个实际做过的场景来讲某个内容平台需要统计每篇文章每天被访问的次数。表结构设计如下CREATE TABLE article_daily_stats ( article_id bigint NOT NULL, stat_date date NOT NULL, view_count integer NOT NULL DEFAULT 0, like_count integer NOT NULL DEFAULT 0, updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (article_id, stat_date) );主键就是(article_id, stat_date)这也天然成为ON CONFLICT的冲突判定目标。这里的设计要点是统计表采用“实体ID日期”作为联合主键能够避免同一篇文章同一天存在多条记录不需要再用单独的id自增列。配合UPSERT每次上报直接一条SQL不用先查再插。3.2 累加更新从零实现PV/UV计数假设业务侧每次上报一个阅读事件服务端需要把view_count加1。最简单的实现INSERT INTO article_daily_stats (article_id, stat_date, view_count) VALUES (42, CURRENT_DATE, 1) ON CONFLICT (article_id, stat_date) DO UPDATE SET view_count article_daily_stats.view_count EXCLUDED.view_count, updated_at now();第一次执行时没有历史记录就插入view_count为1。后继续上报则命中唯一约束走DO UPDATE分支把原有的view_count加1。这个操作全程只需要这一条SQL不需要应用层做任何判断。这里有个细节EXCLUDED.view_count其实就是VALUES里给的那个1如果你后续希望一次累加N次直接把VALUES里的数值改成N即可没必要动态拼接多条INSERT。3.3 幂等去重批量导入如何避免重复另一个高频场景是批处理导入。比如上游消息队列里可能重复投递同一张订单落到本地订单表时要保证订单号只存在一条记录INSERT INTO orders ( order_no, user_id, amount, status, created_at ) VALUES ( SO-20250101-0001, 1024, 99.80, PAID, now() ) ON CONFLICT (order_no) DO NOTHING;即便同一个order_no被重复投递十次最终表里也只有一条。这种写法比应用层判断后再INSERT少了很多网络开销而且天然屏蔽了并发重复投递的问题。值得留意的是即使执行DO NOTHINGPostgreSQL依然会消耗一个序列值如果表里使用自增id作为主键后面单独讲这个坑。3.4 动态更新只在特定状态下覆盖业务上还有一种常见的约束只有在老订单还处于“未处理”状态时才允许覆盖否则保留原值。这种需求可以用DO UPDATE配合WHERE实现INSERT INTO orders ( order_no, user_id, amount, status, created_at ) VALUES ( SO-20250101-0001, 1024, 199.00, PAID, now() ) ON CONFLICT (order_no) DO UPDATE SET amount EXCLUDED.amount, status EXCLUDED.status, updated_at now() WHERE orders.status PENDING;当库里已有订单的状态是PENDING时允许更新为新值如果状态已经是PAID或者CANCELED这个UPDATE会被跳过但INSERT也不会报错整体结果是“什么都没发生”。这个语义非常适合“宁可保守也不覆盖已处理数据”的规则。3.5 用于配置表的覆盖写还有一种需求正好相反外部配置推过来必须直接覆盖旧值不用管原状态。这种场景直接在DO UPDATE SET里把字段全赋给EXCLUDED对应的值即可INSERT INTO config_table ( config_key, config_value, version ) VALUES (max_upload_size, 104857600, 3) ON CONFLICT (config_key) DO UPDATE SET config_value EXCLUDED.config_value, version EXCLUDED.version, updated_at now();注意这里其实有个隐含风险如果两条并发事务都以同一个config_key做UPSERT最后写进去的值可能取决于锁等待顺序。对于配置表这种写频率很低的场景基本无感但如果用在热数据上就需要谨慎评估。3.6 批量UPSERT一次处理多行UPSERT同样支持批量写法也就是在VALUES后面跟多条记录一次完成多行的插入或更新。示例INSERT INTO article_daily_stats ( article_id, stat_date, view_count ) VALUES (1, CURRENT_DATE, 10), (2, CURRENT_DATE, 20), (3, CURRENT_DATE, 30) ON CONFLICT (article_id, stat_date) DO UPDATE SET view_count article_daily_stats.view_count EXCLUDED.view_count, updated_at now();这里有一个非常重要的认知点批量UPSERT并不是逐行独立判断的它会一次性尝试插入所有行然后统一处理冲突。如果这批数据里有好几行同时冲突每一行都会执行DO UPDATE。更关键的是如果其中某一行违反了其他不在conflict_target范围内的约束比如外键、非空整条INSERT语句仍然会报错回滚并不是说每个冲突行都被优雅地消化掉了。4. 常见问题与排查技巧实录4.1 冲突目标必须要有对应的唯一索引用BOOLEAN一个最常见的报错就是“there is no unique or exclusion constraint matching the ON CONFLICT specification”。原因很简单你写的conflict_target列在表上没有唯一索引或主键约束。PostgreSQL在做UPSERT时必须靠约束来判断目标冲突所以一定要先确认表结构。即使你在应用层觉得自己“逻辑上唯一”数据库也不认。排查办法-- 查看表的索引定义 SELECT indexdef FROM pg_indexes WHERE tablename article_daily_stats;如果看不到唯一索引就补一个再去跑UPSERT。注意联合唯一索引的列顺序不需要和conflict_target一致但冲突目标列的集合必须完全匹配。4.2 序列空洞问题DO NOTHING也烧序列假设表结构是这样的CREATE TABLE t_import ( id serial PRIMARY KEY, biz_key text UNIQUE, payload text );然后你执行INSERT ... ON CONFLICT (biz_key) DO NOTHING即便所有行都因为冲突被跳过表的id序列仍然会增长。因为PostgreSQL在尝试插入前就会从序列中取值无法预先知道会不会冲突。结果就是id不连续出现大量空洞。这个现象不影响数据正确性唯一键本来就是biz_key主键id只是用于关联。但如果下游系统正在依赖自增id做绝对递增判断比如用于增量拉取就必须小心了。这种场景有几个解决思路一是别用serial改由业务侧生成业务键二是用DO UPDATE而不用DO NOTHING但序列照样会消耗三是对接数据同步时增量判断改用updated_at时间戳而不是id。4.3 死锁并发执行UPSERT导致互相等锁两个事务同时插入相同的两条记录但顺序相反就可能造成死锁-- 事务A先插入key1再插入key2 -- 事务B先插入key2再插入key1因为UPSERT在冲突时需要等待另一个事务的锁释放两个事务互相持有对方需要的锁就形成死锁。PostgreSQL会自动检测死锁报错并回滚其中一个事务。解决办法很简单对这类并发插入的请求在应用层保持同一套写入顺序比如按业务键哈希分片或按键排序后批量处理或者缩短事务时长尽快提交。另一个实用小技巧是在同一事务内尽量一次性批量UPSERT而不是拆成多条独立SQL依次执行。4.4 更新性能没有想象中快有人以为UPSERT是“一条SQL两用”性能一定比两次查询好。实测下来如果冲突率极低UPSERT确实可以减少一次SELECT但如果冲突率很高每次都要执行DO UPDATE代价可能比“UPDATE 不存在再INSERT”更贵。因为UPDATE本身要定位旧行、加锁、写新版本而这些在低冲突场景下都不必发生。这个问题的应对思路有两个一是根据业务特性选择合适的方案预计写多读少且冲突率低直接INSERT并用ON CONFLICT DO NOTHING兜底二是对更新频繁的行评估是否需要走批量合并把多次UPSERT压成一次批量执行。4.5 ON CONFLICT和ON CONSTRAINT有什么区别除了写列名ON CONFLICT还能指定约束名INSERT INTO t_user (user_name, email) VALUES (aa, aaexample.com) ON CONFLICT ON CONSTRAINT t_user_user_name_key DO UPDATE SET email EXCLUDED.email;好处是不用记住具体列只要知道约束名。坏处是一旦约束删除或修改SQL就会失效。实际项目里我更喜欢直接写列名的形式因为可读性更好而且PostgreSQL的query plan也能更直观地找索引。4.6 注意RETURNING和UPSERT的配合UPSERT后完全可以用RETURNING获取最终行内容INSERT INTO article_daily_stats (article_id, stat_date, view_count) VALUES (42, CURRENT_DATE, 1) ON CONFLICT (article_id, stat_date) DO UPDATE SET view_count article_daily_stats.view_count 1 RETURNING article_id, view_count;需要注意如果走了DO NOTHINGRETURNING不会返回任何行如果走了DO UPDATE返回的是更新后的行。这个特性可以省去一次后续查询但前提是你清楚自己的分支走向不然容易出现“期待返回一行结果结果空手而归”的尴尬。5. 其它方案对比什么时候不该用UPSERT5.1 对比MERGE语句PostgreSQL从15版本引入了MERGESQL标准语法可以处理更复杂的“匹配则更新/插入/删除”场景。语法形态大致是MERGE INTO article_daily_stats t USING (SELECT 42 AS article_id, CURRENT_DATE AS stat_date, 1 AS view_count) s ON t.article_id s.article_id AND t.stat_date s.stat_date WHEN MATCHED THEN UPDATE SET view_count t.view_count s.view_count WHEN NOT MATCHED THEN INSERT (article_id, stat_date, view_count) VALUES (s.article_id, s.stat_date, s.view_count);MERGE能做的更多比如WHEN MATCHED可以根据条件执行DELETE或UPDATE这对同步类场景非常有用。但从我的实践经验看简单场景仍然推荐INSERT ON CONFLICT原因在于MERGE在老版本不支持、语义更重而且很多开发人员对它不熟悉反而容易写出逻辑问题。一句话归纳简单加累计用UPSERT复杂同步再考虑MERGE。5.2 什么时候该用PL/pgSQL做显式控制有些场景下你需要在冲突后执行多步操作比如先更新主表再写日志表或者需要检查某列再决定更新/删除这时候一条UPSERT表达不了就得写PL/pgSQL函数加循环。但要意识到这种写法把控制权全部放在数据库端逻辑复杂度变高也不容易单元测试。我的取舍标准是如果业务逻辑超过三条SQL就把它放回应用层用显式事务而不是硬塞进一条UPSERT。数据库的职责是保证约束不是处理复杂业务。5.3 和唯一索引排除约束的联动PostgreSQL还有EXCLUDE约束用于处理范围冲突比如时间范围不重叠。ON CONFLICT同样能识别EXCLUDE约束但写法和普通唯一索引稍有区别。这类场景少普通业务用不到了解一下即可。真正有用的点在于如果你定义了表达式唯一索引比如LOWER(email)唯一那么ON CONFLICT目标可以直接写表达式CREATE UNIQUE INDEX idx_user_email_lower ON t_user (LOWER(email)); INSERT INTO t_user (email, name) VALUES (TestExample.COM, aa) ON CONFLICT (LOWER(email)) DO UPDATE SET name EXCLUDED.name;这个技巧在做邮箱地址去重的时候非常省心不需要应用层先做大小写归一化。6. 性能调优与索引设计建议6.1 精心设计唯一索引让冲突判定快准狠UPSERT的性能极大依赖索引质量。既然冲突判定要走索引那么索引列长度、类型都应该尽量紧凑。比如统计表的联合主键就比单独的bigint自增id更“贴近业务”在按业务键查找时直接能命中聚簇索引。对于超大表可以考虑用BRIN索引搭配日期列减小索引体积。但OLTP场景更稳妥的选择还是B-tree不要为了省空间牺牲冲突判定的速度。6.2 控制事务长度避免长时间持锁建议把UPSERT放进尽可能短的事务里比如一条SQL单独提交不要和十几个其它的写操作绑在一起。锁持有时间越长其他事务等待越久死锁概率越高。如果确实需要批量处理优先用批量VALUES一次搞定而不是循环单条执行。6.3 开启日志观察等待事件排查UPSERT性能瓶颈时注意pg_stat_activity里的wait_event_type。如果大量会话都卡在transactionid上大概率是行锁等待说明存在热点行竞争。需要从业务上拆分写入路径比如把单条热点行改成“按分片键散列多行”再用聚合查询汇总而不是在UPSERT里硬扛。7. 常见问题速查表问题现象可能原因解决方式报错no unique constraint匹配ON CONFLICT指定的列没有唯一索引补建唯一索引或检查列集合是否完整大量序列空洞DO NOTHING也消耗自增id改用业务键替代自增id作为唯一键死锁报错多事务交叉插入相同行统一写入顺序缩减事务时长冲突率很高但性能不升反降每次冲突都执行DO UPDATE评估批量合并或者用UPDATE优先策略RETURNING返回空走了DO NOTHING分支按业务预期判断是否需要返回与MERGE混用出现奇怪行为两者语义不同统一用一种方案8. 实操总结与个人体会踩过几次坑之后我个人对UPSERT的使用已经形成了一套比较稳定的打法执行频率高、冲突率低的写路径用INSERT ... ON CONFLICT DO NOTHING兜底需要精确控制的覆盖写场景用DO UPDATE只要涉及复杂判断比如“冲突后还要判断字段A再决定更新B或删除C”就不硬塞UPSERT果断拆到事务里写普通SQL。数据库没有银弹一条SQL能省事但它只负责把“冲突”这一件事处理干净业务里真正的复杂决策还是得留给人来理顺。另外一个很容易被忽略的经验是上线前务必检查表的唯一索引定义以及序列增长方式。因为UPSERT只在有唯一约束时才能按预期执行而序列空洞的问题虽然不影响校验却可能影响下游的数据订阅或增量同步逻辑。把这些细节在测试环境提前验证好比上线后被数据问题追着跑要舒服得多。如果你正在从“先查再插”的老方案往UPSERT迁移建议先挑一张低频写表试点跑两个礼拜观察序列、死锁和执行计划再去动核心高频表。这样迁移的稳定性会高很多。