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.

EasyTechnical

87 practiced

Technical coding: Given the table schema orders(order_id INT PK, customer_id INT, status TEXT, amount DECIMAL, created_at TIMESTAMP) and millions of rows, write a SQL query (Postgres) that deletes orders older than one year in efficient batches of 10,000 rows to avoid long-running transactions. Provide the SQL snippet and explain how you would schedule and monitor the job.

MediumTechnical

56 practiced

Technical coding: Given a 'logs' table with columns (id PK, service_id, level, message, created_at), write a SQL query to compute the 95th percentile response time per service using Postgres' percentile_disc or percentile_cont over a large dataset efficiently. If percentile functions are not available, outline an alternative SQL-based approach.

HardTechnical

50 practiced

Case study: A SaaS analytics product stores daily aggregated customer metrics in a single table that grows to billions of rows. Customers complain reports are slow. You can change schema and indexes but not move to a separate data warehouse in the short term. Propose a priority plan covering schema changes, partitioning, indexes, materialized views, and short-term caching to improve report latency within 3 months.

MediumTechnical

42 practiced

Design an index and query strategy for supporting paginated queries for a large photos table where ordering is by popularity but new rows are frequently inserted. The site needs consistent pagination without missing or duplicated items when popularity changes between pages. Discuss cursor-based pagination, keyset pagination, and the role of stable ordering keys.

HardSystem Design

54 practiced

Hard scenario: A report joins a transactional OLTP database with a large historical archive in the same DB. Performance is poor and you cannot provision more RAM. Propose an architecture-level approach (schema separation, materialized views, ETL to read-optimized store, or federated queries) to keep OLTP responsive while providing fast historical reporting. Explain migration steps with minimal downtime.

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.