Re: Speed of conversion from int to bigint

On Wed, 27 Sep 2017 09:08:25 +0100, Jonathan Moules
<jonathan-lists(at)lightpear(dot)com> wrote:

>Hi,
>(Postgres 9.5 and 9.6)
>We have a table of about 650million rows. It's a partitioned table,
>with two "child" tables. We want to change its primary key type
>from int to bigint while retaining the current values.
>
>We're using this:
>
>ALTER TABLE dta.my_table ALTER column table_id TYPE bigint;
>
>But it's taking a very long time, and locking the database. We're
>going to need to do this in production as well, so a long-term
>table-lock isn't workable.

>Is there anything we can do to speed things up?

Better to create a new table having the right structure and then copy
the data from the original table.

>How long is this likely to take?

Coping 650M rows will be [slightly] faster than altering the structure
of the original table, but it still won't be quick.

If you need to keep the original in service while copying, one trick
is to add a boolean "copied" column (default false) to the original
table, That will be very quick [no constraints to check].

Then, in a loop, do something like:

*** warning - pseudo code ***
----
while not done
with
batch as
(update <source>
set copied = true
where not copied
limit 10000
returning <columns_to_copy> )
insert into <target>
select *
from batch

if affected rows < 10000
begin transaction serializable
alter table <source> rename to <backup>
alter table <target> rename to <source>
commit
----

Rinse and repeat until all the rows have been transferred to the new
table. When the insert row count drops below the batch size [assuming
no errors have occurred], you know you have copied the last batch.
Then you quickly rename the tables to put the new table into service.

You need to do a final check for and copy of any updates to the
original that might have snuck in while processing the last batch. And
lastly you can drop the source table.

It won't be fast, but it also won't paralyze your database while its
working.

>Thanks,
>Jonathan

Hope this helps.
George