View vs Materialized View: Always Fresh or Stored and Stale

View vs materialized view in one line: a view is a saved query with a name, expanded into every statement that uses it, so it stores nothing and is always current but costs the full query on every read; a materialized view runs the query once and stores the result like a table, so reads are cheap but the data is only as fresh as the last refresh. PostgreSQL has both. MySQL has views but no materialized views. SQLite has views only, so below a materialized view is emulated with a table and an explicit refresh, run live in Python. Vendor behaviour is quoted from the manuals, as of October 2026.

	View	Materialized view
Stores	the query text only	the query and its result rows
Freshness	always current	as of the last REFRESH
Cost paid	on every read	on every refresh, plus storage
Support	PostgreSQL, MySQL, SQLite	PostgreSQL; emulated with a table elsewhere

The problem it solves

A dashboard runs the same heavy aggregate, a join of three tables grouped by region and month, every time someone opens it. You want to name that query once, so nobody copies it into five services. A view gives you the name. If the query is also slow and the numbers can be an hour old, a materialized view gives you the speed, and the question becomes how stale "an hour old" is allowed to be.

The intuition

A view is a recipe; a materialized view is a dish cooked earlier and kept in the fridge.

• A view is substituted, not stored. When you query it, the engine inlines its definition into your statement and plans the whole thing together. MySQL's manual describes the MERGE algorithm as merging the view definition into the statement that refers to it, and the TEMPTABLE algorithm as computing the view's rows into a temporary table first, but either way the work happens at query time. Nothing is saved for the next reader.
• A materialized view keeps the rows. PostgreSQL says CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it remembers the query so the result can be refreshed on demand. It cannot be updated directly, and you can put indexes on it like a table.
• Staleness is the price. The manual is plain about it: reading a materialized view is often much faster than querying the underlying tables, but the data is not always current. Between refreshes, inserts into the base tables are invisible.

Refreshing is its own design problem. A plain REFRESH MATERIALIZED VIEW recomputes the whole result and can block readers while it runs. REFRESH MATERIALIZED VIEW CONCURRENTLY lets selects continue, but only if the materialized view has at least one UNIQUE index on plain column names, with no expression and no WHERE, and only once it has been populated. Even then, only one refresh at a time may run against it.

When to use which

• Use a view to give a query a name, hide joins, or restrict which columns a role can see, when the query is cheap enough to run on every read or the data must be current.
• Use a materialized view for an expensive aggregate read far more often than the data changes, where minutes or hours of staleness are acceptable: dashboards, reports, search tables.
• In MySQL or SQLite, build the materialized view yourself: a summary table plus a scheduled job, or triggers, that rebuild it.
• Neither if the result is needed once, inside one statement: that is a CTE or subquery's job.

Watch it run

The animation is the lesson's cider press, and it is exactly the query a view would store. The clause strip stays in written order while the highlight moves in evaluated order: FROM apple first, then WHERE rotten = 0 removes the bad fruit, GROUP BY variety tips the rest into bins, HAVING SUM(kilos) >= 50 sets the Kingston pressing aside, SELECT computes the totals and ORDER BY lines them up. A view replays all six steps every time it is read. A materialized view replays them once, at refresh time, and serves the finished list until the next one.

The code

PostgreSQL syntax for the materialized view, with the unique index that CONCURRENTLY requires:

CREATE MATERIALIZED VIEW pressing AS
  SELECT variety, SUM(kilos) AS total FROM apple
  WHERE rotten = 0 GROUP BY variety HAVING SUM(kilos) >= 50;

CREATE UNIQUE INDEX pressing_variety ON pressing (variety);

REFRESH MATERIALIZED VIEW pressing;               -- recompute; may block readers
REFRESH MATERIALIZED VIEW CONCURRENTLY pressing;  -- readers continue; needs the unique index

SQLite has no materialized views, so here is the emulation: a real view over the lesson's apple table, a table holding a copy of its result, and a refresh that swaps the contents inside one transaction. Then one late Kingston delivery:

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.executescript("""
  CREATE TABLE apple (variety TEXT, kilos REAL, rotten INT);
  INSERT INTO apple VALUES ('Dabinett',60,0), ('Dabinett',40,0),
    ('Kingston',30,0), ('Kingston',90,1), ('Yarlington',55,0);
  CREATE VIEW pressing AS
    SELECT variety, SUM(kilos) AS total FROM apple
    WHERE rotten = 0 GROUP BY variety HAVING SUM(kilos) >= 50;
  CREATE TABLE pressing_mv AS SELECT * FROM pressing;     -- the "materialized view"
""")
def refresh():                    # what REFRESH MATERIALIZED VIEW does, by hand
    db.execute("BEGIN")
    db.execute("DELETE FROM pressing_mv")
    db.execute("INSERT INTO pressing_mv SELECT * FROM pressing")
    db.execute("COMMIT")

show = lambda name: db.execute(f"SELECT * FROM {name} ORDER BY total DESC, variety").fetchall()
print(show("pressing"))      # [('Dabinett', 100.0), ('Yarlington', 55.0)]
print(show("pressing_mv"))   # [('Dabinett', 100.0), ('Yarlington', 55.0)]

