Skip to content

Materialized Views#

Problem statement (interviewer prompt)

A dashboard runs a 12-second analytic query on every page load. The underlying data changes hourly. Design a strategy that serves the dashboard in under 200ms without exposing stale-by-the-day data.

A materialized view stores the result of a query as a regular table. Reads hit the precomputed result instead of recomputing. Refresh schedules trade freshness for compute.

flowchart TB
  Q[Expensive query] --> MV[(Materialized view<br/>table-on-disk)]
  Source[(Source tables)] -.refresh.-> MV
  App[App] --> MV

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class App service;
    class MV cache;
    class Source datastore;

Sits between caches (in-memory, fast invalidation) and full denormalization (heavy duplication). Supported by PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery, Kafka Streams KTable.

A materialized view (MV) is the answer to "this aggregation is slow and I'd like to read the result, not recompute it." The trick is keeping the precomputed result fresh enough without burning all your write capacity.

Refresh strategies#

flowchart TB
  subgraph Full[Full refresh]
    F1[REFRESH] --> F2[Truncate] --> F3[Re-run base query] --> F4[Repopulate]
  end
  subgraph Inc[Incremental refresh]
    I1[Source change] --> I2[CDC / triggers] --> I3[Delta query] --> I4[Apply to MV]
  end

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class F1,F2,F3,F4,I1,I2,I3,I4 service;

Full refresh#

Drop and recompute. Simple, correct, expensive.

CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, SUM(total) AS revenue
  FROM orders
  GROUP BY day;

REFRESH MATERIALIZED VIEW daily_revenue;

Postgres lets you REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid blocking readers (requires a unique index).

Incremental / fast refresh#

Update only the rows affected by recent base-table changes. Requires:

  • Tracking what changed (MV log, CDC, triggers).
  • Knowing how the delta combines with the existing MV.

Oracle, BigQuery, Snowflake, ClickHouse, and Postgres extensions like pg_ivm support this for simple aggregations. Streaming engines (Flink, Materialize, RisingWave) make incremental MVs the primary model.

Query rewriting#

-- User wrote
SELECT date_trunc('day', created_at), SUM(total) FROM orders WHERE created_at > '2024-01-01' GROUP BY 1;

-- Optimizer sees an MV with that aggregation; rewrites to
SELECT day, revenue FROM daily_revenue WHERE day > '2024-01-01';

Oracle, SQL Server, BigQuery, and Snowflake do this automatically. PostgreSQL does not - queries must reference the MV explicitly.

Indexing the MV#

An MV is a regular table; it can have its own indexes. Common pattern: index the MV by the dashboard's filter dimensions.

CREATE INDEX ix_daily_revenue_day ON daily_revenue(day);

MV vs cache vs OLAP store#

Approach Freshness Complexity Best for
In-memory cache (Redis) seconds low Hot keys, simple results
Materialized view minutes-hours medium Aggregations, joins
OLAP store (ClickHouse, BigQuery) seconds-minutes high Multi-dimensional analytics
Streaming MV (Flink, Materialize) sub-second high Real-time dashboards

Concurrency and staleness#

If reads need monotonic freshness ("the page must reflect at least my most-recent write"), pair the MV with a read-after-write check against the source table.

Pitfalls#

  • Refresh lock: non-CONCURRENT refresh blocks readers; surprise outage during a 10-min refresh.
  • Cascading MVs: MV B depends on MV A; refresh ordering matters.
  • Storage doubling: a wide-aggregation MV can be larger than the source table.
  • Lost-update: incremental refresh skipped a row due to a missing trigger. Schedule periodic full refreshes as a safety net.

How MVs fit in the data tier#

flowchart TB
  MV((Materialized<br/>view))
  CACHE[Caching strategies<br/>in-memory sibling]
  CDC[Change Data Capture<br/>incremental refresh source]
  LK[Lambda / Kappa<br/>speed-layer pattern]
  LAKE[Data lake / warehouse<br/>gold-layer aggregates]
  CACHE -. sibling .- MV
  CDC --> MV
  LK --> MV
  MV --> LAKE

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class MV service;
    class CACHE,CDC,LK,LAKE datastore;

Glossary & fundamentals#

Tag Concept What it is Page
HLD Caching strategies sibling read-acceleration tactic caching-strategies
HLD Change Data Capture source for incremental refresh change-data-capture
HLD Lambda Kappa architecture MVs in the speed layer lambda-kappa-architecture
HLD Data Lake Warehouse precomputed gold tables are MVs data-lake-warehouse

Quick reference#

What#

Precomputed table holding query results; refresh on schedule or via CDC.

Refresh modes#

  • Full: drop + recompute; simple, expensive
  • Concurrent (PG): non-blocking; needs unique index
  • Incremental: deltas via MV log / CDC / triggers
  • Streaming: Flink/Materialize update on every input event

Query rewrite#

Optimizer rewrites compatible queries to use the MV (Oracle, SQL Server, BigQuery, Snowflake). PG: explicit reference only.

Indexing#

MV is a regular table; index by dashboard's filter dimensions.

When to use MV#

  • Heavy aggregations that change slowly
  • Joins of large tables
  • BI / dashboard SLAs

When NOT#

  • Per-row freshness needed (use cache + invalidation)
  • Result fits in Redis (use Redis)
  • True streaming SLO (use Flink / Materialize)

Pitfalls#

  • Refresh lock if not CONCURRENT
  • Cascading MV dependency ordering
  • Storage can exceed source
  • Lost-update on incremental → periodic full refresh

Refs#

  • PostgreSQL MV docs
  • Snowflake / Oracle MV docs
  • Materialize.com
  • Confluent KTable blog

FAQ#

What is a materialized view?#

A materialized view stores the result of a query as a regular table. Reads hit the precomputed result instead of rerunning the query, refreshed on a schedule or stream.

Materialized view vs cache?#

A cache is best-effort and invalidated by the app. A materialized view is a managed table the database keeps consistent on refresh, with full indexes and persistence.

When should I use a materialized view?#

Use it when an aggregate or join is slow, the underlying data changes more slowly than reads happen, and stale-by-a-few-minutes is acceptable for the use case.

What is incremental view maintenance?#

Instead of rebuilding the whole view, the system applies only the changed rows since the last refresh, keeping the view fresh at much lower cost than a full rebuild.

Which databases support materialized views?#

PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery, and Materialize support them natively. Kafka Streams and Flink expose the same idea as a KTable or sink table.

Further reading#