On 7/21/20 8:30 AM, Mohamed Wael Khobalatte wrote:
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;
Could you not simplify to something like this:
test_(aklaver)5432> create table change_seq(id serial PRIMARY KEY);
CREATE TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------------
id | integer | | not null |
nextval('change_seq_id_seq'::regclass)
Indexes:
"change_seq_pkey" PRIMARY KEY, btree (id)
test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
bigint;
ALTER TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+----------------------------------------
id | bigint | | not null |
nextval('change_seq_id_seq'::regclass)
Indexes:
"change_seq_pkey" PRIMARY KEY, btree (id)
test_(aklaver)5432> alter sequence change_seq_id_seq as bigint;
ALTER SEQUENCE
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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
