Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.300.101.1.3\))
Subject: Re: Foreign key creation on table with huge record count.
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAHOGQfXR8JZk=-GyVeRVUNC4-ZoHUZmv=uRjWUOL2xnPuyDqnQ@mail.gmail.com>
Date: Mon, 30 Jan 2023 14:09:35 -0700
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <954C1F48-B9C7-48FE-A135-E643EBCD9892@elevated-dev.com>
References: 
 <CAHOGQfXR8JZk=-GyVeRVUNC4-ZoHUZmv=uRjWUOL2xnPuyDqnQ@mail.gmail.com>
To: Gambhir Singh <gambhir.singh05@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/954C1F48-B9C7-48FE-A135-E643EBCD9892%40elevated-dev.com>
Precedence: bulk

> On Jan 30, 2023, at 1:12 PM, Gambhir Singh <gambhir.singh05@gmail.com> =
wrote:
>=20
> do we have parallel hint like option in postgreSQL as there in Oracle.=20=


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