Thread: BUG #17707: Foreign key verification is very slow while creating empty partitions
BUG #17707: Foreign key verification is very slow while creating empty partitions
From
PG Bug reporting form
Date:
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 ?