PostgreSQL频繁增删表的主键序列优化：复用闲置ID方案咨询

这确实是序列的典型痛点——序列设计之初就是单调递增、绝不回退的，哪怕你删掉了中间的行，它也不会去捡那些闲置的ID。如果你的业务场景必须要插入时使用首个闲置ID，我给你整理了几种可行方案，不过每种都有各自的权衡，得结合你的并发量和业务需求来选：

方案1：插入前查询最小闲置ID #

核心思路是每次插入前，先找出当前表中第一个缺失的ID。你可以写一个PL/pgSQL函数来封装这个逻辑：

CREATE OR REPLACE FUNCTION get_next_available_id() RETURNS integer AS $$
DECLARE
    next_id integer;
BEGIN
    -- 查找从1开始的第一个缺失ID
    SELECT MIN(t.id + 1) INTO next_id
    FROM market_orders t
    WHERE NOT EXISTS (SELECT 1 FROM market_orders WHERE id = t.id + 1);
    
    -- 如果所有ID都是连续的（或表为空），则取最大ID+1（空表时为1）
    IF next_id IS NULL THEN
        SELECT COALESCE(MAX(id), 0) + 1 INTO next_id FROM market_orders;
    END IF;
    
    RETURN next_id;
END;
$$ LANGUAGE plpgsql;

然后把表的id列默认值改成调用这个函数：

ALTER TABLE market_orders ALTER COLUMN id SET DEFAULT get_next_available_id();

⚠️ 注意：这个方案在高并发场景下会有主键冲突风险——两个事务可能同时查到同一个闲置ID，然后同时插入导致报错。如果要解决这个问题，你需要在查询时加排他锁，比如在函数里对market_orders表加锁，但这会严重影响插入性能，不适合频繁操作的场景。

方案2：维护专门的闲置ID表 #

这个方案更高效，思路是用一个单独的表存储被删除的ID，插入新行时先从这个表取最小的闲置ID，没有的话再用新的递增ID。

步骤1：创建闲置ID表 #

CREATE TABLE unused_ids (
    id integer PRIMARY KEY
);

步骤2：创建删除触发器，自动回收闲置ID #

当market_orders表的行被删除时，把对应的ID插入unused_ids：

CREATE OR REPLACE FUNCTION add_unused_id() RETURNS trigger AS $$
BEGIN
    INSERT INTO unused_ids (id) VALUES (OLD.id);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_market_orders_delete
AFTER DELETE ON market_orders
FOR EACH ROW EXECUTE FUNCTION add_unused_id();

步骤3：写函数获取下一个可用ID #

CREATE OR REPLACE FUNCTION get_next_id() RETURNS integer AS $$
DECLARE
    next_id integer;
BEGIN
    -- 先从闲置表取最小的ID，加FOR UPDATE避免并发冲突
    SELECT id INTO next_id FROM unused_ids ORDER BY id LIMIT 1 FOR UPDATE;
    IF next_id IS NOT NULL THEN
        DELETE FROM unused_ids WHERE id = next_id;
        RETURN next_id;
    END IF;
    
    -- 没有闲置ID时，生成新的递增ID
    SELECT COALESCE(MAX(id), 0) + 1 INTO next_id FROM market_orders;
    RETURN next_id;
END;
$$ LANGUAGE plpgsql;

步骤4：设置表的默认值 #

ALTER TABLE market_orders ALTER COLUMN id SET DEFAULT get_next_id();

这个方案比方案1的并发性能好很多，FOR UPDATE只会锁定unused_ids里的目标行，不会影响整个业务表。但要注意：如果是批量删除，触发器会逐行执行，可能带来额外的性能开销；另外，如果有外部操作直接删除unused_ids里的行，会导致ID永久丢失。

方案3：重新评估是否需要重用ID #

最后想提醒你：很多时候ID膨胀其实不是大问题。PostgreSQL的int类型有21亿左右的上限，bigint更是有9e18的范围，除非你每秒增删几十万行，否则很难耗尽。而且序列的性能是最优的——没有锁竞争，高并发下表现碾压前两种方案。

如果你的业务场景不是必须要求ID连续（比如只是为了看起来整洁），建议还是保留序列方案，接受ID的不连续，因为维护重用ID的代价（性能、复杂度）往往超过ID膨胀的影响。

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