Materialized Views

What it is

A materialized view is a database object that stores the result of a query as a physical table. Unlike a regular (virtual) view, which re-executes the underlying query every time it is accessed, a materialized view computes and persists the result set. Subsequent reads against the materialized view execute a simple table scan (or index lookup) against the pre-computed data instead of re-executing the potentially expensive aggregation, join, or computation. The tradeoff is staleness: the materialized view reflects the state of the base tables at the time of its last refresh, not the current state.

Materialized views are a fundamental technique in read-heavy analytical and reporting systems. The concept extends beyond relational databases: a Redis sorted set of top-100 users by score is a materialized view; a pre-aggregated reporting table updated nightly by an ETL job is a materialized view; the read model in a CQRS architecture is a materialized view built from an event stream. Any time you pre-compute a derived representation of data to serve reads efficiently, you are implementing the materialized view pattern.

Why it exists

Complex analytical queries — multi-table joins, aggregations across millions of rows, window functions — can take seconds to minutes to execute on large datasets. If this query is needed on every API request or dashboard refresh, it becomes a performance bottleneck that cannot be solved by indexing alone. Materialized views solve this by paying the computation cost once (or periodically) during the refresh, and serving all subsequent reads from the pre-computed result at millisecond latency.

The staleness tradeoff is often acceptable for analytical use cases. A sales dashboard showing "revenue by region, last 30 days" can tolerate 15-minute-old data — users do not expect it to reflect a transaction from 30 seconds ago. The cost of recomputing this query on every page load (a 10-second aggregation across 50 million rows) vastly exceeds the benefit of sub-second staleness. Materialized views codify this tradeoff explicitly: define the acceptable staleness in the refresh schedule, and serve all reads from the fast pre-computed result.

When to use

• Expensive queries (aggregations, multi-table joins, window functions) are executed frequently and the results change infrequently relative to the query execution time.
• Reporting and analytics dashboards where users tolerate some staleness in exchange for sub-second response times.
• CQRS read models: the query side of a CQRS architecture maintains materialized views updated by domain events from the write side.
• Cross-database or cross-service denormalization: pre-joining data from multiple microservices into a single queryable structure for the read path.
• Search indexes are essentially materialized views — the index is a pre-computed, optimized representation of the source data for fast query execution.

When not to use

• Data must be real-time accurate — materialized views are stale by definition; for live financial balances or inventory counts, query the source of truth.
• The underlying data changes so frequently that the refresh cost approaches the original query cost — the view is perpetually stale and provides no benefit.
• The query is cheap and fast — the overhead of maintaining a materialized view exceeds the benefit for simple, well-indexed queries.
• Storage is severely constrained — materialized views duplicate data from base tables, potentially significantly increasing storage requirements.

Typical architecture

  PostgreSQL MATERIALIZED VIEW:

  -- Create a materialized view for sales summary
  CREATE MATERIALIZED VIEW monthly_sales_summary AS
  SELECT
    date_trunc('month', order_date) AS month,
    region,
    SUM(amount)                     AS total_revenue,
    COUNT(*)                        AS order_count,
    AVG(amount)                     AS avg_order_value
  FROM orders
  JOIN regions USING (region_id)
  WHERE order_date >= NOW() - INTERVAL '12 months'
  GROUP BY 1, 2
  WITH DATA;  -- populate immediately

  -- Index on the materialized view for fast queries
  CREATE INDEX ON monthly_sales_summary (month, region);

  -- Refresh options:
  REFRESH MATERIALIZED VIEW monthly_sales_summary;          -- full refresh (blocks reads)
  REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary; -- non-blocking (needs UNIQUE index)

  -- Schedule refresh (pg_cron extension):
  SELECT cron.schedule('*/15 * * * *',           -- every 15 minutes
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary$$);

  Refresh Strategies:
  On-commit (triggers):    Refresh on every write → strong consistency, high write cost
  Scheduled (cron):        Refresh periodically   → predictable staleness, low overhead
  Incremental:             Apply only changes      → low refresh cost, complex implementation
  On-demand:               Manual or API-triggered → controlled staleness

  CQRS Read Model as Materialized View:
  Command side → writes events to event store
                  │
                  ▼ event bus
  Read model projector → receives OrderPlaced, OrderShipped events
                         → maintains order_summary table (denormalized, fast reads)
  Query side → reads from order_summary (simple SELECT, no joins)

  vs complex OLTP query:
  SELECT o.*, c.name, c.email, SUM(i.price) ...
  FROM orders o JOIN customers c ... JOIN items i ... JOIN shipments s ...
  → replaced by:
  SELECT * FROM order_summary WHERE order_id = ?   -- 1ms, no joins
          

