How do you drop an index in PostgreSQL?
Run DROP INDEX CONCURRENTLY IF EXISTS index_name; outside a transaction block. Plain DROP INDEX takes an ACCESS EXCLUSIVE lock and blocks every query on the table until it finishes. The command is the easy part: the hard part is proving the index is unused on the primary and on every replica first.
Key facts
| Fact | Value | |---|---| | Lock taken by plain DROP INDEX | ACCESS EXCLUSIVE on the parent table — conflicts with every other lock mode (docs) | | Lock taken by DROP INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE; reads and writes continue (docs) | | CONCURRENTLY restrictions | Cannot run inside a transaction block; cannot be combined with CASCADE; only one index per statement (docs) | | Index backing a constraint | Cannot be dropped directly — drop the constraint with ALTER TABLE ... DROP CONSTRAINT (docs) | | Usage counter to check | pg_stat_user_indexes.idx_scan, per node, never aggregated across a cluster (docs) | | Across 31 monitored instances | 67.7% fail the duplicate-index check; 58.1% have at least one index with zero recorded scans |
Why this happens
Every index is a second copy of part of your data that PostgreSQL has to keep correct. An INSERT writes a row and then writes an entry into each index on the table. An UPDATE does the same unless the update qualifies as heap-only tuple (HOT) — which requires that no indexed column changed and that there is free space on the same page (storage docs). When HOT does not apply, the write cost scales with the number of indexes. Indexes also bloat, lengthen vacuum runs because vacuum has to scan every index on the table (routine vacuuming), and add candidate paths the planner has to cost on every query.
So dropping an index that nobody uses is real work saved. The trouble is the method most people use to find those indexes.
The standard recipe is to sort pg_stat_user_indexes by idx_scan and drop the zeroes. That statistic is maintained by the cumulative statistics system on the instance you are connected to (monitoring docs). It is not replicated and it is not aggregated. A read replica keeps its own counters. If your reporting traffic is routed to a standby — which is the usual reason for having one — then the indexes those reports depend on can read as zero scans on the primary forever. The single-node query will confidently tell you to drop them, and the first person to notice is whoever runs the month-end report.
There are four more reasons a zero-scan index can still be load-bearing:
- It enforces uniqueness. A unique index has a job even if nothing ever reads it as an access path. See unique constraints.
- It is only used periodically. A quarterly close, an annual audit export, a nightly batch that ran last night but not during your seven-day sample.
- The statistics were reset.
pg_stat_reset()zeroes the counters. Checkstats_resetinpg_stat_databasebefore you believe a number. - It supports a foreign key. PostgreSQL does not create an index on the referencing side of a foreign key for you, and without one, every delete or key update on the parent table has to scan the child (constraint docs). That scan is not counted as an
idx_scanon the index you are about to drop, but it will get much slower without it.
How to detect it
Start with the candidates on the node you are connected to, largest first:
SELECT s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
i.indisunique,
i.indisprimary
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;
Read it as a shortlist, not a drop list. Anything with indisunique or indisprimary true is out. Then find out how old the numbers are:
SELECT datname, stats_reset, now() - stats_reset AS stats_age
FROM pg_stat_database
WHERE datname = current_database();
If stats_age is shorter than your longest business cycle, a zero means nothing yet.
Next, the duplicates — two indexes with identical column lists, or one whose columns are a leftmost prefix of another's. The prefix case is the one people miss: an index on (customer_id) is fully covered by an index on (customer_id, created_at), because a B-tree can be scanned on any leading subset of its columns (index docs).
WITH idx AS (
SELECT i.indexrelid,
i.indrelid,
c.relname AS index_name,
t.relname AS table_name,
i.indkey::int2[] AS cols,
i.indisunique,
i.indpred IS NOT NULL AS is_partial,
pg_relation_size(i.indexrelid) AS size_bytes
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'pg_toast')
)
SELECT a.table_name,
a.index_name AS redundant_index,
pg_size_pretty(a.size_bytes) AS redundant_size,
b.index_name AS covered_by
FROM idx a
JOIN idx b
ON a.indrelid = b.indrelid
AND a.indexrelid <> b.indexrelid
AND b.cols[1:array_length(a.cols, 1)] = a.cols
WHERE NOT a.indisunique
AND NOT a.is_partial
AND NOT b.is_partial
ORDER BY a.size_bytes DESC;
The partial-index exclusions matter: a partial index with a WHERE clause is not redundant just because its columns are a prefix of a larger index. Neither is a unique index, which is doing constraint work the wider index is not.
Then repeat the first query on every replica, and add the scan counts up. That is the step the standard recipe leaves out, and the one that turns a correct-looking drop into an incident. If you want the fuller picture of what usage data is worth reading, see index usage optimization.
How MyDBA shows this

