SQL Materialized Views

A view whose result is actually stored — fast to read, but goes stale until manually refreshed.

What & Why

Unlike a regular view, a materialized view physically stores its query's result. Reading it is as fast as reading any real table — but it does not automatically update when the underlying data changes. Someone (or something) has to explicitly refresh it.

See How It Works

BUSINESS QUESTION

Persist daily event counts for a reporting workload that does not require live updates.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

CREATE MATERIALIZED VIEW public.daily_event_summary AS
SELECT
  created_at::date AS event_date,
  event_name,
  COUNT(*) AS events,
  COUNT(DISTINCT user_id) AS users
FROM growth.events
GROUP BY event_date, event_name;

RESULT — public.daily_event_summary snapshot

event_date	event_name	events	users
2024-01-15	signup	1	1
2024-01-16	activated	1	1
2024-02-10	signup	1	1
2024-03-12	report_viewed	1	1

The materialized view stores these four growth.events summary rows until the next refresh.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice