Indexes Maintenance DROP INDEX · IF EXISTS · CONCURRENTLY

DROP INDEX

Remove an index permanently. Use IF EXISTS for safe scripts and CONCURRENTLY in production. Indexes attached to constraints need the constraint dropped instead.

What DROP INDEX does

DROP INDEX permanently removes an index from the database. The table itself is untouched — only the side data structure goes away. Subsequent queries that would have used the index now fall back to scanning the table directly.

Reasons to drop:

The index is unused (check pg_stat_user_indexes).
It's costing more in write overhead than it saves on reads.
You're consolidating several indexes into one multicolumn index.
You're rebuilding it differently — drop, create new shape.

Syntax

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

Option	Meaning
CONCURRENTLY	Drop without blocking concurrent queries.
IF EXISTS	Don't error if the index isn't there. Useful for idempotent scripts.
RESTRICT (default)	Refuse if other objects depend on the index.
CASCADE	Drop dependent objects too. Use carefully.

Example 1 — drop a regular index

SQL

CREATE TABLE customers2 (
    customer_id  INT,
    first_name   TEXT
);

CREATE INDEX idx_first_name_customer_id
    ON customers2 (first_name, customer_id);

-- Remove it
DROP INDEX idx_first_name_customer_id;

Use IF EXISTS when the index might not be there — handy for migration scripts that may run twice:

SQL

DROP INDEX IF EXISTS idx_first_name_customer_id;

Example 2 — DROP CONCURRENTLY

Like CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY, the dropping form lets the database remove an index without taking heavy locks. Read and write traffic continues uninterrupted.

SQL

DROP INDEX CONCURRENTLY idx_first_name_customer_id;

⚠ CONCURRENTLY can't run inside a transaction block. Run it standalone — not inside BEGIN ... COMMIT. Migration tools usually handle this for you, but it's worth knowing.

Indexes you can't drop directly

Some indexes belong to constraints — UNIQUE constraints and PRIMARY KEYs both create indexes automatically, and you can't drop the index without first dropping the constraint.

SQL

ALTER TABLE customers2
    ADD CONSTRAINT idx_first_name_id_unique UNIQUE (first_name, customer_id);

-- This fails — the index belongs to the constraint
DROP INDEX idx_first_name_id_unique;
-- ERROR: cannot drop index idx_first_name_id_unique
--        because constraint idx_first_name_id_unique on table customers2 requires it
-- HINT: You can drop constraint idx_first_name_id_unique on table customers2
--       instead.

-- Drop the constraint instead — index goes with it
ALTER TABLE customers2
    DROP CONSTRAINT idx_first_name_id_unique;

The same applies to the index that backs a primary key — drop the constraint, not the index.

CASCADE vs RESTRICT

CASCADE drops anything depending on the index too. For most index dropping, that's overkill — the dependents are usually constraints (handled differently as shown above) or other indexes (none ever depend on each other). Stick with the default RESTRICT behaviour and address dependencies explicitly.

Find indexes worth dropping

SQL

-- Indexes that have never been used since stats reset
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY pg_relation_size(indexrelid) DESC;

The result is a list of indexes that took up space and slowed down writes for nothing. If you're confident the workload is representative, those are great DROP candidates.

Recap

DROP INDEX removes an index permanently — the table is untouched.
Use IF EXISTS to make scripts safe to re-run.
CONCURRENTLY lets the drop happen without blocking concurrent traffic (can't run inside a transaction).
Indexes backing UNIQUE / PRIMARY KEY constraints can only be dropped by dropping the constraint.
Hunt for unused indexes via pg_stat_user_indexes.idx_scan = 0.