Dropping Indexes Online Without Blocking Queries

You have a suspected-dead index on a busy orders table — a leftover from a feature that shipped and got cut, or a duplicate someone added without checking. The migration is a single line, DROP INDEX idx_orders_legacy_status, so you run it during business hours expecting it to blink and finish. Instead the deploy hangs, your APM lights up with a wall of slow queries, and the connection pool saturates within seconds. The drop is cheap — it is metadata work, not a data rewrite — but on PostgreSQL a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, and that lock cannot be granted until every in-flight query on the table finishes, while every query that arrives after it queues behind the drop. One trivial statement has frozen the whole table. This page covers the two things that make an index drop safe: proving the index is genuinely unused before you touch it — using pg_stat_user_indexes on PostgreSQL — and then removing it without blocking, via DROP INDEX CONCURRENTLY on PostgreSQL and InnoDB’s online DROP INDEX on MySQL.

Dropping an index is the contract half of the expand-and-contract lifecycle applied to indexes: it is only safe once you have proven nothing depends on the thing you are removing, exactly as the Expand and Contract Methodology requires before you drop any legacy structure. It is the mirror image of building indexes with CREATE INDEX CONCURRENTLY, and both belong to the same Online Index Management discipline: change structure additively, verify before you trust, and remove nothing until you have proven it is safe to remove.

Symptom / Error Signatures

There are two distinct failure signatures, and they need opposite responses. The first is the lock pile-up above: the drop itself is blocked. On PostgreSQL a plain DROP INDEX waits on the ACCESS EXCLUSIVE lock, and if you have set a lock_timeout it surfaces as:

ERROR:  canceling statement due to lock timeout

Without a lock budget it simply hangs, and the queue behind it shows up in pg_stat_activity as a fleet of sessions in lock wait state, all wait_event_type = Lock:

wait_event_type | wait_event      | state  | query
----------------+-----------------+--------+---------------------------
 Lock           | relation        | active | SELECT * FROM orders WHERE ...
 Lock           | relation        | active | UPDATE orders SET ...

On MySQL the same blocking drop times out on the metadata lock:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The second, more dangerous signature appears after a drop that succeeded: a query that was millisecond-fast the day before is now scanning the whole table. In PostgreSQL EXPLAIN the tell is a Seq Scan where an Index Scan used to be; in MySQL EXPLAIN it is type: ALL with a large rows estimate. That is the signature of dropping an index some query plan silently relied on — the lock was never the problem, the removal was. This page’s whole purpose is to prevent both: drop without blocking, and only drop what is provably unused.

Root Cause Analysis

The blocking behaviour comes down to lock strength. Removing an index is a catalog change: the row in pg_index and the physical index files go away, but the table’s data pages are untouched. That does not make it lock-free. To rewrite the table’s definition safely, a plain PostgreSQL DROP INDEX acquires an ACCESS EXCLUSIVE lock — the strongest lock there is, conflicting with every other lock including the ACCESS SHARE that a bare SELECT takes. It cannot be granted while any query holds a lock on the table, and while it waits in the queue it blocks everything behind it too, so a single reader running a long query can turn a metadata drop into a full-table stall. DROP INDEX CONCURRENTLY sidesteps this by taking only a SHARE UPDATE EXCLUSIVE lock, which does not conflict with reads or writes; the trade-off is that, like its CREATE counterpart, it cannot run inside a transaction block and it does more bookkeeping under the hood.

MySQL and PostgreSQL diverge enough here that the correct statement depends on the engine:

The unused-detection question is where most of the real risk lives, because a drop that never blocks is still a disaster if it removes an index the planner needed. PostgreSQL tracks per-index usage in the cumulative statistics view pg_stat_user_indexes: its idx_scan column counts how many times the planner has chosen that index since statistics were last reset. An index with idx_scan = 0 over a representative window — one that has seen your full workload including monthly reports and rare admin queries — is a strong candidate for removal. But idx_scan = 0 on a counter reset yesterday proves nothing, and an index backing a UNIQUE or foreign-key constraint may show zero scans yet still be structurally required. MySQL exposes the equivalent signal through the sys.schema_unused_indexes view, which surfaces secondary indexes that the server has not used since the last restart, drawn from performance_schema. Both are necessary-but-not-sufficient evidence: they tell you an index was not read, not that no plan or constraint needs it.

Immediate Mitigation

If a blocking DROP INDEX is hung right now and taking the table with it, the first move is to cancel it, then re-run it the safe way. Run these against the primary as a role that owns the index (or a superuser); on PostgreSQL the concurrent drop must run outside a transaction block.

1. Cancel the blocking drop to release the queue. Find the drop’s backend and cancel it so the pile-up behind the ACCESS EXCLUSIVE lock drains immediately.

-- PostgreSQL · run against the PRIMARY as a monitoring/owner role · pg_cancel_backend needs matching role or superuser.
-- Context: cancels the hung DROP so queued queries proceed; find <pid> from the query text below.
SELECT pid, state, now() - query_start AS running_for, query
FROM pg_stat_activity
WHERE query ILIKE 'drop index%';
-- Then, with the pid from above:
SELECT pg_cancel_backend(12345);

Verify before proceeding: the blocked-session count in pg_stat_activity returns to normal and table latency recovers. Do not re-attempt the plain DROP INDEX — the next step replaces it with the non-blocking form.

2. Prove the index is actually unused before you remove it. Read its scan count from pg_stat_user_indexes, and confirm how long statistics have been accumulating so a zero is meaningful rather than freshly reset.

-- PostgreSQL · run against the PRIMARY (and each replica separately) as a read role · read-only, safe anytime.
-- Context: idx_scan = 0 is only trustworthy if stats_reset is old enough to cover your full workload cycle.
SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()) AS stats_since
FROM pg_stat_user_indexes s
WHERE s.indexrelname = 'idx_orders_legacy_status';

Verify before proceeding: idx_scan is 0 (or negligibly low) across the primary and every read replica — replicas keep their own statistics, so an index unused on the primary may be serving replica read traffic — and stats_since predates your longest reporting cycle. Then confirm the index does not back a constraint (a UNIQUE or foreign-key index cannot simply be dropped) before continuing.

3. Drop the index without blocking. On PostgreSQL use the concurrent form, outside any transaction; on MySQL express the drop as online DDL.

-- PostgreSQL · run as the index owner · MUST run OUTSIDE a transaction block (no BEGIN/COMMIT).
-- Context: SHARE UPDATE EXCLUSIVE lock only — reads and writes continue throughout the drop.
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_legacy_status;

-- MySQL 8.0 (InnoDB) · run as a user with ALTER on the table.
-- Context: LOCK=NONE keeps reads and writes flowing; DDL auto-commits, no explicit transaction.
SET SESSION lock_wait_timeout = 2;   -- bound the brief metadata lock at start/end
ALTER TABLE orders
  DROP INDEX idx_orders_legacy_status,
  ALGORITHM=INPLACE, LOCK=NONE;

Verify before proceeding: the statement returns without error and the index no longer appears in \d orders (PostgreSQL) or SHOW INDEX FROM orders (MySQL). On MySQL, if the drop reports ALGORITHM=INPLACE is not supported, do not fall back to ALGORITHM=COPY on a live table — that rewrites the whole table under a blocking lock.

Permanent Fix / Long-Term Pattern

The durable pattern turns “drop an index and hope” into a small, evidence-driven contract step. First, standardise on the non-blocking form everywhere: make DROP INDEX CONCURRENTLY (PostgreSQL) and DROP INDEX ... ALGORITHM=INPLACE, LOCK=NONE (MySQL) the only index-drop statements your migrations are allowed to emit, and enforce it in review so a bare DROP INDEX never reaches production. Because the concurrent form must run outside a transaction, mark the migration accordingly — executeInTransaction=false in Flyway, disable_ddl_transaction! in Rails — the same requirement that governs the concurrent build. Second, gate the drop on measured non-use rather than on someone’s memory of whether the index still matters. Reset the relevant statistics at the start of an observation window, let a full workload cycle run (including weekly and monthly reports), then trust the counter:

-- PostgreSQL · run once at the START of an observation window · needs pg_stat_reset privilege.
-- Context: resets cumulative index-scan counters so idx_scan measures only the window that follows.
SELECT pg_stat_reset();
-- ... let a full workload cycle elapse (days to a month), then read pg_stat_user_indexes.idx_scan.

