PostgreSQL Unused Indexes and Index Bloat: A Safe Cleanup Checklist

How do you find PostgreSQL unused indexes with pg_stat_user_indexes?

An unused index is one the planner never chooses for any query. A low-value index is one it chooses so rarely, or for such a small table, that a sequential scan or another index would serve almost as well. Both still cost you on every write. Each INSERT, and each UPDATE that cannot use a heap-only tuple, must add an entry to every index on the table. Vacuum has to scan each index to remove dead entries. The index pages compete with hot table data for shared buffers and the OS page cache. Every base backup, replica rebuild, and restore also copies the index bytes. On a write-heavy table with a dozen indexes, an index nobody reads still adds to all of these costs.

The audit query

Start from pg_stat_user_indexes, which tracks idx_scan for each index. Join it to pg_index on indexrelid to get indisunique and indisprimary, so constraint-backing indexes are flagged rather than hidden. Join it to pg_class for the table and index names. Compute pg_relation_size(indexrelid) for the on-disk footprint. Sort by idx_scan ascending, then by size descending. Large indexes with few or no scans then appear first, which is where cleanup pays off most.

Read idx_scan = 0 as a lead to investigate, not a verdict. The count only covers the period since statistics were last reset. It is also local to the server you query.

Who should run it

This audit belongs to the backend and platform engineers who own the schema. It matters most on write-heavy databases, where index maintenance limits throughput, and on storage-constrained ones, where index size drives disk, backup, and replica costs.

Copy-- Since when has idx_scan been counting on this server?
-- A recent reset makes zero scans meaningless.
SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

-- Index usage audit: rarely scanned, large indexes first.
SELECT
  s.schemaname,
  t.relname                                   AS table_name,
  i.relname                                   AS index_name,
  s.idx_scan,                                 -- scans since last stats reset, this server only
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  x.indisunique,                              -- backs a UNIQUE constraint or index
  x.indisprimary                              -- backs the PRIMARY KEY
FROM pg_stat_user_indexes AS s
JOIN pg_index AS x ON x.indexrelid = s.indexrelid
JOIN pg_class AS i ON i.oid = s.indexrelid   -- index name
JOIN pg_class AS t ON t.oid = s.relid        -- table name
ORDER BY
  s.idx_scan ASC,                             -- least used first
  pg_relation_size(s.indexrelid) DESC         -- then biggest footprint
LIMIT 50;

-- Treat idx_scan = 0 as a lead, not a verdict. Unique and primary
-- indexes enforce constraints even when no query scans them.
-- Run this on every replica as well, because counters are per server.

Lists each user index with its scan count, on-disk size, and unique and primary key flags, sorted so large, rarely scanned indexes appear first. A preliminary query shows when the statistics were last reset.

Can you trust idx_scan? Checking the stats window and every replica

idx_scan is a cumulative counter. It shows how many times the planner chose the index since statistics were last reset, not across the index's whole life. Before trusting a zero, check when counting started. The stats_reset column in pg_stat_database gives the timestamp for the database.

Several events can wipe or replace that history:

• pg_stat_reset(): someone ran it, often while debugging, and every counter in the database went back to zero.
• Crash recovery: after a crash or immediate shutdown, PostgreSQL discards the statistics. A clean shutdown preserves them.
• Failover: a promoted replica has only its own counters, which reflect the reads it served as a standby. It does not have the old primary's write-path and query history.

A recent reset leaves a short window, and a short window hides infrequent workloads. Monthly invoicing, quarterly reports, and year-end close may each depend on an index that shows zero scans today.

To get a long window, snapshot pg_stat_user_indexes into a table on a schedule, such as daily. Record a timestamp and the server name with each row. Judge usage by the change in idx_scan between snapshots, and treat a drop in the counter as a sign of a reset. Wait until the history spans a full business cycle before calling any index unused.

Counters are also tracked separately on each server:

Copy            +------------------+
            |     Primary      |
            | idx_scan = 0     |
            +--------+---------+
                     |  WAL streaming
         +-----------+-----------+
         v                       v
+------------------+   +------------------+
|    Replica A     |   |    Replica B     |
| idx_scan = 48210 |   | idx_scan = 0     |
+------------------+   +------------------+

A dropped index is gone from every replica, because the drop replicates. Run the usage query on the primary and on every replica that takes reads, and sum the results before you decide.

Copy-- History table. Keep it on the primary: replicas are read-only.
CREATE TABLE IF NOT EXISTS index_usage_snapshot (
  captured_at  timestamptz NOT NULL,
  server_name  text        NOT NULL,
  is_replica   boolean     NOT NULL,
  stats_reset  timestamptz,           -- when this server's counters last started
  schemaname   name        NOT NULL,
  relname      name        NOT NULL,
  indexrelname name        NOT NULL,
  idx_scan     bigint      NOT NULL
);

-- Run daily on the primary and on every replica that serves reads.
-- On the primary, insert directly. On a replica, run only the SELECT and
-- load its output into the primary's table, for example with psql \copy.
INSERT INTO index_usage_snapshot
SELECT now(),
       coalesce(nullif(current_setting('cluster_name'), ''),
                host(inet_server_addr()), 'local'),
       pg_is_in_recovery(),
       (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()),
       s.schemaname, s.relname, s.indexrelname, s.idx_scan
FROM pg_stat_user_indexes s;

-- Scans per index per server over the whole history. A counter that went
-- down means a reset, so only the post-reset value counts for that interval.
SELECT server_name, schemaname, indexrelname,
       min(captured_at) AS first_seen, max(captured_at) AS last_seen,
       sum(CASE WHEN prev IS NULL THEN 0
                WHEN idx_scan < prev THEN idx_scan   -- reset detected
                ELSE idx_scan - prev END) AS scans_in_window,
       count(*) FILTER (WHERE idx_scan < prev) AS resets_seen
FROM (SELECT *, lag(idx_scan) OVER (PARTITION BY server_name, schemaname, indexrelname
                                    ORDER BY captured_at) AS prev
      FROM index_usage_snapshot) d
GROUP BY server_name, schemaname, indexrelname
ORDER BY indexrelname, server_name;

This example stores daily snapshots of pg_stat_user_indexes from each server in a history table on the primary, then adds up the change in idx_scan per server and treats any drop in the counter as a stats reset.

Which indexes should you never drop even with zero scans?

A unique index enforces its constraint on every INSERT and on every UPDATE that touches the key. Those checks do not count as scans. A primary key, a unique constraint or an exclusion constraint can show idx_scan = 0 and still be the only thing that prevents duplicate or overlapping rows. If you drop it, you lose data integrity silently. Nothing errors until bad data arrives.

Foreign keys need a closer look. PostgreSQL indexes the referenced side of a foreign key, because that side must be unique. It does not index the referencing columns. When a row in the parent table is deleted, or its key is updated, PostgreSQL checks the child table for matching rows. Without an index on the child's foreign key columns, that check is a sequential scan of the child table, run once per affected parent row. A low scan count here may mean only that no parent deletes ran during the stats window. A monthly purge job would bring the problem back.

Filter query

Before you review candidates, exclude these indexes:

• Join pg_index to your candidate list and drop rows where indisprimary or indisunique is true.
• Left join pg_constraint on conindid = indexrelid and drop any index that backs a constraint. This also covers exclusion constraints.
• For foreign keys (contype = 'f'), compare conkey against each candidate's leading columns in indkey on the referencing table. An index whose leading columns match a foreign key should stay unless you have checked its delete patterns.

What the stats don't tell you

Usage stats show that an index was scanned. They don't show why the scan mattered. A lookup run twice a year during incident response, or a quarterly finance report, can depend on an index that looks idle. Before you drop one, ask the people who own those queries.

Copy-- Zero-scan indexes with constraint-backed ones removed,
-- and foreign key coverage flagged for manual review.
SELECT s.schemaname,
       s.relname       AS table_name,
       s.indexrelname  AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       -- true if the index's leading columns cover a foreign key on this table
       EXISTS (
         SELECT 1
         FROM pg_constraint fk
         WHERE fk.contype = 'f'
           AND fk.conrelid = i.indrelid
           AND fk.conkey <@ ARRAY(
                 SELECT u.k
                 FROM unnest(i.indkey::smallint[]) WITH ORDINALITY AS u(k, ord)
                 WHERE u.ord <= array_length(fk.conkey, 1))
       ) AS backs_fk_lookup
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
-- Any constraint that owns this index: PK, UNIQUE, EXCLUDE
LEFT JOIN pg_constraint c ON c.conindid = s.indexrelid
                         AND c.contype IN ('p', 'u', 'x')
WHERE s.idx_scan = 0
  AND NOT i.indisprimary   -- enforces the primary key
  AND NOT i.indisunique    -- enforces uniqueness on every write
  AND c.oid IS NULL        -- not owned by a constraint
ORDER BY backs_fk_lookup DESC, pg_relation_size(s.indexrelid) DESC;

Lists zero-scan indexes after excluding primary key, unique and exclusion constraint indexes, and flags indexes whose leading columns cover a foreign key so you check delete and update patterns on the parent table before dropping them.

How do you detect duplicate and overlapping indexes?

A zero-scan index is one kind of waste. Another is two indexes doing the same job, where the planner uses only one and both slow down every write. To find these, query pg_index and group by indrelid and the key columns in indkey. Indexes with identical key lists are candidates for being exact duplicates. Next, look for prefix overlap: a B-tree on (a) is usually redundant next to one on (a, b), because the planner can use the leading column of the wider index for lookups on a alone. The wider index is larger, so those lookups may cost slightly more. Usually the saving on writes outweighs that.

Matching column lists are not proof of redundancy. Before you treat two indexes as interchangeable, also compare indclass, indpred, indexprs, indnkeyatts and the access method.

Dropping is not the only option. Several overlapping indexes can often be replaced by a single one, such as (a, b) INCLUDE (c). Build the replacement first, confirm that the planner uses it, then remove the old indexes.

Copy-- Find B-tree indexes whose key columns equal, or are a leading prefix of,
-- another index on the same table. Read-only: review each pair before dropping.
WITH idx AS (
  SELECT i.indexrelid, i.indrelid, i.indisunique, i.indisprimary, am.amname,
         -- key columns only; INCLUDE columns sit past indnkeyatts
         (string_to_array(i.indkey::text, ' ')::int2[])[1:i.indnkeyatts] AS keycols,
         (string_to_array(i.indclass::text, ' ')::oid[])[1:i.indnkeyatts] AS opclasses,
         pg_get_expr(i.indpred, i.indrelid)   AS pred,   -- partial-index WHERE
         pg_get_expr(i.indexprs, i.indrelid)  AS exprs   -- expression keys
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  JOIN pg_am am   ON am.oid = c.relam
)
SELECT a.indrelid::regclass   AS table_name,
       a.indexrelid::regclass AS candidate,
       b.indexrelid::regclass AS covered_by,
       CASE WHEN a.keycols = b.keycols THEN 'same keys' ELSE 'prefix' END AS overlap,
       a.indisunique OR a.indisprimary AS candidate_enforces_constraint,
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS candidate_size
FROM idx a
JOIN idx b ON b.indrelid = a.indrelid AND b.indexrelid <> a.indexrelid
WHERE a.amname = 'btree' AND b.amname = 'btree'        -- different AMs are not redundant
  AND a.keycols   = b.keycols[1:cardinality(a.keycols)]     -- leading prefix or equal
  AND a.opclasses = b.opclasses[1:cardinality(a.opclasses)] -- e.g. text_pattern_ops differs
  AND a.pred  IS NOT DISTINCT FROM b.pred                   -- same row subset
  AND a.exprs IS NOT DISTINCT FROM b.exprs
  -- list each exact duplicate pair once
  AND (a.keycols <> b.keycols OR a.indexrelid < b.indexrelid)
ORDER BY 1, 2;

-- Rows where candidate_enforces_constraint is true are NOT safe to drop:
-- a unique index on (a) enforces a rule that (a, b) does not.
-- For 'same keys' rows, also compare INCLUDE columns (pg_get_indexdef) before choosing.

A read-only catalog query that pairs B-tree indexes with identical or leading-prefix key columns, filtering out pairs that differ in operator class, partial predicate, expressions or access method, and flagging indexes that back a unique or primary key constraint.

What causes index bloat and when does it actually matter?

Every non-HOT update writes a new index entry pointing at the new row version, and the old entry stays until vacuum confirms that no snapshot can still see it. Deletes leave dead entries the same way. When inserts hit a full B-tree leaf page, the page splits into two roughly half-full pages. Under heavy churn, many leaves end up sparsely populated. Vacuum removes dead entries and can recycle pages that become completely empty, so the space is reused. It almost never returns space to the filesystem, though, so the index file stays at its high-water mark.

Bloat is worth acting on when it has a measurable cost. Each index page that is mostly empty still takes a buffer in shared memory and the OS page cache, which pushes out hotter data. Range scans read more pages to return the same rows. Physical backups and new replicas copy the full file, including the empty space.

A rebuild alone gives only a temporary improvement if the workload regrows the bloat. Fix the cause as well:

• Fillfactor on the table: a lower fillfactor leaves room on each heap page, which allows more HOT updates. HOT updates skip index writes entirely.
• Fillfactor on the index: a lower index fillfactor reduces page splits on indexes that receive inserts throughout the key range.
• Autovacuum: per-table scale factors and thresholds make vacuum run on hot tables before dead entries accumulate.
• Update and delete patterns: avoid rewriting indexed columns that have not changed. For bulk deletes of old data, partition by time and drop whole partitions instead.

How do you drop or rebuild an index without blocking production?

Before touching anything, record the index definition with pg_get_indexdef('index_name'::regclass) and store it with your change ticket. If a query regresses after the drop, you can rebuild the index exactly as it was instead of reconstructing it from memory.

DROP INDEX CONCURRENTLY waits for in-flight queries to stop using the index instead of taking an exclusive lock on the table. It cannot run inside a transaction block, so migration tools that wrap every step in BEGIN need an exception for it. If it fails partway, the index can be left invalid, and you must run the drop again.

To rebuild a bloated index, REINDEX CONCURRENTLY is the simplest option. It keeps the index name and any constraint attached to it. The manual alternative is CREATE INDEX CONCURRENTLY under a new name, followed by a concurrent drop of the old index and a rename. This gives you control over each step, but it is awkward for indexes that back primary keys or unique constraints.

Both concurrent rebuild methods have costs:

• Disk space: a full second copy of the index exists during the build.
• Runtime: the build scans the table more than once.
• Long transactions: the build waits for every older transaction to finish.
• Invalid leftovers: a failed build leaves an index behind. Find these with pg_index.indisvalid = false and drop them.

Set lock_timeout (for example SET lock_timeout = '5s') before any DDL. A DDL statement waiting for a lock also blocks every query queued behind it. With a timeout, the statement fails quickly instead. Script your retries with backoff.

When should you avoid cleaning up indexes, and what does it save?

The checklist

1. Snapshot pg_stat_user_indexes with a timestamp so later readings can be compared against a baseline.
2. Confirm the stats window covers month-end jobs, reports, and other rare workloads.
3. Check idx_scan on every replica, because the primary's counters don't include reads served there.
4. Exclude indexes that back primary key, unique, or exclusion constraints.
5. Save each definition with pg_get_indexdef(). The saved definition is your recreate script.
6. Drop one index at a time with DROP INDEX CONCURRENTLY.
7. Watch p95/p99 latency and the slow-query log for at least one full business cycle before dropping the next index.

When to hold off

Postpone the cleanup in any of these cases:

• The stats window is short. A recent reset leaves too little history to show what is actually unused.
• Volumes are nearly full. A rebuild or rollback needs room for a second copy of the index.
• You're in peak traffic.
• There is no tested recreate plan.

Rollback is slow. Recreating an index on a large table with CREATE INDEX CONCURRENTLY takes time and I/O, and any regression the drop caused lasts until that build finishes.

Is it worth it?

Illustrative estimate (assumed numbers, not a benchmark). Assume these inputs:

• 40 GB of unused indexes
• 3 copies (primary plus two replicas)
• Storage at $0.10 per GB-month
• 8 engineering hours at $120 per hour

The calculations:

• Monthly savings = size × copies × price = 40 × 3 × 0.10 = $12
• One-time cost = 8 × 120 = $960
• Payback = 960 / 12 = 80 months

Storage alone rarely justifies the effort. The stronger reasons to drop unused indexes are reduced write amplification, faster vacuum, and less WAL.

The checklist also brings the cost down. A scripted, repeatable run turns the next cleanup into an hour of review instead of a day of rushed investigation. It also leaves a record of what was dropped and how to restore it.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql unused indexes, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.