Database Indexing medium
When is a Partial Index (`CREATE INDEX ... WHERE active=true`) a superior choice?
- A When you need to index a small subset of 'hot' data (e.g., Unprocessed Jobs) while ignoring millions of unrelated historical or archived records.
- B In a development environment where you want to minimize the physical disk footprint of the index by only including every tenth record from the table.
- C When implementing large-scale Sharding architectures that require different indexes for various geographic partitions of the same logical dataset.
- D When you must enforce a 'Unique' constraint across the entire table but only have enough storage space to index the most recently inserted records.
Answer: A. When you need to index a small subset of 'hot' data (e.g., Unprocessed Jobs) while ignoring millions of unrelated historical or archived records.
A Partial Index (supported by Postgres) uses a `WHERE` clause during creation. For example, if you have a `jobs` table where 99.9% of rows are 'Completed', indexing them all is a waste of RAM and disk. By creating an index only `WHERE status = 'PENDING'`, you keep the index extremely small, making it more likely to stay cached in RAM and significantly speeding up the queries that actually matter for your application's current operations.
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')?