Materialized Views & Caching

Reviewed & published by Brayan K

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.

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.

What You'll Learn

• The difference between a regular view (re-runs) and a materialized view (stored)
• Create a materialized view with CREATE MATERIALIZED VIEW
• Keep it current with REFRESH — full vs CONCURRENTLY
• Reason about the staleness vs speed trade-off
• Spot the right use case: expensive aggregations and dashboards
• Choose between materialized views and app/Redis caching layers

Our Scenario: a slow orders table

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.

1. A View That Stores Its Answer

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.

-- engine: postgres — this lesson is about MATERIALIZED VIEWs, which SQLite
-- does not have, and it groups with DATE_TRUNC, which SQLite also lacks.
-- Pressing Run on this site uses SQLite, so run these against a real
-- PostgreSQL server.
--
-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id  INTEGER,
    order_date  TEXT,
    quantity    INTEGER,
    total       REAL,
    status      TEXT
);

INSERT INTO orders (id, customer_id, product_id, order_date, quantity, total, status) VALUES
    (1, 101, 1, '2026-01-14', 2,  49.98, 'shipped'),
    (2, 102, 3, '2026-01-22', 1,  79.00, 'shipped'),
    (3, 101, 4, '2026-02-03', 5,  16.25, 'pending'),
    (4, 103, 5, '2026-02-17', 1,  32.00, 'shipped'),
    (5, 104, 6, '2026-02-28', 3,  38.97, 'cancelled'),
    (6, 102, 2, '2026-03-05', 4,  38.00, 'pending'),
    (7, 105, 3, '2026-03-19', 2, 158.00, 'shipped'),
    (8, 101, 6, '2026-03-30', 1,  12.99, 'shipped');

-- A REGULAR VIEW is just a saved query. It stores NO data.
-- Every SELECT against it re-runs the full aggregation underneath.
CREATE VIEW v_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total)  AS revenue,
       COUNT(*)    AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Reading it scans + groups the WHOLE orders table again, every time:
SELECT * FROM v_monthly_revenue;   -- slow on millions of rows (always fresh)

Now the materialized version. Notice the only new word is MATERIALIZED — but the behaviour changes completely: the result is computed once and saved.

-- A MATERIALIZED VIEW runs the query ONCE and STORES the result on disk,
-- like a physical table. Reads are then instant — they touch the stored
-- rows, not the source tables.
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total)         AS revenue,
       COUNT(*)           AS order_count,
       ROUND(AVG(total),2) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Reads the pre-computed result — fast, like SELECTing from a small table:
SELECT * FROM mv_monthly_revenue;   -- instant (but only as fresh as last refresh)

-- A UNIQUE index makes lookups faster AND unlocks CONCURRENTLY refresh later:
CREATE UNIQUE INDEX idx_mv_revenue_month ON mv_monthly_revenue (month);

Your Turn: pre-compute a slow aggregation

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.

-- 🎯 YOUR TURN — fill in the two blanks, then press "Try it Yourself"
-- Goal: turn this slow live aggregation into a fast pre-computed one.
-- The orders table has millions of rows, so the live GROUP BY is painful.

CREATE ___ VIEW mv_daily_sales AS    -- 👉 keyword that makes it STORE the result
SELECT order_date,
       SUM(total)  AS revenue,
       COUNT(*)    AS order_count
FROM orders
___ order_date;                      -- 👉 the clause that buckets rows per day

-- ✅ Expected: a stored view 'mv_daily_sales' you can read instantly,
--    e.g. 2026-06-14 | 18230.50 | 412 ,  2026-06-15 | 9120.00 | 205 , ...

2. Keeping It Fresh: REFRESH

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:

• Full refresh — REFRESH MATERIALIZED VIEW mv_x; rebuilds the whole thing but takes a lock, so reads are blocked until it finishes.
• Concurrent refresh — REFRESH MATERIALIZED VIEW CONCURRENTLY mv_x; rebuilds in the background and swaps the result in. Readers see old data, then new data, with no blocking.

