PostgreSQL复用自动生成ID的可行性及相关技术问题咨询

架构老林

2026-8-21

PostgreSQL复用已删除ID的可行性评估

场景：每日会删除大量记录，希望表的ID标识符不超过9999，现评估复用自动生成ID列中已删除ID的可行性，并提出以下问题：

向表中插入数据时是否会出现并发问题？（每日约插入100条记录）
是否存在正确复用ID的方式？
针对该场景是否应采用其他ID分配方案？
是否该彻底放弃开发？

实操代码示例

创建表

CREATE TABLE IF NOT EXISTS test(
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
data TEXT);

创建获取可用ID的函数

CREATE OR REPLACE function get_id_test()
returns bigint
language plpgsql
as
$$
DECLARE
min_id bigint;
previous bigint;
next_id bigint;
f record;
BEGIN
   SELECT MIN(id) into min_id FROM test;
   IF  min_id > 1 THEN
       RETURN 1;
   END IF;

   FOR f
   IN select t.id from test t order by t.id
   LOOP
       IF  f.id = min_id THEN
           previous = f.id;
           CONTINUE;
       END IF;
       IF  f.id > (previous + 1) THEN
           RETURN previous + 1;
       ELSE 
           previous = f.id;
       END IF;
   END LOOP;
   next_id = previous + 1;
   RETURN nextval(pg_get_serial_sequence('test', 'id'));
end;
$$;

插入5行测试数据

INSERT INTO test (id,data) VALUES (get_id_test(),'test'),
                                  (get_id_test(),'test'),
                                  (get_id_test(),'test'),
                                  (get_id_test(),'test'),
                                  (get_id_test(),'test');

删除ID为2和4的记录

DELETE FROM test WHERE id=2 OR id=4;

插入行（预期复用ID=2）

INSERT INTO test (id,data) VALUES (get_id_test(),'test expecting id 2');

插入行（预期复用ID=4）

INSERT INTO test (id,data) VALUES (get_id_test(),'test expecting id 4');

插入行（预期使用序列生成的下一个ID=6）

INSERT INTO test (id,data) VALUES (get_id_test(),'test expecting id 6');

问题解答

1. 插入数据时是否会出现并发问题？

会出现并发问题。当前的get_id_test函数没有任何并发控制机制：当多个事务同时执行该函数时，可能会同时检测到同一个空闲ID（比如删除后的ID=2），然后多个事务都返回这个ID，最终插入时因为主键唯一性约束报错。

虽然每日仅插入100条记录，并发量不算高，但只要存在并行插入的情况，就有概率触发主键冲突。比如两个请求同时调用get_id_test，都查到ID=2是空闲的，就会导致其中一个插入失败。

2. 是否存在正确复用ID的方式？

有，但需要解决并发冲突和序列同步的问题，推荐两种方案：

方案一：在函数中添加表级锁

修改get_id_test函数，在查询空闲ID前先锁定整个表，避免其他事务同时修改或查询：

CREATE OR REPLACE function get_id_test()
returns bigint
language plpgsql
as
$$
DECLARE
min_id bigint;
previous bigint;
next_id bigint;
f record;
BEGIN
   -- 锁定表，防止并发修改
   LOCK TABLE test IN EXCLUSIVE MODE;

   SELECT MIN(id) into min_id FROM test;
   IF  min_id > 1 THEN
       RETURN 1;
   END IF;

   FOR f
   IN select t.id from test t order by t.id
   LOOP
       IF  f.id = min_id THEN
           previous = f.id;
           CONTINUE;
       END IF;
       IF  f.id > (previous + 1) THEN
           -- 复用空闲ID后，确保序列值不小于当前最大ID
           PERFORM setval(pg_get_serial_sequence('test', 'id'), GREATEST((SELECT MAX(id) FROM test), currval(pg_get_serial_sequence('test', 'id'))));
           RETURN previous + 1;
       ELSE 
           previous = f.id;
       END IF;
   END LOOP;
   next_id = previous + 1;
   RETURN nextval(pg_get_serial_sequence('test', 'id'));
