When to Use What — Mat Views vs CTEs vs Temp Tables vs Views

The four tools overlap. This lesson gives the decision framework.

-- Decision tree as SQL comments:

-- 1. Do you need the result more than once in the SAME query?
--    → CTE (WITH clause) — zero storage cost, always fresh

-- 2. Do you need the result across MULTIPLE queries in the SAME session?
--    → Temp table — lives until session ends, supports indexes

-- 3. Do you need a stable, reusable view with NO performance requirement?
--    → Regular view — just a named query, always fresh, zero storage

-- 4. Do you need fast reads on EXPENSIVE aggregations queried REPEATEDLY?
--    → Materialized view — stored, indexed, refreshed on schedule

-- Comparison on the same query:

-- CTE approach (recomputed every query execution)
WITH stats AS (
    SELECT user_id, COUNT(*) AS tweets, SUM(like_count) AS likes
    FROM tw_tweets GROUP BY user_id
)
SELECT u.username, s.tweets, s.likes
FROM tw_users u JOIN stats s ON s.user_id = u.id
WHERE s.likes > 1000;

-- Temp table (computed once, reused multiple times in session)
CREATE TEMP TABLE tmp_stats AS
SELECT user_id, COUNT(*) AS tweets, SUM(like_count) AS likes
FROM tw_tweets GROUP BY user_id;

CREATE INDEX ON tmp_stats(user_id);
CREATE INDEX ON tmp_stats(likes DESC);

-- Now reuse tmp_stats multiple times without recomputing:
SELECT u.username, s.tweets FROM tw_users u
JOIN tmp_stats s ON s.user_id = u.id WHERE s.likes > 1000;

SELECT AVG(likes), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY likes)
FROM tmp_stats;