Find unused Postgres indexes and stop paying to keep them

Q: How do I find unused indexes in PostgreSQL?

Query pg_stat_user_indexes for rows where idx_scan = 0, exclude anything backing a UNIQUE or primary key constraint, and check how long stats_reset has been accumulating before you trust the count. Run the same query on every replica, because each server tracks its own scan counts separately.

Q: Why does idx_scan miss redundant indexes?

A redundant index gets scanned, so it never lands in an idx_scan = 0 list. An index on (tenant_id) sitting alongside one on (tenant_id, created_at) wins the planner's pick for single-column lookups because it is smaller, which keeps its scan count healthy while the composite already covers the same queries. Find these by matching an index whose column list is a strict prefix of another index on the same table, then compare their sizes before you drop the narrow one.

Q: How do I drop a PostgreSQL index without locking the table?

Run DROP INDEX CONCURRENTLY outside a transaction block, one index per statement. It lets selects, inserts, updates, and deletes keep running while the drop completes. A plain DROP INDEX takes an ACCESS EXCLUSIVE lock and queues every query touching that table.

Q: What is an invalid index in PostgreSQL and why does it still cost money?

An invalid index is what CREATE INDEX CONCURRENTLY leaves behind when it fails partway through the build. Postgres ignores it for queries because it might be incomplete, while every insert, update, and delete still writes to it. Find them with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; and drop them.

A PostgreSQL table ringed by indexes, the unused ones greyed out at zero scans while writes still flow into them

Somewhere in your Postgres there is an index nobody has queried in a year. That unused index still gets written on every insert, copied onto every replica, and packed into every backup. On a Multi-AZ RDS PostgreSQL instance, 40 GiB of indexes with zero scans runs about $9.20 a month in storage before you count backups. The write overhead never arrives as its own line item, which is part of why these indexes survive so long. Here is how to find dead indexes by hand, the four reasons a scan count of zero can lie to you, and the busy-looking index you can drop anyway.

Why a dead index costs more than its storage

A dead index costs more than its storage line because every write to its table still has to update it. It can also block the cheapest update path Postgres has.

Storage is the easy part to price. Amazon RDS for PostgreSQL charges $0.115 per GB-month for gp3 database storage in us-east-1 on a Single-AZ instance. Multi-AZ doubles that to $0.23. A Multi-AZ cluster with readable standbys charges $0.345 for the same gigabyte, because it keeps three copies. Backup storage past your free allocation adds $0.095 per GB-month.

So one wasted gigabyte gets billed once, twice, or three times depending on how available you made your database. Then it gets billed again for as long as you retain snapshots that contain it.

The write cost is larger and much harder to see on an invoice. Every insert has to add an entry to every index on the table. Ten indexes means ten B-tree writes for one row of data. Each of those writes lands in WAL, ships to your replicas, and gets flushed at the next checkpoint.

The sharpest edge is what an extra index does to your updates. Postgres has an optimization called a heap-only tuple update, HOT for short, which lets it rewrite a row in place without touching any index at all. The condition is strict. The documentation requires that "the update does not modify any columns referenced by the table's indexes", and that the page has room for the new row version.

Read that condition again with a dead index in mind. One forgotten index on last_seen_at is enough to disqualify every update that touches last_seen_at. Instead of a cheap in-page rewrite, you pay for a fresh entry in every index on that table, and you pay to clean up the old ones later. An index that serves zero queries can multiply the cost of your busiest write path.

Zero scans does not mean safe to drop

idx_scan = 0 is where the investigation starts. Treating it as a verdict is how somebody drops the index that Tuesday night's report depends on. Four things will lie to you.

The counters may have been reset. Index statistics are cumulative since the last reset, and Postgres throws all of them away in more situations than people expect. The docs say that "when starting from an unclean shutdown (e.g., after an immediate shutdown, a server crash, starting from a base backup, and point-in-time recovery), all statistics counters are reset". Restoring a snapshot into a fresh instance qualifies. If your database was rebuilt last Thursday, every index in it looks unused, and none of them are.

Replicas count their own scans. Statistics are collected per server. Every Postgres process updates shared memory on the machine it is running on, and a physical replica keeps its own separate copy. Index scans that happen on a read replica never appear in the primary's pg_stat_user_indexes. Route your reporting traffic to a replica and the primary will report those indexes as dead with total confidence. Check every node before you drop anything.

Some indexes exist to enforce correctness. A primary key or a unique constraint is implemented with an index that can honestly show zero scans while still rejecting bad rows every day. Postgres will stop you here, because DROP INDEX CONCURRENTLY refuses any index supporting a UNIQUE or PRIMARY KEY constraint. Filter those out first so your candidate list holds only indexes you could actually drop.

