Indexing Strategy and Selection Questions

Covers index design principles and practical selection of indexes to accelerate queries while managing storage and write cost. Topics include index types such as B tree hash and bitmap indexes and full text and functional indexes; single column composite and covering indexes; clustered versus nonclustered index architectures and partial or filtered indexes. Candidates should reason about index selectivity and cardinality and how statistics and histograms influence optimizer choices. Also assess index maintenance overhead fragmentation and rebuild strategies and the trade off between faster reads and slower inserts updates and deletes. Practical skills include reading execution plans to identify missing or inefficient indexes proposing index consolidation or covering index designs testing and benchmarking index changes and understanding interactions between indexing partitioning and denormalization.

EasyTechnical

0 practiced

Explain the difference between a clustered index and a nonclustered index. Use an orders table example where queries often filter by customer_id but the primary key is order_id. Discuss implications for physical layout, read performance, and insert/update cost, and name two databases with different clustered-index semantics.

HardTechnical

0 practiced

Postgres misestimates a query because two columns are correlated. Explain how multi-column (extended) statistics can help. Provide the SQL to create such statistics and describe how the planner uses them to improve cardinality estimates.

EasyTechnical

0 practiced

Define index selectivity and cardinality. As a data engineer, how do these concepts affect index choice for OLTP vs OLAP workloads? Give an example of a low-cardinality column and explain why an index on it might be ineffective.

HardSystem Design

0 practiced

Design an index testing harness to run before deploying index changes: include workload capture (pg_stat_statements or query logs), replay strategy, warm-up, divergence detection, and automated analysis of P50/P95 latency and write throughput. Describe how you would integrate this into PRs.

HardTechnical

0 practiced

As a senior data engineer, design an indexing governance policy for a large organization: include review process for adding indexes, metrics to require (usage, maintenance cost), CI checks, naming conventions, and an approval workflow. How would you enforce and measure compliance?

Unlock Full Question Bank

Get access to hundreds of Indexing Strategy and Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.