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.
MediumSystem Design
0 practiced
Design a database schema for a many-to-many relationship between students and courses for a high-enrollment online learning platform. Consider high-concurrency enrollments (write-heavy periods), how to enforce uniqueness, how to support efficient queries (student->courses, course->students), and strategies to avoid join bottlenecks.
HardTechnical
0 practiced
Design an HTAP (Hybrid Transactional/Analytical Processing) architecture that allows low-latency OLTP and real-time analytics without heavy ETL. Discuss storage choices, indexing, query routing, transactional guarantees, resource isolation, and technologies you might recommend (e.g., distributed RDBMS with columnar engine, materialized views, or separate clusters with CDC).
MediumTechnical
0 practiced
Estimate storage, IOPS, and cost for retaining 100GB/day of transactional data for five years, with 90 days hot and the rest archived. As a Solutions Architect, show the steps you would take to size primary storage, backup, and cold storage, and highlight assumptions you make about compression and replication factor.
MediumTechnical
0 practiced
You are given this schema: orders(order_id, customer_id, created_at, total), order_items(order_item_id, order_id, product_id, qty, price). A slow query joining orders and order_items is running. Describe how you would use an execution plan (EXPLAIN) to diagnose the bottleneck and list specific index changes or query rewrites that commonly fix join performance issues.
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.
