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.

HardTechnical

0 practiced

Explain the CAP theorem and how CAP trade-offs manifest in real distributed databases (for example, Cassandra, MongoDB, CockroachDB, Spanner). For a financial payments system vs a shopping-cart analytics system, recommend consistency and availability settings (for example, quorum sizes, synchronous vs asynchronous replication) and justify your choices in terms of user experience and failure modes.

HardTechnical

0 practiced

Describe in detail how nested loop, hash join, and merge join algorithms work. For each algorithm explain time and space complexity, memory requirements, conditions under which a planner chooses it, and how data ordering or statistics influence the choice. Give practical examples showing when each algorithm is optimal and when it results in poor performance.

EasyTechnical

0 practiced

Explain ACID and BASE consistency models in the context of transactional and distributed databases. For each property (Atomicity, Consistency, Isolation, Durability) give a short definition and typical implementation considerations. Then explain what BASE (Basically Available, Soft state, Eventual consistency) means and provide two production scenarios where BASE is acceptable and two where ACID is required. Discuss how these models influence database selection and pipeline design.

MediumTechnical

0 practiced

Explain the concept of partial/filtered indexes and provide an example use case where a filtered index dramatically reduces index size and improves performance. Show a PostgreSQL CREATE INDEX statement for a hypothetical large orders table where only completed orders are frequently queried.

EasyTechnical

0 practiced

Given a PostgreSQL table events(id serial primary key, user_id int, event_type text, occurred_at timestamptz, payload jsonb) that contains duplicate events for the same (user_id, event_type), write SQL to delete duplicates and keep only the latest event per user/event_type. Explain any indexes you would add to support this deletion efficiently on a table with hundreds of millions of rows.

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.