Database Indexing medium
What is the result of setting a lower `FILLFACTOR` (e.g., 70% instead of 100%) on an index?
- A It enables a specialized background compression routine that automatically removes all duplicate or redundant entries from the index structure.
- B It leaves intentional empty space within each B-Tree page, allowing future inserts and updates to be performed without causing immediate Page Splits.
- C It significantly improves the database's initial read performance by ensuring that every page on the physical disk is packed to its absolute maximum capacity.
- D It acts as a preventive measure against database deadlocks by ensuring that the internal row-level locks are spread across a larger number of physical storage blocks.
Answer: B. It leaves intentional empty space within each B-Tree page, allowing future inserts and updates to be performed without causing immediate Page Splits.
A `FILLFACTOR` of 100 means the B-Tree pages are completely full. This is ideal for static, read-only data. However, for tables with frequent inserts, a full page means any new record will trigger an expensive 'Page Split' immediately. By setting a lower Fill Factor (e.g., 70-80%), you provide 'cushion' space for new data. This reduces write-latency and index fragmentation at the cost of using slightly more disk space and RAM.
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')?