Postgres Index Bloat: Detection and Cleanup

Indexes grow over time even when your table stays the same size. Dead index entries from updates and deletes accumulate silently, inflating index size, degrading scan performance, and wasting I/O bandwidth. This guide explains why bloat happens, how to measure it precisely, and how to reclaim space without taking downtime.

Why indexes bloat

Postgres uses MVCC — every UPDATE writes a new row version rather than modifying in place, and every DELETE marks the old version as dead rather than removing it immediately. Table bloat from this is well understood. What's less obvious is that index entries pointing to dead heap tuples also remain in the index until removed.

When autovacuum runs, it removes dead heap tuples and marks the corresponding index entries as dead too. But it does not immediately reclaim the index pages. Those pages stay allocated, holding nothing useful, until a future index operation reuses them. On a high-churn table — one with heavy UPDATE or DELETE traffic — autovacuum may not keep up, and dead entries pile up faster than they're cleared.

The result: your users_email_idx index is 4 GB but only 1.2 GB of that represents live data. Every index scan, bitmap scan, and index-only scan is reading 3× more pages than necessary.

Measuring bloat with pgstattuple

The pgstattuple extension gives precise per-object statistics. Install it once per database:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Then inspect an index:

SELECT
  index_size,
  dead_leaf_pages,
  dead_tuple_count,
  dead_tuple_len,
  free_space,
  round(100.0 * dead_leaf_pages / nullif(leaf_pages, 0), 1) AS bloat_pct
FROM pgstatindex('users_email_idx');

Key fields:

dead_leaf_pages — leaf pages containing only dead entries. These are entirely wasted I/O.
dead_tuple_count — count of dead index entries across all leaf pages.
free_space — bytes already reclaimed within pages but not returned to the OS.

A bloat percentage above 20–30% on a frequently-scanned index is worth addressing. Above 50% on a large index is a performance emergency.

Estimating bloat without a full scan

pgstatindex() does a full index scan, which can lock shared buffers and take seconds on a large index. For a quick estimate across all indexes, query pg_stat_user_indexes and pg_class:

SELECT
  schemaname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Indexes with a large size but low idx_scan count are either bloated, unused, or both. An unused index that's also large is doubly wasteful: it consumes I/O on writes and never earns it back on reads.

REINDEX CONCURRENTLY: the safe path

The fix for severe index bloat is rebuilding the index from scratch. Plain REINDEX takes a lock that blocks all reads and writes on the table for the duration. For production tables that's rarely acceptable.

REINDEX CONCURRENTLY (available since Postgres 12) rebuilds the index without taking a table lock:

REINDEX INDEX CONCURRENTLY users_email_idx;

Or rebuild all indexes on a table at once:

REINDEX TABLE CONCURRENTLY users;

Under the hood, Postgres builds the new index in the background, then swaps it in. The table remains fully readable and writable throughout. The trade-off: the build takes longer (two table scans instead of one) and consumes more I/O during the rebuild.

Constraints and caveats:

Cannot run inside a transaction block.
If the concurrent reindex is interrupted, it leaves an INVALID index behind. Check for these with SELECT indexname FROM pg_indexes WHERE ... AND NOT indisvalid and drop them manually.
Requires Postgres 12+. On older versions, use CREATE INDEX CONCURRENTLY + DROP INDEX CONCURRENTLY + rename.

Preventing bloat from accumulating

The root cause is usually autovacuum not keeping up. On high-churn tables, the default autovacuum trigger (50 + 0.2 × reltuples dead tuples) fires too infrequently. Tighten it per-table:

ALTER TABLE orders
  SET (autovacuum_vacuum_scale_factor = 0.01,
       autovacuum_vacuum_threshold = 100);

This fires autovacuum when 1% of rows are dead (instead of 20%), keeping dead entries from accumulating. For very large tables (tens of millions of rows), even 1% can be a lot — consider setting autovacuum_vacuum_scale_factor = 0 and using an absolute autovacuum_vacuum_threshold instead.

Quick checklist

Install pgstattuple and run pgstatindex() on your largest indexes.
Bloat > 30% on a frequently-scanned index → schedule REINDEX CONCURRENTLY.
Check for INVALID indexes left by interrupted concurrent reindexes.
Tighten autovacuum thresholds on high-churn tables to prevent accumulation.
Drop unused indexes — they bloat on writes and cost nothing on reads.