Re: Foreign key creation on table with huge record count.

Hi Gambhir. If the PostgreSQL version is 10 or above, you can create the
FK using the option *not valid* in DDL.
You can see more details at
https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-NOTES

*Scanning a large table to verify a new foreign key or check constraint can
> take a long time, and other updates to the table are locked out until the
> ALTER TABLE ADD CONSTRAINT command is committed. The main purpose of the
> NOT VALID constraint option is to reduce the impact of adding a constraint
> on concurrent updates. With NOT VALID, the ADD CONSTRAINT command does not
> scan the table and can be committed immediately. After that, a VALIDATE
> CONSTRAINT command can be issued to verify that existing rows satisfy the
> constraint. The validation step does not need to lock out concurrent
> updates, since it knows that other transactions will be enforcing the
> constraint for rows that they insert or update; only pre-existing rows need
> to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE
> lock on the table being altered. (If the constraint is a foreign key then a
> ROW SHARE lock is also required on the table referenced by the constraint.)
> In addition to improving concurrency, it can be useful to use NOT VALID and
> VALIDATE CONSTRAINT in cases where the table is known to contain
> pre-existing violations. Once the constraint is in place, no new violations
> can be inserted, and the existing problems can be corrected at leisure
> until VALIDATE CONSTRAINT finally succeeds.*
>

All my best!

*Lucio Chiessi*

Senior Database Administrator

Trustly, Inc.

M: +55 27 996360276

<https://www.linkedin.com/company/trustly/>
<https://www.facebook.com/trustly> <https://twitter.com/Trustly>

<https://www.linkedin.com/company/trustly/>
<https://www.facebook.com/trustly> <https://twitter.com/Trustly>

<https://www.trustly.net/>

PayWith*MyBank®* is now part of *Trustly*

On Mon, Jan 30, 2023 at 5:13 PM Gambhir Singh <gambhir(dot)singh05(at)gmail(dot)com>
wrote:

> Hi,
>
> I have a table with a record count of around 100 Million records. while
> creating foreign key on the table took a significant amount time to get
> created.
>
> Is there any way to speed up the execution of creation of foreign key
> constraint ?
>
> do we have parallel hint like option in postgreSQL as there in Oracle.
>
>
> --
> Thanks & Regards
> Gambhir Singh
>
>

--
Please read our privacy policy here
<https://www.trustly.net/about-us/privacy-policy> on how we process your
personal data in accordance with the General Data Protection Regulation
(EU) 2016/679 (the “GDPR”) and other applicable data protection legislation