Database Design and Query Optimization Questions

Principles of database schema design and performance optimization including relational and non relational trade offs, normalization and denormalization, indexing strategies and index types, clustered and non clustered indexes, query execution plans, common table expressions for readable complex queries, detecting missing or redundant indexes, sharding and partitioning strategies, and consistency and availability trade offs. Candidates should demonstrate knowledge of optimizing reads and writes, diagnosing slow queries, and selecting the appropriate database model for scale and consistency requirements.

MediumTechnical

0 practiced

Explain the differences between offset-based pagination and keyset (seek) pagination for UI dashboards. Provide SQL examples for both, discuss performance characteristics as offsets grow, and describe how to implement keyset pagination when ordering by created_at DESC with possible duplicates in created_at.

MediumTechnical

0 practiced

Explain how to use EXPLAIN (FORMAT JSON) output to programmatically detect expensive plan nodes. Sketch an approach (pseudocode or algorithm) that flags suspicious nested loops with high actual loops or nodes where actual_rows >> plan_rows, and explain how you would prioritize fixes for those findings.

EasyTechnical

0 practiced

Describe the difference between clustered and non-clustered indexes (conceptually and practically). Explain how choosing a clustered index affects physical storage, range scans, and write performance, and recommend a clustered key for a time-series fact table used in BI dashboards.

MediumTechnical

0 practiced

Explain why a database optimizer chooses an index scan vs a sequential scan. Provide examples of when forcing an index or scan via hints might help and the risks of forcing a plan. Discuss statistics, selectivity, and partial indexes as ways to influence the optimizer without hints.

MediumTechnical

0 practiced

You're responsible for loading 200M daily rows into a fact table. Describe an efficient bulk load strategy covering: staging approaches, use of COPY/BULK INSERT, disabling/rebuilding indexes, batching and commit size, partition-wise loads, and how to preserve load idempotency and integrity.

Unlock Full Question Bank

Get access to hundreds of Database Design and Query Optimization interview questions and detailed answers.

Join thousands of developers preparing for their dream job.