Re: Switching Primary Keys to BigInt

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(dot)klaver(at)aklaver(dot)com