CTEs can execute once (materialized) or multiple times (inlined). When a CTE is referenced multiple times in a query, the optimizer might execute it repeatedly, wasting resources. A dashboard query that uses the same expensive aggregation in 3 different sections could run that aggregation 3 times. Materialization forces the database to compute the CTE once, store results temporarily, and reuse them. This can reduce query time from 15 seconds to 2 seconds. Critical for reports, dashboards, and complex analytics.

Netflix: Analytics Dashboard Performance Optimization

Netflix analytics dashboards display multiple charts using the same expensive aggregations (viewing hours by title, region, device). Without materialization, each chart triggers the same aggregation, causing 30+ second load times. Materialized CTEs reduced dashboard load time from 45 seconds to 3 seconds.

Reduced dashboard load time by 93% (45s → 3s). Improved user experience for data analysts and executives. Enabled real-time dashboard updates. Reduced database load by 80% (5 aggregations → 1). Critical for business intelligence and decision-making. Materialized CTE stores 2GB of aggregated metrics, reused across 5 charts.

Dashboard with 5 chart sections using same viewing metrics aggregation

-- Netflix viewing metrics dashboard

WITH viewing_metrics AS MATERIALIZED (

SELECT

DATE_TRUNC('day', view_timestamp) as view_date,

title_id,

region,

device_type,

COUNT(DISTINCT user_id) as unique_viewers,

SUM(watch_duration_seconds) / 3600.0 as total_hours,

AVG(watch_duration_seconds) / 3600.0 as avg_hours_per_view,

SUM(CASE WHEN watch_duration_seconds >= title_duration_seconds * 0.9

THEN 1 ELSE 0 END) as completion_count

FROM viewing_events -- 500M rows per day

WHERE view_timestamp >= CURRENT_DATE - INTERVAL '30 days'

GROUP BY 1, 2, 3, 4

)

-- Chart 1: Top titles by viewing hours

SELECT

'Top Titles' as chart_name,

t.title_name,

SUM(vm.total_hours) as total_viewing_hours,

SUM(vm.unique_viewers) as total_viewers

FROM viewing_metrics vm

JOIN titles t ON vm.title_id = t.title_id

GROUP BY t.title_name

ORDER BY total_viewing_hours DESC

LIMIT 10

UNION ALL

-- Chart 2: Regional breakdown

SELECT

'Regional' as chart_name,

region as title_name,

SUM(total_hours)::TEXT as total_viewing_hours,

SUM(unique_viewers)::TEXT as total_viewers

FROM viewing_metrics