The Index Advisor's Unused Indexes card counts an index as unused only after summing idx_scan across the primary and every connected replica in the cluster, so a report that runs on a standby keeps its index off the drop list. The card's subtitle splits the count into what is actually droppable versus how many are FK-supporting or replica-only, and a separate Replica-Only Usage card ("Used on replicas, not primary") lists the ones a single-node check would have told you to remove. Expanding a recommendation shows the per-member breakdown, each instance tagged Primary or Replica with its own scan count.

Across 31 monitored instances, 67.7% fail the duplicate-index check and 58.1% carry at least one index with no recorded scans — enough that it is worth looking at your own numbers. You can see the same view on your database with a free PostgreSQL health check.
How to fix it
1. Record the exact definition first. This is your rollback plan, because PostgreSQL has no ALTER INDEX ... INVISIBLE to hide an index from the planner while you watch.
``sql SELECT indexdef FROM pg_indexes WHERE schemaname = 'public' AND indexname = 'idx_orders_customer_id'; ``
Save the output. If the drop turns out to be wrong, you rebuild it with CREATE INDEX CONCURRENTLY and no downtime — see creating indexes concurrently.
2. If the index backs a constraint, drop the constraint instead. DROP INDEX refuses outright:
``sql ALTER TABLE orders DROP CONSTRAINT orders_email_key; ``
3. Drop it concurrently.
``sql DROP INDEX CONCURRENTLY IF EXISTS public.idx_orders_customer_id; ``
IF EXISTS turns a missing index into a notice instead of an error, which makes the statement safe to re-run in a deploy script. CONCURRENTLY waits for existing transactions that can see the index to finish, then removes it while reads and writes continue. It cannot run inside a transaction block, so no BEGIN, and psql scripts wrapped in a single transaction will fail (docs).
4. If you need a rollback window, use plain DROP INDEX inside a transaction — during a maintenance window only.
``sql BEGIN; DROP INDEX public.idx_orders_customer_id; -- run your critical queries, check the plans ROLLBACK; -- or COMMIT ``
This is the closest PostgreSQL gets to making an index invisible, and the price is honest: the ACCESS EXCLUSIVE lock is held for the whole transaction, so every query on that table queues behind you until you decide.
5. Understand CASCADE before you type it. RESTRICT is the default and refuses if anything depends on the index. CASCADE drops the dependent objects too, which for an index normally means a constraint — so CASCADE can silently remove a uniqueness guarantee. It also cannot be combined with CONCURRENTLY.
6. Watch the table. Check query latency and plans for the queries you expected to be affected before moving to the next index. Drop one at a time.
How to prevent it
- Measure over a full business cycle. A seven-day sample misses monthly and quarterly work. Note
stats_resetat the start of the window and check it has not moved at the end. - Query every replica, every time. Treat a per-node
idx_scanreading as one vote, not a verdict. - Check for FK support before dropping. If the index's leading columns match a foreign key's referencing columns, leave it.
- Stop creating the duplicates. Before adding an index, check whether an existing one already has those columns as a leading prefix. Adding
(customer_id)when(customer_id, created_at)exists costs writes and returns nothing. - Re-check after a rebuild.
REINDEXresets nothing about usage stats, but a drop-and-recreate does — a freshly created index starts at zero scans and will look unused for as long as your window is. See when to use REINDEX. - Keep the
CREATE INDEXstatements somewhere durable, ideally in version control alongside your schema, so any drop is reversible without archaeology.
FAQ
Can I run DROP INDEX CONCURRENTLY inside a transaction?
No. Like CREATE INDEX CONCURRENTLY, it cannot run in a transaction block, and it cannot be combined with CASCADE or drop more than one index per statement (docs). Issue it as a standalone statement with autocommit on.
What happens if DROP INDEX CONCURRENTLY fails partway through?
The index can be left in an invalid state, still present in pg_index with indisvalid = false but no longer usable by queries. Re-run the DROP INDEX CONCURRENTLY to clear it. Check for leftovers with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;.
Why can't I drop this index — it says it's owned by a constraint?
Because it is. A primary key or unique constraint owns its index, and PostgreSQL will not let you remove the implementation while the constraint stands. Drop the constraint with ALTER TABLE ... DROP CONSTRAINT, which removes the index with it.
Does idx_scan = 0 on the primary mean the index is unused?
Only on that node. pg_stat_user_indexes is per-instance and is not replicated, so an index scanned heavily on a read replica reads as zero on the primary. Sum the counters across the primary and every standby before deciding, and confirm stats_reset in pg_stat_database covers a long enough window.
How long should I watch before dropping?
At least one full business cycle — for most systems that means a month, so a quarterly or annual job is the exception you plan for separately. If the index is large enough that keeping it another month is genuinely costing you, drop it in a rollback-able transaction during a window rather than shortening the measurement.
Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.