Re: Switching Primary Keys to BigInt

On 7/21/20 2:18 PM, Mohamed Wael Khobalatte wrote:
>>  > test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
>> bigint;
>> ALTER TABLE
>> test_(aklaver)5432> \d change_seq
>>                              Table "public.change_seq"
>>   Column |  Type  | Collation | Nullable |                Default
>> --------+--------+-----------+----------+----------------------------------------
>>
>>   id     | bigint |           | not null |
>> nextval('change_seq_id_seq'::regclass)
>> Indexes:
>>      "change_seq_pkey" PRIMARY KEY, btree (id)
> 
> This is significant downtime, since it locks exclusively, no? We want to 
> avoid that.

Yeah, I thought the int --> bigint would not do a table rewrite. Testing 
showed otherwise. Forget that idea.

> 
>  > Side note- EOL for 9.6 is coming next year so just a plug for 
> upgrading when possible, perhaps utilizing pglogical to get to v11 or 12.
> 
> Yep, we are painfully aware. The id growth will beat us to it, so we 
> need to deal with that first.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com