Materialized Views

Your app has a leaderboard: the top authors by comment count. It's a beloved page — everyone checks their rank. And it's getting slow, because to build it the database has to scan and count all five million comments, grouped by author, every single time someone loads it.

In the last lesson you'd have reached for a denormalized counter. But this isn't one number on one row — it's a whole query: a scan, a GROUP BY, an ORDER BY, a LIMIT. Maintaining that incrementally on every write is a nightmare.

So you make a different move. You tell the database: run this expensive query once, and keep the answer around. That's a materialized view — and it's denormalization's grown-up sibling. The database stores the precomputed result of an entire query, and hands it back instantly. But — and you can feel this coming by now — a stored answer is a copy, and a copy has a catch. This time the catch is a fundamentally different, and much safer, one than last lesson's.

Same real Postgres: 5,000,000 comments, 391 MB. The leaderboard query scans and aggregates the lot:

Now materialize it. One statement tells the database to run the query and store the result as a real, on-disk table you can read like any other:

375 ms → 1.4 ms (~280× faster), 67 pages instead of ~31,851, and the entire precomputed answer is 752 kB — versus scanning a 391 MB table.

The word doing all the work is materialized. An ordinary VIEW in SQL is just a saved query — it re-runs from scratch every time, exactly as slow as before. A MATERIALIZED view stores the result. That one word is the entire difference between a name for a query and a cache of its answer.

The read is now trivial. So where did the work go? It went into a single word in that read's future: REFRESH.

The instant you stored that answer, it started aging. New comments arrive; the view doesn't know. Watch what happens when an author goes viral:

The view says 589; reality is 100,589. That looks like a disaster — until you notice something crucial, and it's the reason materialized views exist:

589 is not a made-up number. It was the exact count at the moment of the last refresh.

Compare this with the denormalized counter from Denormalization (#32). That counter was maintained incrementally — +1 on every write — so a lost update or a forgotten path could leave it holding a number that was true at no moment in history (remember: counter 75, when the real count had never been anything but climbing past 1,000). A materialized view is recomputed wholesale from the truth on every refresh, so it can only ever be a real past state.

A denormalized column can be WRONG. A materialized view can only be OLD.

It cannot lie. It can only show you a true past — never a false present.

That is a fundamentally safer failure mode. "The leaderboard is a few minutes behind" is an annoyance you can reason about and bound. "The counter says a number that was never real" is a bug you have to hunt. You've traded drift-risk for staleness — and staleness, unlike drift, is honest.

So you refresh. And here's the thing that makes this a genuine engineering decision rather than a free lunch: refreshing re-runs the whole expensive query.

Read that carefully. The refresh pays the exact cost you were trying to avoid — the 375 ms aggregate. Which means:

You cannot refresh on every read. Refresh-on-every-read is the slow query you escaped.

So you refresh periodically, and now you're holding a dial:

How stale may the answer be? = How often do you refresh? = How much recompute do you pay?

Those are three names for the same setting. Refresh every minute → the view is at most a minute stale, and you pay 375 ms every minute. Refresh every hour → cheap, but the leaderboard can be an hour old. There is no setting that is fresh and cheap. You pick a point on the dial, and the right point is not a property of the database — it's a property of the question. A leaderboard that's a minute old is fine. A bank balance that's a minute old is a lawsuit.

There's a second, sharper cost hiding in REFRESH, and it surprises people in production: a plain refresh blocks every reader while it runs.

I proved it from pg_locks. A plain REFRESH MATERIALIZED VIEW takes an AccessExclusiveLock on the view — the strongest lock there is — and holds it for the entire refresh:

AccessExclusiveLock conflicts with the humble AccessShareLock that every single SELECT must acquire. So for the whole duration of the refresh — 375 ms, or 3.7 seconds on a bigger table — every read of that view waits. Your beloved leaderboard page freezes for everyone, every refresh cycle.

The fix is REFRESH MATERIALIZED VIEW **CONCURRENTLY**. It builds the new version in the background and only swaps it in at the end, taking merely an ExclusiveLock — which does not conflict with SELECT. Readers keep reading the old snapshot the whole time and never notice.

The bill for CONCURRENTLY: it (1) requires a UNIQUE index on the view (it diffs old→new row by row); (2) keeps the old and new copies both on disk while it builds; and (3) does more total work. You buy zero read-downtime with time, storage, and a schema requirement. Non-blocking, not free — the recurring theme of this entire section.

Here's the leaderboard, live. The materialized view sits next to the truth; comments keep arriving and piling up unseen by the view — watch the staleness grow on its own.

Hit refresh and the view snaps up to the truth — but notice it re-runs the whole aggregate, which is why you can't do it on every read. Then switch the refresh mode: on plain refresh, the banner turns red and tells you every reader is blocked (that AccessExclusiveLock); on CONCURRENTLY, the old snapshot stays readable throughout.

The question the widget keeps putting in front of you is the only one that matters here: how old is this answer allowed to be — and what will you pay to keep it fresher than that?

You now have three ways to make a read faster by keeping a copy of the answer, and they differ in exactly who maintains the copy and how it can fail. This is the decision, laid out:

Reach for a materialized view when the read is a heavy query over many rows (a leaderboard, a daily revenue rollup, an analytics dashboard), it's read far more than the data changes, and some staleness is acceptable. That last clause is the gate: if the answer must be to-the-second correct, a matview is the wrong tool — use a denormalized counter (always fresh) or compute it live.

One sharp edge to know: PostgreSQL has no built-in incremental refresh — every refresh recomputes the whole query, even if one row changed. If you need a big view kept continuously fresh cheaply, you're into extensions (pg_ivm), trigger-maintained rollup tables, or another engine's continuous aggregates. That's a real frontier, and it's beyond this lesson — but knowing the limitation exists is what stops you from designing a matview you'll have to refresh every second.

"A view makes reads fast." A plain VIEW is just a saved query — it re-runs every time, exactly as slow. Only a MATERIALIZED view stores the result. The word is the whole difference.

"The view is out of date, so it's wrong." It's stale, not wrong — an exact snapshot of a real past state. That's a fundamentally safer failure than a drifted denorm counter, which can hold a value that was true at no moment.

"Just refresh it often to keep it fresh." Each refresh re-runs the full aggregate (375 ms → 3.7 s measured). Refresh-on-every-read is the slow query. Frequency is a cost dial, not a free knob.

"REFRESH is transparent to readers." A plain REFRESH takes an AccessExclusiveLock and blocks every SELECT for its whole duration. Use CONCURRENTLY (needs a unique index) if reads can't pause.

"REFRESH CONCURRENTLY is free non-blocking magic." It costs more time, needs a unique index, and holds two copies on disk while it builds. Non-blocking, not free.

"A matview replaces the table." It's a derived copy. The base tables are the truth; the view is always recomputable from them — and it must be, because it will be stale.

• ⭐⭐⭐ A denormalized column can be WRONG; a materialized view can only be OLD. A matview is recomputed wholesale from the truth, so it's always an exact snapshot of a real past state — it never lies, it just tells you an old truth. That's a fundamentally safer failure than incremental drift.
• ⭐⭐ CREATE MATERIALIZED VIEW runs a heavy query once and stores the answer. Measured: the leaderboard read went 375 ms → 1.4 ms (~280×), the whole view is 752 kB, and the word materialized — not view — is what makes it fast.
• ⭐⭐⭐ The refresh dial: how stale it may be = how often you refresh = how much recompute you pay. Three names for one setting. Refresh re-runs the full aggregate (375 ms → 3.7 s), so you cannot refresh on every read. The right staleness is a property of the question, not the tool.
• ⭐ Plain REFRESH blocks every reader (it holds an AccessExclusiveLock for its whole duration — proven via pg_locks). REFRESH CONCURRENTLY doesn't, but needs a unique index, holds two copies while building, and does more work. Non-blocking, not free.
• Choose a matview when the read is a heavy query over many rows, read far more than written, and some staleness is acceptable. If it must be to-the-second, use a denorm counter or compute live.
• Postgres has no built-in incremental refresh — every refresh recomputes the whole query. Know the limit so you never design a view you must refresh every second.

Next: Connection Pooling for Databases. You've spent four lessons making the query cheaper. Now you'll find that at scale, a shocking amount of the cost isn't the query at all — it's the connection it rides in on.