Short answer: an index is a drop candidate when pg_stat_user_indexes.idx_scan is 0 on every server that runs queries, the counters cover a full business cycle, and the index does not enforce a constraint. The primary alone is not enough evidence: in the test below, the index that served every reporting query on a replica showed zero scans on the primary.

Unused indexes are a real cost. Every insert writes to every index, and an update that changes an indexed column writes to all of them and loses the HOT optimization. But the usual one-line query (WHERE idx_scan = 0) is wrong often enough that dropping from it is risky. I built a PostgreSQL 18.6 primary with a streaming replica in Docker, ran a small workload on each, and checked each assumption. The scripts and full output are in the lab folder.

The test setup

One table, orders, 2,000,000 rows, with six indexes:

IndexSizeRole in the test
orders_pkey43 MBPrimary key
orders_customer_idx14 MBLookups by customer_id on the primary
orders_customer_dup_idx14 MBExact duplicate of the one above
orders_status_created_idx65 MB(status, created_at), used only by reporting on the replica
orders_external_ref_uq60 MBUnique index, never used for reads
orders_updated_at_idx43 MBOn updated_at, a column every update changes; used by nothing

The workload was 500 lookups by customer_id on the primary and 200 reporting queries on the replica, filtering on status and created_at.

Replicas keep their own counters

The same query against pg_stat_user_indexes on each server:

-- PRIMARY
           index           | idx_scan | has_last_scan
---------------------------+----------+---------------
 orders_customer_dup_idx   |      500 | t
 orders_customer_idx       |        0 | f
 orders_external_ref_uq    |        0 | f
 orders_pkey               |        0 | f
 orders_status_created_idx |        0 | f
 orders_updated_at_idx     |        0 | f

-- REPLICA
 orders_customer_dup_idx   |        0 | f
 orders_customer_idx       |        0 | f
 orders_external_ref_uq    |        0 | f
 orders_pkey               |        0 | f
 orders_status_created_idx |      200 | t
 orders_updated_at_idx     |        0 | f

From the primary alone, orders_status_created_idx is the largest unused index, at 65 MB, and the obvious first drop. On the replica it served all 200 reporting queries as an index-only scan. Dropping it on the primary drops it everywhere, because DDL replicates, and the reporting queries would fall back to scanning the table.

This is documented behaviour, not a bug: cumulative statistics are collected in each server's shared memory, and a physical replica keeps its own. It matters most for SaaS teams that send dashboards, exports or analytics to a read replica. On a cluster with several replicas, run the check on each one and add the results together.

Two more things in that output are easy to misread. The planner used orders_customer_dup_idx for all 500 lookups, so its identical twin orders_customer_idx looks unused, and which of the two looks unused is arbitrary. And orders_pkey shows zero scans because nothing had looked rows up by id yet. It is still required.

When the counters reset

A zero only means something if the counters have been collecting long enough to include month-end jobs, quarterly reports and rare admin paths. They reset more often than many teams assume:

  • Reset functions. pg_stat_reset() zeroes every counter in the current database, index scans included. Monitoring tools and runbooks sometimes call it.
  • Unclean shutdown. After a crash, an immediate shutdown, a start from a base backup or point-in-time recovery, all counters start from zero.
  • Major upgrade. pg_upgrade transfers most optimizer statistics in PostgreSQL 18, but not the cumulative statistics behind idx_scan.
  • New replica. A replica built from a base backup starts with empty counters.

I tested the first two cases that are easy to miss:

before: orders_customer_dup_idx idx_scan=500
after immediate stop: orders_customer_dup_idx idx_scan=0
db stats_reset=null

before clean restart: orders_customer_dup_idx idx_scan=3
after clean restart: orders_customer_dup_idx idx_scan=3

After the immediate shutdown, pg_stat_database.stats_reset was still null. That column records calls to the reset functions, not crash-recovery resets, so it cannot tell you how old the counters are. Check pg_postmaster_start_time(), the server log for crash-recovery messages, and your change history for upgrades, failovers and replica rebuilds. If in doubt, take a snapshot now and compare it with another one after a full business cycle. On PostgreSQL 16 and later, last_idx_scan shows when each index was last used, which helps for rarely used indexes.

