What is Index Bloat and When Should You Reindex?
Learn what causes PostgreSQL index bloat and when to use REINDEX or REINDEX CONCURRENTLY to fix it.
Expected Interview Answer
Index bloat is wasted space inside a B-tree index caused by deleted or updated entries whose slots are not fully compacted back, and the fix is to run REINDEX (or REINDEX CONCURRENTLY in production) to rebuild the index from scratch into a compact, freshly balanced structure.
Unlike table heap pages, B-tree index pages only partially reclaim space from VACUUM: dead index entries pointing to removed heap tuples get marked reclaimable, but the page itself may stay partially empty rather than being merged with neighbors, especially under heavy random-key updates that leave many half-full pages scattered across the tree. Over time this means the index consumes far more disk space than the underlying data needs and each index scan touches more pages than necessary, slowing lookups. REINDEX rebuilds the index by scanning the table and writing a brand-new, tightly packed B-tree, discarding all the accumulated slack; REINDEX CONCURRENTLY does this without holding an exclusive lock for the whole duration, making it safe on a live production table at the cost of needing extra disk space temporarily and roughly double the work.
- Shrinks index size back down after heavy churn
- Restores index scan performance by reducing pages touched per lookup
- REINDEX CONCURRENTLY avoids blocking reads and writes during rebuild
- Prevents index bloat from compounding table-level bloat issues
AI Mentor Explanation
Think of a player-lookup card catalog kept in alphabetical order, where retiring a player does not remove their card immediately, it just leaves an empty gap in that alphabetical slot rather than shifting every later card back. After seasons of retirements and transfers, the catalog drawer is mostly gaps with cards scattered thin, so finding a player means flipping through many nearly-empty sections. Reindexing is rebuilding that catalog from scratch in one tightly packed pass, exactly like REINDEX rebuilds a bloated B-tree index into a compact structure.
Index Bloat Before and After REINDEX
Before: bloated B-tree
- many half-empty pages
- dead entries from deletes
- more pages per scan
REINDEX CONCURRENTLY
- scans table
- builds new index
- swaps in atomically
After: compact B-tree
- densely packed pages
- fewer pages per scan
- smaller on-disk size
Step-by-Step Explanation
Step 1
Detect bloat
Compare an index’s actual size against its estimated minimal size using a bloat-estimation query or extension.
Step 2
Decide on downtime tolerance
Choose REINDEX for a maintenance window (fast, but locks) or REINDEX CONCURRENTLY for a live production table (slower, no long lock).
Step 3
Run the rebuild
REINDEX CONCURRENTLY builds a new index alongside the old one, validates it, then swaps it in atomically.
Step 4
Verify and clean up
Confirm the new index is valid and the old bloated one is dropped, then re-check size and scan performance.
What Interviewer Expects
- Understanding that index pages do not compact as fully as heap pages under VACUUM
- Knowledge of REINDEX versus REINDEX CONCURRENTLY and their locking trade-offs
- Ability to describe how to detect index bloat before acting
- Awareness that index bloat can degrade scan performance even when the table itself is healthy
Common Mistakes
- Assuming VACUUM alone fully compacts index pages the way it does the heap
- Running plain REINDEX on a busy production table without considering the lock
- Not verifying the new index size actually shrank after a rebuild
- Ignoring index bloat while focusing only on table bloat
Best Answer (HR Friendly)
“Index bloat is when a B-tree index accumulates a lot of half-empty pages from deletes and updates, so it takes up more space and more pages have to be read per lookup, slowing queries. I would detect it by comparing actual size to expected size, and fix it with REINDEX CONCURRENTLY in production, since it rebuilds a compact new index without holding a long lock on the table.”
Code Example
-- Check index size before rebuilding
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders';
-- Rebuild without a long exclusive lock (safe for production)
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
-- Confirm the new size shrank
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders';Follow-up Questions
- Why doesn’t VACUUM fully compact B-tree index pages the way it reclaims heap space?
- What extra disk space does REINDEX CONCURRENTLY require during the rebuild?
- How would you detect index bloat before it becomes a performance problem?
- What is the difference between REINDEX and REINDEX CONCURRENTLY in terms of locking?
MCQ Practice
1. Why can B-tree indexes bloat even after VACUUM runs?
VACUUM marks dead index entries reclaimable, but pages can remain sparsely filled rather than being merged, causing bloat over time.
2. What is the main advantage of REINDEX CONCURRENTLY over plain REINDEX?
REINDEX CONCURRENTLY builds the new index alongside the old one and swaps it in with minimal locking, unlike plain REINDEX.
3. What is a practical cost of using REINDEX CONCURRENTLY?
Building a new index alongside the old one before swapping requires additional temporary disk space and more total I/O work.
Flash Cards
What is index bloat? — Wasted space in a B-tree index from dead entries and half-empty pages accumulated over updates and deletes.
What command rebuilds a bloated index? — REINDEX, or REINDEX CONCURRENTLY for a production table needing minimal locking.
Why doesn’t VACUUM fully fix index bloat? — It marks dead entries reclaimable but does not always merge or fully compact sparsely filled pages.
What is the cost of REINDEX CONCURRENTLY? — Extra temporary disk space and roughly double the I/O work compared to a locking rebuild.