InletDownload

PostgreSQL migration

Does DROP INDEX lock the table in PostgreSQL?

Yes. A plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, not just the index. The drop itself is instant, but it waits for every query using the table, and every new query waits behind it. DROP INDEX CONCURRENTLY takes a weaker lock and lets reads and writes carry on.

Tested on PostgreSQL 14, 15, 16, 17, 18 · Updated 9 October 2026

Lock
ACCESS EXCLUSIVE
Blocks
Reads and writes
Rewrites the table
No

Short answer

DROP INDEX orders_status_idx takes an ACCESS EXCLUSIVE lock on the table, the strongest lock there is. Removing the index takes a millisecond, but the lock can’t be granted while any query is using the table, and while it waits, every new SELECT and write on the table queues behind it.

DROP INDEX CONCURRENTLY orders_status_idx avoids that. It takes a SHARE UPDATE EXCLUSIVE lock, marks the index unusable, waits for queries that might still be using it, and then removes it. Reads and writes carry on throughout.

What it locks

StatementLock on the tableLock on the indexReads and writes while it waits
DROP INDEXACCESS EXCLUSIVEACCESS EXCLUSIVEWait (queue behind it)
DROP INDEX CONCURRENTLYSHARE UPDATE EXCLUSIVESHARE UPDATE EXCLUSIVERun

We tested the queue directly: with a transaction that had read the table still open, a plain DROP INDEX waited 2.5 s for it, and a SELECT that arrived meanwhile waited behind the DROP INDEX. With DROP INDEX CONCURRENTLY in the same situation, SELECT count(*), a lookup by primary key and an INSERT all ran while it waited. While it waits, the index is already marked invalid (indisvalid = false), so new queries stop using it.

Does it rewrite the table?

No. Neither form touches the table’s data; the index’s file is deleted when the transaction commits. On our 3-million-row table a plain DROP INDEX took 1 ms once it had the lock.

Run it safely

Prefer DROP INDEX CONCURRENTLY:

DROP INDEX CONCURRENTLY orders_status_idx;

Its limits:

  • It can’t run in a transaction block: ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block. Turn off your migration tool’s transaction for this step.

  • One index per statement, and no CASCADE.

  • It can’t drop an index that backs a primary key or unique constraint:

    ERROR:  cannot drop index seo_mig.t_pkey because constraint t_pkey on table seo_mig.t requires it
HINT:  You can drop constraint t_pkey on table seo_mig.t instead.

    ALTER TABLE … DROP CONSTRAINT takes ACCESS EXCLUSIVE, so give it a lock_timeout too.

  • It can’t be used on indexes of partitioned tables (according to the PostgreSQL documentation).

It waits for every transaction using the table to finish, so a long one delays it; it doesn’t hold anyone else up meanwhile.

If you use a plain DROP INDEX (for example inside a transaction with other changes), set lock_timeout so it gives up instead of building a queue:

SET lock_timeout = '2s';
DROP INDEX orders_status_idx;

On ERROR: canceling statement due to lock timeout, nothing changed: retry after a pause. See lock timeout.

Before dropping, check the index isn’t needed. Indexes that are never scanned show idx_scan = 0 (counts since statistics were last reset, on this server only, so check replicas too):

SELECT indexrelid::regclass AS index, idx_scan
FROM pg_stat_user_indexes
WHERE relid = 'orders'::regclass
ORDER BY idx_scan;

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac). Lock and timing on the 3-million-row table, in a rolled-back transaction:

CREATE INDEX big_n_idx ON seo_mig.big (n);

BEGIN;
DROP INDEX seo_mig.big_n_idx;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
ROLLBACK;

(The dropped index no longer appears in pg_class inside the transaction, so only the table’s lock shows in this query.)

For the concurrent drop, session A ran BEGIN; SELECT a FROM seo_mig.t WHERE a = 5; SELECT pg_sleep(4); COMMIT;, session B ran DROP INDEX CONCURRENTLY seo_mig.t_a_idx half a second later, and session C read B’s locks and ran statements with lock_timeout = '1s':

VersionDROP INDEX lock on table (median time)DROP INDEX CONCURRENTLY locks while waitingIndex while waitingSELECT / INSERT meanwhilePlain DROP INDEX behind the same reader
14.24AccessExclusiveLock (1 ms)ShareUpdateExclusiveLock on table and indexindisvalid = falseRuns / runsWaited 2.5 s; a later SELECT waited
15.19AccessExclusiveLock (1 ms)ShareUpdateExclusiveLock on table and indexindisvalid = falseRuns / runsWaited 2.5 s; a later SELECT waited
16.14AccessExclusiveLock (1 ms)ShareUpdateExclusiveLock on table and indexindisvalid = falseRuns / runsWaited 2.5 s; a later SELECT waited
17.11AccessExclusiveLock (1 ms)ShareUpdateExclusiveLock on table and indexindisvalid = falseRuns / runsWaited 2.5 s; a later SELECT waited
18.6AccessExclusiveLock (1 ms)ShareUpdateExclusiveLock on table and indexindisvalid = falseRuns / runsWaited 2.5 s; a later SELECT waited

DROP INDEX CONCURRENTLY returned after 3.5 s on every version, as soon as session A committed. The transaction-block and constraint errors above were the same on all five.

In Inlet

The structure editor shows the DROP INDEX statement before it runs. If a drop is stuck waiting, the Activity monitor shows the session it’s waiting for and the queries queued behind it, and lets you cancel or terminate them.

Related

Sources