PostgreSQL primary key expansion using concurrently maintenance

1. Pre-maintenance

postgres=# \d+ test                                                           table "Public.test"   Column |             Type              |                      Modifiers| Storage | Stats target |  Description--------+-----------------------------+----------------------------------------------------+-------- -+--------------+-------------  id     | integer                      | not null  Defaultnextval (' test_id_seq ':: Regclass)  | plain   |               |  val    | timestamp  without time zone | default now () | plain   |               | indexes:      "Test_pkey"  PRIMARY KEY, btree  (ID)

2. Create a new unique index

postgres=# CREATE UNIQUE INDEX concurrently on test USING btree (ID); CREATE INDEX

3. Replace the primary key

postgres=# BEGIN; beginpostgres=# ALTER TABLE test DROP CONSTRAINT Test_pkey; Alter tablepostgres=# alter TABLE test ADD CONSTRAINT test_id_idx PRIMARY KEY usingindex test_id_idx; ALTER tablepostgres=# COMMIT; COMMIT

4. After maintenance

postgres=# \d+ test                                                           table "Public.test"   Column |             Type              |                      Modifiers| Storage | Stats target |  Description--------+-----------------------------+----------------------------------------------------+-------- -+--------------+-------------  id     | integer                      | not null  Defaultnextval (' test_id_seq ':: Regclass)  | plain   |               |  val    | timestamp  without time zone | default now () | plain   |               | indexes:      "Test_id_idx"  PRIMARY KEY, btree  (ID)

The benefit is that you can maintain the primary key when the load is low during the day

PostgreSQL primary key expansion using concurrently maintenance