Database Index Bloat

When your fast lookups become slow because of Swiss cheese.

The idea

A B-Tree Index organizes pointers into tightly packed memory pages (blocks). When you UPDATE or DELETE a row in a database like PostgreSQL, the old index entry isn't physically removed immediately; it's marked as dead. If you update rows frequently, the index pages become full of these dead entries (like holes in Swiss cheese). The index physically grows on disk—this is Index Bloat. When the database tries to scan the index, it has to load all these bloated, empty pages into RAM, ruining performance.

Step 1: A healthy index. Pointers are tightly packed into a single disk page.

How it works (Reindexing)

Autovacuum cleans up dead tuples in tables, but it's very bad at compacting B-Tree indexes. If an index becomes 80% bloat, the only way to reclaim the space and restore performance is to completely rebuild the index from scratch. Standard REINDEX locks the table (taking down your app). REINDEX CONCURRENTLY builds a brand new index in the background without locking, then seamlessly swaps it in.

-- Checking for bloat (using the pgstattuple extension)
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('users_email_idx');

-- If free_percent > 30% or so, rebuild the index safely:
REINDEX INDEX CONCURRENTLY users_email_idx;

Cost

REINDEX CONCURRENTLY is wonderful, but it requires twice the disk space while running (since the old index and the new index exist simultaneously). It also consumes significant CPU and I/O. You should schedule it during off-peak hours.

Watch out for