CODEWITHSUNDEEP
databasepostgresqlforeign-keyspartitioningpostgresql-14
harsh kumar
harsh kumar

Reputation: 41

Foreign key verification is very slow while creating empty partitions

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 ?

Upvotes: 4

Views: 522

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

Don't create foreign keys between the partitioned tables. They will prevent you from dropping or detaching partitions. Instead, define the foreign keys between the actual partitions. That will probably also get rid of your performance problem.

Don't create too many partitions if you want good performance.

Upvotes: 2

Related Questions