Re: Speed of conversion from int to bigint

On Wed, 27 Sep 2017 11:31:54 +0200
Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:

> On 09/27/2017 10:08 AM, Jonathan Moules 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.
>
> It's taking very long time, because it does a full-table rewrite while
> holding AccessExclusiveLock on it. Which is the strongest lock mode.
>
> > Is there anything we can do to speed things up? How long is this likely
> > to take?
> >
>
> What you can do, is roughly this:
>
> ---
> ALTER TABLE my_table ADD COLUMN new_table_id TYPE bigint;
>
> -- do this in batches, so that a single transaction does not update
> -- all the rows
> UPDATE my_table SET new_table_id = table_id;

After or before each UPDATE in your batch, make sure to run a VACUUM on your
table, to keep bloat as low as possible.

Without vacuum, you will probably end up with a table up to twice bigger than
before the maintenance...And you'll have to handle this in another maintenance
period.

> -- build unique index on the new column
> CREATE INDEX CONCURRENTLY my_table_2_pkey ON my_table (new_table_id);

It misses an "UNIQUE" keyword :

CREATE UNIQUE INDEX CONCURRENTLY my_table_2_pkey ON my_table (new_table_id);

Regards,
--
Jehan-Guillaume de Rorthais
Dalibo