PostgreSQL Index Bloat: Why It Happens and How to Fix It

"We have an index on that column, why is this still slow?" — because the index isn't broken. It's just fat. An index that used to fit in a handful of pages can silently grow to several times its needed size, and every lookup against it pays for that growth, even though nothing about the query or the schema changed.

The misconception to clear up first

It's tempting to assume VACUUM skips indexes and that's the whole story. It doesn't — a normal VACUUM run does clean dead entries out of B-tree indexes as part of the same pass that cleans the heap (the mechanics of that are in the companion post on VACUUM). The actual cause of index bloat is more specific: page-level fragmentation that VACUUM doesn't undo.

How B-TREE Index actually Bloats

A B-tree index is a tree of fixed-size pages, each holding a sorted set of key entries. When a page fills up and a new entry needs to go in the middle of its key range, Postgres does a page split — the page divides into two, each roughly half full, to make room. That's normal, expected behavior for a growing index.

The problem shows up on the other side: deletes and updates. When rows get deleted (or updated in a way that changes the indexed column), their index entries become dead — VACUUM removes them — but the page they lived on doesn't shrink or merge back with a neighbor. It just sits there, half-empty, still occupying its full page on disk, still something every scan through that key range has to read. A table with heavy delete/update churn — especially time-series data purged on a rolling window, or bulk archival deletes — ends up with an index full of sparsely-populated pages: more pages than the data volume needs, and more disk I/O for every scan that walks through them.

Why "having an index" doesn't save you

An index-backed lookup still has to descend the B-tree and read whatever pages are in its path. A bloated index means:

• More pages per scan, even for a simple point lookup — the tree is taller and wider than the data requires.

• Index-only scans lose their advantage, since they're reading through the same fragmented pages.

• The index's on-disk size grows without a matching growth in row count — a clear tell once you're tracking it. None of this shows up as an error. It shows up as a query that used to take 2ms slowly creeping toward 20ms over months, with nothing in the query or schema to point at.

Measuring it

The pgstattuple extension gives you real numbers instead of guesswork:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
 
SELECT
  avg_leaf_density,
  leaf_fragmentation,
  index_size
FROM pgstatindex('orders_pkey');

avg_leaf_density is the percentage of each leaf page actually holding live data. A healthy index typically sits close to its fillfactor (defaults to 90%); anything drifting down toward 40–50% means a large share of every page you're paying to read is empty space. leaf_fragmentation reports how far leaf pages have scattered from their logical sequential order — high fragmentation adds random I/O on top of the wasted space.

Without the extension, track pg_relation_size() for your key indexes over time and compare it against row count growth in the same table — an index growing meaningfully faster than the table it indexes is the practical, low-effort signal that something needs attention.

Fixing bloat that's already happened

Before you rebuild, confirm the plan is actually paying for it: paste the query's EXPLAIN into PlanReader and check whether buffer-cache-inefficiency fires: https://planreader.dev/?utm_source=kiransabne.dev&utm_medium=blog&utm_campaign=index-bloat&utm_content=reindex-section

Once an index is bloated, cleanup means rebuilding it — VACUUM won't undo fragmentation that already exists, only prevent more of it going forward. Three ways to rebuild, with very different operational cost:

	REINDEX INDEX	REINDEX INDEX CONCURRENTLY	pg_repack
Locking	ACCESS EXCLUSIVE — blocks reads and writes on the index for the whole rebuild	Brief lock only at the final swap	Brief lock only at the final swap
Extra disk space	~2x the index's size, temporarily	~2x the index's size, temporarily	~2x the table + indexes, temporarily
Runs inside a transaction	Yes	No — cannot run inside a transaction block	Runs as its own external tool
Best for	Small indexes, or a real maintenance window	A production index that can't take downtime	Rebuilding whole tables and all their indexes at once, no maintenance window

REINDEX INDEX CONCURRENTLY (fully supported since Postgres 12) is the right default for a production table — it builds a new, tightly-packed index alongside the old one and only takes a brief lock to swap them in. pg_repack goes further, rebuilding an entire bloated table plus its indexes with minimal locking, and is the standard tool when a table itself (not just one index) has bloated badly.

Preventing it going forward

• Lower fillfactor on write-heavy indexes. CREATE INDEX idx_orders_customer ON orders (customer_id) WITH (fillfactor = 90); leaves headroom in each page up front, so updates have somewhere to go before triggering a split. It trades a slightly larger initial index for meaningfully less fragmentation over time on tables with frequent updates to indexed columns.

• Tune autovacuum for high-churn tables, the same way you would for heap bloat — ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05); gets dead index entries cleaned sooner, which limits how much fragmentation accumulates before cleanup even runs.

• Know that Postgres 14+ already helps you here. Bottom-up index deletion proactively removes dead duplicate-key entries during inserts, before they'd otherwise force a page split — it measurably reduces bloat on indexes with lots of repeated key values, but it's a mitigation, not a substitute for periodic rebuilds on genuinely heavy-churn tables. (If you're on an older major version, this protection isn't there yet — one more reason bloat monitoring matters more, not less.)

When to actually reindex — and when not to

Reindex when pgstatindex shows real density loss (well below fillfactor) on an index that's frequently scanned, or when index size has grown noticeably faster than the table's row count. Don't make it a routine cron job across every index in the database — a healthy, low-churn index gains nothing from being rebuilt, and REINDEX CONCURRENTLY still costs CPU, I/O, and temporary disk space while it runs. Measure first; rebuild what the numbers actually show is bloated.

Final thoughts

Index bloat and heap bloat come from the same root cause — MVCC leaving dead entries behind — but they don't get cleaned up the same way. VACUUM keeps dead entries from accumulating; it doesn't undo the page fragmentation those deletes already caused. If a table's write pattern is heavy on updates or deletes, expect its indexes to fragment over time regardless of how well-tuned autovacuum is, and put pgstatindex in your regular monitoring rather than waiting for a query to visibly slow down first.

For the full reference on pgstattuple and index maintenance, see the PostgreSQL documentation.