A safer candidate query

This version excludes indexes that enforce something, and invalid indexes left by a failed concurrent build, which you handle separately (see the CREATE INDEX CONCURRENTLY guide). Run it on the primary and on every replica.

SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       s.idx_scan, s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i USING (indexrelid)
WHERE s.idx_scan = 0
  AND i.indisvalid
  AND NOT i.indisunique
  AND NOT i.indisprimary
  AND NOT i.indisexclusion
  AND NOT EXISTS (SELECT 1 FROM pg_constraint AS c WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- How long have the counters been collecting?
SELECT pg_postmaster_start_time() AS server_started,
       d.stats_reset AS db_stats_reset
FROM pg_stat_database AS d
WHERE d.datname = current_database();

On the lab primary it returned three rows: orders_status_created_idx (which the replica needs), orders_updated_at_idx, and orders_customer_idx (one of the duplicates). It excluded the primary key and the unique index. Remove the last_idx_scan column on PostgreSQL 15 and earlier.

Before you drop anything the query returns, check three more things that the counters cannot show:

  • Expression indexes carry planner statistics. ANALYZE collects statistics on the expression of an expression index, such as lower(email). The planner can use them for row estimates even if it never scans the index. Dropping it can change plans elsewhere.
  • Unique indexes protect data. The query excludes them for a reason: an unused unique index may be the only thing stopping duplicate rows. Drop one only after deciding the rule no longer applies.
  • Infrequent code paths. Search the code and the scheduled jobs for the table and column before you trust a zero. A year-end report counts as usage.

Duplicate indexes

Identical indexes are the safest drop there is: the planner treats them as interchangeable, so removing one leaves the other to do the same work. This query groups indexes with the same columns, operator classes, collations, options, expressions and predicate:

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass ORDER BY indexrelid) AS same_definition,
       pg_size_pretty(sum(pg_relation_size(indexrelid))) AS combined_size
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
         indoption::text, coalesce(pg_get_expr(indexprs, indrelid), ''),
         coalesce(pg_get_expr(indpred, indrelid), '')
HAVING count(*) > 1;
 table_name |                same_definition                | combined_size
------------+-----------------------------------------------+---------------
 orders     | {orders_customer_idx,orders_customer_dup_idx} | 27 MB

Keep the one that backs a constraint or is unique, if either does: the query groups a unique and a non-unique index on the same columns together. The query only finds exact duplicates. An index on (customer_id) next to one on (customer_id, created_at) is often redundant too, but not always: the narrower index is smaller and can be cheaper to scan. Decide that from the query plans, not from the definitions.

If you have already identified one proposed index change and want its migration and rollout reviewed, see the bounded Database Code Review scope.

What an unused index costs: HOT and WAL

Disk space is the visible cost. The bigger one is on writes. A heap-only tuple (HOT) update is possible only when the update changes no indexed column and the new row version fits on the same page. A single index on a frequently updated column, such as updated_at, last_seen_at or a status, prevents HOT for every update that changes it. Each of those updates then adds a new entry to every index on the table.

I ran 20,000 single-row updates of updated_at (pgbench, 4 clients), once with orders_updated_at_idx and once after dropping it. The table had fillfactor = 90, so pages had room for HOT. Each run started straight after a CHECKPOINT, so both pay the same full-page-image overhead.

With the unused indexAfter dropping it
HOT updates0 of 20,000 (0%)20,000 of 20,000 (100%)
WAL per update13,997 bytes4,933 bytes
Total WAL280 MB99 MB
Average latency1.386 ms0.584 ms

One index that nothing read was costing 65% of the WAL on this update path. WAL volume drives replica lag, backup size, archive storage and write I/O. These are laptop numbers on a table that fits in memory; read the direction and the ratio, not the absolute values. Without the index, HOT also means less index bloat and less vacuum work.

Check your own tables:

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
WHERE n_tup_upd > 10000
ORDER BY n_tup_upd - n_tup_hot_upd DESC
LIMIT 20;

