On Wed, 27 Sep 2017 11:31:54 +0200 Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
