Re: [GENERAL] Speed of conversion from int to bigint

On Wed, 27 Sep 2017 11:31:54 +0200
Tomas Vondra <tomas.vondra@2ndquadrant.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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general