On Wed, Jul 22, 2020 at 12:23 PM Mohamed Wael Khobalatte < mkhobala...@grubhub.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.
