The following bug has been logged on the website:
Bug reference: 17707
Logged by: harsh kumar
Email address: harshppt75@gmail.com
PostgreSQL version: 15.1
Operating system: Linux
Description:
I am using postgres 14 and dealing with multi-level partitioning. An sample
table design looks like :
Table A :
CREATE TABLE issue (
id bigserial,
catalog_id bigint
NOT NULL,
submit_time timestamp WITH TIME ZONE
NOT NULL,
PRIMARY KEY (id, catalog_id, submit_time)
) PARTITION BY LIST (catalog_id)
Table B :
CREATE TABLE issue_detail (
id bigserial,
catalog_id bigint
NOT NULL,
issue_id bigint
NOT NULL,
submit_time timestamp WITH TIME ZONE
NOT NULL,
PRIMARY KEY (id, catalog_id, submit_time),
FOREIGN KEY (catalog_id, submit_time, issue_id) REFERENCES issue
(catalog_id, submit_time, id)
) PARTITION BY LIST (catalog_id)
So partition key for first level is catalog_id(partition by list) and for
second level is submit_time(partition by range - on weekly basis).
Second level partitioning definition :
For Table A :
```
CREATE TABLE issue_catalog1 PARTITION OF issue FOR VALUES IN (1) PARTITION
BY RANGE (submit_time)
```
For Table B :
```
CREATE TABLE issue_detail_catalog1 PARTITION OF issue_detail FOR VALUES IN
(1) PARTITION BY RANGE (submit_time)
```
Similarly, child partitions are created by range and on weekly basis for
past 3 years.
First level partitioned table is created inclemently, ie, first for
catalog_id = 1, first level partitioned table is created and then it's
partitions are created then for catalog_id = 2 and so on. So, for
catalog_id=1 there would be around 166 partitions (range partition -
partitioned by weekly for past 3 year). Similar for other consecutive
catalog_id, 166 partitions would be created.
While defining the partitions, the time to create empty partitions in case
of ```issue_detail``` table start growing(nearly by 30-50 % increase between
consecutive catalog_id). After looking at postgres server log, I found that
foreign key referential constraint verification is taking time. Then, to
double check I created empty partition creation time without foreign key, in
that case it was very fast(within couple of second).
It's very weird that creating empty partition for issue_detail is taking
more than 10 minutes after catalog_id = 40. How can empty partitions
creation take that much time. Why foreign key integrity verification is that
slow on empty table ?
