Updated 24 October 2026

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.

Try it yourself, in a playground:Composite index column order

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.

Questions people ask

What is denormalisation in database design?

Denormalisation means duplicating data so that a hot read is one lookup instead of a join. Normalised schemas store every fact once, while read-heavy systems are designed so that every screen is one lookup, and those goals conflict. Duplicating a field buys a read and costs a write: every copy has to be updated when the value changes, and that update is no longer atomic with the original.

When should you denormalise data?

Denormalise where the read rate is high and the duplicated field changes rarely, and keep the normalised copy as the source of truth. Copying an author's display name into every post row is right if renames are rare and the read is hot, but a display name copied into a billion rows is a problem. A like_count maintained by an event consumer is normal, while denormalising a field that changes often leads to a rename storm.

What is the difference between fan-out on write and fan-out on read?

Fan-out on write (push) inserts each new post into every follower's feed table, so reads are one range scan of one partition but writes multiply by the follower count. Fan-out on read (pull) stores the post once and merges followed accounts' recent posts at query time, so writes are cheap but reads are expensive and hard to cache. Real systems push for ordinary accounts, pull above a celebrity threshold such as 100 k followers, and merge both lists at read time.

What is write amplification in data modelling?

Write amplification is the number of writes one user action produces, and stating it turns "we denormalise" into an answer with a number. A post by a 500-follower account with fan-out on write costs 1 + 500 writes, while a hybrid with a 100 k celebrity threshold costs one write plus a queue message. A like might be a row insert, a counter increment, a feed-activity row and a notification event, and the arithmetic usually shows whether the design survives peak traffic.

How do you keep denormalised copies up to date?

There are three ways: update the copies in the same transaction, publish an event and update them asynchronously, or recompute them nightly. Same-transaction updates are immediate but only work inside one database and touch many rows. The usual answer is asynchronous events, fresh within seconds, with an outbox so the event cannot be lost; nightly recomputation, hours behind, is fine for analytics but not for a profile name.

What is a materialised view?

A materialised view is 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 all follow this pattern. The view is eventually consistent with the base data, needs a rebuild path for when the consumer has a bug, and usually needs a version or timestamp so a stale rebuild cannot overwrite newer state.