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.

EasyBehavioral

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

HardTechnical

0 practiced

Security/case question: A database compromise exposed schema and some user PII. Outline immediate containment steps, data access audit, disclosure considerations, and long-term changes to prevent future leaks (encryption at rest, column-level encryption, least privilege, query logging). Include database-side and organizational actions.

HardTechnical

0 practiced

You're responsible for migration of a live relational DB schema: adding a NOT NULL column with a default to a 2 TB table without table rewrite or long locks. Explain step-by-step how you'd perform a zero-downtime migration, including schema change sequence, background backfills, and how you'd handle application code rollout to tolerate both old and new schemas.

MediumTechnical

0 practiced

You have two tables: users(id PK, country), sessions(id PK, user_id FK, started_at, duration). A customer reports a query that joins users->sessions filtered by country and time range is slow. Original query:SELECT u.country, count(*) FROM users u JOIN sessions s ON u.id=s.user_id WHERE u.country='US' AND s.started_at >= '2024-01-01' GROUP BY u.country;Propose 3 concrete optimizations (query rewrite, indexes, physical schema changes) and justify each considering write cost.

MediumTechnical

0 practiced

Describe the trade-offs between synchronous and asynchronous replication for a primary-read-replica database topology. Provide recommendations for three scenarios: (a) financial ledger requiring strict durability, (b) global read scaling for a news website, (c) analytics platform ingesting high volume events. Mention RPO/RTO implications and failover considerations.

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.