Btree Index Bloat in PostgreSQL: Measuring It and Fixing It Without Downtime

Every long-lived PostgreSQL installation I have operated has had at least one bloated index quietly eating memory and slowing reads, and nobody noticed for months because nothing errors. The table keeps working, queries keep returning correct results, and the index just keeps growing. Then one day the buffer cache hit ratio slides a few points, p99 latency creeps up, and the incident review ends with someone asking when that index doubled in size. This is the article about not getting to that meeting.

How btree indexes bloat in the first place

PostgreSQL's MVCC means an UPDATE is an insert of a new row version plus a dead old version. Every new version needs entries in every index on the table, and the old entries stay until vacuum removes them. Vacuum cleans up dead index entries, but it rarely shrinks the index: freed space inside pages is reusable, yet pages that become mostly or entirely empty stay attached to the index structure. Over a table with heavy churn — think queue tables, session stores, anything with hot update-and-delete cycles — you end up with an index that is physically huge and logically sparse.

The asymmetry that matters: vacuum marks dead heap space reusable and later writes fill it back in, so table bloat on a steady workload tends to level off — but a btree only gives space back by dropping fully empty pages, and its half-empty pages stay in the tree, sparse. PostgreSQL's empty-page deletion has improved over versions, but there is no general page merging, so a heavily churned index can settle into carrying a third or more wasted space unless you intervene. HOT updates, covered in our piece on HOT updates and fillfactor, reduce index churn at the source, but they do not help indexes on columns you actually update.

Measuring bloat instead of guessing

Never rebuild an index because it "feels bloated". Measure. The pgstattuple extension gives exact statistics by scanning the whole index — for a btree, avg_leaf_density near 100 means tight leaf pages and far below means bloat. That full scan is heavy on big indexes, so aim it at suspects you pick from a cheap size-and-usage pass first:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Exact scan of one suspect: avg_leaf_density near 100 is tight; far below means bloat
SELECT * FROM pgstattuple('orders_created_at_idx');

-- Cheap first pass: the biggest indexes and how much they get used
SELECT 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;

That second query does double duty. Size tells you where the memory is going, and idx_scan tells you whether the index is used at all. An index with idx_scan of zero is a review candidate, not an automatic drop: check when the statistics were last reset, ask whether a rare but critical report depends on the index, and remember that queries on a standby never show up in the primary's counters. Whatever survives that review — outside brand-new deployments and unique-enforcement constraints — is dead weight: it costs write amplification on every insert and update and gives nothing back, so drop it before you rebuild anything. For bloat estimation without pgstattuple, the classic pg_stat_user_indexes plus page-inspection queries work, but honestly, install pgstattuple; it ships with contrib.

Why bloat actually hurts: the cache math

The cost model is simple and brutal. The buffer cache holds pages, not rows. A bloated index spreads the same live entries across twice as many pages, so the working set that used to fit in shared_buffers no longer does. Hit ratio drops, reads go to storage, and latency rises — not because any single query plan changed, but because the same plans now miss cache more often. This is why bloat sneaks up on teams: the degradation is global and gradual, and EXPLAIN never says "your index is sparse".

There is a second cost on the write side. More index pages means more pages vacuum must visit, more WAL when pages split or get their first touch after a checkpoint, and longer index builds when you finally do rebuild. Bloat makes every maintenance operation on the table more expensive, which is why letting it run for a year turns a ten-minute fix into a maintenance-window negotiation.

Deduplication since PostgreSQL 13: the quiet mitigator

PostgreSQL 13 added btree deduplication: when a leaf page fills, entries with the same key are merged into a posting list tuple. On indexes with many duplicate keys — status columns, foreign keys into small dimension tables — this dramatically slows bloat growth and often shrinks indexes outright on rebuild. If you migrated from PG12 or earlier and never rebuilt, your indexes are still in the old physical format: deduplication only happens as pages are rewritten. A REINDEX of your worst duplicate-heavy indexes after upgrading to 13+ is one of the few free lunches in PostgreSQL operations. In 16 and 17 deduplication is on by default for every btree that can use it — it stays off for indexes with INCLUDE columns and for key types where equal-looking values are not safely mergeable, like numeric, jsonb, the floating-point types, and nondeterministically collated text — so the operational question is whether your old indexes ever got the rewrite.

REINDEX INDEX CONCURRENTLY: the standard fix

Since PostgreSQL 12, REINDEX INDEX CONCURRENTLY rebuilds an index without blocking reads or writes on the table. It works like CREATE INDEX CONCURRENTLY under the hood: build a new index alongside the old, take only brief locks to swap them. It cannot run inside a transaction block, it can fail and leave an INVALID index behind, and on a big table it takes a while — but it is the right default tool:

-- Rebuild one index without downtime:
REINDEX INDEX CONCURRENTLY orders_created_at_idx;

-- Check for invalid leftovers from failed concurrent builds:
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE NOT indisvalid;

-- If a build failed, drop the invalid shell and retry:
DROP INDEX CONCURRENTLY orders_created_at_idx_ccnew;

Practical notes from having done this at 2am: run it when write volume is low, because the concurrent build still scans the table and then catches up on changes; watch replication lag if you have replicas, because the build generates WAL; and always check pg_index afterwards, because a failed CONCURRENTLY leaves an invalid index that still costs writes until you remove it.

The old-school swap, for when REINDEX is not enough

Before PG12, and still occasionally today when you want a different storage parameter or a renamed index in one move, the pattern was CREATE INDEX CONCURRENTLY plus a transactional rename-and-drop swap:

CREATE INDEX CONCURRENTLY orders_created_at_new
  ON orders (created_at);

BEGIN;
ALTER INDEX orders_created_at_idx RENAME TO orders_created_at_old;
ALTER INDEX orders_created_at_new RENAME TO orders_created_at_idx;
COMMIT;

DROP INDEX CONCURRENTLY orders_created_at_old;

The rename block takes a strong lock but holds it for milliseconds, and the planner picks up the new index immediately. This trick is also how you change fillfactor or add INCLUDE columns on a hot index without a gap in coverage. Note that CREATE INDEX CONCURRENTLY, like REINDEX CONCURRENTLY, refuses to run inside a transaction — that is why the build is outside the BEGIN block and only the cheap metadata swap is inside it.

Keeping it from coming back

Reindexing fixes the symptom; the causes are workload and vacuum. Aggressive-enough autovacuum keeps dead entries from piling up between reclaims, HOT-friendly fillfactor on churned tables reduces index writes, and dropping unused indexes removes write amplification outright. Put index sizes and pg_stat_user_indexes into your regular review — a monthly look at the top-20 list above catches drift before it becomes an incident.

Bloat is a trend problem, not an event, so the tooling that catches it has to remember history. MonPG monitors PostgreSQL today, and its index-size and idx_scan trends are the review from the last section, except continuous instead of monthly: a bloating index shows up as a growth line you can schedule a REINDEX against, with the buffer cache hit ratio beside it to price the damage. The PostgreSQL index advisor surfaces the unused and underused side of the same data, and there is more measurement background in our guide to table bloat measurement. Rebuild with evidence, not vibes.