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

Estimate storage, IOPS, and cost for retaining 100GB/day of transactional data for five years, with 90 days hot and the rest archived. As a Solutions Architect, show the steps you would take to size primary storage, backup, and cold storage, and highlight assumptions you make about compression and replication factor.

HardSystem Design

0 practiced

Architect a globally distributed, strongly-consistent transactional system for payments across three regions where transactions must be serializable. Discuss consensus algorithms or services (Paxos/Raft/Spanner-style TrueTime), clock synchronization, latency implications, commit protocols, and alternatives if strict serializability is too costly.

EasyTechnical

0 practiced

Explain the CAP theorem and give concrete examples of databases or configurations that lean toward CP, AP, and CA (when CA is realistic). For a global payments system, what consistency/availability trade-offs would you recommend and why?

HardTechnical

0 practiced

You are investigating a complex slow query that joins four large tables and shows long lock wait times and a high number of rows scanned. The EXPLAIN ANALYZE output shows repeated re-scans and a large nested loop cost. As a Solutions Architect, outline a prioritized remediation plan that includes rewriting queries, index changes, statistics updates, partitioning, and concurrency controls.

MediumTechnical

0 practiced

When denormalizing data in a relational database to optimize reads, how do you maintain consistency between normalized source data and denormalized copies? As a Solutions Architect, compare using database triggers, application-level updates, background workers, and stream-based change data capture (CDC) solutions.

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.