-- A materialized view does NOT update itself. New orders won't appear
-- until you REFRESH it. The simplest refresh fully rebuilds the result:
REFRESH MATERIALIZED VIEW mv_monthly_revenue;
-- ⚠️ Takes a lock — readers are BLOCKED until the rebuild finishes.

-- CONCURRENTLY rebuilds in the background and swaps the result in.
-- Readers keep seeing the OLD data, then see the NEW data — no blocking.
-- It REQUIRES the unique index you created earlier.
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

-- See how stale your data is (when was it last refreshed?):
SELECT relname, last_refresh
FROM pg_stat_user_tables
WHERE relname = 'mv_monthly_revenue';

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.

-- Most teams refresh on a schedule so the data is "fresh enough".
-- Example with pg_cron (a PostgreSQL extension): rebuild every hour, on the hour.
SELECT cron.schedule(
    'refresh_revenue',                                    -- a name for the job
    '0 * * * *',                                          -- cron: minute 0 of every hour
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue'
);

-- Choose the interval from the freshness the business actually needs:
--   "live to the second"      → don't use a materialized view (see caching below)
--   "within a few minutes"    → refresh every 1–5 minutes
--   "yesterday's totals"      → refresh nightly  ('0 2 * * *' = 2am daily)

Your Turn: choose a refresh strategy

You're given a freshness requirement ("up to 10 minutes stale, no blocking"). Fill in the blanks to match it.

-- 🎯 YOUR TURN — pick the refresh that fits the requirement, fill the blanks.
-- Requirement: a "Top Sellers" dashboard. The team agreed numbers can be up
-- to 10 minutes stale, and it must NOT block the live store while refreshing.

-- (a) The refresh keyword that avoids blocking readers:
REFRESH MATERIALIZED VIEW ___ mv_top_sellers;   -- 👉 one word

-- (b) Schedule it every 10 minutes with pg_cron:
SELECT cron.schedule('refresh_top_sellers',
    '___ * * * *',                              -- 👉 cron field for "every 10 minutes"
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_sellers');

-- ✅ Expected: (a) CONCURRENTLY  (b) '*/10'  → refreshes ~10-min fresh, no downtime.
--    Reminder: CONCURRENTLY only works if mv_top_sellers has a UNIQUE index.

3. The Killer Use Case: Dashboards

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.

-- Real-world: a dashboard that aggregates millions of rows lives off
-- materialized views. Compute the expensive numbers ONCE, read them instantly.

-- Customer Lifetime Value — a heavy join + GROUP BY you don't want per page load:
CREATE MATERIALIZED VIEW mv_customer_ltv AS
SELECT c.id                 AS customer_id,
       c.name,
       COUNT(o.id)          AS total_orders,
       SUM(o.total)         AS lifetime_value,
       ROUND(AVG(o.total),2) AS avg_order_value,
       MAX(o.order_date)    AS last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Unique index → fast lookups + enables CONCURRENTLY refresh:
CREATE UNIQUE INDEX idx_mv_ltv_customer ON mv_customer_ltv (customer_id);

-- The dashboard query is now a trivial, instant read:
SELECT customer_id, name, lifetime_value, total_orders
FROM mv_customer_ltv
ORDER BY lifetime_value DESC
LIMIT 5;

4. Other Caching Layers (and When to Prefer Them)

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:

• App / Redis cache — a fast key→value store outside the database. Your app reads it first and only hits the database on a "miss", storing the answer with a TTL (time-to-live) so it auto-expires. Sub-millisecond reads, but extra infrastructure and you own invalidation.
• Summary table — a normal table you update incrementally as data changes (e.g. bump a counter per order). Always real-time, never re-scans the source, but you maintain the update logic yourself.

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.

-- A materialized view is ONE caching layer. It lives inside the database
-- and you refresh it on a schedule. Other layers trade freshness differently.

-- 1) APP / REDIS CACHE — a key→value store OUTSIDE the database.
--    Your app checks Redis first; on a miss it runs the query, then stores
--    the result with a TTL (time-to-live) so it auto-expires. Pseudo-code:
--      value = redis.get("revenue:2026-06")        -- sub-millisecond read
--      if value is null:
--          value = db.query("SELECT ... FROM orders ...")
--          redis.set("revenue:2026-06", value, ttl=600)   -- expire in 10 min
--    Fastest reads, but it's extra infrastructure and YOU must invalidate it.

