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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
