Design fast reads for a relational database
PremiumDatabase designMedium
- Covering indexes
- Denormalisation
- Materialized views
Quick answer
This database design interview question asks you to design fast reads for a relational database. Aim for a design that stores data correctly and serves its main queries well, then explain the schema, keys, indexes and trade-offs. Interviewers want to see how you model entities, choose keys and design for the real access patterns.
The problem
Design fast reads for a relational database behind an online store's orders and catalog app. The schema is correct but slow: order history takes 1.8 s and category pages 900 ms. Cut read latency with indexes, denormalisation, rollups, replicas and a cache, without breaking writes or consistency.
You may add indexes, columns and tables, rewrite queries and add replicas or a cache. The database is PostgreSQL, and you may not move to another engine.
What must the system do?
- Order history: A customer sees their latest 20 orders with total, item count and status, newest first, and pages back with a cursor.
- Category listing: Active products in a category sorted by average rating or price, 24 per page, each with rating and review count.
How well must it do it?
- Read latency: p99 under 100 ms for order history, category listing and product page; under 500 ms for the seller dashboard.
- Correct money: Checkout reads price from the primary; a stale cached price is never charged.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Covering indexes
- Denormalisation
- Materialized views
- Read replicas
- Query rewrites
- Keyset pagination
How you’ll practise
- Access patterns
- Entities
- Schema
- Indexes and queries
- Scale and consistency
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any database design round.
- Lists the entities and how they relate before writing tables
- Designs the schema around the main access patterns
- Chooses primary keys, foreign keys and indexes with reasons
- Explains where to normalise and where to denormalise
- Considers integrity, transactions and concurrent writes
- Plans for growth: partitioning, archiving and migrations
Questions people ask about this interview question
- Which design type is this question?
- It is a database design question. You design fast reads for a relational database and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Medium. Entry-level engineers can practise it, but it expects you to reason about requirements and trade-offs, not only list components.
- What should I cover first?
- Start with the entities and the main queries the database must answer. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team