Re: Switching Primary Keys to BigInt

On Wed, Jul 22, 2020 at 9:27 AM Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

> Mohamed Wael Khobalatte wrote:
>
> > > alter table change_seq alter COLUMN id set data
> > > type bigint;
>
> > This is significant downtime, since it locks exclusively, no? We want to
> > avoid that.
>
> Well, in the steps you mentioned upthread, the transaction starts by
> doing LOCK TABLE some_table, so it will hold an exclusive lock on it
> for the rest of the transaction.
>
> If you can test how the ALTER TABLE... SET TYPE ... compares
> to your procedure in terms of downtime, that would be interesting.
> To me, it's not clear why the procedure in multiple steps would
> be better overall than a single ALTER TABLE.
>

We lock the table as a precaution, with the understanding that we are
undergoing a "small" downtime to finish replacing the int id by the new
bigint. The only slow thing in my procedure is the sequential scan that the
ADD CONSTRAINT does because the column is a primary key. A direct alter
table would be far slower, not to mention space requirements?