Re: Foreign key creation on table with huge record count. - Mailing list pgsql-admin

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.)