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

54 practiced

Given this SQL (PostgreSQL) snippet, propose indexes and explain why:

SELECT p.id, p.titleFROM posts pJOIN post_tags pt ON pt.post_id = p.idJOIN tags t ON t.id = pt.tag_idWHERE t.slug = 'backend' AND p.published = trueORDER BY p.published_at DESCLIMIT 20;

State exact indexes you would create and why.

Sample Answer

**Approach (brief)** I’d create indexes that support the WHERE on tags.slug, the join columns, and the ORDER BY + LIMIT on posts so the planner can use an index scan and avoid sorting/large joins.

**Exact indexes to create**- On tags.slug (unique if appropriate):

sql

CREATE UNIQUE INDEX IF NOT EXISTS tags_slug_uq ON tags (slug);

- On post_tags for the join from tag -> posts:

sql

CREATE INDEX IF NOT EXISTS post_tags_tag_id_post_id_idx ON post_tags (tag_id, post_id);

- On posts to filter by published and return recent rows (covering index to avoid lookup):

sql

CREATE INDEX IF NOT EXISTS posts_published_published_at_idx ON posts (published, published_at DESC) INCLUDE (id, title);

**Why these**- tags_slug_uq: quickly finds the tag id for slug = 'backend' (very selective); uniqueness also helps planner.- post_tags (tag_id, post_id): allows finding matching post_ids for a tag fast and supports an index-only scan of the join from t -> pt -> p.- posts (published, published_at DESC) INCLUDE (id, title): filters on published first, then provides rows already ordered by published_at DESC so ORDER BY + LIMIT 20 can be satisfied by the index without an extra sort; INCLUDE makes the index covering so the query can return id/title from the index without visiting the table.

**Notes / trade-offs**- If posts.published is low-selectivity, consider (published_at DESC) alone plus a bitmap/merge; test with EXPLAIN ANALYZE.- Maintain these indexes incurs write cost; monitor pg_stat_user_indexes and re-evaluate if workload changes.

MediumTechnical

84 practiced

You need to speed up an analytics dashboard that frequently runs complex JOINs across large tables. Discuss whether to use materialized views, summary/aggregate tables, or an OLAP store (columnar DB). For each option list the impact on freshness, storage, complexity, and query latency.

EasyTechnical

70 practiced

You see a slow SELECT in production. Describe the steps you would take to diagnose the issue using EXPLAIN/EXPLAIN ANALYZE (or the equivalent), what parts of the plan you inspect first, and what quick changes you might try to improve the query latency without changing application code.

Sample Answer

**Approach — quick triage**

1. Reproduce and capture the query; run EXPLAIN (or EXPLAIN ANALYZE) against the same parameters / sample load.2. Compare cold vs warm cache (EXPLAIN ANALYZE vs EXPLAIN VERBOSE) to see planning vs execution cost.

**Example**

sql

EXPLAIN ANALYZE
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = true AND o.created_at > '2026-01-01';

**What I inspect first**- Top-most node: is it Seq Scan, Index Scan, Bitmap Heap Scan, or Hash Join?- Estimated vs actual rows — large mismatch signals bad statistics or wrong planner choice.- Rows removed by filter and join order.- I/O vs CPU time per node; look for expensive sorts, large table scans, or repeated nested-loop lookups.

**Quick fixes without app changes**- Add or adjust indexes (covering index for WHERE + JOIN columns).- RUN ANALYZE / VACUUM to refresh statistics.- Rewrite query using hints or reformulate joins (e.g., force hash join) if supported.- Create a small partial index for common predicates (e.g., WHERE active = true).- Increase work_mem temporarily to avoid disk-based sorts/hash.- Add or tune composite index to be covering to avoid lookups.

**Why these steps**- Indexes and stats often fix planner decisions; work_mem and partial/covering indexes reduce I/O and eliminate expensive operations quickly without changing application code.

EasyTechnical

75 practiced

Given two scenarios, choose between SQL (relational) and a NoSQL (document or key-value) database: (A) Shopping-cart session store with frequent short-lived updates and high write throughput; (B) User profile storage with complex relational queries and ACID requirements. Justify your choices for each and mention caching options you might add.

HardTechnical

51 practiced

Given this SQL (Postgres) query that uses a correlated subquery and is slow for 1M rows, rewrite it to be performant using joins, CTEs, or window functions. Explain why your rewrite is faster and what indexes you'd add.

Original:SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id AND o.created_at > now() - interval '30 days') AS recent_ordersFROM users u;

Sample Answer

**Approach (brief)**

Replace the correlated subquery with an aggregate over orders joined to users (or a window/CTE) so the database can hash/merge join and aggregate once instead of running a subquery per user.

**Rewritten query (performant)**

sql

-- Using LEFT JOIN + GROUP BY
SELECT u.id,
       COALESCE(counts.recent_orders, 0) AS recent_orders
FROM users u
LEFT JOIN (
  SELECT o.user_id, COUNT(*) AS recent_orders
  FROM orders o
  WHERE o.created_at > now() - interval '30 days'
  GROUP BY o.user_id
) counts ON counts.user_id = u.id;

Alternative with window function if you need ordering or more columns:

sql

SELECT DISTINCT u.id,
       COALESCE(o_cnt.recent_orders, 0) AS recent_orders
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) OVER (PARTITION BY user_id) AS recent_orders
  FROM orders
  WHERE created_at > now() - interval '30 days'
) o_cnt ON o_cnt.user_id = u.id;

**Why this is faster**

- Correlated subqueries run per row; cost is O(users * cost_of_index_scan). The join+aggregation scans orders once, groups by user_id, then joins — far fewer repeated operations.- The DB can use a single sequential/ index scan and an efficient hash or sort-aggregate.

**Indexes to add**

- Composite index on orders(created_at, user_id) or on (user_id, created_at). For this WHERE plus GROUP BY pattern: - Preferred: CREATE INDEX idx_orders_created_at_user_id ON orders (created_at DESC, user_id); - Or: CREATE INDEX idx_orders_user_id_created_at ON orders (user_id, created_at DESC);- Consider partial index for 30-day window if queries always filter recent range: - CREATE INDEX idx_orders_recent_user ON orders (user_id) WHERE created_at > now() - interval '30 days';

**Complexity & notes**

- Time: O(N_orders + N_users) vs O(N_users * log N_orders) for correlated.- Edge cases: large result sets — ensure appropriate memory for hash aggregates; tune work_mem or use GROUP BY sort-aggregate.

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.