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.

HardSystem Design

0 practiced

Architect a reporting solution to support 100 TB of compressed analytical data and 10k dashboard queries per day with many large joins. Compare and justify choices between a partitioned Postgres, columnar MPP (e.g., Redshift), and serverless data warehouses (e.g., BigQuery). Consider concurrency, cost, latency, and ETL patterns.

HardTechnical

0 practiced

You inherit a database with a slow monthly ETL that fails intermittently. Draft a prioritized investigation plan that includes query-level diagnosis (EXPLAIN ANALYZE), OS-level metrics (iostat/cpu), and DB-level views (active sessions, locks). For each step, explain what signs indicate the likely underlying problem (IO, CPU, locking, network).

EasyTechnical

0 practiced

What is a query execution plan and why is it important for diagnosing slow SQL? Describe how you would obtain and interpret an execution plan in PostgreSQL or MySQL. List the key fields you look for in the plan (e.g., cost, rows, actual time) and what they indicate about bottlenecks.

MediumTechnical

0 practiced

You are given this EXPLAIN ANALYZE snippet (abridged):

Seq Scan on events  (cost=0.00..50000.00 rows=1000000 width=40) (actual time=0.1..1200.0 rows=1200000 loops=1)
Hash Join  (cost=... ) (actual time=1200.0..1300.0 rows=...)

As a data analyst investigating a slow report, interpret the key signs in this plan and suggest targeted optimizations that might reduce runtime significantly.

MediumTechnical

0 practiced

A scheduled report shows stale data. As a data analyst, list possible causes (ETL failures, caching, replication lag, permissions) and provide a prioritized troubleshooting checklist. Include queries or checks you would run to determine whether the source tables, materialized views, or dashboard cache is stale.

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.