Monitoring

PostgreSQL Unused Indexes: How to Find Them and Drop Them Safely

Every index costs disk, cache and write time, even when no query touches it. Here is how to find unused, duplicate and invalid indexes in PostgreSQL, what the statistics do not tell you, and how to drop them without surprises.

· 14 min read · AG Data Team

PostgreSQL unused indexes are one of the cheapest performance wins in a mature database. Nobody remembers why orders_status_created_idx exists, but every insert still writes to it, every vacuum still scans it and it still takes space in shared buffers and in every backup. On tables with ten or fifteen indexes, removing the dead ones often makes writes noticeably faster and frees tens or hundreds of gigabytes.

The catch is that "unused" is a statement about statistics, and statistics can lie. Below: the query to find candidates, the indexes you must never drop even if they show zero scans, duplicates and invalid indexes, and a drop procedure that is safe on a large production system.

Why an unused index is not free

You can see how much HOT you get today with this query. A low percentage on a hot table with many indexes is a strong hint that an index review will pay off:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 20;

Find candidates with pg_stat_user_indexes

PostgreSQL counts index usage in the pg_stat_user_indexes view. idx_scan is the number of index scans started on the index, and since PostgreSQL 16 last_idx_scan shows when it was last used. The query below lists indexes with zero scans, largest first, and filters out indexes that enforce something:

SELECT s.schemaname,
       s.relname       AS table_name,
       s.indexrelname  AS index_name,
       s.idx_scan,
       s.last_idx_scan,                       -- PostgreSQL 16+
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_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                     -- includes primary keys
  AND NOT i.indisexclusion
  AND NOT i.indisreplident
  AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

On PostgreSQL 15 and older, remove the last_idx_scan line. The filters rely on the pg_index catalog: indisunique also covers primary keys, indisexclusion marks exclusion constraints and indisreplident marks an index chosen as replica identity for logical replication. The pg_constraint check catches anything else a constraint depends on.

Zero scans does not mean useless for unique indexes. A unique index or primary key does its job on every insert by rejecting duplicates, and a foreign key needs a unique index on the referenced columns. Those indexes can show idx_scan = 0 forever and still be essential. Never drop them based on usage numbers.

Check how long the counters have been counting

A zero is only meaningful if the counters have covered enough time. The statistics documentation says counters are kept across a clean shutdown, but are reset after an immediate shutdown, a crash, starting from a base backup and point-in-time recovery. Anyone with the right privilege can also reset them with pg_stat_reset() or pg_stat_reset_single_table_counters().

SELECT stats_reset, pg_postmaster_start_time()
FROM pg_stat_database
WHERE datname = current_database();

stats_reset shows the last explicit reset. Look at the server start time and the logs as well: if the last start was after a crash or a failover to a freshly restored node, the counters are younger than stats_reset suggests. If they cover two days, you have not seen the weekly report, the month-end close or the quarterly export. We prefer at least one full business cycle, usually a month, and longer for systems with quarterly or yearly jobs.

Replicas have their own statistics

This is the trap that causes most bad drops. Statistics are collected locally on each server, and a physical replica keeps its own copy. Read queries that run on a standby use the primary's indexes, because a physical replica is a copy of the same files, but those scans are counted on the standby, not on the primary.

So an index that shows zero scans on the primary may be the one index that keeps the reporting replica fast. Before you drop anything, run the same query on every replica and treat an index as unused only if it is unused everywhere. The same applies to logical replication subscribers, although they have their own indexes and their own decisions to make.

Duplicate and invalid indexes

Duplicates are easier and safer than unused indexes, because the second copy adds nothing. This query groups indexes by table, columns, operator classes, collations, sort options, expressions and partial predicates:

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS same_definition
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
         indoption::text, coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

If two indexes match, keep the one that backs a constraint, or the one your migrations know by name, and drop the other. Overlapping indexes need more thought: an index on (customer_id) is usually covered by one on (customer_id, created_at), but not if the shorter one is unique, partial, or much smaller and used by a hot query that benefits from the size difference. Review those one by one.

Invalid indexes are often left behind when CREATE INDEX CONCURRENTLY fails, as the CREATE INDEX documentation describes. According to pg_index, an invalid index cannot be used for queries but still has to be maintained by inserts and updates, so it is pure cost. An index that is still being built by a running CREATE INDEX CONCURRENTLY also shows as invalid, so check pg_stat_activity before dropping:

SELECT indexrelid::regclass AS index_name,
       indrelid::regclass   AS table_name
FROM pg_index
WHERE NOT indisvalid;

Drop it and build it again if it was actually needed. We cover rebuilding in our guide to REINDEX CONCURRENTLY.

Drop unused indexes safely

  1. Save the definition. Store the output of pg_get_indexdef in your migration repository, so recreating the index is a copy and paste, not an archaeology project.
  2. Check the application. Search the code and migrations for the index name. Some ORMs and query hints reference indexes by name, and some upsert statements name a unique index or constraint explicitly.
  3. Drop it concurrently. A plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table. DROP INDEX CONCURRENTLY avoids blocking reads and writes and instead waits for conflicting transactions to finish.
  4. Watch for a week. Compare query latency, pg_stat_statements and the slow query log with the baseline. If a query got slower, recreate the index with CREATE INDEX CONCURRENTLY.
-- 1. Keep the definition so you can recreate it
SELECT pg_get_indexdef('orders_status_created_idx'::regclass);

-- 2. Drop without blocking reads and writes (not inside BEGIN ... COMMIT)
DROP INDEX CONCURRENTLY orders_status_created_idx;

The documentation lists the limits of CONCURRENTLY: one index per command, no CASCADE, not inside a transaction block and not for indexes on partitioned tables. Drop one index per migration step, and turn off the transaction wrapper your migration tool adds by default.

What changes on large databases

Key takeaways

PostgreSQL Indexes Performance Monitoring
Work with us

Too many indexes, slow writes?

We review index usage across the primary and every replica, find what is safe to remove and drop it without downtime on large, busy databases.

Get in touch More from the blog