1

I have a table index in my postgres 9.6 db that I think would be more efficient if the table was sorted descending rather than ascending (as it currently is). The data that I want to test is not in full production use, but could potentially be accessed by users at any time, and I'd rather not take it down if I can avoid it. Two questions:

  1. Is it possible to switch the index from ascending to descending on-the-fly?
  2. If not, I guess I need to drop the index and re-create it. Obviously this will slow down search queries while there is no index. Is that the worst that will happen? Nothing will stop working entirely, right? As I understand it, it will get an exclusive lock on the table before dropping the index, right?

Apologies for the n00b question, I'm quite new to databases. Please be gentle with me!

  • Can you show us the table+index definitions and the queries that use the index? – ypercubeᵀᴹ Mar 14 '18 at 15:49
  • 2
    You can always add a 2nd index by the way (using concurrently, no table locks). And them remove the first one. – ypercubeᵀᴹ Mar 14 '18 at 15:49
1

If you dont want to lock table data you can use the clause CONCURRENTLY to drop the index an recreate it, this process is slower but avoids to lock access to the users.

DROP INDEX CONCURRENTLY my_index;
CREATE INDEX CONCURRENTLY my_index ON my_table (my_column DESC);

You can take a look at the postgres index documentation for more detailed information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.