postgres how to modify composite primary key order

All we need is an easy explanation of the problem, so here it is.

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;
  1. 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)?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply