如何在PostgreSQL中为每个账户创建唯一的序列列？

针对你提到的多租户表（含accountid和receipt_number），需要在高并发环境下保证每个账户的收据编号连续且不重复，同时无法使用全局自增的SERIAL列，以下是几种实用的解决方案：

方案一：为每个账户创建专属序列 #

为每个租户账户单独创建序列，利用数据库序列的原子性生成唯一连续的编号，这是高并发场景下性能最优的方案。

操作步骤： #

1. 在创建账户时，自动为该账户生成对应的收据序列（命名建议关联accountid，比如seq_receipt_<accountid>）：

-- 示例：为accountid=123的账户创建收据序列，起始值为1
CREATE SEQUENCE seq_receipt_123 START WITH 1 INCREMENT BY 1;

2. 插入收据记录时，调用对应序列的nextval()获取下一个编号：

INSERT INTO receipts (accountid, receipt_number, other_columns)
VALUES (123, nextval('seq_receipt_123'), '其他数据');

优缺点： #

• ✅ 高并发下几乎无冲突，性能优异
• ✅ 编号天然连续，无需额外计算
• ❌ 账户数量极大时（如百万级），会生成大量序列对象，占用数据库元数据资源
• ❌ 需要维护序列与账户的关联关系（可通过触发器或账户创建逻辑自动处理）

方案二：使用全局计数器表 #

创建一张单独的计数器表，存储每个账户的当前最大收据编号，通过数据库的原子UPDATE操作获取下一个编号，适合不想维护大量序列的场景。

操作步骤： #

1. 创建计数器表：

CREATE TABLE receipt_counters (
    accountid INT PRIMARY KEY,
    current_max_num INT NOT NULL DEFAULT 0
);

2. 原子化获取并更新编号，同时处理账户首次插入的情况：

WITH updated_counter AS (
    UPDATE receipt_counters
    SET current_max_num = current_max_num + 1
    WHERE accountid = $1  -- $1为目标accountid参数
    RETURNING current_max_num
)
-- 若账户不存在则初始化计数器为1，否则返回更新后的编号
INSERT INTO receipts (accountid, receipt_number)
SELECT $1, COALESCE((SELECT * FROM updated_counter), 1)
ON CONFLICT (accountid) DO NOTHING;

3. 应用层需要处理重试逻辑：当UPDATE返回0行时，说明有其他事务抢先更新了计数器，需重新执行上述语句。

关键保障： #

添加联合唯一约束，从数据库层面杜绝重复：

ALTER TABLE receipts ADD CONSTRAINT idx_unique_account_receipt UNIQUE (accountid, receipt_number);

优缺点： #

• ✅ 仅需维护一张表，管理成本低
• ✅ 支持任意数量的账户
• ❌ 高并发下可能出现更新冲突，依赖应用层重试
• ❌ 需额外处理账户首次插入的初始化逻辑

方案三：行级锁+Max值计算 #

通过锁定目标账户的所有收据记录，再计算当前最大编号并加1，适合并发量较低的场景。

操作步骤： #

BEGIN;
-- 锁定该账户的所有收据行，防止其他事务同时修改
SELECT * FROM receipts WHERE accountid = 123 FOR UPDATE;

-- 获取下一个连续编号
SELECT COALESCE(MAX(receipt_number), 0) + 1 INTO next_receipt_num FROM receipts WHERE accountid = 123;

-- 插入新收据
INSERT INTO receipts (accountid, receipt_number, other_columns)
VALUES (123, next_receipt_num, '其他数据');
COMMIT;

优缺点： #

• ✅ 实现简单，无需额外维护序列或计数器表
• ❌ 高并发下锁等待严重，性能瓶颈明显
• ❌ 锁范围大，会阻塞该账户的其他写操作

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