PostgreSQL13无冲突场景下INSERT与INSERT...ON CONFLICT DO UPDATE性能差异问询

核心结论 #

在冲突完全不会触发的场景下，INSERT ... ON CONFLICT DO UPDATE（以下简称upsert）确实比普通纯INSERT有可观测的性能开销，但开销幅度远低于「先拆分待插/待更数据、再分两个事务执行」的方案成本。

具体性能差异细节 #

• 无冲突时upsert的额外开销主要来自两个固定环节：
  1. 必须对冲突约束对应的唯一索引做一次确定性的存在性检查，哪怕最终没有冲突，这个检查步骤也不能跳过，每一行都会多一次索引查找的CPU+IO开销
  2. 执行器层面需要为upsert逻辑预留UPDATE路径的内存、行锁准备，这部分是固定的轻量CPU开销
• PG13版本实测（常规批量加载配置，冲突键为有序值场景）：无冲突时upsert的吞吐量比纯INSERT低10%~25%左右，具体幅度取决于唯一索引的大小、行宽度：唯一索引越小、行越窄，开销占比越高，反之则占比越低。

针对批量加载场景的方案建议 #

你当前的周度批量加载场景，几乎没有重叠数据的前提下，两个方案的取舍可以参考以下标准：

• 如果staging表和历史表的冲突键可以低成本做批量区分（比如冲突键是周编号+供应商ID，直接过滤历史表已经存在的周即可）：可以选择拆分方案，先批量INSERT所有不存在的行，再批量UPDATE存在的行，整体性能会比全量upsert高15%~30%，数据量越大收益越明显。
• 如果区分待插/待更行需要全量关联两个大表，关联成本很高：直接用全量upsert是更优选择，避免全表关联的高IO开销，哪怕upsert有少量开销，整体成本也远低于拆分的关联成本。

批量场景额外优化建议 #

• 不要单行执行upsert，用INSERT ... SELECT ... ON CONFLICT批量处理所有行，减少事务开销
• 加载前临时调大maintenance_work_mem、work_mem参数，提升批量索引查找效率
• 如果历史表是分区表，确保冲突键是分区键的一部分，让upsert的索引查找只落到目标分区，进一步降低开销

内容的提问来源于stack exchange，提问作者mikstravaganza