Postgres无自增ID表使用ON CONFLICT DO NOTHING性能是否达标？

除了自增序列跳号问题外，还有以下需要重点关注的点：

• 冲突检测的性能开销：必须为冲突判断的列/列组合创建有效唯一索引，如果没有索引支撑，每一行插入都会触发全表扫描判断冲突，百万级数据下耗时会指数级上涨。就算有索引，每次插入都要先查一次索引判断冲突，冲突率越高，额外的查询开销占比越大。
• 事务日志（WAL）膨胀：就算插入冲突最终没有写入有效数据，冲突的行依然会生成WAL日志记录，高冲突率场景下WAL生成量会远高于预期，可能打爆存储、拖慢主从同步速度。
• 锁争抢开销：批量插入时如果冲突行很多，会产生大量行级锁争抢，尤其是多个并发任务同时执行ON CONFLICT DO NOTHING操作同一张表时，锁等待会严重拉低吞吐。
• 分区表适配问题：如果操作的是分区表，冲突检测需要每个分区单独判断，冲突率高的情况下分区路由+多次检测的开销会比普通单表高很多。

就算没有自增字段，ON CONFLICT DO NOTHING依然可能引发表膨胀，原因如下：
PostgreSQL的MVCC机制要求，就算插入冲突最终没有写入有效行，数据库也会先尝试写入新的行版本（tuple），之后再标记这个版本为无效（dead tuple）。这些无效tuple如果没有被vacuum及时清理，就会持续占用表的物理存储空间，也就是表膨胀。高冲突率场景下，无效tuple的生成速度会远高于普通插入场景，如果vacuum参数配置不合理，很短时间内就会出现明显的表膨胀，查询性能也会随之下降。
同时索引也会同步产生膨胀：就算最终行没有插入成功，尝试写入时也会先在索引中写入对应条目，之后再标记为无效，同样会占用索引的存储空间，拉高索引扫描的开销。

它不一定是最差方案，但高冲突率场景下确实属于性能较差的实现，可以根据场景选择更适配的方案：

• 如果冲突率极低（<5%），ON CONFLICT DO NOTHING的额外开销可以接受，而且语法简洁、原子性有保障，是可以正常使用的。
• 如果冲突率较高（>20%），它的性能会远低于「先批量去重+过滤已存在的行，再批量插入无冲突数据」的方案：后者只需要一次批量查询捞取已存在的主键/唯一键，过滤后直接批量插入无冲突的行，不需要逐行做冲突检测、也不会生成大量无效tuple和WAL日志，性能能提升几倍到几十倍不等。
• 如果是需要更新冲突行的upsert场景，ON CONFLICT DO UPDATE的开销比DO NOTHING更高，高冲突率下更推荐先做数据预过滤再操作。

内容的提问来源于stack exchange，提问作者Z. M.