Programming Tutorials

  You have a high-throughput PostgreSQL database. You monitor your disk usage and notice your indexes are growing disproportionately to your table data. Query performance is degrading—specifically, Index Scans are becoming I/O bound. You check   pg_stat_user_tables   and confirm that   autovacuum   is running frequently. The natural assumption is that  autovacuum  handles space reclamation. However, in write-heavy environments—especially those with random-access updates or deletes— autovacuum  often fails to shrink the physical footprint of an index. The index becomes "bloated," containing massive amounts of empty space that the OS filesystem still has to cache or read. Historically, reclaiming this space required  REINDEX , which locks the table against writes—a non-starter for 24/7 production systems. The solution introduced in PostgreSQL 12 (and refined since) is  REINDEX CONCURRENTLY . The Root Cause: MVCC and B-Tree Fragmentation To...