Database Index Maintenance: Bloat, Rebuilding, and Removing Unused Indexes

Many teams treat indexes the same way: ask the DBA to add a few before launch, then forget about them. Only when monitoring alerts report 40% disk growth, a surge in slow queries, and write throughput cut in half does anyone investigate. The postmortem often reveals that an 80GB index contains only 20GB of useful data, three indexes added "just in case" have never been used, and the compound index that was actually needed does not exist.

An index is not a one-time investment. It can bloat, become obsolete, and continuously consume resources. This article breaks index maintenance into three tasks you must perform regularly: detecting bloat, removing unused or redundant indexes, and rebuilding indexes online. All examples use PostgreSQL because PG has the most complete index-maintenance toolchain and is where this methodology originated, but the principles also apply to MySQL and SQL Server.

1. Start with the Right Mental Model: An Index Is a Liability

Many developers instinctively assume that "more indexes are better" because they speed up reads. That is an expensive misconception. The correct model is:

Every index is a liability, not an asset. It trades write speed and storage space for query speed.

Specifically, each additional index on a table means:

• Every INSERT: in addition to writing heap data, the database must write the index's B-tree pages. N indexes mean N+1 writes.
• Every UPDATE to an indexed column: under PG's MVCC mechanism, UPDATE means marking the old row and writing a new row. Every index gets a new pointer to the new row, even if you updated only one column. This is the leading cause of bloat in PG.
• Every DELETE: index entries are not reclaimed immediately. They leave dead tuples until VACUUM cleans them up.
• Storage: indexes consume disk space, enlarge backups, increase replication traffic, and compete for buffer cache capacity.

An index is therefore not something to "create now and worry about later." Whenever you add one, you must explain what query acceleration it provides and whether that benefit justifies the write cost. More importantly, that calculation changes over time. An index used heavily today may become irrelevant after the next release. This is why indexes need regular health checks, not a set-it-and-forget-it approach.

2. Index Bloat: Your Index Is Much Larger Than You Think

2.1 How Bloat Develops

After frequent UPDATE and DELETE operations, PG indexes, specifically B-trees, accumulate fragmentation and dead tuples:

• DELETE leaves gaps;
• UPDATE, under PG's MVCC model, performs a delete followed by an insert. This leaves pointers to old row versions, or dead tuples, in the index, and VACUUM leaves free space in pages after cleaning them up;
• B-tree page splits make the index structure sparse;
• Long-running transactions and an autovacuum frequency that is too low allow dead tuples to accumulate for longer.

The result is that the logical size of the index looks normal, while its physical footprint far exceeds what it should require. A typical symptom is that pg_relation_size is much larger than the theoretically required size, the index depth or level increases, I/O grows, and the cache hit rate declines.

Bloat is not a disease, but it is a chronic drain on performance and cost. The most extreme case I have seen was an index on a 120GB orders table that shrank to 18GB after REINDEX. In other words, 85% of its space consisted of fragmentation and dead tuples. Every second, that space was being read into memory, recorded in WAL, replicated to standby servers, and included in snapshot backups.

2.2 Measure It Precisely with pgstattuple

The PG extension pgstattuple, together with pgstatindex, can directly sample index and table bloat and report the actual percentage of free space.

Install the extension first, once per database:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Check bloat for a particular index, including metrics such as the proportion of free space:

SELECT * FROM pgstattuple('orders_idx_user_id_created_at');

pgstatindex is specifically designed for B-trees and exposes version, tree_level, root_pages, internal_pages, leaf_pages, and deleted_pages:

SELECT * FROM pgstatindex('orders_idx_user_id_created_at');

A high deleted_pages value means many deleted pages have not been reused. A continuously increasing tree_level means the index is becoming deeper and should be rebuilt.

2.3 A Database-Wide Bloat Report

Checking indexes one by one is impractical. The following SQL uses pgstattuple_approx, the faster sampling-based variant, to scan tables in the database and report the ten most bloated objects. It is suitable for regular inspections:

SELECT
    schemaname || '.' || relname       AS relation,
    pg_size_pretty(table_len)          AS total_size,
    ROUND(dead_tuple_percent::numeric, 2) AS dead_pct,
    ROUND(free_percent::numeric, 2)    AS free_pct,
    pg_size_pretty(dead_tuple_len)     AS dead_bytes,
    pg_size_pretty(free_space)         AS free_bytes
FROM pgstattuple_approx('public'::regclass)  -- Replace with the target table, or write a loop
ORDER BY (dead_tuple_len + free_space) DESC
LIMIT 10;

Note: a full scan with pgstattuple is not cheap. In production, run it on a standby, during off-peak hours, or use pgstattuple_approx, which is approximate but fast. pgstattuple_approx supports tables only, not indexes; use pgstatindex to inspect the indexes themselves.

