PostgreSQL 11.2中serial4类型ID未按1递增的原因及解决方法

在PostgreSQL中，serial4类型本质是通过绑定**自增序列（sequence）**实现自动赋值的，它的设计目标是保证ID的唯一性，而非严格连续递增。你遇到的跳号现象是正常的，PostgreSQL 11.2中以下场景会导致这类问题：

跳号的常见原因 #

• 事务回滚：当你开启事务插入数据后执行回滚，序列已经生成的ID会被消耗但不会写入表中，直接造成跳号。比如插入时序列生成了ID=3999999，随后事务回滚，下一次插入就会使用4000000，导致3999999缺失。
• 序列缓存机制：PostgreSQL的序列默认带有缓存（cache）配置，默认值为1，但如果显式设置了更大的缓存值（如100），数据库崩溃或重启后，缓存中未使用的ID会丢失，造成批量跳号。你观察到id<4000000区间跳号，有可能是某次数据库重启导致缓存ID失效。
• 手动调用序列函数：如果代码中直接调用nextval()等序列函数但未将生成的ID插入表中，会直接消耗ID导致跳号。
• 高并发/并行插入：高并发场景下，多个事务同时获取序列值，即使部分事务最终失败，已经获取的ID也不会回退，从而造成跳号。

避免跳号的可行方案 #

• 调整预期：接受非连续ID：如果业务不需要ID严格连续，这是最省心的方案——序列的核心作用是保证唯一性，跳号不会影响数据的正确性和业务逻辑。
• 使用NO CACHE创建序列：若必须减少跳号，可以在创建序列时指定NO CACHE，避免因缓存丢失导致的批量跳号。示例语句：

  -- 先删除原有的绑定序列（如果需要）
  DROP SEQUENCE IF EXISTS order_id_seq;
  -- 创建无缓存的序列
  CREATE SEQUENCE order_id_seq NO CACHE;
  -- 将序列绑定到id字段
  ALTER TABLE public.order ALTER COLUMN id SET DEFAULT nextval('order_id_seq');
  

  注意：NO CACHE会降低高并发场景下的性能，因为每次获取ID都需要磁盘同步操作。
• 控制事务内ID生成时机：确保仅在事务成功提交时才生成ID，避免回滚导致的ID消耗。建议让插入语句自动触发默认值赋值，不要提前调用nextval()。
• 禁止手动操作序列：业务代码中不要直接调用nextval()或setval()等序列操作函数，除非有明确的业务需求且能保证ID被正确使用。
• 使用IDENTITY列替代serial（PostgreSQL 10+支持）：PostgreSQL 10及以上版本支持GENERATED AS IDENTITY语法，它本质仍基于序列，但语法更标准，且可通过ALWAYS选项禁止手动修改ID，减少人为操作导致的跳号：

  -- 修改id列为IDENTITY类型
  ALTER TABLE public.order ALTER COLUMN id TYPE int4;
  ALTER TABLE public.order ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY;
  

  注意：IDENTITY列同样无法解决事务回滚、缓存丢失导致的跳号，仅能规范ID的生成逻辑。

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