Views vs Materialized Views In PostgreSQL

This one's short compared to some of the earlier topics in this series, but it's also one of those things that's easy to get backwards if you only skim the definitions once. A view and a materialized view sound like variations on the same idea, and syntactically they're not far apart, but they behave in genuinely different ways once you actually use them.

Where the confusion comes from

Say you've got a dashboard summary showing total venues per city, and it's built on a GROUP BY query that's a bit expensive — maybe it's joining against closures too, computing a few aggregates at once. Someone suggests "just make it a view," and that's reasonable advice, except it doesn't actually solve the performance problem people usually mean when they say that. A regular view doesn't cache anything. It just saves you from retyping the query.

The two, side by side

CREATE VIEW active_venues AS
SELECT * FROM venues WHERE is_closed = FALSE;

CREATE MATERIALIZED VIEW venue_stats AS
SELECT city, COUNT(*) AS total FROM venues GROUP BY city;

REFRESH MATERIALIZED VIEW venue_stats;

A regular VIEW is really just a stored query, nothing more. Every time you SELECT * FROM active_venues, Postgres runs the underlying query against venues again, live, right then. It's always current, because it's not storing anything — it's just a name attached to a query definition that gets substituted in and executed fresh each time.

A MATERIALIZED VIEW actually stores the result physically, like a real table. Reading from venue_stats is fast, because it's just reading stored rows, not recomputing anything. The trade-off is that it goes stale the moment the underlying data changes, and it stays stale until you explicitly run REFRESH MATERIALIZED VIEW to recompute and re-store the result.

Picking one

Use a regular VIEW when the underlying query is cheap enough that running it live isn't a real cost, and when the data behind it absolutely needs to be current every time someone reads it. active_venues above is a good fit — filtering on a boolean is trivial for Postgres, there's no real performance reason to cache it, and you'd generally want to know immediately if a venue's status changed.

Use a MATERIALIZED VIEW when the query is genuinely expensive — heavy aggregation, several joins, something that takes real time to compute — and when the consumers of that data can tolerate it being slightly stale in exchange for speed. A dashboard's summary stats are the classic case: nobody's checking venue_stats expecting it to reflect a venue that closed thirty seconds ago, and recomputing that aggregation on every single page load for every user would be wasteful when refreshing it once every few minutes, or on some schedule, is perfectly acceptable.

Where this leaves you

The names sound similar, but the behavior isn't — a VIEW is a saved query that always runs live, a MATERIALIZED VIEW is a saved result that needs an explicit REFRESH to stay current. Picking between them comes down to one question: does this need to be live every time, or is a periodically refreshed snapshot good enough for what it's being used for.

Next up: window functions — a step past GROUP BY that lets you compute things like running totals and per-group rankings without collapsing your rows down to one per group.