PostgreSQL 11附加带检查约束分区时避免扫描阻塞排查

问题描述 #

在PostgreSQL 11环境中参考官方流程做普通表转分区表的在线改造，目标是全程不中断表写入，执行流程如下：

• 先为现有表添加NOT VALID状态的检查约束，再单独执行约束校验
• 删除原表的现有主键
• 重命名现有原表
• 使用原表名创建新的分区表
• 将重命名后的原表作为分区附加到新创建的分区表

预期最后一步附加分区为元数据操作、执行速度快，但测试环境中1200万行数据的表执行该步骤耗时约30秒，期间阻塞所有插入操作。执行ATTACH时数据库返回如下提示：

INFO: partition constraint for table "my_events_2022_07" is implied by existing constraints

该提示说明分区范围约束配置正确，但仍出现长耗时阻塞，需要定位原因并给出优化方案。

操作涉及的简化DDL #

原表inserted_at字段定义：

inserted_at timestamp without time zone not null

删除主键前提前创建的ID列临时索引，用于保障ID列访问性能：

create unique index concurrently my_events_temp_id_index on my_events (id);

第一个事务中创建NOT VALID检查约束：

alter table my_events add constraint my_events_2022_07_events_check
check (inserted_at >= '2018-01-01' and inserted_at < '2022-08-01')
not valid;

第二个事务中执行约束校验，返回校验成功：

alter table my_events validate constraint my_events_2022_07_events_check;

删除原表主键：

alter table my_events drop constraint my_events_pkey cascade;

最后开启事务执行分区表创建与分区附加：

alter table my_events rename to my_events_2022_07;

create table my_events (
  id uuid not null,
  ... 其余列定义,
  inserted_at timestamp without time zone not null,
  primary key (id, inserted_at)
) partition by range (inserted_at);

alter table my_events attach partition my_events_2022_07
for values from ('2018-01-01') to ('2022-08-01');

问题根因 #

返回的约束匹配提示仅代表PostgreSQL确认现有检查约束完全覆盖分区范围，跳过了全表扫描校验数据合法性的步骤，但ATTACH操作还有其他必须执行的逻辑，30秒阻塞的核心原因是锁内全量索引构建：

• PostgreSQL 11要求，被附加的分区必须存在和分区表定义的主键、唯一约束完全匹配的有效索引，否则会在ATTACH流程中持ACCESS EXCLUSIVE锁自动创建对应索引，该锁会阻塞所有对表的读写请求。
• 提前创建的my_events_temp_id_index是仅包含id列的单字段唯一索引，和新分区表定义的(id, inserted_at)联合主键定义不匹配，因此ATTACH时会在锁内全表构建联合主键索引，1200万行数据的索引构建耗时刚好对应观测到的30秒。
• 删除主键时使用的CASCADE如果级联删除了关联外键，可能带来额外的锁等待，但从返回提示判断这不是主要耗时点。

优化方案 #

调整操作顺序即可将最后一步ATTACH的耗时降到毫秒级，实现真正的无感知在线改造：

1. 完成原表检查约束创建、校验、删除原主键、重命名原表为my_events_2022_07的步骤后，先不创建新分区表，在待附加的原表上并发创建和目标分区表主键完全匹配的联合唯一索引：

   create unique index concurrently my_events_2022_07_pkey_idx
   on my_events_2022_07 (id, inserted_at);
   

   并发创建索引不会阻塞表的正常读写。
2. 确认上述索引创建完成后，再开启事务执行新分区表创建、分区附加操作。此时PostgreSQL检测到待附加分区已存在匹配主键要求的唯一索引，不需要再全表构建索引，仅做元数据修改，整个事务耗时在毫秒级，锁持有时间极短，业务侧几乎感知不到写入中断。
3. 改造完成后，可以删除之前创建的单字段临时索引my_events_temp_id_index：新的联合主键索引以id为最左前缀，完全可以覆盖原单字段索引支撑的ID列查询、写入性能需求，不会出现性能回退。
4. ATTACH操作所在的事务不要加入任何无关操作，尽可能缩短事务执行时长，进一步降低锁冲突概率。

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