Index Maintenance and Bloat: Keeping Access Paths Cheap #

An index that was perfect on the day it was created can quietly become the reason a query got slow, without a single line of SQL changing. Pages fill with dead entries, the tree grows taller and wider, the planner’s cost estimate for scanning it rises, and eventually the optimizer walks away from the index altogether.

This page covers what bloat actually is in a PostgreSQL B-tree, how it reaches the cost model, how to measure it honestly, and how to repair it without taking the table offline. It is the operational counterpart to the design work in the index tuning and strategy section.

How Bloat Reaches the Cost Model #

PostgreSQL never updates a row in place. An UPDATE writes a new heap tuple and new index entries pointing at it, while the old entries remain until vacuum can prove no transaction still needs them. A DELETE leaves entries behind the same way. Between vacuum cycles an index therefore accumulates pointers to tuples nobody will ever read.

Vacuum removes those entries and marks the space reusable, but it does not return leaf pages to the operating system unless a page becomes completely empty and can be recycled. The result is an index that occupies far more pages than its live key count needs. Three consequences follow, in order of how quickly you notice them:

  1. More buffer reads per scan. A range scan touching 400 keys walks more leaf pages when each page is half empty, so EXPLAIN (ANALYZE, BUFFERS) shows a higher shared hit/read count for identical results.
  2. A higher planner cost. The cost of an index scan includes a term proportional to the index’s page count, taken from pg_class.relpages and refreshed by ANALYZE. A doubled page count roughly doubles that term.
  3. A different plan. Once the inflated index cost crosses the cost of a sequential scan or of a competing index, the planner switches, and the query’s shape changes with no code change to blame.
How dead entries turn into extra page reads Two rows of leaf pages. The healthy index packs live keys densely into three pages. The bloated index spreads the same live keys across six pages interleaved with dead entries, so a range scan must read twice as many pages. Healthy index — avg_leaf_density ≈ 90% 3 leaf pages read Bloated index — same live keys, avg_leaf_density ≈ 42% 6 leaf pages read relpages doubles → index scan cost doubles live entries dead / free space

Reading Bloat Signals in a Plan #

Bloat rarely announces itself. It shows up as a slowly rising buffer count for a query whose row counts never changed:

-- Six months ago
Index Scan using events_account_created_idx on events  (actual time=0.08..3.42 rows=412 loops=1)
  Index Cond: (account_id = 91 AND created_at > '2026-01-01')
  Buffers: shared hit=9 read=2                 -- 11 index+heap pages for 412 rows

-- Today, identical query, identical row count
Index Scan using events_account_created_idx on events  (actual time=0.09..11.87 rows=412 loops=1)
  Index Cond: (account_id = 91 AND created_at > '2026-01-01')
  Buffers: shared hit=31 read=27               -- 58 pages for the SAME 412 rows

The row counts are identical and the estimate is fine. Only the page count moved, which is exactly the fingerprint of bloat rather than of a statistics or cardinality problem. Two other signals are worth knowing:

Measuring Bloat Honestly #

Size alone proves nothing — a large index may be legitimately large. Two measurements together are conclusive: the density inside the leaf pages, and the size of a freshly built equivalent.

-- 1. Real density, from the pgstattuple extension (samples the whole index)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT index_size, avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('events_account_created_idx');

avg_leaf_density is the percentage of each leaf page occupied by live entries. A freshly built B-tree sits near 90% because the default fillfactor is 90. Below roughly 50% you are reading two pages for every one you need. leaf_fragmentation reports how far the physical page order has drifted from logical key order, which matters for range scans that expect sequential access.

-- 2. Size against usage, per index, ordered by wasted potential
SELECT s.relname AS table,
       s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan,
       s.idx_tup_read
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE NOT i.indisprimary
 ORDER BY pg_relation_size(s.indexrelid) DESC
 LIMIT 20;

An index with a large size and idx_scan = 0 is not a bloat problem; it is dead weight that still costs every INSERT and every vacuum pass. Drop it rather than rebuild it.

Deciding what to do with an index A two by two matrix. Vertical axis is scan count, horizontal axis is leaf density. High usage with low density means reindex. High usage with high density means leave alone. Low usage with low density means drop. Low usage with high density means review before dropping. idx_scan avg_leaf_density → low high REINDEX CONCURRENTLY hot index, half-empty pages Leave it alone healthy and earning its keep DROP INDEX unused and wasting write bandwidth Review before dropping may serve constraints or rare reports

Repairing Without Downtime #

REINDEX INDEX CONCURRENTLY builds a replacement in a second pass over the table, then swaps the two under a short lock. Reads and writes continue throughout. The trade-offs are real but manageable:

-- Rebuild one index online
REINDEX INDEX CONCURRENTLY events_account_created_idx;

-- Everything on one table (PostgreSQL 12+)
REINDEX TABLE CONCURRENTLY events;

-- Clean up after an interrupted run
SELECT indexrelid::regclass AS invalid_index
  FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY events_account_created_idx_ccnew;

Prevention is cheaper than repair. For an index whose keys are updated frequently, a lower fillfactor leaves room for the new versions to land on the same page, which enables HOT updates and slows bloat accumulation. For append-only keys such as a timestamp or a bigint sequence, the default fillfactor of 90 is already right and lowering it just wastes space.

