PostgreSQL 14多租户场景实现按租户自增复合主键方案

你需要的(id, tenant_id)复合主键下按租户独立自增的逻辑，不能直接在列DEFAULT值里写max(id)+1的子查询——一是PostgreSQL不支持DEFAULT表达式中对当前表做聚合查询，二是并发插入时会读到重复的max值，触发主键冲突。下面提供两种生产可用的实现方案，完全不需要应用层传入id参数。

方案1：租户级独立序列（推荐，高并发场景适用） #

为每个租户维护独立的自增序列，从根本上避免并发冲突，性能最优。

第一步：初始化表结构 #

DROP TABLE IF EXISTS tenant;
CREATE TABLE tenant
(
    id               smallserial primary key,
    company_tax_code character varying(14),
    period           character varying(16), -- 账期，格式为yyyyMMddyyyyMMdd代表起止时间
    created          timestamp with time zone DEFAULT now()
);

DROP TABLE IF EXISTS account_default;
CREATE TABLE account_default
(
    id               smallint,
    ref_type         smallint               not null,
    ref_type_name    character varying(256),
    voucher_type     smallint               not null,
    column_name      character varying(64)  not null,
    column_caption   character varying(128) not null,
    filter_condition character varying(1024),
    default_value    character varying(32),
    sort_order       smallint,
    created          timestamp with time zone DEFAULT now(),
    created_by       character varying(64),
    modified         timestamp with time zone DEFAULT now(),
    modified_by      character varying(64),
    tenant_id        smallint,
    PRIMARY KEY (id, tenant_id),
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);

第二步：创建触发器函数与绑定触发器 #

-- 自动生成租户维度自增id的触发器函数
CREATE OR REPLACE FUNCTION set_account_default_tenant_id()
RETURNS TRIGGER AS $$
DECLARE
    seq_name text := 'account_default_tenant_' || NEW.tenant_id || '_id_seq';
    current_max_id smallint;
BEGIN
    -- 仅当插入时未手动传入id，才执行自动生成逻辑
    IF NEW.id IS NULL THEN
        -- 检查当前租户对应的序列是否存在
        IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = seq_name AND relkind = 'S') THEN
            -- 加表级锁避免并发事务重复创建序列
            LOCK TABLE account_default IN SHARE ROW EXCLUSIVE MODE;
            -- 二次校验，防止锁等待期间序列已被其他事务创建
            IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = seq_name AND relkind = 'S') THEN
                -- 取当前租户已有最大id作为序列起始基准
                SELECT COALESCE(MAX(id), 0) INTO current_max_id FROM account_default WHERE tenant_id = NEW.tenant_id;
                -- 创建租户专属序列，绑定到account_default.id字段，删表时自动级联删除序列
                EXECUTE format('CREATE SEQUENCE %I START WITH %s OWNED BY account_default.id', seq_name, current_max_id + 1);
            END IF;
        END IF;
        -- 取序列下一个值赋值给新记录的id
        EXECUTE format('SELECT nextval(%L)', seq_name) INTO NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- 绑定触发器到表的插入操作
CREATE TRIGGER trg_set_account_default_id
BEFORE INSERT ON account_default
FOR EACH ROW
EXECUTE FUNCTION set_account_default_tenant_id();

方案特点

• 不同租户id序列完全隔离，满足示例中的主键取值规则
• 基于PostgreSQL原生序列实现，支持高并发插入，不会出现重复id
• 兼容手动指定id的场景，不会覆盖显式传入的id值
• 序列绑定到表字段，删除表时自动清理，不会产生垃圾对象

方案2：锁表计算（低并发场景适用，无额外序列维护） #

如果系统单租户插入并发极低（每秒插入<10次），不想维护多组序列，可以用行锁+实时计算max值的方案实现，逻辑更简单。

触发器实现 #

CREATE OR REPLACE FUNCTION set_account_default_tenant_id_simple()
RETURNS TRIGGER AS $$
DECLARE
    current_max_id smallint;
BEGIN
    IF NEW.id IS NULL THEN
        -- 锁定tenant表中对应租户的行，阻塞同租户的并发插入，避免重复id
        PERFORM 1 FROM tenant WHERE id = NEW.tenant_id FOR UPDATE;
        -- 实时计算当前租户下一个id值
        SELECT COALESCE(MAX(id), 0) + 1 INTO NEW.id FROM account_default WHERE tenant_id = NEW.tenant_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- 绑定触发器
CREATE TRIGGER trg_set_account_default_id_simple
BEFORE INSERT ON account_default
FOR EACH ROW
EXECUTE FUNCTION set_account_default_tenant_id_simple();

方案注意事项

• 同租户插入会串行执行，并发性能差，不适合高写入场景
• 删除租户下id最大的记录后，新插入记录会复用被删除的id，如果业务要求id永久递增不允许复用，不要选择该方案

效果验证 #

执行以下测试SQL：

-- 插入3个测试租户
INSERT INTO tenant(company_tax_code, period) VALUES 
('test1', '2023010120231231'),
('test2', '2023010120231231'),
('test3', '2023010120231231');

-- 不传入id，直接插入配置数据
INSERT INTO account_default(ref_type, voucher_type, column_name, column_caption, tenant_id) VALUES
(1,1,'col1','caption1',1),
(1,1,'col2','caption2',1),
(1,1,'col3','caption3',1),
(1,1,'col1','caption1',2),
(1,1,'col2','caption2',2),
(1,1,'col1','caption1',3);

-- 查询主键结果
SELECT id, tenant_id FROM account_default ORDER BY tenant_id, id;

返回结果完全符合预期：

id | tenant_id
----+-----------
  1 |         1
  2 |         1
  3 |         1
  1 |         2
  2 |         2
  1 |         3
(6 rows)

注意事项 #

• smallint类型最大值为32767，如果单租户下account_default记录数会超过该值，请提前将id字段类型调整为integer或bigint，避免数值溢出报错
• 使用方案1时，删除租户可同步执行DROP SEQUENCE IF EXISTS account_default_tenant_<对应租户id>_id_seq;清理无用序列

内容的提问来源于stack exchange，提问作者Đỗ Như Vỹ