DROP INDEX acquires an ACCESS EXCLUSIVE lock on the parent table. On a busy table that is an outage, and CONCURRENTLY is the version you almost always want.
Dropping an index is one of those operations that feels safe because it removes something rather than adding it. The index is not the data, after all — losing it costs performance, not correctness.
That reasoning is right about the consequences and wrong about the risk. The default form of the statement blocks every read and write against the table while it runs, and on a table that matters, that is the part worth planning around.
Table of contents
- Syntax and the options that matter
- Why CONCURRENTLY is the default you should adopt
- Finding the indexes actually worth dropping
- The safer drop: hide it before you delete it
- Cleaning up after a failed concurrent operation
- How this fits the rest of the stack
- FAQ
Syntax and the options that matter
The full form has four modifiers, and three of them earn their place in real usage.
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]-- Simplest form: exclusive lock, fast, blocks everything briefly
DROP INDEX users_email_idx;
-- Non-blocking: the one for production tables
DROP INDEX CONCURRENTLY users_email_idx;
-- Idempotent: notice instead of error if it is already gone
DROP INDEX IF EXISTS users_email_idx;
-- Several at once (not permitted with CONCURRENTLY)
DROP INDEX users_email_idx, users_created_at_idx;RESTRICT is the default and refuses to drop if anything depends on the index. CASCADE drops the dependents too, which for an index usually means a constraint — and that is a much bigger change than you asked for. Reach for CASCADE only when you know exactly what depends on it.
You must own the index to drop it, and the operation cannot be undone. There is no recycle bin; rebuilding means paying the full index build cost again.
Why CONCURRENTLY is the default you should adopt
The plain form takes an ACCESS EXCLUSIVE lock on the table the index belongs to. Reads block. Writes block. The drop itself is quick, so the window is short — but short is not zero, and the lock queue makes it worse than it looks.
The queuing behaviour is the real danger. If a long-running query already holds a lock on the table, your DROP waits, and every query arriving after yours queues behind it. A drop that would have taken ten milliseconds now blocks the table for as long as that slow query runs.
-- Non-blocking, but with rules attached
DROP INDEX CONCURRENTLY users_email_idx;The concurrent form takes a weaker SHARE UPDATE EXCLUSIVE lock, letting reads and writes continue. It works in two phases: mark the index invalid so no new query plans use it, wait for every transaction that might still reference it to finish, then remove it.
- It cannot run inside a transaction block, which rules out most ORM migration wrappers
- It cannot drop multiple indexes in one statement
- It takes longer in wall-clock time because it waits for open transactions to drain
- A long-running transaction elsewhere will stall it indefinitely
That last point is worth internalising. DROP INDEX CONCURRENTLY waits for every transaction that started before it to finish. One forgotten session sitting idle in a transaction will hold it up forever, and the fix is to find and close that session rather than to wait longer.
Finding the indexes actually worth dropping
Before dropping anything, know which indexes earn their keep. PostgreSQL tracks scan counts per index, and the numbers are usually startling on a schema that has grown organically.
SELECT
s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan AS scans,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisunique
AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;Excluding unique and primary indexes is essential — those back constraints, and dropping them changes what the database will accept, not just how fast it answers. The query above deliberately never shows them.
Two caveats on idx_scan. The counters reset when statistics are reset or the server is rebuilt from a base backup, so a zero on a freshly restored replica means nothing. And an index used only by a quarterly report will look unused for eleven weeks out of twelve. Check pg_stat_reset_time and think about your reporting calendar before you act.
Also worth hunting: duplicate indexes. An index on (a) is redundant when an index on (a, b) exists, because a leading-column prefix serves the same lookups. These accumulate quietly whenever two people add indexes for two features.
The safer drop: hide it before you delete it
Dropping an index is irreversible and rebuilding a large one can take hours. There is a middle step that lets you test the consequences without committing.
-- Make the planner ignore the index without removing it
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'users_email_idx'::regclass;
-- Watch query performance, then either restore it...
UPDATE pg_index SET indisvalid = true
WHERE indexrelid = 'users_email_idx'::regclass;
-- ...or drop it for real once you are confident
DROP INDEX CONCURRENTLY users_email_idx;This is a direct catalogue update, which is not something to do casually — but it is the established trick for this specific case. The index stays on disk and keeps being maintained on writes; the planner simply stops choosing it. Reversing takes a second, where rebuilding might take an hour.
Run it in a transaction you can roll back, and remember that the index is still costing you write overhead while hidden. This is a test, not a destination.
Cleaning up after a failed concurrent operation
If DROP INDEX CONCURRENTLY is interrupted, it can leave the index marked invalid but still present. The same applies to a failed CREATE INDEX CONCURRENTLY. These are dead weight: the planner ignores them, but writes still maintain them.
-- Find invalid indexes left behind by interrupted operations
SELECT
i.indexrelid::regclass AS index_name,
i.indrelid::regclass AS table_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_index i
WHERE NOT i.indisvalid;Clean these up with a plain DROP INDEX — an invalid index is not serving queries, so there is nothing to protect and no reason to use the concurrent form.
Make this query part of your routine database checks. Invalid indexes are invisible in normal operation and cost write throughput on every insert and update against the table.
How this fits the rest of the stack
Index maintenance is one of those tasks that only gets attention when queries slow down, which is usually the worst time to be experimenting. Having a database where the size, the connection count, and the backup schedule are visible in the same place as the deploy makes it a routine job instead of an investigation. RunxBuild managed Postgres instances are provisioned alongside the services that use them, and the RunxBuild hosting calculator shows the database and its storage as their own line items so index bloat shows up as a number you can see.
Useful related references:
- LIKE in PostgreSQL: ILIKE, Wildcards, and Why It Ignores Your Index
- Postgres-XL: What the Distributed Fork Was, and Why It Lost to Citus
- Postgres Switch Database: \connect, USE, and psql Defaults
- Databases on RunxBuild
FAQ
Does DROP INDEX lock the table in PostgreSQL?
Yes. The default form takes an ACCESS EXCLUSIVE lock, blocking reads and writes for the duration. Use DROP INDEX CONCURRENTLY to take a weaker lock that permits normal traffic to continue.
Why can’t DROP INDEX CONCURRENTLY run in a transaction?
It works in phases and must commit between them so other sessions observe the intermediate state. That is incompatible with running inside a transaction block, which is why ORM migration tools that wrap everything in BEGIN often fail on it.
How do I find unused indexes?
Query pg_stat_user_indexes for entries where idx_scan is zero, excluding unique and primary indexes. Check when statistics were last reset first, since a recent reset makes every index look unused.
Can I undo a dropped index?
No. The only recovery is recreating it, which costs a full build. For large indexes, test by marking indisvalid = false so the planner ignores it, then drop it once you have confirmed nothing regressed.
What is an invalid index?
One left behind by an interrupted CREATE or DROP CONCURRENTLY. It is ignored by the query planner but still maintained on every write, so it costs throughput and gives nothing back. Find them via pg_index where indisvalid is false and drop them normally.