You have a dashboard showing daily sales metrics. The query joins 5 tables, aggregates 10 million rows, and takes 45 seconds. Users refresh the dashboard 500 times per day. That's 6.25 hours of query time daily.

Option 1: Regular view - Still takes 45 seconds every time. No improvement.

Option 2: Materialized view - Pre-compute results, refresh hourly. Query takes 10ms. Dashboard loads instantly. Users are happy.

This is what materialized views do: they trade storage and refresh overhead for query speed. Netflix uses materialized views for "Top 10 in your country" - refreshed every 6 hours, queried billions of times. Reddit uses them for subreddit statistics - refreshed every 15 minutes, displayed on every page.

Regular views are different. They don't store data - they're saved queries. You use them for security (hide sensitive columns), abstraction (simplify complex joins), or convenience (reusable query logic). A view that shows "active_users" (WHERE status = 'active') is always current because it queries the base table every time.

Netflix: Top 10 Content by Country

Netflix shows "Top 10 in your country" based on billions of viewing records. Calculating in real-time would take minutes. Materialized view refreshes every 6 hours.

Materialized view pre-aggregates viewing data by country and content. Refreshes every 6 hours. Queries return in milliseconds. Staleness acceptable because trending content doesn't change minute-to-minute.

CREATE MATERIALIZED VIEW top_content_by_country AS

SELECT

country_code,

content_id,

content_title,

COUNT(DISTINCT user_id) as unique_viewers,

SUM(watch_duration_seconds) as total_watch_time,

ROW_NUMBER() OVER (PARTITION BY country_code ORDER BY COUNT(DISTINCT user_id) DESC) as rank

FROM viewing_history

WHERE view_timestamp >= CURRENT_TIMESTAMP - INTERVAL '7 days'

GROUP BY country_code, content_id, content_title;

CREATE INDEX idx_top_content_country_rank

ON top_content_by_country(country_code, rank);

-- Query top 10 in US

SELECT content_title, unique_viewers

FROM top_content_by_country

WHERE country_code = 'US' AND rank <= 10;

Query time: 3 minutes -> 50ms (3,600x faster)

Handles 500 million daily queries for trending content

Data freshness: 6 hours (acceptable for trending)

Materialized view size: 2GB (vs 500GB raw data)

Refresh time: 5 minutes every 6 hours

PostgreSQL

Reddit: Subreddit Statistics

Reddit shows subscriber counts, active users, and post counts for each subreddit. Calculating from billions of records is expensive. Materialized view refreshes every 15 minutes.

Materialized view aggregates user subscriptions, activity, and posts per subreddit. Refreshes every 15 minutes. Displayed on every subreddit page.

CREATE MATERIALIZED VIEW subreddit_stats AS

SELECT

s.subreddit_id,

s.name,

COUNT(DISTINCT sub.user_id) as subscriber_count,

COUNT(DISTINCT CASE WHEN a.last_active >= NOW() - INTERVAL '24 hours'