Adding constraints faster

From: sud <suds1434(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Adding constraints faster
Date: 2024-05-14 19:59:16
Message-ID: CAD=mzVUrWysYQGy336aDM8=wqFBctv3ikW=_g9JWZDPpbrsKNQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dirschel, Steve 2024-05-14 20:24:49 Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure
Previous Message Dimitrios Apostolou 2024-05-14 18:26:20 Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions