Skip to main content

PG.L2.DROP_INDEX_NOT_CONCURRENT — Dropping an index locks the table, not the index

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

Dropping an index feels like cleanup, which is why this one surprises people. DROP INDEX takes an ACCESS EXCLUSIVE lock on the table the index belongs to — not on the index — and holds it until the drop completes. That lock excludes everything, readers included.

The drop itself is usually quick. The wait to acquire the lock is not: it queues behind every transaction currently touching the table, and everything arriving after it queues behind the queue. A long-running report can turn a one-millisecond drop into a minute of stalled traffic.

Flagged​

DB::statement('DROP INDEX idx_orders_customer_id');

Preferred​

DB::statement('DROP INDEX CONCURRENTLY idx_orders_customer_id');

Same caveat as the build: CONCURRENTLY cannot run inside a transaction, so the migration needs public bool $withinTransaction = false;. It also drops only one index per statement.

Set a lock timeout regardless​

CONCURRENTLY removes the exclusive lock; it does not remove the queue that forms while a statement waits for whatever lock it does need. PG.L3.MISSING_LOCK_TIMEOUT exists for that half, and the two rules are meant to be read together: one bounds what you hold, the other bounds what you wait for.

Before you drop it, check it is unused​

An index that is never read costs writes and disk; an index that is read once an hour by a report nobody remembers costs a lot more when it is gone. The audit suite's PG.L7.INDEX_UNUSED reads pg_stat_user_indexes against a live database and is deliberately conservative about it — a linter reading a migration cannot answer this at all.

Sources​

  • PostgreSQL 18 — DROP INDEX — a plain drop takes an ACCESS EXCLUSIVE lock on the index's table for the duration; CONCURRENTLY avoids it, at the cost of running outside a transaction and dropping a single index per statement

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • concurrently — Build the index without taking the write lock the ordinary form takes.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.