Materialized View Pattern

What Is a Materialized View?

A materialized view is a pre-computed, stored result of a query. Unlike a regular (virtual) SQL view which re-executes the underlying query on every access, a materialized view is physically stored on disk. When a query runs against it, the data is already computed — no joins, no aggregations, no full table scans.

The pattern extends beyond SQL databases. In distributed systems, materialized views appear as read-side projections in CQRS architectures, Redis caches pre-populated with aggregated data, Elasticsearch indexes that denormalize data from multiple source tables, and pre-built Cassandra tables designed for a specific query access pattern.

Refresh Strategies

The key trade-off in materialized views is between freshness and cost. You choose how frequently to refresh:

Materialized Views in CQRS

In a CQRS architecture, the read model is a materialized view. Every time a command produces an event, a projector updates one or more read-model tables. This lets you design each read table specifically for its query — no joins needed at query time.

SQL Materialized Views

-- Create a materialized view of order summaries
CREATE MATERIALIZED VIEW order_summary AS
  SELECT
    o.id         AS order_id,
    u.name       AS customer_name,
    COUNT(i.id)  AS item_count,
    SUM(i.price * i.quantity) AS total,
    o.status,
    o.created_at
  FROM orders o
  JOIN users u ON o.user_id = u.id
  JOIN order_items i ON i.order_id = o.id
  GROUP BY o.id, u.name, o.status, o.created_at;

-- Refresh the materialized view (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

-- Query is now a fast single-table scan
SELECT * FROM order_summary WHERE order_id = 12345;

Consistency Considerations

When a materialized view is invalidated (source data changed), you have three options: mark-and-recompute (flag it as stale, compute on next read), eager refresh (recompute immediately in the background), or serve stale (return the old value with a cache-control hint). The right choice depends on how much staleness the consumer can tolerate.

Real-World Examples

• Twitter/X pre-computes follower timelines as materialized views in Redis — pulling a timeline is a fast list read, not a JOIN of 10,000 follows.
• Google BigQuery automatically maintains materialized views and refreshes them as source data changes, charging only for the delta computation.
• Airbnb uses Elasticsearch as a materialized view of listing data from Postgres, enabling full-text search and geo-queries that would be expensive in the relational DB.
• Shopify maintains denormalized product views in Elasticsearch for storefront search, kept in sync with the Postgres source via CDC.