Materialized View
Precompute an expensive query result and keep it fresh incrementally.
IntermediateData
A background job or stream processor keeps a denormalised, precomputed view up to date as source data changes, so the read path never re-runs the expensive aggregation live.
When to use it
- A query is read frequently but is expensive to compute (joins, aggregations)
- Slight staleness in the view is acceptable in exchange for fast reads
Trade-offs
- The view can lag behind the source during bursts or processor downtime
- Every new query shape may need its own materialized view to maintain
Components used
Relational DatabaseStream ProcessingDocument DatabaseManaged App Service
How it works
- An expensive query — typically a large aggregate or multi-way join — is computed once and its result stored as a physical table.
- Reads hit the stored result directly, turning a heavy computation into a simple lookup.
- The view is refreshed either on a schedule or incrementally as the underlying data changes.
Used in the wild
- Dashboards and leaderboards that would otherwise re-aggregate millions of rows per page load.
- Denormalised read models in a CQRS system.
- Rollups over event or metrics data where the raw grain is far too fine to query interactively.
Good to know
- The entire design question is refresh strategy. Full recomputation is simple but expensive; incremental maintenance is cheap but only tractable for certain aggregate shapes.
- A materialised view is a cache that happens to live in the database, and it has all the same staleness problems — the naming just makes it sound more respectable.
Related patterns
Retrieval-Augmented Generation (RAG)
Ground an LLM's answers in retrieved, up-to-date, private documents.
Vector Search + Rerank
Cheaply retrieve a broad candidate set, then precisely re-rank the top results.
Feature Store
Compute features once, serve them consistently to training and inference.
CQRS
Separate models and stores for writes and reads.