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

Design a backup and disaster recovery (DR) strategy for a large OLTP database with RTO < 15 minutes and RPO < 1 hour. Discuss types of backups (snapshot, WAL shipping), replication topology (hot/warm/cold standby), restore testing, and automation for failover.

HardTechnical

0 practiced

Implement a PostgreSQL PL/pgSQL stored procedure that creates a new partition for a partitioned table and rebalances/moves rows older than a threshold from the hot partition into the new one in small batches to avoid locks. Provide pseudocode or real PL/pgSQL focusing on correctness and minimal downtime.

MediumTechnical

0 practiced

A complex report query uses many CTEs and performs worse than an equivalent flattened query. Explain why CTEs can hurt performance in some databases and describe optimization approaches you would try to improve execution time.

MediumTechnical

0 practiced

Given these tables and a heavy analytic query joining orders and order_items:

orders(id BIGINT PRIMARY KEY, created_at TIMESTAMP, status TEXT, customer_id BIGINT)order_items(id BIGINT PRIMARY KEY, order_id BIGINT, product_id BIGINT, price NUMERIC)

The query filters recent orders and groups by order id to compute totals. Propose concrete indexes (CREATE INDEX statements) to speed up the JOIN and aggregation, and justify them.

EasyTechnical

0 practiced

Write a PostgreSQL query that computes a rolling 7-day average of 'value' per user, using window functions. Schema:

events(user_id BIGINT, event_ts TIMESTAMP, value NUMERIC)

Return user_id, event_ts, rolling_avg_7d. Pay attention to time gaps (use a frame definition appropriate for time-based rolling windows).

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.