Some indexes earn their keep once a quarter. A month-end close, an annual audit export, a compliance query somebody runs by hand in March. Zero scans across a 30-day window tells you nothing about the quarterly job. Check how long your statistics have been accumulating before you trust the window.

One more case is worth flagging on its own, because it produces an honest zero rather than a misleading one: an index on a foreign key column. Postgres does not create these for you, and one can sit at idx_scan = 0 for a perfectly good reason when nothing queries that column directly. Drop it anyway and a delete or update on the referenced parent row can force a sequential scan of the child table to check for orphaned rows, which turns into lock contention on a busy table. Check pg_constraint for foreign keys before you drop an index whose columns line up with one.

Find your dead indexes by hand

Start by asking how old your statistics actually are. Everything downstream depends on this number.

SELECT
    stats_reset,
    now() - stats_reset AS stats_age
FROM pg_stat_database
WHERE datname = current_database();

If stats_age is measured in days, stop and come back later. You want at least one full business cycle, and preferably a quarter.

Now list the candidates. This excludes anything backing a constraint, so what comes back is droppable in principle.

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    s.idx_scan,
    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
  AND NOT EXISTS (
      SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid
  )
ORDER BY pg_relation_size(s.indexrelid) DESC;

Add up what that pile is costing you:

SELECT
    count(*) AS unused_indexes,
    pg_size_pretty(sum(pg_relation_size(s.indexrelid))) AS wasted_space
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT EXISTS (
      SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid
  );

On PostgreSQL 16 and later you get a much better signal than a raw counter. Version 16 added a last_idx_scan timestamp, so you can find indexes that are technically alive but have not been touched in months.

SELECT
    indexrelname AS index_name,
    idx_scan,
    last_idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE last_idx_scan IS NULL
   OR last_idx_scan < now() - interval '90 days'
ORDER BY pg_relation_size(indexrelid) DESC;

Run all of this on your replicas too. pg_is_in_recovery() returns true on a replica and false on the primary, so you can confirm which node you are connected to before you trust its scan counts:

SELECT pg_is_in_recovery();

Next, hunt the worst case of all. When CREATE INDEX CONCURRENTLY fails partway through, it leaves behind an invalid index. The documentation describes exactly what that costs you: the index "will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead". You are paying the full write tax for something no query can ever use.

SELECT
    i.indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM pg_index i
WHERE NOT i.indisvalid;

Finally, look for exact duplicates. Two indexes with identical definitions on the same columns double the write cost of that column for no read benefit.

SELECT
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS total_size,
    array_agg(idx::text) AS duplicates
FROM (
    SELECT
        indexrelid::regclass AS idx,
        indrelid::text || E'\n' || indclass::text || E'\n'
            || indkey::text || E'\n' || indcollation::text || E'\n'
            || indoption::text || E'\n' || coalesce(indexprs::text, '')
            || E'\n' || coalesce(indpred::text, '') AS key
    FROM pg_index
) sub
GROUP BY key
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

The grouping key carries indcollation and indoption for a reason. Postgres keeps each column's collation in one and its sort direction in the other, and both decide which queries an index can answer. Leave them out and the key sees only table, columns, and operator class.

That shorter key files distinct indexes as identical twins. An index on (created_at DESC NULLS LAST) lands in the same group as one on (created_at), and scanning the ascending index backwards yields DESC NULLS FIRST, so the ordering the first one serves has no other source on that table. A composite on (tenant_id, created_at DESC) groups with (tenant_id, created_at) for the same reason, and neither direction of scan on the all-ascending index produces the mixed ordering. An index on (email) groups with one on (email COLLATE "C"), which sorts text by a different rule entirely.

Build those three pairs on a scratch table next to one genuine duplicate, and the shorter key reports four groups. Three of them are access paths you would be sorry to lose. The five-vector key above returns the real duplicate on its own.

The indexes a zero scan count will never show you

Every query so far starts from idx_scan = 0. That filter has a blind spot, and the index hiding in it is usually the biggest one you can drop.

Picture a table carrying an index on (tenant_id) and another on (tenant_id, created_at). Both get used. The planner reaches for the narrow one whenever a query filters on tenant_id alone, because it is smaller and cheaper to walk. So the narrow index reports a healthy scan count, sails past every dead-index sweep you just ran, and keeps billing you anyway.

The composite already answers those queries. The documentation is explicit that "a multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns". A lookup on tenant_id is precisely that leading-column case. You are paying for two access paths and getting one.

