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

You notice a sudden increase in replication lag on read replicas after deploying a release. Describe a prioritized checklist to diagnose and mitigate replication lag (e.g., long-running queries on replica, increased write volume, network issues, vacuum). Include immediate mitigations to protect read traffic.

HardSystem Design

0 practiced

Hard scenario: A report joins a transactional OLTP database with a large historical archive in the same DB. Performance is poor and you cannot provision more RAM. Propose an architecture-level approach (schema separation, materialized views, ETL to read-optimized store, or federated queries) to keep OLTP responsive while providing fast historical reporting. Explain migration steps with minimal downtime.

MediumTechnical

0 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.

HardTechnical

0 practiced

Cross-shard transactions are expensive. You're building a banking service where most operations are within a single account but transfers between accounts (possibly on different shards) are required. Describe designs to support consistent transfers across shards: two-phase commit, application-level saga, and atomic transfer via co-located accounts. Discuss pros/cons, latency, failure modes, and which you'd choose for high throughput.

MediumTechnical

0 practiced

You're given this slow query and EXPLAIN (simplified):Query: SELECT p.id, p.name FROM posts p JOIN comments c ON c.post_id = p.id WHERE c.created_at >= '2024-01-01' GROUP BY p.id;EXPLAIN shows a large cost on JOIN with many rows. List a step-by-step diagnostic plan to find the root cause and propose at least three distinct fixes (indexing, query rewrite, schema change).

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.