Postgres 11高活跃分区表无停机添加主键方案咨询

针对高写入（1000-5000条/秒）、1000万行数据且无法停机的分区表场景，以下是一套低锁、无停机的主键添加方案，同时给出替代UUID的主键选型建议：

一、分步操作方案（核心无锁/短锁流程） #

PostgreSQL 11中直接添加带默认值的列或主键会触发全表锁，因此拆分步骤将锁表时间降到最低：

1. 新增可空主键列（无锁，即时完成） #

先为主表和所有分区新增一个可空列，类型根据后续选型选择（UUID或bigint）。Postgres 11新增无默认值的可空列仅修改元数据，不会扫描或修改现有行，因此无锁且瞬间完成：

-- 主表操作
ALTER TABLE your_partitioned_table ADD COLUMN id uuid;

-- 逐个分区执行（声明式分区需单独操作每个子分区）
ALTER TABLE your_partition_202401 ADD COLUMN id uuid;
ALTER TABLE your_partition_202402 ADD COLUMN id uuid;
-- ... 遍历所有分区

2. 分批回填主键值（无锁，后台执行） #

不要一次性更新全表，分批处理避免IO过载和锁表。每次处理1-10万行，中间加短暂休眠，不影响正常写入：

-- 示例：用psql脚本循环更新（DO块无法手动提交，脚本更灵活）
WHILE true; DO
  UPDATE your_partitioned_table
  SET id = uuid_generate_v4()
  WHERE id IS NULL
  LIMIT 10000;
  
  -- 检查是否还有未更新行
  SELECT count(*) INTO remaining FROM your_partitioned_table WHERE id IS NULL;
  IF remaining = 0 THEN EXIT; END IF;
  
  -- 休眠1秒，降低对业务的影响
  SELECT pg_sleep(1);
END LOOP;

优化建议：直接对单个分区执行更新，避免跨分区扫描，效率更高。比如针对每个分区单独运行上述循环。

3. 设置列默认值（短锁，毫秒级） #

等所有现有行的id回填完成后，设置列的默认值，确保新插入的行自动生成主键：

ALTER TABLE your_partitioned_table ALTER COLUMN id SET DEFAULT uuid_generate_v4();

此操作仅修改表元数据，锁表时间极短，几乎不影响业务。

4. 设列为NOT NULL（短锁，快速完成） #

ALTER TABLE your_partitioned_table ALTER COLUMN id SET NOT NULL;

Postgres会扫描表确认无空值，但因为已完成回填，扫描速度极快，锁表时间很短。

5. 并发创建主键约束（无锁，耗时较长） #

关键注意：PostgreSQL分区表的主键必须包含分区键（如created_date），否则无法创建全局唯一的主键约束。

用CONCURRENTLY选项创建唯一索引（无锁），再将其转换为主键：

-- 并发创建唯一索引，不锁表
CREATE UNIQUE INDEX CONCURRENTLY idx_your_table_pkey ON your_partitioned_table (id, created_date);

-- 将索引转换为主键约束（短锁，仅修改元数据）
ALTER TABLE your_partitioned_table ADD CONSTRAINT your_table_pkey PRIMARY KEY USING INDEX idx_your_table_pkey;

CONCURRENTLY创建索引耗时较长，但不会阻塞读写，适合在业务低峰期执行。声明式分区下，主表的主键约束会自动同步到所有分区。

二、替代UUID的主键选型（减少空间占用） #

如果担心UUID的16字节空间占用，可选择以下方案：

• 复合业务主键：若业务中已有唯一标识（如order_id），结合分区键组成复合主键（如(order_id, created_date)），无需新增列，直接利用现有数据。
• 雪花ID（Snowflake）：生成64位bigint类型ID，包含时间戳、机器ID、序列号，全局唯一且仅占8字节。可通过Postgres函数生成，写入时自动填充，适合高并发场景。
• 分区独立序列：为每个分区分配独立的序列起始值，结合分区键组成复合主键（如(seq_id, partition_key)），避免序列竞争，同时保证全局唯一。

三、关键注意事项 #

• 低峰期执行：回填数据和创建并发索引会占用IO/CPU资源，尽量选择业务流量最低的时段操作。
• 监控负载：执行过程中监控CPU、磁盘IO、锁等待指标，若出现异常立即暂停。
• 备份前置：操作前务必完成数据库全量备份，避免数据风险。
• DMS验证：添加主键后，测试AWS DMS的迁移任务，确保主键能被正确识别用于变更捕获。

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