Step-by-Step Maintenance Workflow #

  1. Baseline the query, not the index. Record EXPLAIN (ANALYZE, BUFFERS) for the affected statement so you can prove the repair changed something.

  2. Rank candidates by size and usage with the pg_stat_user_indexes query above. Ignore indexes below a few hundred megabytes; the rebuild will not pay for itself.

  3. Confirm real bloat with pgstatindex. Act on avg_leaf_density below about 50%, not on size alone.

    SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('events_account_created_idx');

  4. Check for a cheaper answer first. An unused index should be dropped. A duplicate index — one whose column list is a prefix of another’s — should be dropped rather than rebuilt; the covering index design page explains when a wider index makes a narrower one redundant.

  5. Rebuild online during a low-traffic window, one index at a time, watching pg_stat_progress_create_index for progress.

  6. Refresh statistics and re-measure. ANALYZE updates relpages so the planner sees the smaller index immediately:

    ANALYZE events;
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;   -- expect the Buffers count to fall

  7. Automate the detection, not the rebuild. Track index size over time and alert on growth outstripping row growth; scheduled blind rebuilds burn I/O for no benefit.

Common Pitfalls #

Treating size as bloat. A large index over a large table may be perfectly dense. Diagnostic signal: high pg_relation_size but avg_leaf_density near 90%. Fix: measure density before scheduling any rebuild.

Running plain REINDEX on a busy table. The non-concurrent form takes a lock that blocks writes for the whole rebuild. Diagnostic signal: a spike of lock waits in pg_stat_activity during maintenance. Fix: always use CONCURRENTLY on live systems.

Leaving invalid _ccnew indexes behind. An interrupted concurrent rebuild leaves an index that is never used for reads but is still maintained on every write. Diagnostic signal: rows in pg_index with indisvalid = false. Fix: drop them explicitly as part of the runbook.

Rebuilding an index the planner has already abandoned. If a query stopped using the index months ago for a different reason, a rebuild changes nothing. Diagnostic signal: idx_scan flat while table traffic grew. Fix: find out why the plan flipped first — stale statistics and predicate mismatches are more common causes than bloat.

Confusing table bloat with index bloat. A bloated heap inflates sequential scans and index heap fetches, and no amount of reindexing helps. Diagnostic signal: pgstattuple on the table shows a low tuple_percent. Fix: address vacuum settings, or use VACUUM FULL/pg_repack on the heap during a maintenance window.

Dropping an index that enforces a constraint. A unique or exclusion constraint owns its index; dropping it fails or removes the constraint. Diagnostic signal: pg_index.indisunique or a matching row in pg_constraint. Fix: drop the constraint deliberately, or rebuild the index instead.

Letting duplicate indexes accumulate. Migrations, framework generators, and well-meaning fixes leave overlapping definitions: an index on (a) beside one on (a, b), or two indexes differing only in direction. Every duplicate is maintained on every write and scanned by every vacuum, and the planner has to consider both. Diagnostic signal: two indexes on the same table whose column lists share a prefix. Fix: drop the narrower one, after confirming it does not back a constraint.

Rebuilding on a schedule instead of on evidence. A nightly reindex job burns I/O and WAL every night regardless of whether anything degraded, and on append-only indexes it accomplishes nothing at all. Diagnostic signal: maintenance windows dominated by rebuilds of indexes whose density was already near the fillfactor. Fix: measure first and rebuild only what the measurement justifies.

Forgetting that indexes must be vacuumed too. Each vacuum cycle scans every index on the table to remove entries pointing at dead heap tuples, so index count directly drives vacuum duration. A table with fifteen indexes takes roughly three times as long to vacuum as the same table with five. Diagnostic signal: vacuum cycles lengthening after new indexes are added, with no change in write volume. Fix: treat index count as a maintenance budget, not a free optimisation.

Overlooking the write amplification of a wide index set. Every insert writes one entry per index plus the WAL to describe it, and an update that changes an indexed column writes a new entry in that index. Ten indexes turn one row insert into eleven page modifications. Diagnostic signal: write latency climbing while read plans stay unchanged. Fix: audit the index set against actual query predicates, then drop what nothing uses.

Deferring maintenance moves cost into every query A rising curve of pages read per query as bloat accumulates, with a marked rebuild point after which the curve drops back to its baseline. time since last rebuild → pages per query REINDEX CONCURRENTLY 10 pages 58 pages

Assuming a fresh restore has no bloat. A dump-and-restore rebuilds every index compactly, which is why a benchmark on restored data often fails to reproduce a production problem at all. The bloat returns at the rate the workload generates it, so the useful measurement is always on the live system. Diagnostic signal: a staging environment where the query is fast and stays fast. Fix: measure density on production and compare against a freshly built copy there.

Measuring bloat and never acting, or acting and never measuring. Both failure modes are common and both waste the same effort. A dashboard nobody responds to is decoration, and a monthly rebuild job with no density check burns I/O on indexes that were already compact. Diagnostic signal: either a bloat metric with no alerting threshold, or a maintenance job with no precondition. Fix: connect the measurement to the action — alert on density crossing a threshold, and make the rebuild job read that threshold rather than the calendar.

Frequently Asked Questions #

Does index bloat change which plan the optimizer picks? Yes. The index-scan cost includes a term proportional to pg_class.relpages, so a bloated index is costed as more expensive than a compact one holding the same keys. Enough bloat tips the comparison toward a sequential scan or a competing index, and the extra pages also cost real buffer reads at execution time.

How much bloat is worth acting on? Below about 20% free space a B-tree is behaving as designed, since it deliberately leaves room for inserts. Treat 20–40% as worth monitoring, and above roughly 50% — or when the index is several times the size of a fresh rebuild — a REINDEX CONCURRENTLY usually pays for itself.

Is REINDEX CONCURRENTLY safe on a live table? It is built for that case: a replacement index is created alongside the original and swapped under a brief lock, so reads and writes continue. It needs space for both copies, does more total work than a plain rebuild, and can leave an invalid index behind if interrupted.

Should I lower fillfactor to prevent bloat? Only on indexes over frequently updated columns, where leaving free space on each page lets new tuple versions stay on the same page. For append-only keys such as timestamps or sequences, a lower fillfactor simply makes the index bigger and slower to scan.