Re: Switching Primary Keys to BigInt
От | Adrian Klaver |
---|---|
Тема | Re: Switching Primary Keys to BigInt |
Дата | |
Msg-id | 1a7885cf-43b4-fc41-4825-190b6d2defc3@aklaver.com обсуждение исходный текст |
Ответ на | Switching Primary Keys to BigInt (Mohamed Wael Khobalatte <mkhobalatte@grubhub.com>) |
Ответы |
Re: Switching Primary Keys to BigInt
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
Список | pgsql-general |
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@aklaver.com
В списке pgsql-general по дате отправления: