PostgreSQL中serial类型主键值未连续递增问题求助

先定位跳号根源 #

首先执行以下SQL查看序列的配置和状态（序列名默认是表名_id_seq）：

SELECT * FROM applications_id_seq;

重点关注cache_size和last_value两个字段：

• 若cache_size为120：这是最常见的原因——序列缓存机制导致跳号。PostgreSQL会预取cache_size数量的序列值到内存，当数据库重启、连接异常断开时，未使用的预取值会被直接丢弃，下一次插入会从下一批值的起始点开始。比如缓存120的情况下，用到120后预取了121-240，重启后这批值作废，新插入就会从241开始。
• 若cache_size为1：则需要排查两种情况：
  • 是否存在批量事务回滚：比如插入了120条数据（id范围121-240）后执行了回滚，序列值不会随事务回滚，下一次插入会直接从241开始。
  • 是否有人手动调用过nextval函数：比如多次执行SELECT nextval('applications_id_seq')，直接将序列值推进到了241。

实现主键严格连续的方案 #

serial/identity类型的设计目标是高性能，本身无法保证主键绝对连续（序列的递增是独立于事务的）。如果必须确保连续，可采用以下两种方案：

方案1：触发器生成主键（绝对连续，但牺牲性能） #

创建触发器函数，每次插入时取当前表最大id加1，同时加排他锁避免并发冲突：

CREATE OR REPLACE FUNCTION get_next_app_id()
RETURNS TRIGGER AS $$
BEGIN
  LOCK TABLE applications IN EXCLUSIVE MODE;
  NEW.id = COALESCE((SELECT MAX(id) FROM applications), 0) + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_applications_id
BEFORE INSERT ON applications
FOR EACH ROW
WHEN (NEW.id IS NULL)
EXECUTE FUNCTION get_next_app_id();

注意：排他锁会导致插入操作串行化，仅适合并发量极低的场景。

方案2：调整序列缓存减少跳号概率（无法保证绝对连续） #

将序列缓存设为1（默认值，但可能被修改），降低因缓存丢失导致的跳号概率：

ALTER SEQUENCE applications_id_seq CACHE 1;

但此方案无法解决事务回滚或手动调用nextval导致的跳号。

修复当前的跳号问题 #

若要将id从241恢复到121，先确认241及之后的id未被使用，再执行：

ALTER SEQUENCE applications_id_seq RESTART WITH 121;

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