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