11 · Materialized Views — Precomputed Query Results for Speed

Performance & Distribution · Topic 11 of 13

---

What is a Materialized View?

A materialized view is a precomputed snapshot of a query result, stored as a physical table. Unlike a regular view (which re-runs the query), a materialized view reads from stored data.

---

Regular View vs Materialized View

	Regular View	Materialized View
Storage	None (virtual)	Physical table
Query speed	Same as base query	Fast (pre-aggregated)
Freshness	Always current	Stale until refreshed
Best for	Simplifying queries	Fast aggregations/reports

---

Refresh Strategies

• Manual: REFRESH MATERIALIZED VIEW view_name;
• Scheduled: cron job or event-based trigger
• Incremental (fast refresh): only process changed rows (not all DBs support this)
• On-commit: refresh on every base table write (very expensive)

---

Cloud Implementations

PostgreSQLGoogle BigQueryGoogle SpannerDynamoDBCassandraMongoDB

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, SUM(amount) AS total
FROM orders
GROUP BY 1;

-- Refresh without locking reads:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

Requires a unique index for CONCURRENTLY refresh.

• Native materialized views with automatic refresh
• BigQuery rewrites queries transparently to use MVs when beneficial
• Incremental refresh for supported query patterns

  CREATE MATERIALIZED VIEW project.dataset.mv_daily_sales AS
  SELECT DATE(created_at) AS day, SUM(amount) AS total
  FROM project.dataset.orders
  GROUP BY 1;
  

• No native materialized views (as of 2024)
• Pattern: maintain a separate summary table updated via CDC or triggers

• No native MVs
• Pattern: duplicate data in a separate table tailored to the read pattern (single-table design)

• Materialized Views (Cassandra MV feature)
• Automatically maintained by Cassandra on write path
• ⚠️ Performance overhead; use with caution on high-write tables

• No native MVs; use aggregation pipeline + $out / $merge

  db.orders.aggregate([
    { $group: { _id: "$day", total: { $sum: "$amount" } } },
    { $merge: "daily_sales" }
  ]);
  

---