In Postgres, why does dropping an index lock the table?

Question

CREATE/DROP INDEX in postgres has the option "concurrently" which can be used to make the CREATE/DROP operation not lock the database.

https://www.postgresql.org/docs/current/sql-dropindex.html

It made me wonder, why does dropping the index regularly need to lock the table it indexes?

I assume it has something to do with "CASCADE" or the inability to do it ACID within a transaction since those are the features lost but I'm not really sure what exactly it would be that prevents it.

It does not lock the "database", it only locks the table. Any DDL statement needs to lock the table to prevent other DDL or DML. But as dropping is typically a really quick operation, that isn't such a big issue — user330315
– user330315, Commented Dec 7, 2021 at 21:42
updated title to say table thanks. Do you have any reference for the dropping being quick thing? I'd be useful when I want to present that as an option to a coworker — ThinkBonobo
– ThinkBonobo, Commented Dec 7, 2021 at 21:45
An index is basically a look up table over whatever it is indexing. It locks the table to prevent some query that needs the index doing a look up and having the index disappear during the query. This could lead to corrupted results. — Adrian Klaver
– Adrian Klaver, Commented Dec 8, 2021 at 0:42
@AdrianKlaver if you post this as the answer I can mark it as the accepted answer — ThinkBonobo
– ThinkBonobo, Commented Jul 22, 2022 at 18:21

Jhoval · Accepted Answer · 2023-02-16 10:27:36Z

When you drop an index in PostgreSQL, the database needs to lock the table to ensure that no other transactions modify the table while the index is being dropped. This is because the index is used to maintain the integrity of the data in the table, and if other transactions were allowed to modify the table while the index was being dropped, it could lead to data inconsistencies and other issues.

Locking the table during the index drop operation ensures that any changes to the table are temporarily blocked until the operation is completed, which helps to maintain the integrity of the data. This locking behavior is necessary to prevent other transactions from accessing the table while it is being modified.

However, locking the table can cause performance issues for other transactions that are waiting for the lock to be released. This is especially true for large tables or complex indexes, where the drop operation can take a significant amount of time. To mitigate this issue, PostgreSQL provides a DROP INDEX CONCURRENTLY command that allows you to drop an index without locking the table, by creating a new index and tracking table modifications (as OP correctly mentioned).

Is interesting to remark the following for DROP INDEX CONCURRENTLY:

PostgreSQL creates a new index with a different name and then use this new index to catch any modifications that happen to the table during the drop operation.
Dropping an index concurrently can be a resource-intensive operation.
Concurrent index drops can cause increased disk space usage.

One extra thing you can do to speed up the process is to increase the maintenance_work_mem setting: PostgreSQL uses the maintenance_work_mem setting to control the amount of memory it can use for maintenance operations like index drops. Increasing this setting can speed up the drop operation by allowing PostgreSQL to allocate more memory for the drop operation.

This is the answer for why ADD index locks a table but doesn't at all explain why DROP index locks which is why the question was specifically about DROP.

Collectives™ on Stack Overflow

In Postgres, why does dropping an index lock the table?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related