postgreSQL concurrently change column type from int to bigint

Question

I have a pretty big table (around 1 billion rows), and I need to update the id type from SERIAL to BIGSERIAL; guess why?:).

Basically this could be done with this command:

execute "ALTER TABLE my_table ALTER COLUMN id SET DATA TYPE bigint"

Nevertheless that would lock my table forever and put my web service down.

Is there a quite simple way of doing this operation concurrently (whatever the time it will take)?

Answer 1

If you don't have foreign keys pointing your id you could add new column, fill it, drop old one and rename new to old:

alter table my_table add column new_id bigint;

begin; update my_table set new_id = id where id between 0 and 100000; commit;
begin; update my_table set new_id = id where id between 100001 and 200000; commit;
begin; update my_table set new_id = id where id between 200001 and 300000; commit;
begin; update my_table set new_id = id where id between 300001 and 400000; commit;
...

create unique index my_table_pk_idx on my_table(new_id);

begin;
alter table my_table drop constraint my_table_pk;
alter table my_table alter column new_id set default nextval('my_table_id_seq'::regclass);
update my_table set new_id = id where new_id is null;
alter table my_table add constraint my_table_pk primary key using index my_table_pk_idx;
alter table my_table drop column id;
alter table my_table rename column new_id to id;
commit;