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

Compare implementing full-text search using built-in RDBMS features (e.g., PostgreSQL tsvector/GIN indexes) versus an external search engine (e.g., Elasticsearch). As a Solutions Architect, recommend one for product search supporting typo-tolerance, faceted filters, and near-real-time updates, and justify your recommendation.

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.

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.

HardTechnical

0 practiced

How do you detect and mitigate index bloat and statistics staleness in a high-write PostgreSQL database? As a Solutions Architect, propose maintenance policies (vacuum, analyze, reindex, pg_repack), scheduling considerations, and how to perform these operations without significant downtime.

EasyTechnical

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

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.