View vs Materialized View: A Saved Question vs a Saved Answer

The dashboard takes forty seconds to load. Someone opens a ticket. Someone else — probably you — reaches for the fix everybody reaches for:

CREATE VIEW daily_revenue AS
SELECT
  date_trunc('day', o.created_at) AS day,
  c.region,
  count(*)                        AS orders,
  sum(oi.qty * oi.unit_price)     AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers  c  ON c.id = o.customer_id
GROUP BY 1, 2;

The query now has a name. The code that calls it is four lines instead of forty. It reads beautifully.

And the dashboard still takes forty seconds.

This is the most common non-fix in backend engineering, and it survives because nobody ever checks. So let's check.

---

A view is a saved question

CREATE VIEW stores no data. None. It writes one row into the catalog containing, essentially, the text of your query. That's the whole object.

You can watch it store nothing:

CREATE VIEW daily_revenue AS SELECT ...;

SELECT pg_size_pretty(pg_total_relation_size('daily_revenue'));
--  0 bytes

So when you run this:

SELECT * FROM daily_revenue WHERE region = 'EU';

the planner does something that surprises people the first time they see it: it substitutes the view's definition into your query and plans the whole thing as if you had typed the forty-line version by hand. Same joins. Same aggregate. Same sequential scan over order_items. Same forty seconds.

That gives you two genuinely valuable properties:

1. The answer is always current. There is no window in which a view can be wrong, because there is nothing to be wrong — it re-derives the truth on every call.
2. It costs exactly what the query costs. No more, no less. No hidden storage, no background job, no failure mode of its own.

Which leads to the sentence worth keeping:

A view is a naming convenience, not a performance feature.

That is not an insult. It is a real and correct tool — when the query is already fast and you simply wanted a name, a stable interface, or a place to hang column-level permissions. A view is how you stop copy-pasting the same six-table join into eleven services.

It is never, ever the answer to "the dashboard is slow." You named the query. You didn't fix it.

---

A materialized view is a saved answer

One extra word changes the object entirely:

CREATE MATERIALIZED VIEW daily_revenue_mv AS
SELECT
  date_trunc('day', o.created_at) AS day,
  c.region,
  count(*)                        AS orders,
  sum(oi.qty * oi.unit_price)     AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers  c  ON c.id = o.customer_id
GROUP BY 1, 2;

Now the database runs the query once, right now, and writes the result rows to disk. What you have afterwards is not a definition — it is a real, physical table that happens to remember where it came from.

SELECT pg_size_pretty(pg_total_relation_size('daily_revenue_mv'));
--  14 MB

And because it is a real table, you can do real table things to it:

CREATE UNIQUE INDEX ON daily_revenue_mv (day, region);

That index is the payoff. The dashboard's forty-second aggregate becomes an index lookup against fourteen megabytes of pre-computed rows:

SELECT * FROM daily_revenue_mv WHERE region = 'EU' AND day > now() - interval '30 days';
--  9 ms

Forty seconds to nine milliseconds, and nothing about your data got faster. You just stopped asking the expensive question.

---

It is not a cache, and it is not an index

Two comparisons people reach for, both slightly wrong in ways that matter.

It is not a cache. A cache — Redis in front of Postgres, say — is a separate system. It lives outside the database, it has a TTL and an eviction policy, it can be cold, it can be evicted under memory pressure, and your application code has to know it exists (check cache → miss → query → write back). A materialized view lives inside the database, is durable, is backed up with everything else, never evicts itself, and your application just queries a table name. It goes stale on a schedule you own, not on a policy the cache manages for you.

It is not an index. This distinction is sharper than it sounds:

• An index makes the same query faster. The planner still executes your aggregate; the index just helps it find rows.
• A materialized view means not running the query at all. The work happened earlier, at refresh time.

That is the real category difference: an index optimises execution, a materialized view moves execution to a different moment in time. Which is exactly where the cost hides.

---

The honest cost: everything you gained, you gained by moving work into the past

Here is the trade in one line: that result was true at REFRESH time, and is wrong from the first write afterwards.

Not "eventually wrong." Wrong immediately, the instant the next order lands. The only question is how wrong you're willing to be, and for how long.

Refreshing is not free, and the default one is hostile

REFRESH MATERIALIZED VIEW daily_revenue_mv;

That statement takes an ACCESS EXCLUSIVE lock on the view for the entire rebuild. Every reader blocks. If the underlying aggregate takes forty seconds, your dashboard is unavailable for forty seconds, every refresh.

