3

I have a Postgres table with many indexes some of which I need to remove as they are unused. To do this without blocking reads and writes to the table I want to use DROP INDEX CONCURRENTLY. I have a total of 10 indexes I want to remove and due to the setup of my migration system it has to go through a full deploy for each migration which is about a 45 minute process. Ideally I would like to not have to do each of these drops in a separate migration but looking through the documentation I cannot find a clear statement on whether it is safe to run multiple DROP INDEX CONCURRENTLYs on the same table at once. In the documentation for CREATE INDEX CONCURRENTLY there is a note:

Regular index builds permit other regular index builds on the same table to occur simultaneously, but only one concurrent index build can occur on a table at a time.

I do not see a similar note in the documentation for DROP INDEX CONCURRENTLY. Is it safe to run multiple DROP INDEX CONCURRENTLY's on the same table at once? Also worth noting some of the indexes are on the same column. Would it be safe to drop those at the same time?

1 Answer 1

3

My quick experiment finds that you can successfully run multiple DROP INDEX CONCURRENTLY statements simultaneously (on PG14 anyway). The indexes being dropped are marked INVALID so no new queries use them, then it waits out any locks before completing the drop. Only one DROP is active at a time so there is no possibility of conflict.

Indexes are not dependent on each other, even if they are using the same columns. If that were the case, they would have to be dropped in a particular order.

CREATE INDEX is more dicey as updates to the table have to be tracked during the process, thus the one-at-a-time restriction. No such problem when dropping the index.

For the record, the non-concurrent DROP INDEX allows you to specify multiple indexes at once to drop, while DROP INDEX CONCURRENTLY only allows one index per statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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