Hi all,
We are running 9.6, and we are planning to move some primary keys from int to bigint because we are approaching the type limit. We understand this requires some downtime, but we want to know if there are things we can do to limit it.
Here are our steps, with questions at the end.
ALTER TABLE some_table ADD COLUMN new_id bigint;
/* in batches, we update all the rows to new_id = id */
CREATE UNIQUE INDEX CONCURRENTLY some_table_pkey_new ON some_table(new_id);
/* take the apps down */
BEGIN;
LOCK TABLE some_table;
UPDATE some_table SET new_id = id WHERE new_id IS NULL;
ALTER SEQUENCE some_table_id_seq OWNED BY some_table.new_id;
ALTER TABLE some_table ALTER COLUMN new_id SET DEFAULT nextval('some_table_id_seq'::regclass);
ALTER TABLE some_table DROP CONSTRAINT some_table_pkey;
ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY USING INDEX some_table_pkey_new;
ALTER TABLE some_table DROP COLUMN id;
ALTER TABLE some_table RENAME COLUMN new_id to id;
COMMIT;
We are concerned with this step:
> ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY USING INDEX some_table_pkey_new;
which requires a table scan. Is there a way to avoid that? Would a not null constraint on new_id that is created as invalid first, then validated later help us? I tried on a table with about 50 million records, and I see a drop from 19 seconds spent on the alter to 8 seconds, which is inconclusive (both after restarts for cold cache). Is there another way to tell? Or does PG just have to do a sequential scan?
If the constraint idea works, we would probably need to add a trigger to update new_id, but that's TBD.