Pros and cons

Implementation notes

Refresh strategies in PostgreSQL: REFRESH MATERIALIZED VIEW performs a full recompute and requires an exclusive lock, blocking all reads during the refresh. For production systems with high-traffic views, always use REFRESH MATERIALIZED VIEW CONCURRENTLY, which computes the new result set, then atomically swaps it in using a diff-based approach. CONCURRENT requires a unique index on the materialized view. Schedule refreshes during low-traffic periods or use pg_cron for automated periodic refresh. Monitor refresh duration — a view that takes 5 minutes to refresh but is scheduled every minute will cause overlapping refreshes and increasing database load.

Application-level materialized views: For cross-service or cross-database scenarios where database-native materialized views cannot span service boundaries, implement application-level materialized views: a dedicated process subscribes to change events (Kafka, CDC, database triggers) and maintains a denormalized read table in a read-optimized store (PostgreSQL read replica, Redis, Elasticsearch). This is the CQRS read model pattern. The key principle is that the read store is rebuilt entirely from the event stream — it is derived data, not a source of truth. This enables rebuilding the read model from scratch by replaying the event stream whenever the schema needs to change.

Common failure modes

• Stale data served after refresh failure: A scheduled refresh fails silently (pg_cron job dies, projector crashes). The view continues serving increasingly stale data without any alert. Monitor the last_refresh timestamp of materialized views; alert when staleness exceeds the maximum acceptable window.
• Refresh lock blocking reads: A non-concurrent refresh of a large materialized view takes 60 seconds, blocking all reads for a minute. Always use CONCURRENT refresh for large views; always set statement_timeout on refresh jobs to prevent runaway refreshes.
• Read model divergence from event stream: A bug in the CQRS projector causes incorrect state in the read model that is not detected. Design read models to be fully rebuildable from the event stream; implement periodic consistency checks comparing read model values against authoritative source queries.
• Storage explosion: A materialized view pre-joining fact and dimension tables creates a result set larger than the source tables combined due to denormalization. Pre-estimate output size before creating large materialized views.

Decision checklist

• Acceptable staleness for the use case has been defined and the refresh schedule matches it.
• CONCURRENT refresh is used in production; a unique index exists on the materialized view.
• Refresh duration has been measured and the refresh interval is comfortably longer than the refresh duration.
• Last-refresh timestamp monitoring and alerting is in place to detect stale views from failed refreshes.
• Storage impact of the materialized view has been estimated and is acceptable.
• For CQRS read models: the read model is fully rebuildable from the event stream by replaying events from the beginning.

Example use cases

• E-commerce analytics dashboard: A reporting dashboard shows daily revenue by category, cohort retention, and top-selling products. These queries join orders, products, and customer tables across 200 million rows, taking 30–90 seconds each. Materialized views pre-aggregate these results every 15 minutes; dashboard queries return in under 50ms with no change to the reporting query logic.
• CQRS order management system: An order service writes events (OrderPlaced, PaymentReceived, Shipped) to an event store. A separate read projector consumes these events and maintains an order_summary table with the latest denormalized state per order. API reads against order_summary are a single-row lookup; the complex multi-table join is replaced by event-driven incremental updates to the read model.
• Product search index: A product catalog maintained in a normalized relational database is projected nightly into an Elasticsearch index. The index is a materialized view of the product data in a search-optimized denormalized form, enabling full-text search and faceted filtering that would be impractical with SQL. Nightly refresh tolerates product data being up to 24 hours stale in search results.

Related patterns

• CQRS — CQRS read models are application-level materialized views maintained from domain events.
• Caching Strategies — caching and materialized views both trade staleness for read performance; caching is memory-based and ephemeral, materialized views are durable and database-native.
• Lazy Loading — the opposite tradeoff: compute on demand rather than pre-compute eagerly.

Further reading

• PostgreSQL Documentation — Materialized Views
• Martin Fowler — CQRS (read models as materialized views)
• Databricks — Incremental Materialized Views with Delta Live Tables