Indexing, Denormalization and Materialized Views

1The core idea#

Here is the mental model I give every junior engineer.

• Your database answers a query by doing work at write time or at read time. It cannot avoid the work. All three techniques shift it earlier so reads get cheap.
• An index is a side data structure (usually a B-tree) mapping a column value to the rows that have it.
• Denormalization stores the same fact in more than one place, or precomputes a value, so a read does not have to join or aggregate.
• A materialized view is the database doing denormalization for you, formally: you write the query once, it stores the result as a real table, reads hit the stored result.

What an index buys you, concretely.

• Instead of scanning 50 million rows for email = 'sam@x.com', the database walks a tree in a handful of steps.
• For 50 million rows a B-tree is roughly 4 to 5 levels deep, so that lookup is about 5 page reads instead of 50 million row touches.
• You pay on every insert and update, because the index has to be kept in sync.

What denormalization looks like.

• A classic case: store author_name directly on the posts table instead of joining to users every time you render a feed.
• The read skips the join. The write now has to keep every copy in agreement.

Where materialized views sit.

• You define the query once, the database runs it and stores the result on disk.
• Reads hit the stored result. The whole catch is keeping it fresh.

2How it works#

Start with indexes, because they are the default and the cheapest win.

B-tree indexes.

• Keys stored in sorted order with pointers down to the rows.
• Lookups, range scans (created_at > '2026-01-01'), and ORDER BY on the indexed column all get fast.

Composite indexes.

• An index on (tenant_id, created_at) serves both a filter on tenant_id and a sort on created_at, but only in that column order.
• Equality column first, range column second.
• Get the order wrong and the index may not be used at all.

Covering indexes.

• If an index includes every column a query needs, the database answers from the index alone and never touches the table.
• On a hot query that can cut latency roughly in half, because you skip the second read that fetches the full row from the heap.

Denormalization has no special machinery.

• You change your schema and your write code.
• When a user changes their name, you update users and every posts row that copied it.
• That fan-out is the whole cost: if a user has 40,000 posts, one name change becomes a 40,000-row update.

Materialized views are where freshness becomes a real decision.

• On refresh (manual or scheduled): recomputed on a cron or on demand. Simple, but stale between refreshes. Fine for a daily revenue dashboard.
• Incremental or continuous: the engine updates only the rows affected by recent changes. Lower latency to freshness, higher write cost. This is what warehouses like Snowflake and stream processors like Materialize give you.

Here is the request path once these are in place.

3A worked example: the feed count that killed the database#

Let me make this concrete, because interviewers reward numbers over adjectives.

The setup.

• A social app. The posts table has 500 million rows.
• The home screen shows each author's total post count: SELECT count(*) FROM posts WHERE author_id = ?.
• Reads are heavy: 50,000 per second at peak. Writes are light: about 2,000 new posts per second.

Option A: index only.

• Add an index on posts(author_id). The count query scans just that author's rows, not all 500 million.
• But a prolific author with 40,000 posts still forces the database to count 40,000 index entries on every read.
• At 50,000 reads per second, many landing on the same hot authors, the planner is doing tens of millions of index-entry counts per second.
• Latency sits around 30 to 80ms on the hot authors and the CPU is pinned.
• Better than a full scan, not good enough.

Option B: denormalize the count.

• Add a post_count column on the users table. Every read is now a single-row primary-key lookup, well under 1ms.
• The cost moves to writes: each new post does UPDATE users SET post_count = post_count + 1.
• That is 2,000 extra single-row updates per second (trivial), contending on one row per author, so no cross-row locking storm.
• The risk is drift: a hard-delete by an admin script that forgets to decrement leaves the count wrong forever.
• The fix: a nightly reconciliation job that recomputes counts for any user touched that day and corrects drift.
• Reads go from 30 to 80ms down to under 1ms.

Option C: materialized view.

• Define author_counts AS SELECT author_id, count(*) FROM posts GROUP BY author_id and refresh every 5 minutes.
• Reads hit the stored result, also sub-millisecond. The database owns correctness, so no application drift bug.
• The cost: a full aggregate over 500 million rows every 5 minutes (expensive), plus the count can be up to 5 minutes stale.
• For a vanity post count on a profile, 5 minutes stale is completely fine.

What I would ship here, and why.

• Option B for this exact case: tiny write cost, fastest possible read, and a count is cheap to maintain incrementally.
• Option C instead if the aggregate were complex (say a trending-topics rollup joining three tables) and read far more often than the data changes.
• The point in a round is to show you can put a number on each path and then choose.

Put a number on every rung of the ladder, then pick the cheapest one that clears the bar.

4Trade-offs#

Every one of these techniques makes writes slower or data staler. That is the sentence I want to hear in an interview.

Indexes are not free.

• Each index roughly doubles the write cost for the columns it covers, because an insert writes the row and updates the tree.
• I have seen a table with 14 indexes where inserts were the bottleneck, not reads.
• They take storage too, often 10 to 30 percent of table size each, and the planner can pick the wrong one.
• More indexes is not better. Index what you filter and join on, measure, then stop.

