Views vs Materialized Views vs CTEs — Choosing the Right Tool

Before writing a materialized view, understand what it is and when it wins.

Tool	Stores Data?	Fresh?	Indexed?	Use When
View	No	Always	Via base	Simple aliasing, security
CTE	No (usually)	Always	No	Single-query reuse
Temp Table	Yes (session)	On demand	Yes	Multi-step within session
Materialized View	Yes	On refresh	Yes	Expensive aggregations, dashboards

-- Regular view: re-executes the query every time
CREATE OR REPLACE VIEW v_user_stats AS
SELECT
    u.id,
    u.username,
    u.follower_count,
    COUNT(t.id)        AS tweet_count,
    SUM(t.like_count)  AS total_likes
FROM tw_users u
LEFT JOIN tw_tweets t ON t.user_id = u.id
GROUP BY u.id, u.username, u.follower_count;

-- Every SELECT here re-runs the full aggregation:
SELECT * FROM v_user_stats WHERE total_likes > 1000;

-- Materialized view: executes ONCE, stores result, queries hit stored data
DROP MATERIALIZED VIEW IF EXISTS mv_user_stats CASCADE;
CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT
    u.id,
    u.username,
    u.follower_count,
    COUNT(t.id)        AS tweet_count,
    SUM(t.like_count)  AS total_likes,
    NOW()              AS computed_at
FROM tw_users u
LEFT JOIN tw_tweets t ON t.user_id = u.id
GROUP BY u.id, u.username, u.follower_count
WITH DATA;   -- populate immediately (WITH NO DATA = empty until refreshed)

-- Now this hits stored rows — no aggregation at query time:
SELECT * FROM mv_user_stats WHERE total_likes > 1000;

-- Add indexes on the materialized view just like a regular table
CREATE INDEX ON mv_user_stats (total_likes DESC);
CREATE INDEX ON mv_user_stats (username);
CREATE UNIQUE INDEX ON mv_user_stats (id);  -- required for CONCURRENT refresh

-- Inspect all materialized views
SELECT schemaname, matviewname, ispopulated, definition
FROM pg_matviews
WHERE schemaname = 'absolutelearning';

Purchase this course to unlock the full lesson.

← Previous Next →