Views and Materialised Views — PostgreSQL Mastery

As queries grow complex and get reused, copying the same SQL everywhere becomes a liability. Views solve this by giving a stored query a name: a view is a saved SELECT that you query like a table, so the logic lives in one place. It simplifies access, hides complexity, and provides a stable interface even as the underlying tables change.

A regular view stores no data — it is a query that runs fresh each time you reference it, always reflecting the current tables. A materialized view, by contrast, stores the computed result physically and serves it instantly, at the cost of being a snapshot that must be explicitly refreshed. Choosing between them is a trade-off between freshness and speed.

This lesson covers creating and using views, when a view is updatable, the freshness-versus-performance trade-off of materialized views, and how to refresh them without blocking readers. Views are also a security and abstraction tool, exposing a curated slice of the data while hiding the tables beneath.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.