Database Indexing medium
What is 'Index Bloat' in MVCC databases like PostgreSQL?
- A A specialized architectural failure where indexes begin to store massive binary data blobs (LOBs) that were intended for the primary table storage.
- B A condition where frequent updates create 'dead' version entries in the index that cannot be reclaimed until a vacuum or maintenance routine is executed.
- C When the database's internal transaction log overflows and begins writing raw metadata directly into the index storage blocks to prevent data loss.
- D When the total number of indexes on a single table exceeds the database's hard limit, causing the engine to revert to a less efficient storage format.
Answer: B. A condition where frequent updates create 'dead' version entries in the index that cannot be reclaimed until a vacuum or maintenance routine is executed.
In Multi-Version Concurrency Control (MVCC) databases like PostgreSQL, updating a row doesn't overwrite it; it creates a new version. The index must point to BOTH the old and new versions until the old version is no longer visible to any running transaction. If you update rows frequently but don't 'Vacuum' often, the index will fill up with these 'dead' pointers. This 'bloat' makes the index massive and significantly slower, as the database must read more disk pages for every query.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?