BUG #17707: Foreign key verification is very slow while creating empty partitions - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #17707: Foreign key verification is very slow while creating empty partitions
Date
Msg-id 17707-b7aba5bf7d09b73c@postgresql.org
Whole thread Raw
List pgsql-bugs
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 ?


pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: BUG #17706: ALTER TYPE leads to crash
Next
From: hubert depesz lubaczewski
Date:
Subject: Re: WAL segments removed from primary despite the fact that logical replication slot needs it.