postgres how to modify composite primary key order

Question

I want to modify the composite primary key order, for example, from (col_b, col_a) to (col_a, col_b) because I want to query with prefix column (i.e, col_a) and want to take advantage of the primary key index.

The way I tried to do this is :

1. drop pk constrain

ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey;

2. add it back with correct order

ALTER TABLE <table_name> ADD PRIMARY KEY (col_a, col_b);

Is it the right approach to do this? And will this cleanup the legacy index and create new index (which is the behavior I desire)?

Answer 1

That will work, but there is possibility someone could catch you in between those two statements and insert an illegal value. If you run them in one transaction, you would preclude that by keeping one lock on the table with no gap. But it would also block other work while the new index was being built.

So if you don't want to have a maintenance window, you could do it this way:

create unique index concurrently new_key on foo (col_a,col_b);
begin;
alter table foo drop constraint foo_pkey ;
alter table foo add constraint foo_pkey primary key using index new_key;
commit;

This will still take a strong lock on the table (several of them at different times) but should only hold each one for a tiny fraction of a second. This could still cause problems if the lock attempts block for a long time, though.

• oliora: Amazing answer!