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

A production database shows slow transactional throughput. Describe the methodical steps you would take to determine whether the cause is CPU, disk IO, network, lock contention, or poor query plans. List specific metrics, system views, and tools you would use and what thresholds or signals you would look for.

HardTechnical

0 practiced

Describe a zero-downtime schema migration plan for a large table with 1B rows that requires adding a new column with a non-null default and an index on it. Explain steps to perform this migration safely, ways to backfill data incrementally, and how to maintain compatibility with older application versions during the migration.

EasyTechnical

0 practiced

Explain the difference between clustered and non-clustered indexes. As a Solutions Architect, recommend which type to use for a large orders table where queries frequently filter by order_date range and also by customer_id, and justify how that choice impacts storage, inserts, and range queries.

MediumTechnical

0 practiced

Given a slow ad-hoc analytical query, describe how to detect missing indexes using database metadata and system views. Provide an outline of SQL queries or steps (for PostgreSQL or MySQL) you would run to identify candidate missing indexes and how to rank them by potential benefit.

EasyTechnical

0 practiced

You must decide between a relational database and a document database for storing user profiles with variable, nested attributes and frequent reads with occasional updates. As a Solutions Architect, list evaluation criteria and recommend one model, describing schema examples, indexing strategies, and how you would handle queries that filter by nested attributes.

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.