Materialized View

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.

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.

Step through a Monday morning below. Before the 9:00 rush hits, predict how much work the database does for 200 readers who all want the same two numbers.

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.

Step through it below. A new order lands after the view was built: predict what the dashboard shows, then switch to compute-on-read and compare what the same three reads cost.

How stale the view gets depends on how you refresh it, and there are two broad strategies.

• Full refresh on a schedule. Every 10 minutes, rerun the whole query and swap in the new result. It's simple and correct after every run, but the view can be up to 10 minutes stale, and each refresh pays the full cost of the query even if only one order changed.
• Incremental refresh on change. Each new order adds its amount to the right row (EU: $12,400 + $300). The stale window shrinks to seconds and each update is tiny, but the update logic has to handle every kind of change, such as a refund, a cancelled order or a customer who moves region, or the view slowly drifts from the truth.

Whichever you pick, show readers how fresh the numbers are, with something like "as of 9:00" on the screen, so a stale view is honest about its age.

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.