Practical thresholds used internally by my team, for reference only:

• Add the index to the rebuild candidate list when free_percent > 30% or dead tuples plus free space exceed 40% of its physical size.
• Rebuild when tree_level has increased by at least one level from its baseline.
• Do not aim for zero bloat. B-trees inherently need some free space to avoid frequent page splits. The goal is to control abnormal bloat, not pursue perfection.

3. Rebuilding Indexes: REINDEX CONCURRENTLY Is the Only Production Option

Once you detect bloat, rebuilding compacts the index. But how you rebuild matters more than whether you rebuild. Never run a bare REINDEX against a production database.

3.1 Why Regular REINDEX Is Unsafe

-- Never do this in production:
REINDEX INDEX orders_idx_user_id_created_at;

A regular REINDEX acquires an exclusive lock (AccessExclusiveLock). While the table is locked, even SELECT queries cannot enter, effectively removing the table from service for anywhere from seconds to tens of minutes. In a 24/7 production system, this is an incident-level operation.

3.2 REINDEX CONCURRENTLY: Online Rebuilding

PG 12+ provides REINDEX CONCURRENTLY. It works as follows:

1. Create a temporary index with the new definition and a _ccnew suffix alongside the original index;
2. Keep reads and writes flowing: index all existing table data into the new index while every new INSERT, UPDATE, and DELETE maintains both indexes;
3. Perform a final "catch-up" pass to apply changes produced concurrently;
4. Replace atomically: rename the new index to the original index name and drop the old index.

The entire process does not block reads or writes, making it the only approach you should use in production:

-- Correct production approach
REINDEX INDEX CONCURRENTLY orders_idx_user_id_created_at;

-- Rebuild every index on a table
REINDEX TABLE CONCURRENTLY orders;

-- Or rebuild the entire database (use cautiously; it takes a long time, so process tables in batches)
REINDEX DATABASE CONCURRENTLY your_db;

Costs and caveats:

• Storage doubles temporarily: the old and new indexes coexist during the rebuild, requiring free space equal to the size of the original index. Evaluate capacity in advance when disk usage is already high.
• Write amplification increases: every write must maintain two indexes during the rebuild, reducing write throughput. Schedule the operation off-peak.
• The operation can fail: if a unique-constraint conflict or deadlock occurs during rebuilding, CONCURRENTLY may leave an INVALID _ccnew index behind. Always inspect and clean up after rebuilding:

-- Find indexes left behind by failed rebuilds
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;
-- Remove after confirming it is unused (an invalid concurrently rebuilt index must be dropped CONCURRENTLY)
DROP INDEX CONCURRENTLY IF EXISTS orders_idx_user_id_created_at_ccnew;

In practice, rebuilding a large index starting at hundreds of gigabytes with CONCURRENTLY can take hours. Validate the duration on a standby first, then run it on the primary during off-peak hours while monitoring I/O, long-running transactions, and replication lag. If the database is too large to tolerate the write amplification, use a third-party tool such as pg_repack or pg_squeeze, which provides finer control over the rebuild pace.

3.3 When to Rebuild and When Not To

Not every bloated index needs rebuilding. Rebuilding has a cost; it is not a routine chore.

• ✅ Bloat exceeds the threshold and the index is on a hot path, serving frequent queries or showing a clear decline in cache hit rate
• ✅ Index depth, or tree_level, has increased abnormally
• ✅ Routine cleanup is needed before compressing or migrating a large table
• ❌ Dead tuples have just fallen after VACUUM; wait for another two autovacuum cycles and measure again
• ❌ The index should be deleted anyway, as discussed in Section 4; deletion is vastly cheaper than rebuilding

Increasing autovacuum frequency by configuring autovacuum_vacuum_scale_factor separately for large tables can reduce dead-tuple accumulation at the source and lower rebuild frequency. This addresses the root cause more effectively than "running REINDEX regularly."

4. Unused Indexes: The Silent Cost of idx_scan=0

Bloated indexes may need rebuilding, but some indexes are not worth rebuilding at all: indexes that nobody uses. They silently consume space and slow every write without ever serving a query.

4.1 Find Never-Used Indexes with pg_stat_user_indexes

PG's cumulative statistics view pg_stat_user_indexes records how many times each index has been scanned:

SELECT
    schemaname,
    relname        AS table_name,
    indexrelname   AS index_name,
    idx_scan       AS scans,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND idx_scan = 0          -- Zero scans since statistics were last reset
  AND indexrelname NOT LIKE '%_pkey'  -- Exclude primary keys (constraints or foreign keys may depend on them; do not delete casually)
ORDER BY pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 is a suspect that has never been used since creation. Sort by size and inspect the largest first. Removing one unused 20GB index matters much more than removing ten 1MB indexes.

4.2 Three Critical Pitfalls

idx_scan = 0 does not mean "safe to delete immediately." Before taking action, rule out three false positives:

1. Statistics may have been reset. Counts in pg_stat_user_indexes accumulate from the stats_reset timestamp. A recent database restart or manual call to pg_stat_reset() creates a short observation window. Check pg_stat_database.stats_reset first and ensure the window covers at least one complete business cycle, preferably 30 days or more:

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

2. Indexes may serve periodic queries. An index used by month-end reports, quarterly reconciliation, or annual archiving may show idx_scan=0 during normal periods, then become critical later. Evaluate it against the business cycle and observe at least one complete high-activity cycle.

3. Indexes may implicitly enforce constraints or uniqueness. Unique and primary-key indexes perform uniqueness checks on every INSERT and UPDATE even when no query explicitly uses them. Never touch _pkey indexes or indexes backing unique constraints. Verify before deletion:

   -- Confirm that no constraint, foreign key, or CLUSTER operation depends on this index
   SELECT conname, contype
   FROM pg_constraint
   WHERE conindid = 'orders_idx_user_id_created_at'::regclass;
   

   If the query returns a row, do not delete the index. It is the physical implementation of a constraint.

4.3 Drop Indexes CONCURRENTLY Too

Dropping an index also acquires a lock. In production, use:

DROP INDEX CONCURRENTLY IF EXISTS orders_idx_user_id_created_at;

A concurrent DROP does not block reads or writes. After deletion, monitor the system for one or two business cycles to ensure that no hidden query path has degraded, such as ORM-generated SQL that depended on the index. Only then remove it permanently from migration scripts and documentation.

5. Redundant Indexes: (a,b,c) Already Covers (a,b)

More subtle than an unused index is a redundant index. It may be used, but another larger index already provides all of its functionality, making it pure overhead.

5.1 Redundancy Under the Leftmost-Prefix Rule

Compound B-tree indexes follow the leftmost-prefix rule. If you have (user_id, created_at, status), queries beginning with user_id can use it, including:

WHERE user_id = ?                       -- Uses (user_id, ...)
WHERE user_id = ? AND created_at > ?    -- Uses (user_id, created_at, ...)
WHERE user_id = ? AND created_at > ? AND status = 1  -- Uses the complete index

This means that if separate (user_id, created_at) or (user_id) indexes also exist, they are probably redundant. The SELECT optimizer may prefer the narrower index, but writes still pay the cost of maintaining both.

5.2 Query for Finding Redundant Indexes

The following SQL identifies indexes whose prefixes are completely covered by another index:

SELECT
    a.relname        AS table_name,
    a.indexrelname   AS redundant_index,
    b.indexrelname   AS covering_index,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS redundant_size
FROM pg_index a
JOIN pg_index b
  ON a.indrelid = b.indrelid
 AND a.indexrelid <> b.indexrelid
 AND a.indkey <@ b.indkey          -- a's columns are a prefix subset of b's columns
 AND b.indkey @> a.indkey
 AND a.indisunique = false         -- Not unique (unique indexes cannot be removed casually)
 AND b.indpred IS NULL             -- b is not a partial index
 AND a.indpred IS NULL             -- a is not a partial index either
JOIN pg_class c ON c.oid = a.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(a.indexrelid) DESC;

The logic is that a's column set is a subset of b's, using the @> and <@ operators on the indkey arrays; b is not a partial index, because coverage would not necessarily hold otherwise; and a is not unique. Matches are redundancy candidates.

Important caveat: partial indexes and indexes with INCLUDE columns cannot be evaluated with this simple rule. (a) WHERE active = 1 is not equivalent to (a), and INCLUDE columns do not participate in B-tree ordering or count toward indkey matching. Review these indexes manually.

5.3 Another Kind of Redundancy: Exact Duplicates

Sometimes two indexes have identical definitions because developers created them under different names. Query them directly:

SELECT indexrelname, pg_get_indexdef(indexrelid)
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_get_indexdef(indexrelid);

Review the definitions and, when two are identical, keep one and delete the other. This type of redundancy is common in small teams and projects with parallel feature branches, making it an easy win.

6. Account for Write Costs When Adding the Index

Return to the mental model from Section 1: every index is a write liability. Here are concrete numbers.

Suppose a table has ten indexes and receives 5,000 writes per second:

• Each INSERT writes one heap plus ten indexes, for 11 writes;
• Each UPDATE also produces 11 writes because of MVCC, regardless of which column changes;
• If 50% of the indexes are unused or redundant, 25,000 writes per second are pure waste.

Less obvious costs include:

• WAL bloat: every write must be recorded in WAL. More indexes produce more WAL, increasing primary-to-standby replication bandwidth and archive storage costs.
• Cache pressure: unused indexes still occupy the buffer cache, evicting hot data and indirectly reducing the overall cache hit rate.
• Slower autovacuum: VACUUM must clean dead tuples from every index. More indexes make it slower, which further increases bloat and creates a vicious cycle.

The correct process when adding an index is therefore:

1. Analyze the query with EXPLAIN and confirm that no existing index can serve it, avoiding duplication;
2. Determine whether the query benefit justifies the write cost. Read-heavy, write-light tables are good candidates; exercise extreme caution on write-heavy tables;
3. Monitor it in pg_stat_user_indexes for two weeks after deployment. Remove it if idx_scan is lower than expected;
4. Document it in the index inventory or migration comments, including its purpose, so future maintainers can determine whether it is redundant.

Without an index, reads become slower. With an index, writes become slower and storage grows. It is always a trade-off; there is no free lunch.

7. A Practical Regular Index Health-Check Plan

The methods above can be combined into a monthly index health-check process. My team has used it internally for two years, reclaiming an average of 5% to 15% of storage each time and preventing several potential slow-query incidents.

7.1 Health-Check Script (Safe to Run on a Standby)

-- ============ Index Health-Check Report ============
-- Prerequisite: CREATE EXTENSION IF NOT EXISTS pgstattuple;

\echo '\n=== [1] Statistics window check (must be >= 30 days) ==='
SELECT stats_reset,
       now() - stats_reset AS window,
       now() - stats_reset >= interval '30 days' AS ok
FROM pg_stat_database WHERE datname = current_database();

\echo '\n=== [2] Top 20 largest indexes (priority targets) ==='
SELECT schemaname, relname, indexrelname,
       idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

\echo '\n=== [3] Unused indexes (idx_scan=0, excluding primary keys and constraints) ==='
SELECT s.schemaname, s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       pg_get_indexdef(s.indexrelid) AS def
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND i.indisunique = false          -- Not unique
  AND s.indexrelname NOT LIKE '%_pkey'
  AND NOT EXISTS (                   -- Not required by any constraint
      SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid)
  AND s.schemaname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_relation_size(s.indexrelid) DESC;

\echo '\n=== [4] Redundant indexes (covered prefixes) ==='
-- (Use the query from Section 5; omitted here)

\echo '\n=== [5] Bloat detection (run pgstatindex on each of the ten largest indexes) ==='
-- Run this manually for each large index:
-- SELECT * FROM pgstatindex('schema.idx_name');
-- Focus on free_percent / deleted_pages / tree_level

7.2 Priorities: My Team's Red, Yellow, and Green Levels

Prioritize by return on investment. Do not start by rebuilding every bloated index:

7.3 Make It an Organizational Practice

• Run the health-check script once a month, produce a Markdown report, and archive it for trend comparisons. Changes in index size and the idx_scan distribution matter more than absolute values.
• Run an additional check after every major release or large refactor. Query patterns change, and index effectiveness changes with them.
• Have the DBA and backend owner review it together. Only the developers know whether the business feature associated with an index still exists, while only the DBA knows its physical cost. Both perspectives are required for sound decisions.
• Maintain an index inventory: in migration comments, record each index's purpose, associated query, and creation date, then verify it against the inventory during each health check.

Summary

Index maintenance should not be emergency work performed only after something breaks. It should be routine, just like monitoring and alerting. Remember these principles:

1. Indexes are liabilities, not assets. Each one trades write speed and storage for query speed, so evaluate the trade-off carefully.
2. Detect bloat with pgstattuple, focusing on free_percent and tree_level. Do not target zero bloat; control abnormal bloat instead.
3. Always rebuild with REINDEX CONCURRENTLY. Running regular REINDEX in production is self-inflicted damage because it locks the table.
4. Find unused indexes with idx_scan=0, but first ensure the statistics window is long enough and exclude periodic queries and constraint dependencies. Then clean them up with DROP INDEX CONCURRENTLY.
5. Identify redundant indexes using the leftmost-prefix rule. (a,b,c) covers (a,b), so the latter can probably be removed. Review partial indexes and INCLUDE columns manually.
6. Create a monthly health-check script and process findings in this order: remove unused indexes, remove redundant indexes, then rebuild hot indexes. Make index maintenance a standard practice rather than an occasional inspiration.

Key takeaway: creating an index is not the end of an engineering task. It creates a liability that requires continuous auditing. An index you think is helping may be silently consuming write throughput and storage budget. Run regular health checks, remove what is unnecessary, and compact what is bloated so your indexes continue working for you instead of making you work for them.