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

> On Jan 30, 2023, at 1:12 PM, Gambhir Singh <gambhir.singh05@gmail.com> wrote:
>
> do we have parallel hint like option in postgreSQL as there in Oracle.

I don't know of a hint option you can put on a query. But if you are running a recent enough version of PG, there are
optionsto support parallel queries, and you can tune up number of workers etc. I don't actually know if it can use
multipleworkers on constraint creation, but you can use "explain" on your command to create the constraint, to see if
theplanner will be using parallel workers. 

Of course make sure that the field being referred to is indexed, in other words given

col1 references table2(col2)

make sure col2 is indexed

Is this operation effectively taking your database out of service while it runs? If so, then you can temporarily use a
configtuned for just this: let work mem for this one connection use up a big % of RAM, use multiple workers etc. In
somecases I've even turned fsync off--this means if you crash you could wind up with a corrupted database, but if you
havea current backup, then you either succeed and turn fsync back on when you're done, or you have your backup if
somethingbad happens. 

Please read our privacy policy here 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

On 1/30/23 14:12, Gambhir Singh 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 ?

I combine the suggestions of the other two emails:
1. Make sure that there's an index on the relevant field of the referenced table.
2. ALTER TABLE foo ADD CONSTRAINT foo_fk FOREIGN KEY(bar) REFERENCES blarge (bar) NOT VALID;
3. ALTER TABLE foo VALIDATE CONSTRAINT.

It's really fast.

(Step 1 is vital for operations like purging old records.  Without that index deletes can take months.)