SQL View vs Materialized View
Always current and recomputed every time, versus fast and stored but potentially stale — pick based on which tradeoff you can live with.
What & Why
The whole decision comes down to one question: does this query need to reflect the data right now, or is slightly-stale-but-fast an acceptable tradeoff?
See How It Works
BUSINESS QUESTION
Compare a live daily growth-event summary with a stored snapshot built from the same internal source table.
| 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
-- Re-runs the source query whenever it is read
CREATE VIEW public.daily_event_summary_live AS
SELECT
created_at::date AS event_date,
event_name,
COUNT(*) AS events
FROM growth.events
GROUP BY event_date, event_name;
-- Stores the same result until it is refreshed
CREATE MATERIALIZED VIEW public.daily_event_summary_snapshot AS
SELECT
created_at::date AS event_date,
event_name,
COUNT(*) AS events
FROM growth.events
GROUP BY event_date, event_name;RESULT — event summary query shared by either object
| event_date | event_name | events |
|---|---|---|
| 2024-01-15 | signup | 1 |
| 2024-01-16 | activated | 1 |
| 2024-02-10 | signup | 1 |
| 2024-03-12 | report_viewed | 1 |
A view calculates these rows live; a materialized view stores them until refresh.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario