使用INSERT...WHERE NOT EXISTS插入PostgreSQL数据仍报唯一约束冲突

环境信息 #

• 操作系统：SUSE Linux 15 SP3
• PostgreSQL版本：13.2

问题现象 #

执行「先检查主键是否存在再插入」的SQL语句时，频繁触发主键重复约束错误。image_uuid是image表的主键，表包含60余列，简化后的SQL如下：

INSERT INTO image ( image_uuid, image_series_uuid )
SELECT 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f', '85c4a85a-10de-47d3-8af5-1c090e119a86' 
WHERE NOT EXISTS (
     SELECT 1 FROM image WHERE image_uuid = 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f'
);

错误信息：

duplicate key value violates unique constraint "image_pkey"
--------
DETAIL:  Key (image_uuid)=(d9aaf41a-9c88-488b-b371-0c0bb1165a4f) already exists.
SCHEMA NAME:  public
TABLE NAME:  image
CONSTRAINT NAME:  image_pkey
_bt_check_unique, nbtinsert.c:656

额外上下文：报错时数据库正执行checkpoint操作，该SQL属于一个单次插入200_{300条类似记录的事务，正常耗时40}80毫秒，报错时耗时超过4秒。

1. 先查后插的非原子性：WHERE NOT EXISTS检查与INSERT是两个独立步骤，高并发场景下，其他事务可能在这两个步骤之间插入相同主键的记录，导致当前事务插入时触发唯一约束冲突。
2. checkpoint放大冲突概率：checkpoint操作会触发大量磁盘IO，拖慢事务执行速度，使得先查后插的时间窗口被放大，并发冲突的概率显著提升。

1. 用原子化语法替换先查后插 #

PostgreSQL原生的INSERT ... ON CONFLICT DO NOTHING语法将主键检查与插入合并为一个原子步骤，彻底消除并发间隙问题。改写后的SQL如下：

INSERT INTO image (image_uuid, image_series_uuid)
VALUES ('d9aaf41a-9c88-488b-b371-0c0bb1165a4f', '85c4a85a-10de-47d3-8af5-1c090e119a86')
ON CONFLICT (image_uuid) DO NOTHING;

批量插入场景可使用多行VALUES：

INSERT INTO image (image_uuid, image_series_uuid)
VALUES 
('uuid1', 'series1'),
('uuid2', 'series2'),
...
ON CONFLICT (image_uuid) DO NOTHING;

2. 优化checkpoint配置缓解IO压力 #

调整PostgreSQL的checkpoint相关参数，减少其对业务的影响：

• 修改postgresql.conf中的参数：
  • checkpoint_timeout：延长检查点间隔（默认5分钟，可调整为10-15分钟，需结合业务容忍的恢复时间调整）
  • max_wal_size：增大WAL日志上限（默认1GB，可调整为4-8GB，减少checkpoint触发频率）
  • checkpoint_completion_target：设置为0.9，让checkpoint操作平缓执行，避免IO突增

修改后需重启PostgreSQL生效，同时确保存储设备IO性能满足业务需求。

3. 确认事务隔离级别 #

默认的READ COMMITTED隔离级别下，先查后插的间隙问题无法避免，若业务无需更高隔离性则无需调整；若使用REPEATABLE READ或更高级别，可能会增加锁等待，需根据实际场景权衡。

内容的提问来源于stack exchange，提问作者Chaoyang.Wang