Finding Unused Indexes Safely #

An index nobody reads is pure cost: extra WAL on every write, extra work for every vacuum cycle, extra pages competing for shared buffers, and one more path for the planner to consider. Dropping it is usually a clean win — right up until you drop the one that quietly enforces uniqueness, or the one only the reporting replica uses.

This page is the verification procedure that turns “probably unused” into “provably unused”. It sits alongside the bloat work in index maintenance and bloat.

The Evidence and Its Limits #

pg_stat_user_indexes.idx_scan counts how many times the planner’s chosen path actually opened the index on this node since statistics were last reset. That single sentence contains all four caveats:

Four checks before dropping an index Starting from idx_scan equals zero, four sequential gates: statistics age, replica counters, constraint ownership, and foreign key support. Failing any gate routes to keep the index; passing all four routes to drop concurrently. idx_scan = 0 candidate stats age > 30 days? replicas all zero? constraint not unique/FK? DROP INDEX CONCURRENTLY any gate fails → keep the index and re-check later a zero counter is a hypothesis, not a verdict

Building the Candidate List #

Start from the counters, but carry enough context to judge each row:

SELECT s.schemaname,
       s.relname               AS table,
       s.indexrelname          AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan,
       i.indisunique,
       i.indisprimary
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE s.idx_scan < 50
   AND NOT i.indisprimary
   AND pg_relation_size(s.indexrelid) > 50 * 1024 * 1024
 ORDER BY pg_relation_size(s.indexrelid) DESC;

Then date the evidence, because a counter without an age is meaningless:

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

If stats_reset is a week old, park the analysis and come back after a full business cycle — month-end and quarter-end jobs are exactly the workloads that use an index rarely and critically.

Finally, exclude the indexes that exist for reasons other than query plans:

-- Indexes owned by a constraint: dropping them means dropping the constraint
SELECT c.conname, c.contype, c.conindid::regclass AS index
  FROM pg_constraint c
 WHERE c.conindid <> 0;

-- Indexes that support foreign keys pointing AT this table (used during deletes/updates)
SELECT conrelid::regclass AS child_table, conname, confrelid::regclass AS parent_table
  FROM pg_constraint WHERE contype = 'f';

A foreign key without an index on the referencing column turns every parent delete into a sequential scan of the child — a use that never appears in idx_scan for the parent’s index but very much depends on the child’s.

Step-by-Step Retirement Workflow #

  1. Collect counters from every node, primary and replicas, on the same day. Store them; do not eyeball them once.

  2. Wait out a full business cycle before acting — at minimum a month, so period-end reporting has had its chance to use the index.

  3. Check for duplicates and prefixes. An index on (a) is redundant when (a, b) exists, because the wider one serves the same predicates:

    SELECT indrelid::regclass AS table, indexrelid::regclass AS index, indkey
  FROM pg_index ORDER BY indrelid, indkey;

    The redundancy rules are the same ones described in covering index design.

  4. Test the plans without it. On a clone or in a maintenance window:

    BEGIN;
DROP INDEX events_legacy_status_idx;
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;   -- the queries you care about
ROLLBACK;                                -- index is back

    The drop holds an ACCESS EXCLUSIVE lock for the transaction’s duration, so keep it short and never do it casually on a busy primary.

  5. Drop it online once satisfied:

    DROP INDEX CONCURRENTLY events_legacy_status_idx;

  6. Keep the definition. Save pg_get_indexdef() output in the change record so recreating it is a copy-paste, not an archaeology project:

    SELECT pg_get_indexdef('events_legacy_status_idx'::regclass);

  7. Watch the affected queries for a week. A plan that quietly switched to a sequential scan will show up as rising buffer counts before it shows up as a complaint.

What an unused index still costs Four cost bars: write amplification on every insert, update and delete; vacuum work on every cycle; shared buffer occupancy; and a small planning overhead from considering an extra path. write amplification index entry + WAL per row vacuum cost scanned on every cycle buffer cache pages evicting useful data planning one more candidate path none of this appears in idx_scan — the cost is invisible in the metric that judges the index

Before and After #

-- BEFORE: 11 indexes on events, three of them unused for 90 days
INSERT throughput  : 14,200 rows/s
VACUUM events      : 412 s per cycle
events index total : 18.4 GB

-- AFTER dropping three unused indexes (4.9 GB reclaimed)
INSERT throughput  : 19,700 rows/s        -- +39%
VACUUM events      : 268 s per cycle      -- -35%
events index total : 13.5 GB

The read plans were unchanged — that is the point. The gain is entirely on the write and maintenance side, which is where an unused index does its damage.

Common Pitfalls #

Trusting a freshly reset counter. A restore or an upgrade zeroes everything and every index looks unused. Diagnostic signal: stats_reset close to now. Fix: date the evidence before acting on it.

Forgetting replicas. Analytics traffic often runs entirely on a replica with its own counters. Diagnostic signal: zero scans on the primary for an index whose name matches a reporting query. Fix: gather counters from every node.

Dropping a unique index. Uniqueness enforcement does not increment idx_scan, so a constraint’s index can look idle. Diagnostic signal: indisunique = true or a matching pg_constraint row. Fix: exclude constraint-backed indexes from the candidate list.

Dropping the index a foreign key relies on. Deletes on the parent then scan the child table. Diagnostic signal: a referencing column with no index. Fix: check pg_constraint for contype = 'f' before dropping anything on a child table.

Dropping non-concurrently on a busy table. DROP INDEX without CONCURRENTLY takes an ACCESS EXCLUSIVE lock. Diagnostic signal: a lock queue in pg_stat_activity. Fix: always use the concurrent form in production.

The retirement timeline A timeline with four stages: at least one full business cycle of observation, a plan test on a clone, the concurrent drop, and a week of watching the affected queries. observe ≥ 1 business cycle test drop + EXPLAIN + rollback drop CONCURRENTLY, keep the DDL watch buffer counts for a week skipping the observation stage is how a month-end job loses its index

Frequently Asked Questions #

Does idx_scan = 0 mean an index is safe to drop? Not by itself. The counter is per node, resets with statistics, and never counts constraint enforcement. Confirm its age, check every replica, and exclude constraint-backing and foreign-key-supporting indexes before deciding.

How do I test a drop without committing to it? Drop it inside a transaction, run EXPLAIN on the queries that matter, and roll back. The drop holds a strong lock for the transaction, so do this on a clone or in a maintenance window rather than on a busy primary.

What does an unused index actually cost? Extra index maintenance and WAL on every write that touches its columns, extra work in every vacuum cycle, shared-buffer occupancy that evicts useful pages, and a marginally longer planning phase. None of it shows up in idx_scan.