PostgreSQL自增ID跳增求助：INSERT执行后ID非连续递增

问题现象 #

• 首次执行插入，得到id=1的记录；删除后再次执行，插入id=2，此过程正常。
• 未删除记录时重复执行查询，现有记录无变化；删除记录后重新执行，插入的记录id=4而非3。
• 未删除记录连续执行3次查询，删除后再执行，插入的记录id=8。
• 核心现象：自增ID持续递增，但现有记录不变；新增记录时直接使用已递增的ID值。

涉及的查询语句 #

WITH ins AS (
  INSERT INTO "table" (user_id, length) VALUES (1, 100) ON CONFLICT (user_id) DO NOTHING RETURNING *
)
SELECT * FROM ins UNION ALL SELECT * FROM "table" WHERE user_id = '${data.user_id}' -- only executed if no INSERT
LIMIT 1

表结构 #

id SERIAL PRIMARY KEY
length INTEGER DEFAULT 0
-- 注：user_id需存在唯一约束，否则ON CONFLICT逻辑不会触发

问题根源 #

PostgreSQL中SERIAL类型依赖序列（Sequence）生成自增ID，序列的递增操作是不可回滚的：

• 执行INSERT语句时，PostgreSQL会先从序列中获取下一个值作为id，再执行插入逻辑。
• 如果触发ON CONFLICT DO NOTHING（即user_id已存在，插入被跳过），已经获取的序列值不会回退，序列计数器已完成递增。
• 每次重复执行查询，哪怕插入失败，序列都会被消耗一次，导致后续成功插入时ID跳号。

对应你的场景：

1. 第3次执行查询（user_id已存在）：插入失败，但序列从2递增到3。
2. 第4次执行（删除记录后）：插入成功，使用序列当前值4。
3. 连续执行3次查询：每次都消耗序列值（4→5→6→7），删除后插入时就用8。

解决方案 #

根据业务需求选择合适的处理方式：

1. 接受ID非连续：序列的设计本来就不保证连续性（事务回滚、并发插入失败等都会导致跳号），如果业务对ID连续性无强制要求，这是正常行为，无需修改。
2. 改用ON CONFLICT DO UPDATE：如果需要在冲突时更新记录而非跳过，这样每次执行都会成功写入，序列仅在真正插入新记录时递增：

   WITH ins AS (
     INSERT INTO "table" (user_id, length) VALUES (1, 100) 
     ON CONFLICT (user_id) DO UPDATE SET length = EXCLUDED.length
     RETURNING *
   )
   SELECT * FROM ins
   

3. 先检查再插入：插入前先查询是否存在该user_id，仅当不存在时执行插入。注意并发场景下可能出现竞态条件，需加事务或锁保证原子性：

   BEGIN;
   SELECT 1 FROM "table" WHERE user_id = '${data.user_id}' FOR UPDATE;
   IF NOT FOUND THEN
     INSERT INTO "table" (user_id, length) VALUES (1, 100);
   END IF;
   SELECT * FROM "table" WHERE user_id = '${data.user_id}';
   COMMIT;
   

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