Views & Materialized Views

A view is a saved query with a name — PostgreSQL also has materialized views, which go a step further and actually store the result, rather than re-running the query every time.

A regular view

CREATE VIEW pro_users AS
SELECT payload->'user'->>'name' AS user_name
FROM events
WHERE payload @> '{"user": {"plan": "pro"}}';

SELECT * FROM pro_users;

 user_name 
-----------
 Priya
(1 row)

This should look familiar from the SQL course's Views lesson — a plain view is just a named, reusable query. Every time you SELECT from pro_users, PostgreSQL re-runs the underlying query from scratch, so the result is always current.

Materialized views: trading freshness for speed

For a query expensive enough that re-running it on every read is wasteful — a report scanning millions of rows, say — a materialized view stores the result physically, like a table:

CREATE MATERIALIZED VIEW event_type_counts AS
SELECT payload->>'type' AS event_type, COUNT(*) AS total
FROM events
GROUP BY payload->>'type';

SELECT * FROM event_type_counts;

 event_type | total 
------------+-------
 signup     |     2
(1 row)

Materialized views don't auto-update

INSERT INTO events (payload) VALUES ('{"type": "signup", "user": {"name": "Alex", "plan": "free"}}');
SELECT * FROM event_type_counts;

 event_type | total 
------------+-------
 signup     |     2
(1 row)

REFRESH MATERIALIZED VIEW event_type_counts;
SELECT * FROM event_type_counts;

 event_type | total 
------------+-------
 signup     |     3
(1 row)