Subject: Adding constraints faster

From: sud <[email-masked]>
Date: Wed, 15 May 2024 01:29:16 +0530

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

---

From: sud <[email-masked]>
Date: Wed, 15 May 2024 08:32:50 +0530

On Wed, May 15, 2024 at 2:09 AM Ron Johnson <[email-masked]> wrote:

> On Tue, May 14, 2024 at 3:59 PM sud <[email-masked]> wrote:
>
>> *****
>>
> This is what we did, back in the PG 12.x period.  VALIDATE CONSTRAINT was
> almost instantaneous.  (Supporting indices existed, though.)
>
>
Thank you. Actually we do have a composite index on the child table columns
which is referring to the parent table. And in the parent table those
columns are the primary keys. So even then it's taking ~20minutes for each
partition.

So is there some other way to make it run faster
or
 Can we run it concurrently
or
Should we keep the existing constraints in "not valid" state only?