Views and Materialized Views

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Views and Materialized Views
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A regular view is a saved query evaluated against current tables. A materialized view stores its result, so reads can be faster but remain stale until refreshed. Do not treat a view as the only security boundary; privileges and row-level security still matter.

Connect it to a real scenario

Expose a stable published-catalog interface with a view and store daily analytics in a materialized view. `REFRESH MATERIALIZED VIEW CONCURRENTLY` requires a suitable unique index.

A view is a saved live query
A materialized view stores results
Choose based on freshness requirements

Try the working example

sql

CREATE VIEW app.published_catalog AS
SELECT tutorial_id, title, slug, published_at
FROM app.tutorials
WHERE is_published = true;

CREATE MATERIALIZED VIEW app.tutorial_stats AS
SELECT t.tutorial_id, count(l.lesson_id) AS lesson_count
FROM app.tutorials t
LEFT JOIN app.lessons l USING (tutorial_id)
GROUP BY t.tutorial_id;

CREATE UNIQUE INDEX ON app.tutorial_stats (tutorial_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY app.tutorial_stats;

You should see

You have a live catalog view and a refreshable statistics view.

5-minute try-it

Create a view that exposes only public profile columns for active users.

One important caution

A materialized view without an explicit refresh schedule can quietly serve stale reports.

PostgreSQL — Views — PostgreSQL Global Development Group

Easy traps

A materialized view without an explicit refresh schedule can quietly serve stale reports.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Create a view that exposes only public profile columns for active users.

You'll know it worked when: You have a live catalog view and a refreshable statistics view.