MIME-Version: 1.0
From: sud <suds1434@gmail.com>
Date: Wed, 15 May 2024 01:29:16 +0530
Message-ID: <CAD=mzVUrWysYQGy336aDM8=wqFBctv3ikW=_g9JWZDPpbrsKNQ@mail.gmail.com>
Subject: Adding constraints faster
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000816c0f06186f70a7"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVUrWysYQGy336aDM8%3DwqFBctv3ikW%3D_g9JWZDPpbrsKNQ%40mail.gmail.com>
Precedence: bulk

--000000000000816c0f06186f70a7
Content-Type: text/plain; charset="UTF-8"

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

--000000000000816c0f06186f70a7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<br>It&#39;s=C2=A0postgres version 15.4. We want to cre=
ate foreign keys on three different partitioned tables which already have d=
ata 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.<br><br>It&#39;s =
easy to create the foreign key on the new partitions of the child table whi=
ch 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 ~20min=
utes. So this way , it&#39;s going to take a long time and we may not have =
the application down for such a long time.<br><br>I have the following ques=
tions.<br>To make this activity faster we were thinking of using the &quot;=
NOT VALID&quot; option. I.e create the foreign key constraints on the exist=
ing partitions with &quot;NOT VALID&quot; option and create the foreign key=
 on the blank future partitions with the VALID option. Is this okay? As bec=
ause we also see in some documents stating that , if the foreign key is in =
the &quot;NOT VALID&quot; state ,optimizer won&#39;t be using it for estima=
ting the row counts during making join cardinality estimation, so want to u=
nderstand from experts if its fine or we have to make that foreign key cons=
traints &quot;VALID&quot; anyway, even if that runs longer?<br><br>We also =
tried to set the max_parallel_workers_per_gather to 8 and then run the &quo=
t;validate constraint&quot; step but that is still running in a single thre=
ad only. So wondering if we have any other options available to make this f=
oreign key addition faster with existing data in it? <br><br>******<br><fon=
t face=3D"monospace">ALTER TABLE ADD FOREIGN KEY ... NOT VALID. <br>ALTER T=
ABLE ... VALIDATE CONSTRAINT;<br></font><div><br></div><div>Regards</div><d=
iv>Sud</div></div>

--000000000000816c0f06186f70a7--