Re: Switching Primary Keys to BigInt

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Switching Primary Keys to BigInt
Дата
Msg-id CAHOFxGowsW+99mNaeQ2UPwNLVg0EaFQBJ7duX6PjJy+TdMkkjg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Switching Primary Keys to BigInt  (Mohamed Wael Khobalatte <mkhobalatte@grubhub.com>)
Ответы Re: Switching Primary Keys to BigInt
Список pgsql-general
On Wed, Jul 22, 2020 at 12:23 PM Mohamed Wael Khobalatte <mkhobalatte@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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10
Следующее
От: Mohamed Wael Khobalatte
Дата:
Сообщение: Re: Switching Primary Keys to BigInt