Index Bloat

Scenario

A DBA investigates a B-tree index on an events table that has grown to 8 GB while the table itself is only 3 GB. The table experiences high DELETE and UPDATE volume — millions of event rows are inserted daily and purged after 30 days. Index scans on this table are slower than expected and query plans sometimes fall back to sequential scans.

How to Identify

Conditions:

• Index size (pg_relation_size on the index) is disproportionate to table size
• pg_stat_user_indexes shows low idx_scan count despite queries that should use the index
• EXPLAIN ANALYZE shows slower-than-expected index scans or unexpected Bitmap Heap Scan fallback
• pgstattuple on the index reveals high dead_tuple_percent
• No REINDEX has been run since the table had heavy churn

Analysis Steps

-- 1. Compare index size vs table size for all user tables
SELECT
    t.relname                                            AS table_name,
    i.relname                                            AS index_name,
    pg_size_pretty(pg_relation_size(t.oid))              AS table_size,
    pg_size_pretty(pg_relation_size(i.oid))              AS index_size,
    round(pg_relation_size(i.oid)::numeric /
          nullif(pg_relation_size(t.oid), 0), 2)         AS index_to_table_ratio
FROM pg_class t
JOIN pg_index ix  ON t.oid = ix.indrelid
JOIN pg_class i   ON i.oid = ix.indexrelid
WHERE t.relkind = 'r'
  AND t.relnamespace = 'public'::regnamespace
ORDER BY pg_relation_size(i.oid) DESC
LIMIT 20;

-- 2. Check index usage statistics
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM   pg_stat_user_indexes
WHERE  relname = 'events_bloat'
ORDER  BY pg_relation_size(indexrelid) DESC;

-- 3. Measure actual index bloat with pgstattuple
-- CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('idx_events_bloat_created_at');

-- 4. Check HOT update ratio to understand if index updates are avoidable
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(n_tup_hot_upd::numeric / nullif(n_tup_upd, 0) * 100, 1) AS hot_pct
FROM   pg_stat_user_tables
WHERE  relname = 'events_bloat';

-- 5. Verify index scan performance after REINDEX
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events_bloat WHERE created_at > now() - INTERVAL '1 day';

Pitfalls

• REINDEX blocks reads AND writes — it takes a ShareLock on the table. Use REINDEX CONCURRENTLY (PostgreSQL 12+) to rebuild without blocking production traffic.
• Index bloat does not self-heal with regular VACUUM — VACUUM marks dead index entries as reusable for future inserts but does not compact the index B-tree structure. Only REINDEX or VACUUM FULL rebuilds the index compactly.
• REINDEX CONCURRENTLY can fail if the new index build encounters errors — the old index remains valid but a leftover INVALID index may appear in pg_indexes. Check with SELECT * FROM pg_indexes WHERE indexdef IS NULL after the operation.
• Unused indexes still incur maintenance overhead on every INSERT/UPDATE/DELETE even when they are heavily bloated.

Resolution Approach

Index bloat accumulates when rows are deleted or updated on indexed columns — B-tree pages fill with dead entries that VACUUM cannot compact. The fix path: