Re: Switching Primary Keys to BigInt

On Wed, Jul 22, 2020 at 12:23 PM Mohamed Wael Khobalatte <
mkhobalatte(at)grubhub(dot)com> wrote:

> No worries. I suppose the answer to the original question, which is how to
> avoid a table scan when adding a primary key constraint to a newly
> backfilled column is "there is no way"? Downtime might be at least as long
> as the table scan.
>

One presumes you may be planning to use pglogical or another similar
solution to upgrade to a new Postgres version soon, and would have a
convenient time then to change schema. I am curious, why not just stick
with the single column unique index and forgo for the primary key
constraint for now? If you are concerned about the possibility of a single
null value being inserted, then you could add a not valid check constraint
to enforce that for future rows.

You do you. Obviously testing the primary key on a full replica of the data
with similar hardware and configs will give you a pretty good idea of the
time for that tablescan and adding the constraint in real life. Given your
email domain, I can guess why you would need to absolutely minimize
downtime.