Data modelling for reads

Modelling from access patterns rather than entities: denormalisation, precomputed views, fan-out on write versus read, and the write amplification each choice buys you.

Normalised schemas are designed so that every fact is stored once. Read-heavy systems are designed so that every screen is one lookup. Those two goals conflict, and the interview is mostly about where you land between them and what you pay for it.

Start from the screens, not the entities

Write down the queries the product actually makes, in order of frequency, before you draw a single table:

• "the 20 newest posts from the accounts this user follows" — 30 k/s
• "one post with its author, like count and my like state" — 100 k/s
• "every like by this user, for the profile page" — 200/s

Now each table exists to answer one of those. A model that answers the 100 k/s query with a five-way join is wrong no matter how clean it looks, and a model that duplicates the author's display name into every post row is right if renames are rare and the read is hot.

The sentence to say: "I'll denormalise where the read rate is high and the duplicated field changes rarely, and keep the normalised copy as the source of truth."

Denormalisation, and what it costs

Duplicating a field buys a read and costs a write: every place the value is copied has to be updated when it changes, and the update is no longer atomic with the original. Three ways to pay:

Pick the field carefully. A display name copied into a billion rows is a problem; a like_count maintained by an event consumer is normal.

Precomputed views

A materialised view is denormalisation with a name: a table whose rows are the answer to a query, maintained by a job or a stream consumer rather than by the reader. Feed tables, unread counters, leaderboards, "people you may know" and search indexes are all this pattern.

The tradeoffs to name: the view is eventually consistent with the base data, it needs a rebuild path for when the consumer has a bug, and it usually needs a version or a timestamp so a stale rebuild cannot overwrite newer state.

Fan-out on write versus fan-out on read

Fan-out on write (push). When a post is created, insert a row into each follower's feed table. Reads are one range scan of one partition, which is why it is the default for consumer products. The cost is write amplification equal to the follower count: 1 000 followers is 1 000 writes, and an account with 50 M followers is a background job, not a request.

Fan-out on read (pull). Store the post once; at read time, fetch the recent posts of everyone the reader follows and merge them. Writes are cheap, reads are expensive and hard to cache, and a reader who follows 5 000 accounts is now the problem.

The hybrid, which is the actual answer. Push for ordinary accounts, pull for accounts above a celebrity threshold (say 100 k followers), and merge the two lists at read time. Interviewers are looking for the threshold, where it is stored, and what happens to the feed ordering when the two sources are merged.

Write amplification is the number to state

For any model, say how many writes one user action produces:

• a post by a 500-follower account with fan-out on write: 1 + 500 writes
• the same with the hybrid and a 100 k threshold: 1 write plus a queue message
• a like: 1 row insert, 1 counter increment, 1 feed-activity row, 1 notification event

That arithmetic is what turns "we denormalise" into an answer with a number, and it usually shows immediately whether the design survives peak traffic.

Relationships and joins

Three ways to model a relationship when you cannot join:

1. Embed the child in the parent document when the child is small, bounded and always read with the parent (a post's first three comments).
2. Reference by id and do a second lookup, batched — a multi-get of 20 ids from a cache is one round trip, not 20.
3. Duplicate into both sides when both directions are queried hot: followers(user_id, follower_id) and following(user_id, followee_id) are the same fact stored twice because both lists are read.

Say out loud which one you picked and why; "we'd do a join" is the answer that gets probed hardest at scale.

Common mistakes

• Designing the schema before listing the queries, then discovering the hot read needs a join across shards.
• Denormalising a field that changes often, and inheriting a rename storm.
• Maintaining a counter with UPDATE … SET count = count + 1 on a hot row instead of a sharded counter or a stream aggregate.
• Forgetting the rebuild path: every derived table needs a way to be recomputed from the source of truth.
• Unbounded rows: a document that grows forever (all comments in the post) eventually exceeds the row or document limit.

Checklist

• The three hottest queries, with their rates.
• Which fields are duplicated, and how copies are updated.
• Write amplification per user action.
• The celebrity or hot-key exception and its threshold.
• How every derived table is rebuilt.