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; -- build unique index on the new column CREATE INDEX CONCURRENTLY my_table_2_pkey ON my_table (new_table_id); BEGIN; -- switch the PK constraint ALTER TABLE my_table DROP CONSTRAINT my_table_pkey; ALTER TABLE my_table ADD CONSTRAINT my_table_pkey PRIMARY KEY (new_table_id) USING my_table_2_pkey; -- drop, rename the columns ALTER TABLE my_table DROP COLUMN table_id; ALTER TABLE my_table ALTER COLUMN new_table_id RENAME TO table_id; COMMIT; You may need to tweak this to handle the inheritance tree. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
