Materialized Views
By the end of this lesson you'll be able to take a slow, expensive query — the kind that powers a dashboard — and make it return instantly by pre-computing and storing its result. You'll know when a materialized view is the right tool, how to keep it fresh with REFRESH , and when an app/Redis cache is the better choice instead.
Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.
Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
Imagine an orders table with millions of rows. A revenue dashboard groups it by month on every page load — and crawls. Here's a tiny slice of the raw data; the goal of this lesson is to pre-compute the monthly summary so reads become instant.
A regular view is just a saved query with a name. It stores no data — every time you read it, the database re-runs the underlying query from scratch. That's always fresh, but on a big aggregation it's slow every single time .
A materialized view runs that query once , then stores the result on disk like a real table ("materializes" it). Reading it after that is instant — it just hands back the saved rows. The catch: the stored result is a snapshot, so it can be stale until you refresh it.
A regular view is like a live Google search — it runs again every time you open it, so results are current but you wait. A materialized view is like a printed report — instant to read, but it shows the numbers as of when it was printed. You "reprint" it (refresh) when the data has moved on enough to matter.
Now the materialized version. Notice the only new word is MATERIALIZED — but the behaviour changes completely: the result is computed once and saved.
Fill in the blanks to turn a slow daily-sales aggregation into a stored materialized view. The expected result is in the comments so you can check yourself.
A materialized view never updates itself. New rows in orders won't appear in mv_monthly_revenue until you run REFRESH MATERIALIZED VIEW . There are two flavours, and the difference matters in production:
In practice you rarely refresh by hand — you schedule it so the data stays "fresh enough" for the business. The interval is a staleness trade-off : refresh more often for fresher data and more load; less often for cheaper but staler data.
You're given a freshness requirement ("up to 10 minutes stale, no blocking"). Fill in the blanks to match it.
Materialized views shine when a query is expensive (big joins, heavy GROUP BY ), queried often (every dashboard load), and allowed to be slightly stale . Customer lifetime value, product leaderboards, and revenue trends are textbook examples — compute them once, read them thousands of times.
A materialized view is one kind of cache — it lives inside the database and you refresh it on a schedule. Two common alternatives trade freshness differently:
Rule of thumb: need data live to the second ? A materialized view is the wrong tool — reach for a summary table or an app/Redis cache. Fine with "a few minutes behind"? A materialized view on a schedule is usually the simplest win.
Q: Does a materialized view update automatically when the source data changes?
No. It's a stored snapshot. It only changes when you REFRESH it — manually, on a schedule (pg_cron), or from a trigger. That's the whole staleness trade-off.
Q: Full refresh or CONCURRENTLY — which should I use?
Use CONCURRENTLY in production so reads never block (it needs a unique index). A plain full REFRESH is fine for off-hours jobs or when a brief lock doesn't matter, and it's slightly faster.
Q: When should I use Redis instead of a materialized view?
When you need sub-millisecond reads, per-key expiry (TTL), or to cache things that aren't a single SQL result (sessions, API responses). A materialized view is simpler when the cached thing is a query result and "a few minutes stale" is acceptable.
Q: Can I query a materialized view like a normal table?
Yes — SELECT , WHERE , JOIN , and ORDER BY all work, and you can index it. You just can't INSERT / UPDATE it directly; change the source tables and refresh.
Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a PostgreSQL playground to confirm.
Practice quiz
How does a regular view differ from a materialized view?
- A regular view stores its result on disk; a materialized view re-runs the query
- Both store their results identically
- A regular view re-runs the query each read; a materialized view stores the result
- A regular view can only be read once
Answer: A regular view re-runs the query each read; a materialized view stores the result. A regular view is a saved query that stores no data and re-runs every read. A materialized view runs the query once and stores the result on disk.
What is the trade-off of using a materialized view?
- Reads are fast but the stored result can be stale until refreshed
- Reads are slower but always perfectly fresh
- It uses no disk space at all
- It can never be indexed
Answer: Reads are fast but the stored result can be stale until refreshed. Reading a materialized view is instant because it hands back saved rows, but that snapshot is only as fresh as the last REFRESH.
How do you bring a materialized view's data up to date?
- It updates automatically whenever source data changes
- Run an INSERT directly into the view
- Drop and recreate the source tables
- Run REFRESH MATERIALIZED VIEW
Answer: Run REFRESH MATERIALIZED VIEW. A materialized view never updates itself; you run REFRESH MATERIALIZED VIEW (manually or on a schedule).
What does REFRESH MATERIALIZED VIEW CONCURRENTLY avoid?
- The need for a unique index
- Blocking readers while the view rebuilds
- Re-running the underlying query
- Storing the result on disk
Answer: Blocking readers while the view rebuilds. CONCURRENTLY rebuilds in the background and swaps the result in, so readers see old then new data without being blocked.
What does REFRESH MATERIALIZED VIEW CONCURRENTLY require?
- A unique index on the materialized view
- Superuser privileges
- An empty source table
- Row-level security to be enabled
Answer: A unique index on the materialized view. CONCURRENTLY needs a unique index on the view so PostgreSQL can diff old rows against new ones efficiently.
A plain REFRESH MATERIALIZED VIEW (without CONCURRENTLY) does what to readers?
- Lets them keep reading the old data uninterrupted
- Returns an error to every reader
- Takes a lock, blocking reads until the rebuild finishes
- Silently returns partial results
Answer: Takes a lock, blocking reads until the rebuild finishes. A full refresh takes a lock, so reads are blocked until the rebuild completes.
Which is the ideal use case for a materialized view?
- Data that must be live to the second
- An expensive aggregation queried often where slight staleness is acceptable
- A tiny lookup table that changes every second
- Storing user session tokens
Answer: An expensive aggregation queried often where slight staleness is acceptable. Materialized views shine for expensive, frequently-queried aggregations (dashboards, leaderboards) that can tolerate being slightly stale.
For data that must be live to the second, what should you prefer instead?
- A materialized view refreshed every second
- A regular view with no caching
- Dropping all indexes
- A summary table or an app/Redis cache
Answer: A summary table or an app/Redis cache. When you need live-to-the-second data or sub-millisecond reads, a summary table (updated incrementally) or an app/Redis cache fits better than a materialized view.
What characterises an app/Redis cache compared to a materialized view?
- It lives inside the database and refreshes on a schedule
- It is a key-value store outside the database with TTL-based expiry
- It can never become stale
- It is always slower than the database
Answer: It is a key-value store outside the database with TTL-based expiry. A Redis cache is a key-value store outside the database; your app reads it first and, on a miss, queries the DB and stores the result with a TTL.
Can you write directly to a materialized view with INSERT or UPDATE?
- Yes, just like a normal table
- Only if CONCURRENTLY is enabled
- No — change the source tables and REFRESH instead
- Only inside a transaction
Answer: No — change the source tables and REFRESH instead. You can't INSERT/UPDATE a materialized view directly; you change the source tables and refresh. You can, however, SELECT, JOIN, and index it.