Vacuum Is a Lie: About Your Indexes
2 days ago
- #Database Maintenance
- #PostgreSQL
- #Index Bloat
- VACUUM in PostgreSQL cleans dead tuples but doesn't restructure B-tree indexes, leading to index bloat.
- Index bloat occurs when deleted entries leave empty spaces in index pages, which VACUUM cannot compact.
- Heap (table) data can be compacted by VACUUM because it has no ordering constraints, unlike B-tree indexes.
- REINDEX is the solution for index bloat, rebuilding the index from scratch to reclaim space.
- pg_squeeze is a tool for online table and index maintenance, reducing bloat without significant downtime.
- VACUUM FULL is a last-resort option for bloat removal but locks the table entirely during operation.
- Index bloat can mislead the query planner, causing inefficient query plans and unnecessary I/O.
- Monitoring index bloat is crucial, especially after large deletions or updates affecting index columns.
- The fillfactor setting can help manage index page splits but doesn't solve bloat from deletions.
- Understanding the difference between heap and index bloat is key to effective PostgreSQL maintenance.