Third, make the drop safely re-runnable and reversible. Guard it with IF EXISTS so a retried migration does not error on an already-dropped index — a direct application of idempotent script design to index DDL — and record the index’s exact definition first so a mistaken drop can be rebuilt. The reversal is not free: recreating a dropped index is a full concurrent build, so keep the CREATE INDEX CONCURRENTLY statement in the down-migration.

-- PostgreSQL · reversal / down-migration · MUST run OUTSIDE a transaction block.
-- Context: rebuilding a dropped index is a full online build, not a metadata flip — budget for it.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_legacy_status
  ON orders (legacy_status);

For the deeper mechanics — how the concurrent build’s two-pass design mirrors the drop, tuning the lock budget, and watching progress — the parent Online Index Management guide and its companion on building indexes with CREATE INDEX CONCURRENTLY carry the full context. Treating index removal as a contract step gated on idx_scan evidence, rather than a one-line cleanup, is what keeps a routine drop from becoming an incident.

Verification Checklist

• The idx_scan count in pg_stat_user_indexes is zero (or negligible) on the primary AND every read replica over a full workload cycle.
• stats_reset predates your longest reporting cycle, so a zero count is trustworthy rather than freshly reset.
• The index does not back a UNIQUE or foreign-key constraint (those cannot simply be dropped).
• The drop used DROP INDEX CONCURRENTLY (PostgreSQL) or ALGORITHM=INPLACE, LOCK=NONE (MySQL), never a bare blocking DROP INDEX.
• pg_stat_activity showed no Lock-wait pile-up during the drop, and table latency held steady throughout.
• EXPLAIN on the table’s hot queries still shows Index Scan / ref on the remaining indexes — no query regressed to a Seq Scan / type: ALL.
• The down-migration keeps a CREATE INDEX CONCURRENTLY statement so the drop is reversible.

Up one level: Online Index Management.

Related

• Online Index Management
• Building Indexes with CREATE INDEX CONCURRENTLY
• Cleaning Up Invalid Indexes After a Failed Build

Frequently Asked Questions

Why does a simple DROP INDEX block every query on the table? Because on PostgreSQL a plain DROP INDEX acquires an ACCESS EXCLUSIVE lock on the parent table — the strongest lock there is, conflicting even with the ACCESS SHARE lock a bare SELECT takes. The lock cannot be granted while any query holds a lock on the table, and while it waits in the queue it blocks every query that arrives after it, so one long-running reader can turn a metadata-only drop into a full-table stall. DROP INDEX CONCURRENTLY avoids this by taking only a SHARE UPDATE EXCLUSIVE lock that does not conflict with reads or writes; the cost is that it cannot run inside a transaction block. On MySQL the blocking form waits on a metadata lock and surfaces as ERROR 1205 ... Lock wait timeout exceeded; the online form with ALGORITHM=INPLACE, LOCK=NONE keeps traffic flowing.

How do I know for certain an index is safe to drop? Read its usage from pg_stat_user_indexes on PostgreSQL (or sys.schema_unused_indexes on MySQL) and look for idx_scan = 0, but treat that as necessary, not sufficient. A zero count only means something if statistics have been accumulating long enough to cover your full workload — including weekly and monthly reports and rare admin queries — so check stats_reset and, ideally, run pg_stat_reset() to start a clean observation window. Check every read replica separately, because replicas keep their own statistics and an index idle on the primary may be serving replica reads. Finally, confirm the index does not back a UNIQUE or foreign-key constraint; those show few scans yet are structurally required and cannot simply be dropped.

Is dropping an index reversible if I get it wrong? Yes, but the reversal is a full index build, not a cheap flip. Removing an index discards its data structure entirely, so recreating it means re-scanning the table with CREATE INDEX CONCURRENTLY, which on a large table can run for many minutes — keep that statement in your down-migration and budget for it. Record the index’s exact definition with pg_get_indexdef before dropping so the rebuild is faithful. The bigger risk than reversibility is the interval before you notice: dropping an index a query plan silently relied on turns a fast index scan into a sequential scan the instant it disappears, so prove the index is unused first rather than counting on being able to put it back.