PostgreSQL bigserial类型ID不连续问题求助（FastAPI场景）

问题背景 #

开发基于FastAPI的应用，通过原生SQL操作post表，表中id字段为bigserial类型。初始调用接口插入数据时ID按顺序生成，但后续出现跳号情况（如从1、2、3直接跳到37、38），需要ID按时间顺序连续生成。

跳号原因分析 #

PostgreSQL的serial/bigserial本质是序列（Sequence）+字段默认值的组合，跳号通常由以下场景导致：

• 事务回滚：插入操作启动后若触发回滚，序列已完成递增，不会回退到原数值
• 数据库重启：部分情况下，序列的预分配缓存会丢失未使用的ID
• 并发操作失败：并发插入时，失败的事务会消耗掉已分配的ID
• 手动修改序列：若曾手动调整过序列的当前值，也可能引发跳号

解决方案 #

1. 禁用序列缓存（不适合高并发场景） #

修改对应序列的缓存为1，减少因缓存预分配导致的跳号，但会降低插入性能（每次插入都要同步更新序列）：

ALTER SEQUENCE posts_id_seq CACHE 1;

注意：即使设置缓存为1，事务回滚依然会消耗ID，因为序列的递增操作是事务独立的。

2. 用IDENTITY列替代bigserial（PostgreSQL 10+推荐） #

IDENTITY是SQL标准的自增字段实现，相比bigserial更可控，可通过NO CACHE避免缓存导致的跳号：

-- 修改现有表的id列（需先删除原序列关联）
ALTER TABLE posts DROP COLUMN id;
ALTER TABLE posts ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY (NO CACHE) PRIMARY KEY;

-- 新建表时直接使用
CREATE TABLE posts (
    id bigint GENERATED ALWAYS AS IDENTITY (NO CACHE) PRIMARY KEY,
    title text,
    content text,
    published boolean,
    rating integer
);

同样，事务回滚仍会消耗ID，但能解决大部分缓存导致的跳号问题。

3. 业务层维护连续编号（彻底解决连续需求） #

数据库自增ID的设计目标是唯一标识记录，而非生成连续序列号。如果业务必须需要连续编号，建议单独维护计数器表，通过事务保证原子性：

-- 创建计数器表
CREATE TABLE sequence_counter (
    name text PRIMARY KEY,
    value bigint NOT NULL DEFAULT 0
);

-- 初始化帖子编号计数器
INSERT INTO sequence_counter (name) VALUES ('post_number');

-- 定义获取下一个编号的函数
CREATE OR REPLACE FUNCTION get_next_post_number() RETURNS bigint AS $$
BEGIN
    UPDATE sequence_counter SET value = value + 1 WHERE name = 'post_number';
    RETURN (SELECT value FROM sequence_counter WHERE name = 'post_number');
END;
$$ LANGUAGE plpgsql VOLATILE;

-- 插入数据时调用函数获取连续ID
INSERT INTO posts (id, title, content, published) 
VALUES (get_next_post_number(), '示例标题', '示例内容', true) RETURNING *;

这种方式下，事务回滚会撤销计数器的更新，能保证编号绝对连续。

代码优化提示 #

当前代码中get_post接口的SQL参数传递存在问题：

cursor.execute(""" SELECT * FROM posts WHERE id = %s """,(str(id)))

这里(str(id))不是元组，psycopg2要求参数必须是元组/列表，应改为：

cursor.execute(""" SELECT * FROM posts WHERE id = %s """, (id,))

无需手动转字符串，psycopg2会自动处理类型转换。

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