Redesign an Existing Relational Database to Reduce Read Latency

In a staff-level engineering screen, the interviewer describes the database design behind one of the team's real projects and asks how you would change it to reduce read latency. The report does not include that project's details, so practice with this framing: an existing service keeps its data in a normalized relational database, its most frequent reads assemble each response from several related tables, and read latency on those paths is higher than the team wants. Propose how you would change the database design and the read path to bring read latency down, and explain what each change costs on the write path and in data freshness. In the interview this took only the last 15 minutes of the round, so lead with the changes that matter most and be ready to go deeper on any one of them. ```hint Trace one slow read Take the hottest read and count what it touches: tables, rows, round trips, and how often the same result is computed again for different callers. ``` ```hint Price every speedup Each change that makes a read cheaper moves work somewhere else. For every proposal, say what now happens when data is written and how stale a reader can become. ``` ### Clarifying Questions - Which reads are slow, and what do they return: single-record lookups, lists, or aggregates such as counts and averages? - What is the ratio of reads to writes on those paths? - How fresh must a read be after a write, and must a user always see their own latest change? - Where does the time go today: joins, aggregates computed at read time, missing indexes, several queries per request, or contention with writes? - How large are the tables, how fast are they growing, and is the database already replicated? - Is the target median latency, tail latency, or both? ### What a Strong Answer Covers - Diagnosis before redesign: measuring the hot read paths and ruling out cheap fixes. - Data-model changes aimed at specific read paths, with their write cost and consistency implications. - A caching layer with a clear invalidation strategy, a bound on staleness, and protection against load spikes when entries expire. - How derived data is kept correct over time, and how the change is rolled out on a live system. - Prioritization and clear communication within a short time slot. ### Follow-up Questions - A denormalized field goes stale because one write path forgot to update it. How do you detect and repair it? - A popular cache entry expires and many requests miss at the same moment. What happens to the database, and how do you prevent it? - When would you move reads to replicas instead, and what does replication lag mean for a user who has just written? - How would you roll out the schema change on a live table without downtime?