Обсуждение: Foreign key creation on table with huge record count.
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
Gambhir Singh
> 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.
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!
On Mon, Jan 30, 2023 at 5:13 PM Gambhir Singh <gambhir.singh05@gmail.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 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:
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.)
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.
Born in Arizona, moved to Babylonia.
Hi,
"Join" need to be studied from table to table basis for different databases!
Ofcourse! You can speed up the execution of creation of FKC (Foreign Key 🔐 constraint) simply by running the queries that involve joins between the parent and child tables.
Foreign key indexes can significantly improve performance for queries that involve joins between the parent and child tables.
Regards,
Abhishek Singh
*****************************************
*****************************************
*****************************************
Sent using Panasonic - Egula 810 9P
*****************************************
*****************************************
On Tue, 31 Jan, 2023, 01:43 Gambhir Singh, <gambhir.singh05@gmail.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