Materialized View
Precomputed query result stored as table — fast reads for dashboards, feeds, and aggregates at cost of staleness.
Interview tip Lead with a 30-second definition, then one real system example and name 2–3 designs where Materialized View is non-negotiable.
① What it is (30 seconds)
Precomputed query result stored as table — fast reads for dashboards, feeds, and aggregates at cost of staleness.
② How it works in system design
Periodic or trigger-based refresh. Cassandra feed cells, Redis precomputed home feed, ClickHouse rollup tables. Trade freshness vs read latency.
Typical placement
Client→Edge / Gateway→Materialized→Services→Data stores
③ Concrete system design example
Scenario: Twitter home timeline: materialized inbox per user (fan-out on write). Read = single row fetch, not merge 1000 follows at read time.
④ Important interview Q&A
| Question | Answer |
|---|---|
| Materialized view vs cache? | Materialized view in DB/storage layer with defined refresh; cache ephemeral with TTL. |
| Refresh strategies? | On-write incremental, scheduled batch, or stream processing (Flink). |
| Stale reads? | State acceptable staleness SLA — "timeline few seconds behind". |
⑤ Seen in these system designs
- Twitter — timeline inbox
- News Feed — ranked feed cells
- Google Trends — daily rollups
In interviews, after explaining the concept, say: "This shows up directly in …" and link two designs.
⑥ Revision checklist
- Precompute vs on-read
- Refresh trigger
- Staleness SLA
- Incremental update