A heavily updated table with a low hot_pct is where an unneeded index on an updated column costs the most. A BRIN index is the exception: as a summarizing index, it does not prevent HOT.

idx_scan says how often an index was used, not which queries used it. I am building an open-source extension for that question, pg_stat_index_queries. It lists the queries that executed through each index. Its SQL edition reads captured plans on RDS and Aurora and can estimate the plan change if an index were dropped, using HypoPG in your session only. It is experimental and has not been run in production, so treat its output as one input to the decision and benchmark it on a non-production system first.

Drop it safely, with a rollback

  1. Save the definition, which is your rollback:
    SELECT pg_get_indexdef('orders_updated_at_idx'::regclass);
    -- CREATE INDEX orders_updated_at_idx ON public.orders USING btree (updated_at)
    Add CONCURRENTLY to that statement when you run it as a rollback. Measure how long the rebuild takes on a copy, so you know how long the rollback takes.
  2. Drop without blocking reads and writes: DROP INDEX CONCURRENTLY orders_updated_at_idx;. It waits for transactions that use the table, so a long-running transaction delays it. It cannot run inside a transaction block, which matters for migration tools that wrap each file in BEGIN:
    ERROR:  DROP INDEX CONCURRENTLY cannot run inside a transaction block
  3. Constraint indexes are dropped through the constraint. In the lab, DROP INDEX on an index that backed a unique constraint failed with cannot drop index orders_ref_key because constraint orders_ref_key on table orders requires it. ALTER TABLE … DROP CONSTRAINT takes an ACCESS EXCLUSIVE lock; plan it as a schema change.
  4. One index per change. If latency moves, you know which drop caused it.
  5. Validate: compare pg_stat_statements for the affected table's queries and the WAL rate before and after, on the primary and on the replicas. The pg_stat_statements guide shows how to diff snapshots.

RDS and Aurora notes

The queries above work on RDS for PostgreSQL and Aurora PostgreSQL with a normal application or monitoring role. Each RDS read replica and each Aurora reader instance keeps its own counters, so connect to each instance's own endpoint rather than the Aurora cluster reader endpoint, which spreads connections across readers. Failovers, instance replacements and major version upgrades are all events after which the counters may restart, so check the event history for the period the counters cover. Performance Insights or your own pg_stat_statements snapshots can confirm which statements touch the table. To inspect a specific plan, paste it into PlanReader.

FAQ

How do I find unused indexes in PostgreSQL?

Query pg_stat_user_indexes for indexes with idx_scan = 0, exclude primary keys, unique and exclusion indexes and any index that backs a constraint, and check last_idx_scan on PostgreSQL 16 and later. Then repeat the query on every read replica, because each server keeps its own counters, and confirm the counters cover a full business cycle.

Does idx_scan include scans on read replicas?

No. Cumulative statistics are per server. A scan on a streaming replica increments idx_scan on that replica only. In a tested PostgreSQL 18 setup, an index used 200 times on the replica showed idx_scan = 0 on the primary.

When are PostgreSQL index usage statistics reset?

On pg_stat_reset() and related reset functions, and after an unclean shutdown: a crash, an immediate shutdown, a start from a base backup or point-in-time recovery. pg_upgrade does not carry cumulative statistics over. A clean restart keeps them. After an immediate shutdown in a test, idx_scan went from 500 to 0 while stats_reset stayed null.

Is it safe to drop an index with idx_scan = 0?

Not by itself. A unique index still enforces uniqueness, an expression index can supply planner statistics, and a rarely used index may serve a monthly job or a replica. Save the definition with pg_get_indexdef, drop it with DROP INDEX CONCURRENTLY, and have the CREATE INDEX CONCURRENTLY statement ready as the rollback.

What does an unused index cost?

Disk, backup and cache space, plus work on every insert and on every update that changes an indexed column. An index on a frequently updated column also prevents HOT updates. In a test, dropping one such index raised HOT updates from 0% to 100% and cut WAL per update from about 14 KB to 4.9 KB.

Sources