This is how a performance optimisation quietly becomes a nightly outage. The 03:00 cron looks harmless right up until the view grows, the rebuild stretches to eleven minutes, and someone in another timezone spends eleven minutes staring at a spinner.

The escape hatch:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue_mv;

Readers keep reading throughout. But it is not free either:

• It requires a UNIQUE index on the view — no unique index, and the statement simply errors out. (This is the real reason the CREATE UNIQUE INDEX above is not optional.)
• It is slower overall, because it builds the new result in a temp table and then computes and applies the diff rather than swapping wholesale.
• It cannot be used on a view that has never been populated.

The failure mode that actually hurts

A refresh is a job, and jobs fail. The cron gets disabled during an incident. The role loses a permission. The statement hits a lock timeout. The rebuild starts overlapping with the previous one.

And your dashboard keeps loading in nine milliseconds, serving numbers from last Tuesday, with total confidence and no warning whatsoever.

A silently failing refresh serves confidently stale numbers. That is the worst failure mode available to you — far worse than "slow" — because nothing looks broken. If you take one operational rule from this article: monitor refresh recency, not refresh success. Alert on now() - last_successful_refresh > your_budget, and store that timestamp inside the view itself so a reader can see it:

CREATE MATERIALIZED VIEW daily_revenue_mv AS
SELECT ..., now() AS computed_at
FROM ...;

And the boring costs

You now have duplicated data to size, back up, and monitor. A 14 MB view is nothing; a 400 GB one is a capacity conversation.

---

Side by side

	View	Materialized View
What it stores	The SQL text. Zero rows.	The result rows, on disk.
On SELECT	Re-runs the underlying query	Reads a table
Speed	Exactly the query's cost, forever	Milliseconds, regardless of query cost
Freshness	Always current, by construction	As of the last refresh
Can be indexed	No (it has no rows of its own)	Yes — and should be
Storage	None	A full copy of the result set
Ops burden	None	Scheduled refresh + recency monitoring
Fails by	Being slow, visibly	Being stale, invisibly
Reach for it when	The query is fast and you want a name	The query is slow and slightly-old is acceptable

---

The same trade-off, one layer up: precomputation in AI serving

If you work on LLM and retrieval systems rather than dashboards, you have already made this exact decision several times — just without the SQL keyword on it. The pattern generalises: a view is compute-on-read, a materialized view is compute-on-write, and the price of compute-on-write is always freshness.

• Embeddings are a materialized view of your documents. You do not re-embed the corpus per query — you embed once, write the vectors to disk, and index them (HNSW, IVF-Flat). Fast reads, and the identical staleness bug: edit a document without re-embedding it and retrieval confidently returns the old meaning. "Silently failing refresh serves confidently stale numbers" is exactly the RAG pipeline whose re-index job died three weeks ago while search kept answering in 12 ms.
• A semantic cache is a cache, not a materialized view — same distinction as above. It sits outside the model, has a TTL and an eviction policy, and is cold on the first hit. A precomputed answer table for your top 500 support questions is a materialized view: durable, inside your system, stale on your schedule.
• A feature store's offline table is a materialized view of your event stream. That is literally what "training/serving skew" is: your served features were true at materialisation time and wrong from the first event afterwards.
• Prefix / KV caching for prompts is the same move again at the token level — pay the compute once for a shared prefix, reuse it, and accept that the reused state is only valid while the prefix is genuinely unchanged.

The engineering lesson transfers cleanly in both directions: whenever you move work earlier to make reads fast, you have not removed the work. You have converted a latency cost into a correctness window, and someone has to own that window.

---

The verdict

You are not choosing between fast and slow. You are choosing between:

"always right" and "fast, with a staleness budget you say out loud."

That second phrase is the whole discipline. The moment you can write the budget down as a sentence — "this report may be up to 10 minutes old" — three things happen. You can put it in the UI. You can alert on it. And you can hand it to a product owner, who is the person who should have been deciding it all along. It stops being a database question and becomes a product one.

So the one question you must answer before you ship a materialized view is not "how do I refresh it?" It is:

How stale is this allowed to be, and who is allowed to be surprised by that?

If nobody can answer it, you do not have a caching problem. You have a requirements problem wearing SQL.

A view saves you typing. A materialized view saves you time, and charges you freshness. Two different products — one of which people buy by accident.

---

▶ Watch the reel: View vs Materialized View in 98 seconds · join the breakdowns on Telegram.