使用Diesel向PostgreSQL13插入数据时如何避免冲突导致自增ID浪费

你遇到的ID空洞问题本质是PostgreSQL的GENERATED ALWAYS AS IDENTITY底层用序列实现，序列的取值操作是非事务性的：只要INSERT语句触发了序列取值，哪怕后续因为冲突插入失败，已经消耗的序列值也不会回滚，就会出现ID跳变的情况。

方案1：判断业务是否真的需要无空洞ID #

绝大部分业务场景下，ID只要保证唯一即可，连续性没有强制要求。如果没有明确的ID连续的业务规则，完全不需要处理这个问题，序列的空洞不会影响数据库功能，也不会带来性能损耗。

方案2：用INSERT ... WHERE NOT EXISTS替代ON CONFLICT DO NOTHING #

如果确实要避免ID浪费，可以用带存在判断的插入写法，只有不存在匹配数据时才会执行插入逻辑，不会触发序列取值：

原生SQL逻辑 #

INSERT INTO songs (title, artist, other_fields)
SELECT $1, $2, $3
WHERE NOT EXISTS (
    SELECT 1 FROM songs WHERE 你的唯一约束字段 = $1
);

这种写法在数据已存在时根本不会执行插入动作，自然不会消耗序列值。

Diesel对应写法 #

你可以用Diesel的select配合filter构造插入源：

use diesel::dsl::select;
use diesel::prelude::*;

// 示例假设你的唯一约束是title字段，按实际业务替换即可
let insert_stmt = diesel::insert_into(songs::table)
    .values(
        select((
            new_song.title,
            new_song.artist,
            // 对应你NewSong结构的所有字段
        ))
        .filter(diesel::dsl::not(
            songs::title.eq(new_song.title)
        ))
    )
    .execute(&connection)
    .unwrap();

注意：高并发场景下该写法仍然有极小概率触发唯一约束冲突（两个请求同时判断不存在，同时执行插入），如果你的业务并发很高，可以在应用层加个轻量重试，冲突时直接忽略即可，这种极端场景下的ID消耗可以忽略。

方案3：先查询再插入（适合冲突概率极高的场景） #

如果你的业务中插入冲突的概率非常高（比如超过30%的插入请求都是重复数据），先查再插的性能收益更高，也完全不会浪费ID：

let exists = songs::table
    .filter(songs::title.eq(&new_song.title))
    .first::<Song>(&connection)
    .optional()?;

if exists.is_none() {
    diesel::insert_into(songs::table)
        .values(&new_song)
        .execute(&connection)?;
}

如果要完全避免并发竞态，可以把这两句放到同一个可重复读事务中，或者给查询加FOR UPDATE行锁。

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