Imagine a librarian who, every morning, writes a one-page summary of which books were borrowed yesterday and pins it to the door. Anyone walking in gets the answer instantly — nobody has to dig through thousands of checkout slips. The summary cost a little effort to prepare, but it saves every single visitor a long search.
A materialized view is that pinned summary for your data. Instead of recomputing an expensive query every time someone asks, you compute it once, store the result, and serve that stored result on demand.
The problem
Well-structured data is usually normalized — split across many tables so nothing is duplicated and updates stay clean. That's great for writing, but it can be brutal for reading. A single dashboard tile might need to join orders to customers to products to regions, then group and sum across millions of rows.
Run that query once and it's fine. Run it on every page load, for every user, and your database spends all day re-deriving the same answer. The structure that keeps writes honest is now actively fighting your reads.
- On-the-fly queryEach page load re-runs the costly join and aggregation across millions of rows instead of reusing a stored answer.
Normalized tablesSplit apart to keep writes clean — but a single dashboard tile must join several of them every single time.
Every readPer user, per refresh, the database does the same heavy work again, spending its day re-deriving identical results.
How it works
You move the expensive work out of the read path and do it ahead of time. A background process runs the costly query — the joins, the aggregations, the filtering — and writes the finished result into a separate store shaped exactly like what readers want. That store is the materialized view.
Now a read is a single, cheap lookup against pre-shaped data. The source tables stay normalized and authoritative; the view is purely derived, so if it's ever lost or corrupted you simply regenerate it. You refresh it on a schedule, or incrementally as the source changes, depending on how fresh it needs to be. The diagram below shows source data feeding a generator that builds the view, with reads served straight from it.
- Source tablesThe normalized, authoritative data — great for writes, but expensive to join and aggregate on every read.
- View generatorRuns the costly query ahead of time and writes the finished result into the view, on a schedule or incrementally.
- Materialized viewA precomputed, query-shaped copy. Reads hit this directly as a single cheap lookup; it can always be rebuilt.
Treat the view as disposable, never as the source of truth. Because it's fully derived, you can drop and rebuild it any time without losing data — which means you're free to change its shape as your screens evolve. The hard question is always freshness: decide how stale a result is allowed to be before you reach for a refresh strategy, and pair the view with caching only when the staleness budgets line up.
When to use it
Reach for a materialized view when the same heavy query is read far more often than the underlying data changes — dashboards, leaderboards, reports, and summary screens are textbook cases. It's a natural partner to CQRS, where the read model is deliberately separate from the write model and can be optimized independently.
Skip it when your data is highly volatile and readers demand the absolute latest value, since you'd be regenerating the view constantly for little gain. And remember it's not free storage — you're keeping a second, redundant copy of data, so reserve it for queries whose cost actually justifies precomputing the answer.