A view versus a materialized view

A view stores a query. A materialized view stores the answer. Everything else about them follows from that one difference.

The plain view

CREATE VIEW active_course_counts AS
  SELECT level, COUNT(*) AS total FROM courses WHERE active GROUP BY level;

This stores no data. Selecting from it runs the underlying query, every time, against the live tables. It's a named query: a way to stop repeating SQL, not a way to make it faster.

The result is always current, because it's computed on the spot.

The materialized view

CREATE MATERIALIZED VIEW active_course_counts AS
  SELECT level, COUNT(*) AS total FROM courses WHERE active GROUP BY level;

One word, and the meaning flips. Postgres runs the query once and stores the rows on disk. Selecting from it reads those stored rows, with no aggregation and no scan of courses.

And the result is a snapshot. It reflects the tables as they were when it was last built, and it doesn't update itself when the underlying rows change.

flowchart TB
  V["CREATE VIEW"] --> V1["stores the query text"]
  V1 --> V2["always current. costs the full query every time"]
  M["CREATE MATERIALIZED VIEW"] --> M1["stores the resulting rows"]
  M1 --> M2["fast to read. stale until refreshed"]
  M2 --> M3["you now own the refresh"]

What you gain

The expensive work moves off the read path. An aggregation over millions of rows becomes a scan of a few hundred stored rows.

And it's a real relation. You can SELECT from it, join it to other tables, add indexes to it and put it in a WHERE clause: everything a table can do, on data that was expensive to compute.

What you take on

The refresh. Nothing updates a materialized view automatically. You decide when it's rebuilt, and everything between rebuilds is stale.

That's the same trade as any cache, which is why the next lessons keep comparing the two. The interesting question isn't "a materialized view or nothing" but "a materialized view or the cache you already have".

Where this application stands

It has neither.

materialized views: 0
regular views:      0

What it has instead is an application-level cache around the kind of query a materialized view exists for:

Rails.cache.fetch("admin/stats/#{resolved}", expires_in: 5.minutes) do

Admin::StatsQuery aggregates across many tables to build eight chart datasets, and it's cached for five minutes. That's a materialized view implemented in Ruby, and lesson 3 compares the two properly.

Key takeaways

• A view stores a query and is always current. A materialized view stores rows and is a snapshot.
• The difference is one keyword, and it flips what the object is for.
• A materialized view is a real relation: you can join it, index it and use it in a WHERE clause.
• Nothing refreshes it automatically. You own that, and everything between refreshes is stale.
• This application has zero views of either kind, and caches Admin::StatsQuery in Ruby instead.