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.

HardSystem Design

55 practiced

Explain how to design for multi-region read replicas providing low-latency reads while preserving global consistency for profile changes. Cover replication lag mitigation, read routing, conflict resolution for multi-master vs single-master, and user experience considerations for slightly stale reads.

HardTechnical

56 practiced

Design a safe roll-out strategy for adding a large compound index that will rebuild a multi-hundred-gigabyte table and is expected to take hours. Consider online index creation, resource throttling, maintenance windows, and fallback. Describe monitoring signals that indicate it is safe to proceed or to abort.

HardTechnical

42 practiced

Problem-solving: A single query produces an explosion of temporary files and high IO causing other queries to degrade. Outline a strategy to diagnose why temp files are used (sorts, hashes), tune the database to reduce temp IO (memory settings, work_mem, temp_tablespaces), and provide a safe plan to apply changes in production.

EasyBehavioral

52 practiced

Behavioral: Describe a time you identified a production database performance regression. Explain how you diagnosed the issue (tools, metrics), what immediate mitigations you applied to reduce customer impact, and what long-term fixes you implemented to prevent recurrence. Use the STAR format.

MediumTechnical

43 practiced

Explain eventual consistency vs strong consistency in the context of a distributed database. Provide concrete examples of client-visible anomalies for eventual consistency (stale reads, lost updates) and techniques to mitigate them (read-your-writes, causal consistency, vector clocks). Give guidance on when to accept eventual consistency and when to require strong consistency.

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.