From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Foreign key creation on table with huge record count.
Date: Mon, 30 Jan 2023 15:55:38 -0600
Message-ID: <5b627408-a8b1-050b-cac1-3fa71c13b691@gmail.com> (raw)
In-Reply-To: <CAHOGQfXR8JZk=-GyVeRVUNC4-ZoHUZmv=uRjWUOL2xnPuyDqnQ@mail.gmail.com>
References: <CAHOGQfXR8JZk=-GyVeRVUNC4-ZoHUZmv=uRjWUOL2xnPuyDqnQ@mail.gmail.com>
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.)
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Foreign key creation on table with huge record count.
In-Reply-To: <5b627408-a8b1-050b-cac1-3fa71c13b691@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox