Rebuilding a primary key index

Primary keys may require regular maintenance like any other index. But extra care needs to be taken with primary keys in order to maintain data integrity. This article explains the recommended procedure for maintaining primary key indexes.

The summary of steps that need to be carried on for this process are:

1. Create a new unique index on the primary key columns.
2. Wait for the new index to finish building.
3. Start a transaction to: a. Drop referencing foreign key constraints b. Drop the primary key index c. Alter the new unique index to add the primary key constraint d. Recreate the foreign key constraints
4. commit the transaction

In the following example, we will demonstrate how to rebuild a primary key index on table a where table b has a foreign key constraint that references the primary key on table a. If the table a has other foreign key constraints pointing to it, additional DDL statements to drop and recreate such foreign keys will be needed as well.

postgres =# \d a
Table "public.a"
Column | Type | Collation | Nullable | Default
z | bigint | | not null |
Indexes:
"a_pkey" PRIMARY KEY, btree (z)
Referenced by:
TABLE "b" CONSTRAINT "b_y_fkey" FOREIGN KEY (y) REFERENCES a(z)

postgres =# \d b
Table "public.b"
Column | Type | Collation | Nullable | Default
y | bigint | | not null |
Indexes:
"b_pkey" PRIMARY KEY, btree (y)
Foreign-key constraints:
"b_y_fkey" FOREIGN KEY (y) REFERENCES a(z)

The first step is to create a new unique index on the primary key columns. Creating the index CONCURRENTLY may take extra time and resources but allows concurrent reads, inserts, updates, or deletes to continue on table a.

CREATE UNIQUE INDEX CONCURRENTLY new_a_pkey ON a(z);

Note that we explicitly name the new index and we can rename it to match the original primary key index name towards the end of the procedure.

Once the new index has finished building, the original primary key index needs to be dropped, along with the foreign key constraints that refer to it, before finally altering the new unique index with the primary key constraint.

BEGIN;

-- Remove the primary key and related foreign keys.
ALTER TABLE b DROP CONSTRAINT b_y_fkey;
ALTER TABLE a DROP CONSTRAINT a_pkey;

-- Add the primary key constraint to the new index and use the original index name
ALTER TABLE a add CONSTRAINT a_pkey PRIMARY KEY USING INDEX new_a_pkey;

-- Recreate all of the foreign key constraints.
ALTER TABLE b ADD FOREIGN KEY (y) REFERENCES a(z);

COMMIT;