Denormalization is the sharpest knife.

• It makes a specific read fast, but now you own consistency in application code.
• Miss one update path and your copies drift.
• The bug is nasty because the database looks healthy and the data is just wrong.
• Reach for it only when a join is provably too expensive at your scale.

Materialized views move the consistency burden back into the database.

• Safer, because the engine keeps the result correct.
• But you inherit staleness and refresh cost, and a full refresh on a big view can lock or hammer the database.

My rule of thumb.

• Try an index first.
• Reach for a materialized view when a complex aggregate runs far more than the underlying data changes.
• Denormalize by hand only when the first two cannot hit your latency target and you accept owning the consistency.

5Where it is used#

Indexes are everywhere, so talk about the interesting ones.

• A user-facing search on email or username: a unique index.
• A feed sorted by time per tenant: a composite index on (tenant_id, created_at desc).
• Full-text search: specialized inverted indexes like GIN in Postgres, or a separate engine like Elasticsearch.

Denormalization shows up anywhere joins get too hot.

• Document stores like MongoDB and wide-column stores like Cassandra basically force it: one table per query pattern, data duplicated across them freely.
• A Cassandra schema often stores the same message under messages_by_user and messages_by_channel.
• That is denormalization as a design philosophy, not a patch.

Materialized views are the backbone of analytics and dashboards.

• A leaderboard that aggregates millions of scores, or a billing summary rolled up per customer per day.
• Running that aggregate on every page load would melt the primary.
• Compute it once an hour, serve the stored result in single-digit milliseconds.
• Warehouses and tools like Materialize and ClickHouse make this a first-class feature.

Which one in a design round?

• Reads 100x writes and a query is slow: an index is the first answer.
• The slow query is a big aggregate read constantly: a materialized view.
• Even the join itself is the problem at huge scale: denormalize the read model.

6Common pitfalls and gotchas#

Over-indexing.

• The most common mistake I see: an index for every query, and the dead ones never removed.
• Each one taxes every write.
• Audit for unused indexes. Postgres exposes this directly through pg_stat_user_indexes, where idx_scan = 0 means nobody is using it.

Wrong composite order.

• An index on (created_at, tenant_id) will not efficiently serve WHERE tenant_id = 5 ORDER BY created_at.
• Equality columns go first. A frequent gotcha in real systems and a great thing to call out unprompted.

A function that kills the index.

• WHERE lower(email) = ... will not use an index on email, because the function wraps the column and the planner cannot match it to the stored keys.
• You need a functional index on lower(email), or you are back to a full scan.

Denormalized data drifting silently.

• Copy author_name into posts, forget the update path, and old posts keep the stale name forever.
• Always know every write path that touches a duplicated field.
• A background reconciliation job that recomputes copies is cheap insurance, exactly like the nightly recount in the worked example above.

Refreshing a materialized view with a lock.

• A naive refresh can block reads on the view for the whole recompute.
• Use a concurrent refresh if your engine supports it (Postgres has REFRESH MATERIALIZED VIEW CONCURRENTLY), or build a new copy and swap it atomically.

Freshness expectations left unspoken.

• The worst outcome: a product owner expecting real-time numbers from a view that refreshes hourly.
• Make the staleness window explicit, and put it in the UI if it matters.

7How it shows up in interviews#

Interviewers are not testing whether you know what an index is. They are testing whether you understand the cost of making reads fast.

The weak answer.

• "The query is slow, so I will add an index."
• No mention of write cost, which columns, or any number.
• The interviewer has nothing to probe and no signal you have shipped this.

The strong answer walks the ladder and quantifies each rung.

• "Reads dominate here, say 50k per second against 2k writes, so first I add a composite index on the filter and sort columns, equality first."
• "If the hot query is a heavy aggregate read far more often than the data changes, I precompute it as a materialized view and refresh every 5 minutes, accepting that staleness window."
• "If even the base joins are too expensive at this scale, I denormalize into a read-optimized model, and I add a nightly reconciliation job because now my application owns consistency."

Put those two next to each other and the gap is obvious. The strong answer signals three things the interviewer wants:

• You default to the cheapest option.
• You quantify the trade: write cost, staleness window.
• You name the new risk you just introduced.

That last part separates intermediate from senior. The weak answer signals none of them.

Have an answer ready for the obvious probes.

• "What happens to writes now?" and "How stale can this get?" and "What if two services update the denormalized copy?"
• "The view is at most 5 minutes behind, and I surface that in the dashboard header" lands far better than hand-waving.

And the one they love most: when NOT to do this.

• If writes dominate reads, piling on indexes hurts.
• If the data must be strongly consistent, say an account balance, a stale materialized view is the wrong tool.
• Knowing when to say no is the senior signal.

Want to see these trade-offs play out end to end? Try the related System Design Question walkthrough on designing a high-traffic news feed, where indexing and denormalization decide whether the feed loads in 50ms or 5 seconds.