From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Gambhir Singh <gambhir.singh05@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Foreign key creation on table with huge record count.
Date: Mon, 30 Jan 2023 14:09:35 -0700
Message-ID: <954C1F48-B9C7-48FE-A135-E643EBCD9892@elevated-dev.com> (raw)
In-Reply-To: <CAHOGQfXR8JZk=-GyVeRVUNC4-ZoHUZmv=uRjWUOL2xnPuyDqnQ@mail.gmail.com>
References: <CAHOGQfXR8JZk=-GyVeRVUNC4-ZoHUZmv=uRjWUOL2xnPuyDqnQ@mail.gmail.com>
> 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.
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: scott_ribe@elevated-dev.com, gambhir.singh05@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Foreign key creation on table with huge record count.
In-Reply-To: <954C1F48-B9C7-48FE-A135-E643EBCD9892@elevated-dev.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