如何复用BIGSERIAL主键的已删除ID序列（无需UUID节省存储）

BIGSERIAL本质是BIGINT类型字段绑定一个自增序列，默认序列只会单调递增，不会自动复用已删除的ID——这是PostgreSQL序列的设计初衷，避免并发插入时的ID冲突，保证唯一性。要实现自动复用已删除ID且无需手动指定ID，可以通过触发器+自定义函数实现，具体步骤如下：

1. 创建获取可用ID的函数 #

这个函数会优先查找表中最小的未使用ID（即已删除的ID），如果没有缺失的ID，则使用序列的下一个值：

CREATE OR REPLACE FUNCTION get_reusable_id()
RETURNS BIGINT AS $$
DECLARE
    next_id BIGINT;
BEGIN
    -- 加表锁避免并发插入时的ID竞争（根据业务并发量调整锁级别）
    LOCK TABLE my_table IN SHARE MODE;

    -- 查找最小的缺失ID
    SELECT MIN(t.id + 1) INTO next_id
    FROM my_table t
    WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE id = t.id + 1)
    AND t.id + 1 <= (SELECT COALESCE(MAX(id), 0) FROM my_table);
    
    -- 无缺失ID时，使用序列的下一个值
    IF next_id IS NULL THEN
        next_id := nextval('my_table_id_seq'); -- 序列名格式为「表名_id_seq」，BIGSERIAL自动生成
    END IF;
    
    RETURN next_id;
END;
$$ LANGUAGE plpgsql;

2. 创建插入前触发器 #

通过触发器在插入时自动给id字段赋值（仅当未手动指定ID时触发）：

CREATE TRIGGER set_reusable_id_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
WHEN (NEW.id IS NULL)
EXECUTE FUNCTION get_reusable_id();

3. 验证效果 #

执行删除操作后直接插入：

DELETE FROM my_table WHERE id=3;
INSERT INTO my_table(column_x) VALUES('xxxxx');

此时新插入行的id会自动复用3，而非序列原本的下一个值。

注意事项 #

• 并发性能：加表锁会降低并发插入的性能，如果业务并发量高，这种方式可能不适用，需权衡ID复用需求和性能。
• 序列同步：如果手动插入过ID或复用了缺失ID，序列的当前值可能与表中最大ID不一致，可定期同步：

  SELECT setval('my_table_id_seq', (SELECT COALESCE(MAX(id), 0) FROM my_table));
  

• 适用场景：这种方案更适合小表或删除操作较少的场景，大表频繁删除时，查找缺失ID的扫描操作会带来明显性能开销。

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