Scenario
Index scan times are increasing disproportionately to table size — B-tree index pages have accumulated dead entries from updates and deletes that VACUUM reclaims on the page level but does not compact across the tree structure.…
Investigation Path
Index scan times are increasing disproportionately to table size — B-tree index pages have accumulated dead entries from updates and deletes that VACUUM reclaims on the page level but does not compact across the tree structure.
Diagnose it
-- Indexes that have never or rarely been scanned relative to their size:
-- (high size + low recent scans may indicate bloat from past heavy writes)
SELECT s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan,
s.last_idx_scan, -- PostgreSQL 16+
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
pg_size_pretty(pg_total_relation_size(s.relid)) AS table_total_size
FROM pg_stat_all_indexes s
WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;For precise bloat measurement, the pgstattuple extension provides
pgstatindex() which reports leaf_fragmentation — this is the
authoritative measure. The query above is a low-overhead screen to identify candidates.
Why it happens
B-tree indexes accumulate partially-empty pages when rows they indexed are deleted or updated.
VACUUM marks the dead index entries as reusable but does not merge pages or reduce the index
tree depth. After years of heavy churn, an index can be many times larger than a freshly built
equivalent, with many nearly-empty leaf pages causing extra I/O per scan.
How to fix it
-- Rebuild concurrently (no read/write downtime in PostgreSQL 12+):
REINDEX INDEX CONCURRENTLY schema.index_name;
-- Or rebuild the whole table's indexes at once:
REINDEX TABLE CONCURRENTLY schema.table_name;REINDEX CONCURRENTLY requires PostgreSQL 12+. Monitor progress in
pg_stat_progress_create_index during the operation.
Prevent it next time
Schedule periodic REINDEX CONCURRENTLY for the largest, most frequently updated
indexes after major bulk operations. Consider increasing index fillfactor from
the default 90 to leave room for HOT updates without page splits on write-heavy tables (guidance).
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.