How to Drop Indexes in PostgreSQL

Indexes are one of those things that get added enthusiastically and removed reluctantly. I’ve inherited databases with dozens of indexes on a single table, half of them redundant, some of them never used even once according to PostgreSQL’s own statistics, all of them quietly costing write performance and disk space. Learning to drop indexes confidently — backed by actual data rather than guesswork — is just as important a skill as knowing how to create them in the first place.

Why Would You Drop an Index?

Before the syntax, it’s worth being clear on why you’d remove an index at all, since indexes generally speed up reads:

• Unused indexes — an index that’s never actually used by the query planner still has to be maintained on every INSERT, UPDATE, and DELETE, slowing down writes for no benefit.
• Redundant indexes — a duplicate or subset index (e.g., an index on (a) when you already have one on (a, b)) usually adds no value.
• Bloated indexes — indexes that have grown inefficient over time due to heavy update/delete activity, which you might drop and recreate rather than just drop.
• Schema changes — a column the index was built on is being dropped or restructured.
• Performance tuning — sometimes removing an index is part of a deliberate trade-off, favoring write throughput over a query pattern that’s no longer common.

Basic Syntax

DROP INDEX [IF EXISTS] index_name [CASCADE | RESTRICT];

• IF EXISTS — prevents an error if the index doesn’t exist, useful in scripts that need to be idempotent.
• CASCADE — drops objects that depend on the index (rare for plain indexes, more relevant for indexes backing constraints).
• RESTRICT — the default; refuses to drop if something depends on it.

Basic Example

DROP INDEX idx_employees_department;

Safer, idempotent version:

DROP INDEX IF EXISTS idx_employees_department;

Dropping Multiple Indexes

DROP INDEX idx_employees_department, idx_employees_hire_date;

Dropping an Index in a Specific Schema

If you’re not relying on search_path, qualify the index with its schema:

DROP INDEX reporting.idx_sales_region;

Dropping Indexes Without Blocking Writes: CONCURRENTLY

Here’s the detail that catches people off guard in production: a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, which blocks all reads and writes for the (usually brief, but not always) duration of the drop. On a busy production table, even a “quick” operation can cause a noticeable stall if it has to wait for existing queries to finish first, or blocks new ones from starting.

DROP INDEX CONCURRENTLY idx_employees_department;

CONCURRENTLY avoids taking that exclusive lock, allowing normal reads and writes to continue while the index is dropped. The trade-offs:

• It cannot be run inside a transaction block (no wrapping it in BEGIN/COMMIT with other statements).
• It’s slower than a plain drop.
• If it fails partway through, it can leave behind an “invalid” index that neither works nor is cleanly gone, requiring manual cleanup.

For any index drop on a live production table, I default to CONCURRENTLY unless I have a specific reason not to (like doing it during a maintenance window where blocking briefly genuinely doesn’t matter).

Dropping an Index That Backs a Constraint

If an index was automatically created to support a UNIQUE or PRIMARY KEY constraint, you generally can’t drop it directly with DROP INDEX — you need to drop the constraint instead, which removes the backing index along with it:

ALTER TABLE employees DROP CONSTRAINT employees_email_key;

Trying to DROP INDEX on a constraint-backed index directly will typically raise an error telling you to drop the constraint instead.

Finding Indexes to Drop

This is really the most important part of the whole topic — dropping the right indexes. A few queries I use regularly:

Finding Unused Indexes

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 means the index has never been used by the planner since statistics were last reset. Be cautious with this on a database that hasn’t been running long, or where statistics were recently reset (pg_stat_reset()) — a low-traffic monthly report query might just not have run yet.

Finding Duplicate Indexes

SELECT
    indrelid::regclass AS table_name,
    array_agg(indexrelid::regclass) AS duplicate_indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

This groups indexes by table and the exact set of columns they cover — any group with more than one index is a candidate for consolidation.

Checking Index Size

SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'employees'
ORDER BY pg_relation_size(indexrelid) DESC;

A Safe Workflow for Dropping Indexes in Production

I follow roughly this process whenever I’m cleaning up indexes on a live database:

1. Query pg_stat_user_indexes to identify unused or redundant indexes, over a representative time window (ideally covering weekly and monthly query patterns, not just a few days).
2. Cross-check against any known reporting jobs or batch processes that might run infrequently and wouldn’t show recent usage.
3. Rather than dropping outright, consider renaming the index first, or in newer PostgreSQL versions, marking it invisible to the planner if that feature is available, to catch any unexpected usage before committing to removal — otherwise, monitor closely after the drop for query performance regressions.
4. Drop with CONCURRENTLY during a period of lower traffic, even though it doesn’t block, just to minimize any incidental overhead.
5. Monitor query performance and pg_stat_statements after the drop to confirm nothing regressed.
6. Keep the CREATE INDEX statement handy so you can quickly recreate it if something unexpected shows up.

Common Use Cases

• Cleaning up unused indexes identified through pg_stat_user_indexes to reduce write overhead and disk usage.
• Removing redundant indexes that duplicate coverage already provided by a composite index.
• Dropping an index before a large bulk data load, then recreating it afterward (often faster than maintaining the index row-by-row during the load).
• Restructuring indexing strategy as query patterns evolve over the life of an application.
• Removing indexes tied to columns that are being dropped or renamed as part of a schema migration.

Troubleshooting Common Issues

“cannot drop index because constraint depends on it” — the index backs a UNIQUE or PRIMARY KEY constraint. Drop the constraint with ALTER TABLE ... DROP CONSTRAINT instead.

Drop hangs indefinitely — a plain (non-concurrent) DROP INDEX is waiting to acquire an exclusive lock, likely blocked by a long-running query or transaction on the same table. Check pg_stat_activity for blocking sessions, or switch to DROP INDEX CONCURRENTLY.

“DROP INDEX CONCURRENTLY cannot run inside a transaction block” — remove any surrounding BEGIN/COMMIT, or if you’re running this from a migration tool, check whether it wraps every statement in a transaction automatically and needs a specific flag to exclude this one.

Index drop left an invalid index behind — this can happen if a CONCURRENTLY operation is interrupted. Check for it with:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Drop the invalid index (a plain DROP INDEX works fine on an already-invalid index) and, if you still need it, recreate it with CREATE INDEX CONCURRENTLY.

Query performance regressed after dropping an index — this is exactly why keeping the original CREATE INDEX statement handy matters. Recreate it (ideally with CONCURRENTLY) and revisit your usage analysis — the index may have been supporting a query pattern your monitoring window missed.

Best Practices

• Never drop an index based on a gut feeling — check pg_stat_user_indexes first, and consider a monitoring window long enough to capture infrequent but important queries.
• Use DROP INDEX CONCURRENTLY on production tables to avoid blocking reads and writes.
• Keep a record (a migration file, a script, a note) of the exact CREATE INDEX statement before dropping anything, so recreating it is trivial if needed.
• Watch for indexes backing constraints — you’ll need to drop the constraint, not the index directly.
• After dropping, monitor query performance for a reasonable period rather than assuming everything is fine because nothing broke immediately.
• Periodically audit indexes as part of routine database maintenance, not just when something’s already going wrong — index bloat and redundancy tend to accumulate quietly over time.

Wrapping Up

Dropping an index is a simple command, but doing it responsibly means backing the decision with real usage data, understanding the locking implications on a live table, and being ready to reverse the decision quickly if it turns out you were wrong. Get comfortable with pg_stat_user_indexes, default to CONCURRENTLY in production, and treat index cleanup as an ongoing part of database maintenance rather than a one-time cleanup project.