-- 2) SUMMARY TABLE — a normal table you update INCREMENTALLY as data changes,
--    so it's always real-time without ever re-scanning the source:
CREATE TABLE daily_stats (
    stat_date     DATE PRIMARY KEY,
    total_orders  INT  DEFAULT 0,
    total_revenue NUMERIC(15,2) DEFAULT 0
);

-- Bump the counters on each new order — cheap, and instantly fresh:
INSERT INTO daily_stats (stat_date, total_orders, total_revenue)
VALUES (CURRENT_DATE, 1, 149.99)
ON CONFLICT (stat_date) DO UPDATE
SET total_orders  = daily_stats.total_orders  + 1,
    total_revenue = daily_stats.total_revenue + EXCLUDED.total_revenue;

Common Errors (and the fix)

• Stale data served as if it were live: a materialized view only shows data as of its last refresh. If users report "wrong" totals, you probably forgot it's a snapshot — surface the refresh time (e.g. "Updated 12 min ago") so stale never looks broken.
• Forgetting to refresh at all: a freshly created materialized view is populated once and then frozen. No REFRESH (manual or scheduled) means it drifts further from reality forever. Always pair CREATE MATERIALIZED VIEW with a refresh plan.
• Refreshing too often: a full REFRESH re-runs the whole expensive query and locks reads. Refreshing every few seconds can cost more than the live query you were trying to avoid. Match the interval to the freshness actually required.
• cannot refresh ... concurrently ... create a unique index: REFRESH MATERIALIZED VIEW CONCURRENTLY needs a unique index on the view. Add one (CREATE UNIQUE INDEX ... ON mv_x (key);) before refreshing concurrently.
• cannot change materialized view "mv_x" with INSERT/UPDATE: you can't write to a materialized view directly — change the source tables and REFRESH.

📘 Quick Reference

Pick a caching layer:

Frequently Asked Questions

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.

Mini-Challenge: Product Leaderboard

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.

-- 🎯 MINI-CHALLENGE
-- A "products leaderboard" page is slow because it joins products to
-- order_items and aggregates on every load. Using ONLY this lesson's ideas:
--   1. CREATE a MATERIALIZED VIEW called mv_product_sales with, per product:
--        product_id, product_name, units_sold (SUM of quantity),
--        revenue (SUM of quantity * unit_price)
--      (join products p to order_items oi, GROUP BY product_id, product_name)
--   2. Add a UNIQUE index on product_id so you can refresh CONCURRENTLY
--   3. Write the refresh command the hourly pg_cron job will run
--
-- ✅ Expected: an instant leaderboard read, e.g.
--    101 | Wireless Mouse | 980 | 24480.20 ,  102 | Coffee Mug | 1500 | 14250.00

-- your SQL here

🎉 Lesson Complete

• ✅ A regular view re-runs its query; a materialized view stores the result for fast reads
• ✅ CREATE MATERIALIZED VIEW pre-computes; reads are then instant but can be stale
• ✅ REFRESH brings it current — CONCURRENTLY avoids blocking (and needs a unique index)
• ✅ The interval is a staleness trade-off — match it to the freshness the business needs
• ✅ Best for expensive, frequent, slightly-stale aggregations (dashboards, leaderboards)
• ✅ For live-to-the-second data or sub-ms reads, prefer a summary table or app/Redis cache
• ✅ Next: Advanced Aggregations — window functions, rollups, and grouping sets

Practice quiz

Continue this course

• Previous: Sharding & Distributed SQL Concepts (Vitess, Yugabyte, CockroachDB)
• Next: Advanced Aggregations & Pivot/Unpivot Techniques — GROUPING SETS, ROLLUP, CUBE, and pivot tables in SQL
• Quick reference: SQL cheat sheet

Related lessons

• Views