pg_stat_all_indexes helps identify indexes that have not served an index scan. Treating every idx_scan = 0 result as safe to drop is the operational mistake: the statistics belong to one PostgreSQL instance, not the whole cluster. section 6.11 section 6.12
The tempting cleanup
A first-pass investigation might inspect pg_stat_all_indexes for rows where idx_scan = 0 and remove the associated indexes. That can reduce disk consumption and DML maintenance, but it can also break reporting performance when a standby serves queries: the primary’s index statistics do not include scans performed on that standby, and physical replication propagates the index drop. section 6.11 section 6.12
Use the view as evidence for cleanup, not as a single-node deletion command:
- Inspect indexes with idx_scan = 0 on every PostgreSQL node that handles queries, including streaming-replication standbys. section 6.11 section 6.12
- Account for the workload before removal: indexes can slow INSERT, UPDATE, and DELETE operations, consume space, and add maintenance overhead, while sequential scans may be preferable for large result sets. section 6.11
- Check for duplicate indexes as well; repeatedly running CREATE INDEX without an explicit name can create redundant indexes with no query-performance benefit. section 6.11
