What are Materialized Views?

Definition
A materialized view is physical storage of a query result that is automatically kept in sync with base data. Unlike a regular view (which is just a saved query that runs fresh each time), a materialized view stores actual precomputed results that you can query instantly.
The Core Problem: Analytical queries over large datasets are prohibitively slow. Computing daily active users or revenue per region over billions of events can take 10 to 60 seconds on raw tables. Product dashboards need to feel responsive with p95 latency under 500 to 800 ms at high query rates, often 100 to 1000 queries per second across internal users.

Running heavy aggregations for every single request simply does not meet these requirements. The same patterns get repeated constantly: revenue by region for last 30 days, user retention cohorts, p95 latency metrics. You end up doing the same expensive computation thousands of times per day.

How Materialized Views Solve This: Instead of scanning billions of raw events each time, the system precomputes results and stores them. When someone queries "DAU by country for last 30 days," they read a 30 row summary table instead of scanning 30 billion raw events.

Query Performance Impact
RAW DATA SCAN
30 sec
→
MATERIALIZED VIEW
< 100 ms
Common Aggregation Types: Materialized views typically store aggregations like counts, sums, averages, percentiles, and rollups grouped by dimensions such as time, user ID, region, or device type. The key is identifying which computations get repeated and are expensive enough to justify the maintenance overhead.

The Trade: You exchange extra storage space plus maintenance complexity for dramatically faster reads and predictable performance. Every platform implements this differently: BigQuery, Snowflake, ClickHouse, and Databricks all offer variants with different refresh strategies and integration approaches.

💡 Key Takeaways

✓A materialized view stores physical precomputed query results, unlike regular views which are just saved queries that execute fresh each time

✓Solves the problem of repeated expensive aggregations: reading 30 precomputed rows in under 100 ms versus scanning 30 billion raw events in 30 seconds

✓Common use cases include metrics aggregations like daily active users, revenue rollups, and percentile calculations grouped by time or dimensions

✓The fundamental trade is extra storage plus maintenance overhead in exchange for dramatically faster query latency and predictable performance at scale

📌 Examples

1Dashboard showing "DAU by country for last 30 days" queries a 30 row daily summary materialized view instead of scanning billions of raw clickstream events

2Analytics platform maintaining per minute aggregates per user and region, which are 1000 times smaller than raw events but flexible enough to roll up into hourly and daily metrics

← Back to Materialized Views & Aggregations Overview