Skip to content

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

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;
    
  • Do not assume PostgreSQL-style REFRESH MATERIALIZED VIEW support
  • A portable pattern is a summary table maintained transactionally by application code or asynchronously via change streams; do not assume generic SQL 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" }
    ]);
    

Freshness Is Part of the Interface

Expose a refresh watermark alongside a report. A fast total that silently excludes the last hour may be worse than a slower correct query. PostgreSQL's CONCURRENTLY refresh requires an already populated view and a qualifying unique index covering all rows; create that index before running the refresh shown above.

For incremental aggregates, define how updates, deletes and late events retract old contributions. A SUM can be adjusted with signed deltas; a distinct count or median needs more state. Keep a full rebuild path to reconcile drift. A regular view sees data according to the caller's transaction snapshot, not an absolute "latest" wall-clock state.

Check Your Understanding

#

Question 1

Why is incrementing the sales total for every CDC message unsafe?

#

Question 2

When is an index preferable to a materialized view?