避免扫描带有检查约束的附加分区

Question

我在PostgreSQL 11中将一个已存在的表重新创建为分区表。

经过一番研究，我使用以下过程接近它，以便在表上仍然发生写操作时在线完成:

1. 在现有表上添加一个检查约束，首先是无效的，然后验证
2. 删除现有主键
3. 重命名现有表
4. 在先前的表名
下创建分区表
5. 把现有表分区新分区表

我的期望是,最后一步会相对较快,但是我真的没有很多。在我的测试中，大约需要30秒。我想知道我的期望是不正确的，还是我在约束或其他方面做错了什么。

这是DDL的简化版本。

首先，像这样声明inserted_at列:

inserted_at timestamp without time zone not null

我想在ID上有一个索引，即使我删除了现有查询和写入的PK，所以我创建了一个索引:

create unique index concurrently my_events_temp_id_index on my_events (id);

检查约束在一个事务中创建:

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,
... other columns,
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');

最后一个事务阻塞插入，在我的测试数据库中的12M行需要大约30秒。

编辑

我想添加它来响应attach，我看到这个:

INFO: my_events_2022_07"由现有约束隐含

这让我觉得我做得对。

Answer 1

问题不在于check约束，而在于主键。

如果您使原始唯一索引包含两个列:

create unique index concurrently my_events_temp_id_index on my_events (id,inserted_at);

如果你让新表在这两列上有一个唯一索引，而不是一个主键，那么这个附加几乎是即时的。

这些在我看来像是在PostgreSQL不必要的限制,这一列上的惟一索引不能被用来暗示两列上的独特性,而不能用来暗示两列上唯一索引的主键(甚至也不是一个独特的约束——但只有唯一的索引)。