基于Citus的分布式Sequence生成问题咨询

针对你的需求——让每个按Org_Id分片的角色表中，每个组织的RoleNumber从R0001开始独立递增，Citus提供以下两种可行方案：

方案一：使用分片本地序列（Shard-local Sequences） #

Citus原生支持分片本地序列，每个分片会维护自己的序列实例，而非全局共享一个序列，完美匹配按组织分片的场景。

实现步骤： #

1. 创建分片本地序列：

CREATE SEQUENCE role_number_seq SHARD;

添加SHARD关键字后，每个分片都会生成该序列的独立副本。

2. 编写格式化函数生成带前缀的编号：

CREATE OR REPLACE FUNCTION generate_role_number()
RETURNS TEXT AS $$
BEGIN
  RETURN 'R' || TO_CHAR(NEXTVAL('role_number_seq'), 'FM0000');
END;
$$ LANGUAGE plpgsql VOLATILE;

该函数会调用当前分片的序列值，格式化为R0001、R0002这类字符串。

3. 插入数据时调用函数：
   确保角色表是按Org_Id分片的分布式表，插入时直接用函数赋值：

INSERT INTO role_table(org_id, role_number, ...)
VALUES (1, generate_role_number(), ...),
       (2, generate_role_number(), ...);

此时Org_Id=1和Org_Id=2的记录会落在不同分片，各自的序列从1开始，所以RoleNumber都会是R0001。

方案二：自定义组织级计数器（适用于复杂场景） #

如果需要更灵活的控制逻辑，可以手动维护每个组织的计数器，通过触发器实现编号生成。

实现步骤： #

1. 创建计数器表：
   用于存储每个组织当前的序列值：

CREATE TABLE org_sequence_counter (
  org_id INT PRIMARY KEY,
  current_counter INT DEFAULT 1
);

若组织数量较多，可将此表设为按Org_Id分片的分布式表；数量较少的话，放在协调节点即可。

2. 编写触发器函数：
   插入角色记录时，获取对应组织的计数器值，生成编号并更新计数器：

CREATE OR REPLACE FUNCTION generate_role_number_trigger()
RETURNS TRIGGER AS $$
DECLARE
  next_counter INT;
BEGIN
  -- 加锁避免并发插入导致重复编号
  SELECT current_counter INTO next_counter 
  FROM org_sequence_counter 
  WHERE org_id = NEW.org_id 
  FOR UPDATE;

  -- 若组织是首次插入，初始化计数器
  IF next_counter IS NULL THEN
    next_counter := 1;
    INSERT INTO org_sequence_counter(org_id, current_counter)
    VALUES (NEW.org_id, next_counter + 1);
  ELSE
    UPDATE org_sequence_counter 
    SET current_counter = current_counter + 1 
    WHERE org_id = NEW.org_id;
  END IF;

  NEW.role_number := 'R' || TO_CHAR(next_counter, 'FM0000');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

3. 绑定触发器到角色表：

CREATE TRIGGER trigger_generate_role_number
BEFORE INSERT ON role_table
FOR EACH ROW EXECUTE FUNCTION generate_role_number_trigger();

之后插入数据时无需手动指定RoleNumber，触发器会自动生成对应编号。

方案对比 #

• 分片本地序列：实现简单，性能优异，依赖Citus内置特性，适合大多数常规场景。
• 自定义计数器：逻辑更灵活，可扩展额外规则（如重置序列、自定义步长），但需要自行维护计数器表和处理并发问题。

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