Hi,
It's postgres version 15.4. We want to create foreign keys on three different partitioned tables which already have data in them in production. They all are referring to the same parent table which is also partitioned. All the tables(both parent and child) are having ~2TB+ in size each and having ~100 million rows in each of the partitions. These are range partitioned on truncated date columns and the total number of partitions in the tables is around 30 in each of them.
It's easy to create the foreign key on the new partitions of the child table which are blank and going to be filled with data in future, however adding the foreign key on the existing partition with data is going to take time. We tried with one sample partition with existing data in it and it took ~20minutes. So this way , it's going to take a long time and we may not have the application down for such a long time.
I have the following questions.
To make this activity faster we were thinking of using the "NOT VALID" option. I.e create the foreign key constraints on the existing partitions with "NOT VALID" option and create the foreign key on the blank future partitions with the VALID option. Is this okay? As because we also see in some documents stating that , if the foreign key is in the "NOT VALID" state ,optimizer won't be using it for estimating the row counts during making join cardinality estimation, so want to understand from experts if its fine or we have to make that foreign key constraints "VALID" anyway, even if that runs longer?
We also tried to set the max_parallel_workers_per_gather to 8 and then run the "validate constraint" step but that is still running in a single thread only. So wondering if we have any other options available to make this foreign key addition faster with existing data in it?
******
ALTER TABLE ADD FOREIGN KEY ... NOT VALID.
ALTER TABLE ... VALIDATE CONSTRAINT;
Regards
Sud