PostgreSQL UPSERT实践：INSERT ON CONFLICT解决写入冲突

以前做用户行为统计的时候，最头疼的一步不是写SQL，而是怎么把“新用户要插入、老用户要累加”这个逻辑在一个事务里干完。早期方案是先查一次，有了就更新、没有就插入，这在单机低并发下勉强能用，但流量一起来马上踩坑，要么主键冲突报错，要么更新重复数据。后来换了PostgreSQL的UPSERT（INSERT 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后面也可以跟着写WHERE：

INSERT 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 NOTHING，PostgreSQL依然会消耗一个序列值（如果表里使用自增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先插入key=1，再插入key=2 -- 事务B先插入key=2，再插入key=1

因为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', 'aa@example.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 NOTHING，RETURNING不会返回任何行；如果走了DO UPDATE，返回的是更新后的行。这个特性可以省去一次后续查询，但前提是你清楚自己的分支走向，不然容易出现“期待返回一行结果，结果空手而归”的尴尬。

5. 其它方案对比：什么时候不该用UPSERT

5.1 对比MERGE语句

PostgreSQL从15版本引入了MERGE（SQL标准语法），可以处理更复杂的“匹配则更新/插入/删除”场景。语法形态大致是：

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 ('Test@Example.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. 常见问题速查表

8. 实操总结与个人体会

踩过几次坑之后，我个人对UPSERT的使用已经形成了一套比较稳定的打法：执行频率高、冲突率低的写路径用INSERT ... ON CONFLICT DO NOTHING兜底；需要精确控制的覆盖写场景用DO UPDATE；只要涉及复杂判断，比如“冲突后还要判断字段A再决定更新B或删除C”，就不硬塞UPSERT，果断拆到事务里写普通SQL。数据库没有银弹，一条SQL能省事，但它只负责把“冲突”这一件事处理干净，业务里真正的复杂决策还是得留给人来理顺。

另外一个很容易被忽略的经验是：上线前务必检查表的唯一索引定义，以及序列增长方式。因为UPSERT只在有唯一约束时才能按预期执行，而序列空洞的问题虽然不影响校验，却可能影响下游的数据订阅或增量同步逻辑。把这些细节在测试环境提前验证好，比上线后被数据问题追着跑要舒服得多。

如果你正在从“先查再插”的老方案往UPSERT迁移，建议先挑一张低频写表试点，跑两个礼拜观察序列、死锁和执行计划，再去动核心高频表。这样迁移的稳定性会高很多。