Re: Switching Primary Keys to BigInt - Mailing list 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