2

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 ?

harsh kumar
  • 21
  • 3
  • The new table is empty but it's checked against a pretty big structure. Do you have any indexes in place apart from those tied to your primary keys? Do these execution times change after you `vacuum analyze` your tables? – Zegarek Dec 08 '22 at 08:43
  • @Zegarek I spin up new database(empty one) to test this. Creation of only empty partitions is taking lot of time after a certain partition count. The worst part is it's increasing in time with increase in catalog_id. For empty partitions(no data at all, in any table), foreign key verification should be quick but it's very slow. – harsh kumar Dec 08 '22 at 08:51
  • 1
    Unlike inheritance-based partitioning, declarative partitioning will enforce the constraints against the entire structure, so you're effectively setting up a one-to-many constraint each time. PostgreSQL might not be recognising the coincidence in the logic of how the linked relations are partitioned, in which case an fk has to set up a constraint against all the subpartitions of the other structure, not just their counterparts. I wonder if you could move the FK definition down to the lowest subpartitions and "manually" link directly to their corresponding subpartitions of the other table. – Zegarek Dec 08 '22 at 09:11
  • @Zegarek It's looks like this is a limitation of declarative partitioning. It would have been nice if there could have been a syntax to explicitly tell that there is a one-to-one mapping between referenced and referencing table. I was already thinking about this option but it would introduce schema maintenance overhead – harsh kumar Dec 08 '22 at 09:41
  • Thanks @Zegarek, I was wanting to hear about this option from someone else also, so that I can be sure something wrong is not being done from our end :) – harsh kumar Dec 08 '22 at 09:43
  • Take my comments as guesses, not a definitive answer. From what I tested on my v15.1, partitions of both `issue` and `issue_detail` for 40 `catalog_id`'s each, times 166 weekly subpartitions each, got created pretty much immediately. No difference between how much time it took to build the 1st one and the 6640th. I don't have a v14 around and this test is way too long for dbfiddle, but you might check if newer versions behave better. [Here's the script](https://dbfiddle.uk/6g11-HLG) – Zegarek Dec 08 '22 at 09:54
  • Interesting observation is that even on dbfiddle above, on 3 id's times 3 weeks, v15 sets the partitions up in microseconds, while v14 needs milliseconds. – Zegarek Dec 08 '22 at 10:05
  • There is one more thing here : For second level of partition, I am using pg_partman whose syntax is different for adding new partition. It does something like : ALTER TABLE issue_detail_catalog40 ATTACH PARTITION issue_detail_catalog40_week170 FOR VALUES FROM ('2026-04-08 10:32:26.751539') TO ('2026-04-15 10:32:26.751539'). I am editing your script to check if time changes with ATTACH PARTITION Syntax. – harsh kumar Dec 08 '22 at 11:29
  • DBfiddle already uses 14.6 but in case you don't there are a [couple of fixes](https://www.postgresql.org/docs/release/14.6/#:~:text=Fix%20construction%20of,a%20duplicative%20index) that might be related. Adding `pg_partman` to the mix opens up a whole new angle of possible snowballing extension overhead. – Zegarek Dec 08 '22 at 11:45
  • @Zegarek ATTACH PARTITION is slow on high partition count as compare to CREATE TABLE .. PARTITION OF .. PARTITION BY.. . Can you check this : [Updated script](https://dbfiddle.uk/btGcOH30). I have changed catalog range, total week and added attach partition in the end. – harsh kumar Dec 08 '22 at 12:10
  • Your link leads to an empty fiddle. I've set up a 14.6 - the test with 40 id's * 166 weeks took 3min. Now I'll try to see how pg_partman and partition attachment change this result. – Zegarek Dec 08 '22 at 12:15
  • Sorry, My bad. I have added this two statement : CREATE TABLE issue_detail_catalog2_week170 ( LIKE issue_detail_catalog2 INCLUDING DEFAULTS INCLUDING STORAGE INCLUDING COMMENTS INCLUDING GENERATED INCLUDING INDEXES ); ALTER TABLE issue_detail_catalog2 ATTACH PARTITION issue_detail_catalog2_week170 FOR VALUES FROM ('2026-04-08 10:32:26.751539') TO ('2026-04-15 10:32:26.751539'); . Apart from it, updated catalog range to 40 and week to 166 for each catalog. Your script ran fast but these two statement ran very slow. – harsh kumar Dec 08 '22 at 12:32
  • It is best that you are directly checking using pg_partman – harsh kumar Dec 08 '22 at 12:32
  • 1
    Swapping out a `create table..partition of` for `create table t_p (like t); alter table t attach partition t_p...` results in 1s, 3s, 5s, 14s, 1min14s, 1min46s, 2min22s for the first 7 id's * 166 weeks on my v14.6. Adding an [analogous check constraint](https://www.postgresql.org/docs/14/ddl-partitioning.html#:~:text=Before%20running%20the,as%20mentioned%20above.) improves the situation a bit, but doesn't bring it anywhere near the previous score, which sounds like the entire attachment mechanism is to blame. – Zegarek Dec 08 '22 at 12:50
  • I think it'd be enough to make pg_partman or pg_cron set up the partitions ahead of time using `create table..partition of` if the id order is predictable (weeks certainly are). – Zegarek Dec 08 '22 at 12:50
  • @TheImpaler Nope. It's not malformed. It has to be that way due to multi-level partitioning – harsh kumar Dec 08 '22 at 13:27
  • @harshkumar My bad. I misread the definition. – The Impaler Dec 08 '22 at 16:21

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • The only limitation I see when creating foreign key reference between partitioned table is that foreign key schema maintenance overhead will increase a lot since partition set are created dynamically. For first level partition(list partition by catalog_id) I use trigger and for second level partition(range partition by submit_time) we use pg_partman. Do you suggested any clean approach to create foreign key reference between the partitioned tables ? – harsh kumar Dec 09 '22 at 04:23
  • The approach would be to create foreign key constraints between the partitions, not between the partitioned tables. Is that possible from the data contents? I can't tell from the information you posted. – Laurenz Albe Dec 09 '22 at 08:59