postgresql change index order

user_2028385 0
Viewed 230

I created an index as follows:

CREATE INDEX index_name_desc_idx
          ON table_name
       USING btree (updated_at ASC)

now: ASC was an error, I need to change it to DESC. I'm trying several things with ALTER INDEX however nothing seems to work and I'm afraid the only thing to do is to remove the index and recreate it. Is there a way to edit the index ordering?

indexing postgresql sql
1 Answers

I'm afraid the only thing to do is to remove the index

Don't be afraid, You can do it without any downtime :

  • first create your new index in the good order, but with CONCURRENTLY to avoid any lock,
  • then, drop the old index.

No lock, and no query without index, with the only downside of having a 2n index size while you do the change.

user_55462670
Related