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