That duplicate bills on every write. Each insert updates both B-trees, both entries ship through WAL to every replica, and both land in every snapshot you retain. The storage multipliers from the first section apply to the redundant copy at full price.

This query finds indexes whose column list is a strict prefix of another index on the same table.

WITH usable AS (
    SELECT
        i.indexrelid,
        i.indrelid,
        i.indexrelid::regclass AS index_name,
        i.indrelid::regclass   AS table_name,
        i.indkey::text         AS cols,
        i.indclass::text       AS opclasses,
        i.indcollation::text   AS collations,
        i.indoption::text      AS sortopts,
        i.indisunique,
        i.indexprs,
        am.amname,
        EXISTS (
            SELECT 1 FROM pg_constraint pc WHERE pc.conindid = i.indexrelid
        ) AS backs_constraint
    FROM pg_index i
    JOIN pg_class c ON c.oid = i.indexrelid
    JOIN pg_am am   ON am.oid = c.relam
    WHERE i.indisvalid
      AND i.indpred IS NULL
),
droppable AS (
    SELECT * FROM usable
    WHERE NOT indisunique
      AND NOT backs_constraint
      AND indexprs IS NULL
)
SELECT
    narrow.table_name,
    narrow.index_name AS redundant_index,
    pg_size_pretty(pg_relation_size(narrow.indexrelid)) AS reclaimable,
    wide.index_name   AS already_covered_by
FROM droppable narrow
JOIN usable    wide
  ON  wide.indrelid    = narrow.indrelid
  AND wide.indexrelid <> narrow.indexrelid
  AND wide.amname      = narrow.amname
  AND wide.cols       LIKE narrow.cols       || ' %'
  AND wide.opclasses  LIKE narrow.opclasses  || ' %'
  AND wide.collations LIKE narrow.collations || ' %'
  AND wide.sortopts   LIKE narrow.sortopts   || ' %'
ORDER BY pg_relation_size(narrow.indexrelid) DESC;

The two CTEs answer two different questions, and collapsing them into one is the mistake that makes this query quietly useless. droppable is the narrow side: what you are permitted to drop, which excludes unique indexes and anything backing a constraint. usable is the covering side: what the planner can reach for, which includes those same unique and constraint-backed indexes. A PRIMARY KEY (tenant_id, id) cannot be dropped, but it absolutely does serve lookups on tenant_id, so it belongs in wide even though it can never appear in narrow. Join a single filtered set to itself and the most common redundancy in a multi-tenant schema, a plain (tenant_id) index sitting beside exactly that primary key, returns no rows at all.

Postgres stores an index's columns, operator classes, collations, and sort options as space-separated vectors, so each LIKE ... || ' %' test is a prefix comparison on one of them. Checking all four matters. Comparing columns alone would report a DESC NULLS LAST index as covered by an ascending one that cannot serve the same ordering, or a text index as covered by a composite that sorts the same column under a different collation.

Two restrictions stay on both sides deliberately. Partial indexes are excluded because a WHERE-qualified index covers only some rows and cannot stand in for one that covers them all. Expression indexes are excluded from narrow because indkey stores a 0 placeholder for every expression, so an index on lower(email) would prefix-match one on upper(email) and the query would tell you to drop a genuinely distinct access path. On the wide side expressions are harmless: an index on (tenant_id, lower(email)) really does cover tenant_id.

Size decides the call. A narrow index on a wide table can be a fraction of the composite's size, and a smaller index means fewer pages touched per scan. When the two are close in size, the narrow one is overhead you can reclaim today. When it is much smaller and sits on a hot read path, measure before you act: open a transaction, drop the index, run your real query under EXPLAIN (ANALYZE, BUFFERS), and roll back. A plain DROP INDEX holds an ACCESS EXCLUSIVE lock for the length of that transaction, so do this on a replica or during a quiet window, and never on a busy primary at noon.

Partitioned tables split one index across every partition

Every query so far reads pg_stat_user_indexes, which carries one row per index. On a partitioned table that means one row per partition, and the arithmetic stops meaning what you think it means.

Take an events table partitioned by month with two years of history and an index on (customer_id). Postgres builds that index on the parent and attaches a copy to each of the 24 partitions. Your application only queries the last 30 days, so 23 of those copies honestly report idx_scan = 0. They sit on the oldest partitions, which are also the largest, so a list sorted by size puts all 23 at the top. The candidate query hands you a page of big dead-looking indexes that all belong to an index your busiest query depends on.

Try to drop one and Postgres refuses, for a reason worth knowing before you write the script. An attached partition index has no independent existence. The documentation says that "an attached index cannot be dropped by itself, and will automatically be dropped if its parent index is dropped".

Judge the parent index instead. Sum the scan counts across the whole partition tree with pg_partition_tree, which walks partitioned indexes the same way it walks partitioned tables.

SELECT
    parent.oid::regclass  AS partitioned_index,
    pi.indrelid::regclass AS partitioned_table,
    sum(s.idx_scan)       AS scans_across_partitions,
    pg_size_pretty(sum(pg_relation_size(s.indexrelid))) AS reclaimable
FROM pg_class parent
JOIN pg_index pi ON pi.indexrelid = parent.oid
CROSS JOIN LATERAL pg_partition_tree(parent.oid) t
JOIN pg_stat_user_indexes s ON s.indexrelid = t.relid
WHERE parent.relkind = 'I'
  AND NOT pi.indisunique
  AND NOT EXISTS (
      SELECT 1 FROM pg_inherits h WHERE h.inhrelid = parent.oid
  )
  AND t.isleaf
GROUP BY parent.oid, pi.indrelid
HAVING sum(s.idx_scan) = 0
ORDER BY sum(pg_relation_size(s.indexrelid)) DESC;

The pg_inherits check keeps intermediate levels of a sub-partitioned hierarchy out of the results, so each logical index reports once from its top-level parent. A zero in this list is the claim idx_scan = 0 was only ever pretending to make on a partitioned table: no partition of that index has served a scan since the counters were reset.

Dropping it is the step with no concurrent path. DROP INDEX CONCURRENTLY is unavailable on partitioned tables, so a plain DROP INDEX on the parent is what you get, and it takes an ACCESS EXCLUSIVE lock. Save pg_get_indexdef for every partition first, then schedule the drop for a window where that lock is affordable.

Rebuilding without the lock takes three steps. Create the parent with ON ONLY, which leaves it marked invalid and blocks nothing. Build each partition's copy with CREATE INDEX CONCURRENTLY. Attach them one at a time, and the parent flips to valid when the last one lands.

CREATE INDEX idx_events_customer_id ON ONLY public.events (customer_id);

CREATE INDEX CONCURRENTLY idx_events_2026m08_customer_id
    ON public.events_2026m08 (customer_id);
ALTER INDEX idx_events_customer_id
    ATTACH PARTITION idx_events_2026m08_customer_id;

Repeat the last two statements for every partition. Miss one and the parent stays invalid, which puts you back at an index that costs writes and serves no queries.

How to drop an unused index online without locking the table

Before you drop an index, save its definition with pg_get_indexdef. This one line is your rollback, so put the output somewhere you will still have it next week.

SELECT pg_get_indexdef('public.idx_events_last_seen_at'::regclass);

Then drop it without blocking traffic:

DROP INDEX CONCURRENTLY public.idx_events_last_seen_at;

A plain DROP INDEX takes an ACCESS EXCLUSIVE lock, which queues every query touching that table until it finishes. CONCURRENTLY drops the index while selects, inserts, updates, and deletes keep running. It comes with four restrictions worth knowing before you script it:

DROP INDEX CONCURRENTLY cannot run inside a transaction block, so you cannot wrap a batch of drops in BEGIN.
Only one index name is allowed per DROP INDEX CONCURRENTLY statement.
DROP INDEX CONCURRENTLY refuses any index that backs a UNIQUE or PRIMARY KEY constraint.
CASCADE is not supported by DROP INDEX CONCURRENTLY, and indexes on partitioned tables are out of scope.

To roll back, recreate the index from the definition you saved, again without locking writes:

CREATE INDEX CONCURRENTLY idx_events_last_seen_at
    ON public.events USING btree (last_seen_at);

Watch the CREATE INDEX CONCURRENTLY rebuild actually finish. If it fails partway, it leaves an invalid index behind that costs you writes and serves no queries. Postgres is direct about the fix: drop the invalid index and run CREATE INDEX CONCURRENTLY again, or rebuild it with REINDEX INDEX CONCURRENTLY. Confirm with the invalid-index query above before you call the rollback done.

Drop one index at a time and give the database a day between the big ones. If something regresses, you want to know which statement caused it.

A quick decision tree

graph TD
  A[Index on the bill] --> P{On a partition?}
  P -- Yes --> Q[Sum idx_scan across the partition tree and judge the parent]
  P -- No --> Z{idx_scan = 0?}
  Q --> Z
  Z -- No --> Y{Columns are a prefix of another index?}
  Y -- Yes --> X[Redundant. Compare sizes, then drop the narrow one]
  Y -- No --> W[Keep it. Queries are reading it]
  Z -- Yes --> B{Stats reset in the last month?}
  B -- Yes --> C[Wait for a full business cycle]
  B -- No --> D{Backs a unique or PK constraint?}
  D -- Yes --> E[Keep it. It enforces correctness]
  D -- No --> F{Any replica reports scans on it?}
  F -- Yes --> G[Keep it. A replica depends on it]
  F -- No --> H{Could a quarterly job need it?}
  H -- Yes --> I[Recheck after one full cycle]
  H -- No --> J[Save pg_get_indexdef, then DROP INDEX CONCURRENTLY]

The same shape of waste turns up anywhere you provision capacity once and never revisit it.

Migrate gp2 EBS volumes to gp3 is the same argument about storage you are overpaying for without noticing.
Find idle NAT gateways covers a resource that bills every hour whether anything uses it or not.

Common questions

How do I find unused indexes in PostgreSQL? Query pg_stat_user_indexes for rows where idx_scan = 0, exclude anything backing a UNIQUE or primary key constraint, and check how long stats_reset has been accumulating before you trust the count. Run the same query on every replica, because each server tracks its own scan counts separately.

Does idx_scan = 0 mean an index is safe to drop? On its own, no. A reset statistics window, a replica handling the real read traffic, a constraint the index enforces, or a report that only runs once a quarter can each produce a zero scan count on an index you still need. Confirm all four before you drop anything.

Why does idx_scan miss redundant indexes? A redundant index gets scanned, so it never lands in an idx_scan = 0 list. An index on (tenant_id) sitting alongside one on (tenant_id, created_at) wins the planner's pick for single-column lookups because it is smaller, which keeps its scan count healthy while the composite already covers the same queries. Find these by matching an index whose column list is a strict prefix of another index on the same table, then compare their sizes before you drop the narrow one.

Are two indexes on the same column always duplicates? No. Sort direction and collation both belong to an index's definition, and either one can make two indexes on the same column serve different queries. An index on (created_at) cannot answer ORDER BY created_at DESC NULLS LAST, because a backward scan of it produces DESC NULLS FIRST. An index on (email COLLATE "C") sorts text by byte value rather than by your database's collation rules. Compare indoption and indcollation alongside indkey and indclass before you call a pair redundant.

How do I drop a PostgreSQL index without locking the table? Run DROP INDEX CONCURRENTLY outside a transaction block, one index per statement. It lets selects, inserts, updates, and deletes keep running while the drop completes. A plain DROP INDEX takes an ACCESS EXCLUSIVE lock and queues every query touching that table.

What resets PostgreSQL index usage statistics to zero? An unclean shutdown, a server crash, starting from a base backup, and point-in-time recovery all reset the counters, as does an explicit call to pg_stat_reset(). Check stats_reset in pg_stat_database first, because a database rebuilt last week reports every index as unused.

Why do the indexes on my partitioned table show zero scans? Because each partition's index keeps its own scan count. An index on a partitioned table is built on the parent and copied onto every partition, so a table partitioned by month with two years of history spreads one logical index across 24 counters. Queries that only touch recent data leave every older partition's copy at idx_scan = 0, and those are the biggest ones. Sum idx_scan across the whole partition tree with pg_partition_tree and judge the parent index rather than any single partition.

Can I drop the index on one partition in PostgreSQL? No. An index attached to a partitioned index cannot be dropped on its own, and Postgres removes it automatically when you drop the parent. DROP INDEX CONCURRENTLY is also unavailable on partitioned tables, so the parent drop takes an ACCESS EXCLUSIVE lock. To rebuild without that lock, create the parent with CREATE INDEX ... ON ONLY, build each partition's index with CREATE INDEX CONCURRENTLY, then ALTER INDEX ... ATTACH PARTITION each one until the parent goes valid.

What is an invalid index in PostgreSQL and why does it still cost money? An invalid index is what CREATE INDEX CONCURRENTLY leaves behind when it fails partway through the build. Postgres ignores it for queries because it might be incomplete, while every insert, update, and delete still writes to it. Find them with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; and drop them.

How OhChimp approaches this

OhChimp connects a read-only user to your database and runs these probes for you, reading pg_stat_user_indexes for indexes with zero scans and finding duplicates that share a definition on the same columns. It reports the storage each one wastes, then drafts the drops as a reviewable plan with a risk level and rollback steps, and nothing runs until you press apply. The projected saving stays flagged as not implemented until your real bill drops and holds for several consecutive checks. See PostgreSQL cost optimization for the rest of what it probes from inside the database.

All OhChimp posts