CockroachDB：将UNIQUE CONSTRAINT转为PRIMARY KEY及移除冗余列的最优方案

我们在CockroachDB中的部分表的主键包含两个冗余前缀列，例如(u1, u2, id)，目前这些列已不再需要。为提升性能，我们已将实际主键设为UNIQUE CONSTRAINT，具体约束如下：

CONSTRAINT table1_pk PRIMARY KEY (u1, u2, id),
CONSTRAINT table1_ck_id UNIQUE (id)

我们希望移除表中的u1和u2列，但部分表拥有数十亿条数据，重新索引耗时极长。CockroachDB不支持CONCURRENTLY，因此据了解这会长时间阻塞UPDATE查询。

我已梳理出三种实现方式：

方案A：将UNIQUE CONSTRAINT“升级”为PRIMARY KEY #

ALTER TABLE tnt.xxx DROP CONSTRAINT xxx_pk;
ALTER TABLE tnt.xxx ADD CONSTRAINT xxx_pk PRIMARY KEY USING INDEX xxx_pk_id;
ALTER TABLE tnt.xxx DROP CONSTRAINT xxx_pk_id;
ALTER TABLE tnt.xxx DROP COLUMN u1, DROP COLUMN u2;

方案B：修改主键列 #

ALTER TABLE tnt.xxx ALTER PRIMARY KEY USING COLUMNS (id);
ALTER TABLE tnt.xxx DROP CONSTRAINT xxx_ck_id;
ALTER TABLE tnt.xxx DROP COLUMN u1, DROP COLUMN u2;

方案C：单个事务中删除并添加主键 #

ALTER TABLE tnt.xxx DROP CONSTRAINT xxx_pk;
ALTER TABLE tnt.xxx ADD CONSTRAINT xxx_pk PRIMARY KEY (id);
ALTER TABLE tnt.xxx DROP CONSTRAINT xxx_ck_id;
ALTER TABLE tnt.xxx DROP COLUMN u1, DROP COLUMN u2;

疑问 #

1. 是否有其他实现方式？
2. 哪种方式性能最优？

注：迁移操作需在事务中执行，且要确保操作期间SELECT查询不会因表无主键而受影响。

其他实现方式 #

可以考虑分区交换的方式，适合超大规模表：

1. 创建一个和原表结构一致但主键为(id)的空表，暂时保留u1、u2列；
2. 若原表是分区表，直接交换分区；若不是，则分批将原表数据插入新表；
3. 交换原表和新表的名称，完成业务切换；
4. 清理旧表（原表）并删除冗余列。
   这种方式步骤繁琐，但能把锁的影响降到最低，适合数十亿级别的超大表。

性能最优方案分析 #

方案A：最优选择（前提是可行）

这个方案的核心是复用已有的xxx_pk_id唯一索引——因为该索引已经是基于id的唯一非空索引，直接将其升级为主键属于元数据变更，几乎不消耗IO和CPU，不会触发全表重索引。
需要确认：xxx_pk_id未被其他外键或约束关联，且索引列与目标主键列完全一致。如果满足条件，这一步瞬时完成，后续删除旧主键、冗余约束和列的操作也都是轻量级的。

方案B：次优选择

ALTER PRIMARY KEY USING COLUMNS命令在这种场景下会重新构建整个主键索引：原主键是(u1,u2,id)，新主键是(id)，索引结构完全不同，必须全表扫描重建索引。对于数十亿条数据的表，耗时极长，锁持有时间久，会严重阻塞UPDATE操作。

方案C：最差选择

先删除原主键再添加新主键，事务执行过程中会存在表无主键的窗口，违反了“确保SELECT不受无主键影响”的要求。同时添加新主键需要全表构建索引，资源消耗大、耗时久，会长时间阻塞所有写操作。

关键注意事项 #

• 所有操作必须放在单个事务中执行，避免中间状态暴露给业务；
• 方案A中，务必提前确认xxx_pk_id索引的可用性：唯一、非空、无其他关联约束；
• 大表执行DROP COLUMN时，CockroachDB会先标记列为删除，后台异步清理数据，锁持有时间短，不会长时间阻塞业务。

内容的提问来源于stack exchange，提问作者Ondra Žižka