pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign key creation on table with huge record count.
5+ messages / 5 participants
[nested] [flat]
* Foreign key creation on table with huge record count.
@ 2023-01-30 20:12 Gambhir Singh <gambhir.singh05@gmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: Gambhir Singh @ 2023-01-30 20:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Foreign key creation on table with huge record count.
@ 2023-01-30 21:09 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Gambhir Singh <gambhir.singh05@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Scott Ribe @ 2023-01-30 21:09 UTC (permalink / raw)
To: Gambhir Singh <gambhir.singh05@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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 options to support parallel queries, and you can tune up number of workers etc. I don't actually know if it can use multiple workers on constraint creation, but you can use "explain" on your command to create the constraint, to see if the planner 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 config tuned for just this: let work mem for this one connection use up a big % of RAM, use multiple workers etc. In some cases I've even turned fsync off--this means if you crash you could wind up with a corrupted database, but if you have a current backup, then you either succeed and turn fsync back on when you're done, or you have your backup if something bad happens.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Foreign key creation on table with huge record count.
@ 2023-01-30 21:21 Lucio Chiessi <lucio.chiessi@trustly.com>
parent: Gambhir Singh <gambhir.singh05@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Lucio Chiessi @ 2023-01-30 21:21 UTC (permalink / raw)
To: PGSql Admin list <pgsql-admin@lists.postgresql.org>
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.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
<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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Foreign key creation on table with huge record count.
@ 2023-01-30 21:55 Ron <ronljohnsonjr@gmail.com>
parent: Gambhir Singh <gambhir.singh05@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Ron @ 2023-01-30 21:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Foreign key creation on table with huge record count.
@ 2023-01-30 23:02 Abhishek Singh <abhi.singh.solanki@gmail.com>
parent: Gambhir Singh <gambhir.singh05@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Abhishek Singh @ 2023-01-30 23:02 UTC (permalink / raw)
To: Gambhir Singh <gambhir.singh05@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
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.
"Join" need to be studied from table to table basis for different databases!
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-01-30 23:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-30 20:12 Foreign key creation on table with huge record count. Gambhir Singh <gambhir.singh05@gmail.com>
2023-01-30 21:09 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-01-30 21:21 ` Lucio Chiessi <lucio.chiessi@trustly.com>
2023-01-30 21:55 ` Ron <ronljohnsonjr@gmail.com>
2023-01-30 23:02 ` Abhishek Singh <abhi.singh.solanki@gmail.com>
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