Materialized View: Store a Ready-Made Answer for Fast Reads
A materialized view is a stored, precomputed copy of data shaped for one kind of read. It trades a little freshness for much faster and simpler reads.
In short
A materialized view is a stored, precomputed copy of data shaped for one kind of read, trading a little freshness for much faster and simpler reads.
11 min readFree
What You Will Learn
This lesson explains one design tool: the materialized view. You will learn why it exists, how it is built and kept up to date, and the rules that keep it safe to use. You will also walk through one realistic screen, step by step, to see the idea at work.
Key words
- Materialized view
- A stored copy of a query's result. It is built in advance and shaped for one kind of read.
- Source of truth
- The main place where the correct data is kept. Other copies are built from it.
- Read model
- A data shape made only for reading. Users read it but do not change it directly.
- Stale
- Old. A copy is stale when the source has changed but the copy has not caught up yet.
- Join
- Combining rows from two or more tables using a shared key, such as a customer ID.
- Aggregation
- Combining many rows into one result, such as a sum, a count, or an average.
- Refresh
- Rebuilding or updating a view so it matches the source again.
- Precompute
- Work out an answer before anyone asks for it.
Why Do Plain Queries Get Slow?
Most systems store data the way it is written, not the way it is read. An order might sit in one table, its items in another, and the customer's details in a third. Every screen that shows an order must then combine these parts again. When a screen also adds up many rows, the database has to do that work every time. As the data grows, the work grows too.
Joins on every load
A page that combines four tables must do the join work on every visit, even when nothing has changed.
Totals over many rows
A dashboard that sums millions of rows repeats the same large sum for every viewer.
Data spread across stores
When the parts live in different places, the screen waits on several systems before it can answer.
Indexes can help, but they do not always solve the problem. An index is a sorted lookup list that helps the database find rows quickly. Many stores cannot be re-indexed for every possible question without slowing down writes. So tuning the read path alone is often not enough.
Generating views in advance is a common fix. You run the expensive work ahead of time, save the result in a shape that suits the screen, and let readers use that saved result. The Azure guidance describes this as a read model that holds data derived from one or more source stores.
What Is a Materialized View?
A normal view is only a saved query. Each time you read it, the database runs the query again. A materialized view works differently. It runs the query once, writes the answer to storage, and serves later reads from that stored copy. Reads become fast and simple. The cost is that the stored copy can fall behind the source.
Think of it as a special kind of cache. A cache is a fast copy kept close to the reader. The Azure pattern notes that the view is never updated by the application directly, which is why it works like a specialized cache. It can also hold calculated values, such as totals, and it can be built for just one query.
Tip
Keep the two words apart. A view is a name for a query. A materialized view is the query's result, stored. The stored result is what goes stale.
Because every row in the view comes from the source, the view can always be thrown away and rebuilt. Losing it is not a disaster. This is also why the view is never the source of truth. The source stays the one place where the correct data lives.
How Is the View Built and Refreshed?
- 1
Pick one question
Start with one screen or report. Write down the exact answer it needs. Do not build a view for every possible question.
- 2
Build the first copy
Run the query over the source data once. Store the result in the shape the screen needs.
- 3
Choose a refresh trigger
Decide what tells the view to update. It can be an event, a database trigger, or a scheduled job.
- 4
Apply each update
When the trigger fires, recompute the affected rows, or rebuild the whole view.
- 5
Watch the age
Track how old the view is. Raise an alert when it grows older than the limit you agreed on.
Three kinds of trigger are common. An event is a message that says something happened, such as an order being placed. A database trigger is a rule inside the database that runs when a row changes. A scheduled job is a task that runs at fixed times, such as every night.
Event-driven refresh
The view updates when a change event arrives. It stays close to live. The Azure guidance warns that a flood of rapid changes can cause heavy overhead.
Trigger-based refresh
The database updates the view as each source row changes. The view stays current, but every write does extra work.
Scheduled refresh
A job rebuilds the view at set times. It is simple and predictable, but the view is stale between runs.
Whatever the trigger, a refresh can work in two ways. A full rebuild recomputes everything. It is simple but can be slow. An incremental update changes only the rows that moved. It is faster but needs careful design.
What Are the Four Tradeoffs?
Freshness
How new the data is. Event-driven refresh keeps it closest to live, but it costs more work on every change.
Staleness
How far behind the view may be. It follows the refresh interval or the event delay. Agree on a limit before you choose a method.
Storage cost
A view copies data, so it takes space. Many views for many queries can use a lot of storage.
Rebuild effort
Full rebuilds are simple but slow. Incremental updates are quicker but harder to get right and to debug.
Tip
Measure the refresh, not only the read. A common mistake is to test how fast the view reads and forget how much extra work the refresh adds to the write side.
What Are the Rules for Using a Materialized View?
Rule 1: It is disposable
Every row can be rebuilt from the source. If the view is lost, rebuild it. Do not patch it by hand.
Rule 2: It is never the source of truth
Facts are decided in the source data. The view only reports them.
Rule 3: The app never writes to it
Apps read the view. Only the refresh process changes it. A direct write breaks the link back to the source.
Rule 4: Reads must far outnumber writes
The view pays off when many readers ask the same question and some staleness is acceptable.
Two more checks help. First, ask whether the query is already cheap. A plain query that runs in a few milliseconds with an index does not need a view. The view would only add a refresh job and a staleness window. Second, ask whether the data must always be exact. If it must, read from the source.
Quick check
A dashboard shows each store's total sales for today. It takes 20 seconds to load because it adds up millions of order rows on every visit. The business accepts that the total may lag a little behind the latest sale. What is the best change, and what new problem must you handle?
Worked Example: An Order History Screen
An online shop has an order history page. It lists each customer's past orders, the items in each order, the shipping status, and the order total. The data sits in four tables: customers, orders, order items, and shipments. Before the change, each page load joined all four tables.
- 1
Study the slow path
Each visit joins four tables and adds up the item prices. The database repeats this work for every visit, even when no order has changed.
- 2
Design the view shape
Build a per-customer table with one row for each order. Each row already holds the item count, the order total, and the shipping status.
- 3
Pick the trigger
Each order event, such as order placed, item changed, or shipment updated, tells the refresh step to rebuild that customer's rows.
- 4
Serve the page
The page reads the view using the customer ID. It never touches the four source tables.
- 5
Handle the lag
If an event arrives late, the page may show an old status for a short time. Track the event delay and alert when it grows.
The small program below imitates the idea. It builds a view that holds one total per customer. Then a new order arrives. The first print shows the view before the event is handled, so it is still stale. After the refresh step updates the view, the copy matches a full rebuild from the source.
orders = [
{"id": 1, "customer": "ana", "total": 30},
{"id": 2, "customer": "ben", "total": 12},
{"id": 3, "customer": "ana", "total": 45},
]
def build_view(rows):
view = {}
for row in rows:
name = row["customer"]
view[name] = view.get(name, 0) + row["total"]
return view
view = build_view(orders)
print(view)
new_order = {"id": 4, "customer": "ben", "total": 20}
orders.append(new_order)
print(view)
view[new_order["customer"]] = view.get(new_order["customer"], 0) + new_order["total"]
print(view)
print(view == build_view(orders))
Output
{'ana': 75, 'ben': 12}
{'ana': 75, 'ben': 12}
{'ana': 75, 'ben': 32}
True
The page now reads one stored shape built for it. The price is the refresh work and the storage for the copy. The page also shows a lag. A change appears only after its event has been handled, so the team must watch that delay.
Quick check
A warehouse app shows stock counts from a materialized view. A support agent fixes a wrong count by typing the correct number straight into the view table. At the next refresh the number goes back to the wrong value. Which rule was broken, and what should happen instead?
Common Mistakes to Avoid
Never refreshing
A view that is never refreshed serves old data for good. Set up refresh schedules or triggers from day one.
Too many views
Each view uses storage and adds refresh work. Build views only for expensive, frequently run queries.
No staleness limit
Asking for the freshest possible data is not a plan. Write down the maximum age the screen may show.
Silent failure
If a refresh event is lost or a job fails, the view keeps serving stale results without any error. Monitor the age of the view.
Tip
When a refresh fails halfway, do not leave a half-built view in use. Build the new version beside the old one, check it, and then switch over. Many databases offer a way to refresh without blocking readers.
The PostgreSQL documentation describes a concurrent refresh option that lets reads continue during a refresh, but only one refresh can run at a time on a given view. Some engines require a unique index before this option works. Check your own database before you rely on it.
Key Takeaways
A materialized view stores the answer to one expensive query, shaped for the reader. It makes reads fast and simple, and it is kept current by events, triggers, or scheduled jobs. It is disposable, it is never the source of truth, and apps never write to it. Use it when reads far outnumber writes and a known amount of staleness is acceptable. For exact, live answers, read the source instead.
Sources
- Materialized View pattern, Azure Architecture Center (Microsoft Learn)
- Architecture design patterns that support performance efficiency, Microsoft Azure Well-Architected Framework
- REFRESH MATERIALIZED VIEW, PostgreSQL 18 documentation
- Views, Materialized Views, and Refresh Strategies, PostgreSQL guide (programmingline)
- What is a materialized view? (Mart Traagel)
- The Real Tradeoffs of Materialized Views in Production (Technori)
- View vs Materialized View: Differences, Performance, and When to Use Each (Tacnode)
- Materialized View Pattern: Pre-computed Query Results (Layrs)
- Materialized Views System Design Guide (DosiBridge)
Last updated on 18 November 2026
Related lessons
- System designKey-Value Store: Fast Lookups by Key, and Why Queries Are Hard
- System designDatabase Caching: Keep Copies of Repeated Reads in Fast Memory
- System designCQRS: Split the Code That Changes Data From the Code That Reads It
- System designEvent Sourcing: Store Every Change as an Event and Rebuild State From It
Learn with me
Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.