db.execute("INSERT INTO apple VALUES ('Kingston', 25, 0)")   # Kingston reaches 55 kg
print(show("pressing"))      # [('Dabinett', 100.0), ('Kingston', 55.0), ('Yarlington', 55.0)]
print(show("pressing_mv"))   # [('Dabinett', 100.0), ('Yarlington', 55.0)]  <- stale
refresh()
print(show("pressing_mv"))   # [('Dabinett', 100.0), ('Kingston', 55.0), ('Yarlington', 55.0)]

plan = lambda q: [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + q)]
print(plan("SELECT * FROM pressing"))
# ['CO-ROUTINE pressing', 'SCAN apple', 'USE TEMP B-TREE FOR GROUP BY', 'SCAN pressing']
print(plan("SELECT * FROM pressing_mv"))
# ['SCAN pressing_mv']

The view saw Kingston's new 25 kilos the moment they were inserted; the stored copy kept the old answer until refresh ran. The query plans show why: reading the view scans apple and groups it again, while reading the copy only scans the stored rows.

The same comparison at size: 300,000 more deliveries, twenty reads through the view against one refresh and twenty reads of the copy:

import random
import time

random.seed(9)
db.execute("BEGIN")
db.executemany("INSERT INTO apple VALUES (?, ?, ?)",
               ((random.choice(["Dabinett", "Kingston", "Yarlington", "Bramley"]),
                 random.randint(5, 90), int(random.random() < 0.1)) for _ in range(300000)))
db.execute("COMMIT")

def timed(fn, times=20):
    start = time.perf_counter()
    for _ in range(times):
        fn()
    return round(time.perf_counter() - start, 4)

print("view, 20 reads:", timed(lambda: show("pressing")))      # view, 20 reads: 1.6436
print("refresh, once: ", timed(refresh, times=1))              # refresh, once:  0.0834
print("table, 20 reads:", timed(lambda: show("pressing_mv")))  # table, 20 reads: 0.0001
print(show("pressing") == show("pressing_mv"))                 # True
# on one machine, roughly; the seconds vary, the True does not

On one machine, roughly, one refresh cost about what one read of the view costs, and every later read of the copy was close to free. Right after the refresh both return the same rows; they drift apart again with the next insert.

The complexity

• View: the full query on every read, O(rows scanned) plus grouping or joins, with no storage beyond the definition. The planner can push your filters into it.
• Materialized view, read: a scan or index lookup on the stored result, usually far smaller than the base tables.
• Materialized view, refresh: the full query once, plus writing the result. The manual notes that CONCURRENTLY may be faster when only a few rows are affected, while a plain refresh tends to use fewer resources when many rows change.

Where it goes wrong

• Forgetting that nothing refreshes it. PostgreSQL does not refresh a materialized view on its own; schedule it, or the numbers age silently.
• CONCURRENTLY without a unique index. The refresh fails. It also fails on a materialized view created WITH NO DATA, which cannot be queried until its first refresh.
• Expecting a view to cache. A view is not faster than its query; a slow query behind a view is still slow.
• Updating through it. A materialized view cannot be updated directly; write to the base tables and refresh.
• Refreshing too often. A full recompute every minute on a large base table can cost more than the reads it saved.

When it shows up in interviews

As "what is the difference between a view and a materialized view?", or inside a design question: "the dashboard query takes eight seconds, what would you do?". Good answers name the staleness budget and the refresh strategy, not only the speed-up. Intermediate results inside one statement are compared in CTE vs subquery vs temp table, and the general freshness trade-off of stored copies is in caching explained. Why HAVING and aliases behave as they do inside the stored query is in the Query Execution Order lesson.

How to say it in an interview

"A view is a named query: the engine expands it into each statement, so it is always current and stores nothing, but every read pays the full cost. A materialized view stores the result, so reads are as cheap as reading a small table and it can be indexed, but it is stale until refreshed. In PostgreSQL I would refresh it on a schedule, using CONCURRENTLY with a unique index so readers are not blocked. MySQL has no materialized views, so there I would keep a summary table rebuilt by a job. The deciding question is how stale the business can tolerate."

Frequently asked questions

What is the difference between a view and a materialized view?

A view stores only its query and runs it every time it is read, so results are always current. A materialized view stores the result rows, so reads are fast and can use indexes, but the data stays as it was at the last refresh until you refresh it again.

Does MySQL support materialized views?

No. MySQL 8.4 has ordinary views, processed by merging into the outer query or through a temporary table at query time. To get materialized behaviour, create a summary table and rebuild it on a schedule or with triggers. PostgreSQL supports materialized views natively.

Does REFRESH MATERIALIZED VIEW lock the view?

A plain refresh can block other connections reading the materialized view while it runs. REFRESH MATERIALIZED VIEW CONCURRENTLY lets selects continue, but requires a unique index on plain columns covering all rows and an already populated view. Only one refresh can run against a view at a time.

Sources

• CREATE MATERIALIZED VIEW — PostgreSQL Documentation
• REFRESH MATERIALIZED VIEW — PostgreSQL Documentation
• Materialized Views — PostgreSQL Documentation
• View Processing Algorithms — MySQL 8.4 Reference Manual
• CREATE VIEW — SQLite Documentation