end;
$$;

表级锁会降低并发插入的性能，但每日100条的量完全可以接受。同时添加了setval来同步序列值，避免后续nextval生成的ID和手动插入的ID冲突。

方案二：维护空闲ID池

创建一个单独的表存储已删除的ID，删除记录时将ID插入该表，插入新记录时优先从这个表取ID：

-- 创建空闲ID表
CREATE TABLE IF NOT EXISTS free_ids (id BIGINT PRIMARY KEY);

-- 修改删除操作，将ID存入空闲表
CREATE OR REPLACE FUNCTION delete_test_record(p_id BIGINT)
RETURNS VOID AS $$
BEGIN
   DELETE FROM test WHERE id = p_id;
   INSERT INTO free_ids(id) VALUES(p_id);
END;
$$ LANGUAGE plpgsql;

-- 获取可用ID的函数
CREATE OR REPLACE FUNCTION get_id_test()
RETURNS BIGINT AS $$
DECLARE
    free_id BIGINT;
BEGIN
    -- 尝试从空闲池取ID（带锁避免并发）
    SELECT id INTO free_id FROM free_ids LIMIT 1 FOR UPDATE SKIP LOCKED;
    IF free_id IS NOT NULL THEN
        DELETE FROM free_ids WHERE id = free_id;
        RETURN free_id;
    END IF;

    -- 没有空闲ID时，检查是否超过9999
    IF currval(pg_get_serial_sequence('test', 'id')) >= 9999 THEN
        -- 若已达上限，尝试找最小的未使用ID
        SELECT MIN(t.id + 1) INTO free_id FROM test t WHERE NOT EXISTS (SELECT 1 FROM test WHERE id = t.id + 1);
        IF free_id IS NULL OR free_id > 9999 THEN
            RAISE EXCEPTION 'ID已达到上限9999，无可用ID';
        END IF;
        RETURN free_id;
    ELSE
        RETURN nextval(pg_get_serial_sequence('test', 'id'));
    END IF;
END;
$$ LANGUAGE plpgsql;

这种方式更高效，锁的粒度更小，适合更高并发的场景。

3. 针对该场景是否应采用其他ID分配方案？

如果核心需求是ID不超过9999且循环复用，可以考虑以下更简单的方案：

方案一：使用带CYCLE的序列

放弃IDENTITY列，手动创建支持循环的序列，当序列达到9999后自动从1开始（需处理可能的主键冲突）：

-- 创建循环序列，最大值9999
CREATE SEQUENCE test_id_seq START 1 MINVALUE 1 MAXVALUE 9999 CYCLE;

CREATE TABLE IF NOT EXISTS test(
id BIGINT PRIMARY KEY DEFAULT nextval('test_id_seq'),
data TEXT);

注意：序列循环后可能生成已存在的ID，需要在插入时捕获主键冲突并重试，或者结合空闲ID池使用。

方案二：基于范围的ID复用

如果数据有明确的生命周期，可以按时间段分配ID范围，当旧数据被删除后，重新使用对应范围的ID。这种方式适合批量删除、批量插入的场景。

4. 是否该彻底放弃开发？

完全没必要放弃。这个需求是合理的，无论是通过改进现有复用逻辑，还是换用其他ID分配方案，都能实现“ID不超过9999且复用已删除ID”的目标。只要解决并发冲突和序列同步的问题，就能稳定运行，每日100条的插入量完全在PostgreSQL的处理能力范围内。

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

PostgreSQL复用自动生成ID的可行性及相关技术问题咨询

实操代码示例 #

创建表 #

创建获取可用ID的函数 #

插入5行测试数据 #

删除ID为2和4的记录 #

插入行（预期复用ID=2） #

插入行（预期复用ID=4） #

插入行（预期使用序列生成的下一个ID=6） #

问题解答 #

1. 插入数据时是否会出现并发问题？ #

2. 是否存在正确复用ID的方式？ #