The Index Advisor That Checks Your Replicas Before Recommending a DROP

The Index Advisor That Checks Your Replicas Before Recommending a DROP

The Problem

A team is cleaning up their PostgreSQL database. They run a query against pg_stat_user_indexes on the primary and find idx_orders_customer_region with zero scans. Three hundred megabytes of disk, never used. They drop it. Within four minutes, their analytics service — running all its dashboard queries against the read replica — starts returning timeouts. Every query that filtered orders by customer region was using that index on the replica. On the primary, those queries never ran.

This is not an edge case. It happens constantly in any setup where primaries and replicas serve different workloads. The primary handles transactional writes — inserts, updates, point lookups by primary key. The replicas serve analytical reads — aggregations, filtered scans, joins across large tables. The indexes each workload needs are fundamentally different, but pg_stat_user_indexes only shows usage statistics for the node you are connected to.

The problem gets worse with multiple replicas. A reporting replica might use idx_orders_created_at heavily while a search replica never touches it but relies on idx_orders_fulltext. Checking only the primary — or even the primary plus one replica — misses indexes that are critical elsewhere in the cluster. The safe way to evaluate index usage requires connecting to every node, collecting stats from each, and cross-referencing before making any drop decision.

How to Detect It

On a single node, finding unused indexes is straightforward:

-- Unused indexes on the current node (primary or replica)
SELECT
    schemaname, relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans_since_reset,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_stat_get_last_reset_time() AS stats_reset_time
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;

The stats_reset_time is critical context. If statistics were reset yesterday, zero scans might just mean the index has not been needed in the last 24 hours. You need weeks of data to make a confident drop decision.

The limitation is that this query only reflects the current node's workload. To check replicas, you would need to connect to each one and run the same query:

-- On each replica, check if the "unused" index is actually used there
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_customer_region';

For duplicate detection — two indexes on the same columns — you can query pg_index:

-- Find indexes with identical column sets
SELECT array_agg(indexrelid::regclass) AS duplicate_indexes,
       indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass ORDER BY indexrelid) AS index_names
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

Manually connecting to every node, correlating results, and identifying duplicates across a fleet of databases is tedious enough that most teams skip it — and that is when indexes get dropped that should not be.

How myDBA.dev Shows It

myDBA.dev's Index Advisor aggregates index usage statistics across all monitored nodes — primary and every replica. The unused index view shows each index's scan count, size, the table it belongs to, and a pre-built DROP INDEX CONCURRENTLY statement. Indexes are sorted by size so you can prioritize the largest space savings first. Each recommendation includes the total space that would be reclaimed, accounting for both the index itself and any associated TOAST data.

The cluster-aware view is where myDBA.dev prevents the mistake described in the problem section. An index like idx_orders_customer_region shows zero scans on the primary but 14,000 scans on the replica. Instead of recommending a drop, the advisor flags it as "used on replica" and excludes it from the drop list. This single check eliminates the most dangerous class of index management errors — dropping an index that is critical on a node you forgot to check.

The advisor also detects duplicate indexes (same columns, different names — often created by ORM migrations that do not check for existing indexes) and overlapping indexes (a single-column index that is redundant because a composite index with the same leading column already exists).

How to Fix It

Index cleanup follows a priority order: duplicates first, then unused, then overlapping.

Duplicate indexes are always safe to drop because they are functionally identical:

-- Two indexes on orders(customer_id) — drop the one without a unique constraint
DROP INDEX CONCURRENTLY idx_orders_customer_id_v2;
-- Space reclaimed: 1.2 GB

Unused indexes (confirmed unused across all nodes) should be dropped with CONCURRENTLY to avoid locking the table:

-- Unused on primary AND all replicas, stats window > 30 days
DROP INDEX CONCURRENTLY idx_orders_legacy_status;
-- Space reclaimed: 340 MB

If you are uncertain about an index, you can disable it without dropping by setting it to invalid. This prevents the planner from using it while keeping the definition intact for easy recreation:

-- "Soft disable" — make the index invisible to the planner
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_legacy_status'::regclass;

-- Re-enable if needed
UPDATE pg_index SET indisvalid = true
WHERE indexrelid = 'idx_orders_legacy_status'::regclass;
REINDEX INDEX CONCURRENTLY idx_orders_legacy_status;

Overlapping indexes require more analysis. If you have both idx_orders_customer_id and idx_orders_customer_id_created_at, the single-column index is redundant for equality lookups (the composite index's leading column serves the same purpose). But if queries frequently sort by only customer_id without created_at, the single-column index may produce smaller index scans. Check the query patterns before dropping.

How to Prevent It

Run index audits regularly — monthly at minimum — and always across the entire cluster. A single-node audit creates a false sense of safety. Application changes introduce new query patterns, and ORM migrations frequently create indexes without checking whether equivalent ones already exist. A monthly audit catches these accumulations before they waste significant disk space.

Establish a policy: no index is dropped without checking replica usage. myDBA.dev enforces this automatically by aggregating stats across all nodes, but even if you are doing manual audits, the rule should be explicit in your team's runbook. The 5 minutes spent checking replica stats can save hours of incident response when an analytics dashboard goes down.

Track total index size as a percentage of table size. A healthy ratio is typically 30-50% — if your indexes consume more space than the table data itself, you almost certainly have redundant or unused indexes worth cleaning up.