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

50 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).

HardTechnical

41 practiced

Evaluate index choices (B-tree, hash, GiST, GIN) for queries over JSONB documents, arrays, and geospatial types in PostgreSQL. For a use case that requires filtering by JSONB keys, indexing text search over arrays, and occasional spatial queries, propose which indexes to create and why.

EasyTechnical

40 practiced

Explain the CAP theorem and give concrete examples of databases or configurations that lean toward CP, AP, and CA (when CA is realistic). For a global payments system, what consistency/availability trade-offs would you recommend and why?

EasyTechnical

55 practiced

Describe horizontal (sharding) vs vertical partitioning vs table partitioning. Provide concrete examples of when each is appropriate, and for a logs ingestion system that receives 500K events/second, which strategy or combination would you propose and why.

MediumTechnical

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

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.