Summary

Evidence

• ·PostgreSQL MVCC creates dead tuples on UPDATE and DELETE: these remain in indexes until autovacuum reclaims them
• ·A table with 50% update rate grows index size at ~2x the live data size without aggressive autovacuum
• ·pg_stat_user_indexes.idx_scan / seq_scan ratio degrades as index bloat increases (planner switches to seq scan)
• ·REINDEX CONCURRENTLY rebuilds indexes without dead tuples: commonly required on write-heavy tables quarterly
• ·Heroku's PostgreSQL team documented index bloat as a top-5 production performance issue

Operational Context

• ·Reduce autovacuum_vacuum_scale_factor to 0.01 for high-churn tables: triggers vacuum more frequently
• ·Monitor pg_stat_user_tables.n_dead_tup: sustained high values indicate insufficient vacuum frequency
• ·REINDEX CONCURRENTLY can reclaim 30-50% index space on a bloated table without downtime

Tradeoffs

• ·Aggressive autovacuum consumes I/O and CPU: may contend with production query load during business hours
• ·REINDEX CONCURRENTLY holds an AccessShareLock: does not block reads but does block DDL
• ·Partitioning by time enables DROP PARTITION as an efficient alternative to autovacuum on old data

Evidence grounding

Index bloat from write-heavy OLTP is a well-documented PostgreSQL operational issue, addressed extensively in autovacuum tuning guides and production PostgreSQL operational playbooks.