PostgreSQL serial类型列值非连续的解决方法及现有数据修复方案

问题原因 #

PostgreSQL的serial类型底层绑定独立的序列对象，只要执行过插入请求（哪怕插入失败、事务回滚、行被删除），序列值都会被消耗且不会回退，天然不保证生成的数值连续，你遇到的max值远大于总行数就是这个特性导致的。 #

修复现有数据为连续序列 #

操作前建议先备份表数据避免误操作：

-- 备份原表
CREATE TABLE public.table_name_bak AS TABLE public.table_name;

如果有其他表的外键关联到当前_id列，需要先暂停外键约束或者同步更新关联表后再执行以下操作：

-- 临时移除_id列的默认序列值，避免更新时干扰
ALTER TABLE public.table_name ALTER COLUMN "_id" DROP DEFAULT;

-- 按原有_id升序重新生成连续值，可按需替换为其他排序规则（如按创建时间排序）
UPDATE public.table_name t
SET "_id" = t2.new_id
FROM (
    SELECT "_id", ROW_NUMBER() OVER (ORDER BY "_id") AS new_id
    FROM public.table_name
) t2
WHERE t."_id" = t2."_id";

-- 重置序列到当前最大_id，适配后续插入需求
SELECT setval('public.table_name__id_seq', (SELECT MAX("_id") FROM public.table_name));

-- 恢复_id列的默认值配置
ALTER TABLE public.table_name ALTER COLUMN "_id" SET DEFAULT nextval('public.table_name__id_seq'::regclass);

操作完成后可执行以下语句验证结果：

SELECT max(_id), count(*) FROM public.table_name;

正常会返回两个相等的数值。 #

后续保证_id严格连续的方案 #

注意：严格连续的自增ID在高并发插入场景下会引入锁开销，吞吐量会比普通serial低很多，如非业务强制要求100%无缺口，不建议做该改造。
如果确实需要严格连续，可根据业务并发度选择以下两种方案：

方案1：插入时加锁计算（适合低并发场景） #

插入语句手动加表级排他锁，避免并发插入导致ID重复：

BEGIN;
-- 锁表避免其他事务同时插入
LOCK TABLE public.table_name IN EXCLUSIVE MODE;
INSERT INTO public.table_name (你的字段1, 你的字段2)
VALUES (值1, 值2)
RETURNING "_id";
COMMIT;

方案2：触发器自动生成ID #

创建触发器在插入前自动计算当前最大ID+1赋值给_id：

CREATE OR REPLACE FUNCTION public.gen_continuous_id()
RETURNS TRIGGER AS $$
BEGIN
    SELECT COALESCE(MAX("_id"), 0) + 1 INTO NEW."_id"
    FROM public.table_name;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 移除原serial默认值后绑定触发器
ALTER TABLE public.table_name ALTER COLUMN "_id" DROP DEFAULT;
CREATE TRIGGER trg_table_name_gen_id
BEFORE INSERT ON public.table_name
FOR EACH ROW EXECUTE FUNCTION public.gen_continuous_id();

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