Incremental Materialized Views: The Complete Guide (2026)

Incremental materialized views automatically update query results by computing only the changes caused by new data, instead of recomputing everything from scratch. They are the core technology behind streaming databases like RisingWave and Materialize, and are increasingly available in platforms like Databricks, PostgreSQL (pg_ivm), and ClickHouse. For real-time analytics, incremental materialized views eliminate the need for batch refresh jobs and provide sub-second data freshness.

This guide explains how incremental materialized views work, compares implementations across databases, and helps you choose the right approach for your workload.

What Are Materialized Views?

A materialized view is a database object that stores the precomputed result of a query. Unlike a regular view (which re-executes the query every time), a materialized view persists results to disk, making subsequent reads fast.

The problem with traditional materialized views is staleness. In PostgreSQL, you must manually run REFRESH MATERIALIZED VIEW to update the results. During refresh, the entire query is recomputed from scratch — even if only a single row changed in the source table. For large datasets, this refresh can take minutes or hours, and the view is stale until the next refresh completes.

The Refresh Problem

Consider a materialized view that computes total revenue per region across a billion-row orders table:

CREATE MATERIALIZED VIEW revenue_by_region AS
SELECT region, SUM(amount) as total_revenue
FROM orders
GROUP BY region;

In PostgreSQL, refreshing this view means re-scanning and re-aggregating all billion rows — even if only 100 new orders arrived since the last refresh. This is wasteful, slow, and blocks concurrent reads (unless you use CONCURRENTLY, which is even slower).

How Incremental Materialized Views Work

Incremental materialized views solve the refresh problem by tracking changes and computing only the delta.

When a new row is inserted into the orders table, an incremental system:

Detects the change — a new order of $500 in the "APAC" region
Computes the delta — add $500 to the APAC total
Applies the update — update the materialized view in place

Instead of re-scanning a billion rows, the system processes only the new row. This is called Incremental View Maintenance (IVM).

The Math Behind IVM

The formal foundation is straightforward. For a view V = Q(T) where Q is the query and T is the source table:

Full refresh: Recompute V' = Q(T + ΔT) from scratch
Incremental: Compute ΔV = Q_incremental(ΔT) and apply V' = V + ΔV

For simple aggregations like SUM, COUNT, and AVG, the incremental computation is trivial. For complex operations like joins, the incremental computation is more involved but still dramatically cheaper than full recomputation.

What Makes It Hard

Not all queries are easy to maintain incrementally:

Joins: When a row is inserted into one side of a join, it may need to be matched against the entire other table.
Aggregations with DISTINCT: Removing a row from a COUNT(DISTINCT) requires knowing whether other rows with the same value exist.
Window functions: Changes can affect the ordering and partitioning of results.
Subqueries and CTEs: Nested queries create complex dependency chains.

The quality of a streaming database is largely determined by how efficiently it handles these complex incremental computations.

Incremental Materialized Views Across Databases

RisingWave: Purpose-Built for Streaming Materialized Views

RisingWave is a PostgreSQL-compatible streaming database where materialized views are the primary abstraction. Every streaming pipeline is expressed as a CREATE MATERIALIZED VIEW statement.

How it works:

-- Create a source from Kafka
CREATE SOURCE orders_stream (
  order_id INT,
  region VARCHAR,
  amount DECIMAL,
  order_time TIMESTAMP
) WITH (
  connector = 'kafka',
  topic = 'orders',
  properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

-- Create an incrementally maintained materialized view
CREATE MATERIALIZED VIEW revenue_by_region AS
SELECT region, SUM(amount) as total_revenue, COUNT(*) as order_count
FROM orders_stream
GROUP BY region;

-- Query the view — always up to date
SELECT * FROM revenue_by_region WHERE region = 'APAC';

Key advantages of RisingWave's approach:

Cascading materialized views. Build complex pipelines by stacking views on top of each other. Downstream views automatically update when upstream views change.

-- Base aggregation
CREATE MATERIALIZED VIEW regional_revenue AS
SELECT region, SUM(amount) as revenue FROM orders_stream GROUP BY region;

-- Derived view on top of the base
CREATE MATERIALIZED VIEW top_regions AS
SELECT * FROM regional_revenue WHERE revenue > 1000000 ORDER BY revenue DESC;

Sub-second freshness. Views update within milliseconds of data arriving, with checkpoint intervals as low as 1 second (compared to Flink's default of 30 minutes).
Consistent snapshots. All queries return results that are consistent with a specific point in time, preventing partial or inconsistent reads.
State on S3. All view state is stored in object storage, enabling elastic scaling and fast recovery.
PostgreSQL tools work. Query views with psql, connect BI tools via JDBC/ODBC, use any PostgreSQL client library.

Materialize: Academic Foundations, Cloud-Only

Materialize implements incremental materialized views using Microsoft Research's Timely Dataflow and Differential Dataflow frameworks.

Key characteristics:

Strict-serializable consistency. Every read reflects a consistent global state.
Recursive CTEs. Supports recursive materialized views for graph-like computations.
Cloud-only. No self-hosted option. Available only as a managed SaaS.
Source-available. Business Source License, not open source.

Materialize's consistency model is its primary differentiator. For workloads where strong consistency is non-negotiable (financial calculations, regulatory reporting), Materialize's strict-serializable guarantee is stronger than RisingWave's snapshot consistency.

PostgreSQL with pg_ivm: Extension-Based Approach

pg_ivm is a PostgreSQL extension that adds incremental view maintenance to standard PostgreSQL.

Key limitations:

Not production-ready. The extension itself is not recommended for production deployments.
Blocks writes. Maintaining consistency requires blocking writes until view updates complete, impacting throughput in high-write environments.
Limited query support. Only simple queries with basic aggregations and joins are supported.
Self-managed PostgreSQL only. Cannot be used on managed PostgreSQL services (RDS, Cloud SQL, etc.) that don't support custom extensions.
Performance degrades with frequency. IVM overhead becomes significant when base tables are modified frequently — exactly the scenario where you most want incremental views.

pg_ivm is suitable for experimenting with IVM concepts but is not a viable solution for production real-time analytics.

Databricks: Batch-Oriented Incremental Refresh

Databricks supports materialized views with incremental refresh in Databricks SQL.

How it works: Databricks detects changes in source tables and incrementally updates materialized views, but this happens on a scheduled or triggered basis — not continuously. In benchmarks, incremental refresh on a 200-billion-row table was 98% cheaper and 85% faster than full refresh.

Key limitation: Databricks materialized views are not streaming. They update on a schedule (minutes to hours), not continuously with sub-second latency. This is a better batch refresh, not a streaming materialized view.

ClickHouse: Aggregate-Focused Materialized Views

ClickHouse supports materialized views that trigger on inserts, updating aggregate state incrementally.

Key characteristics:

Materialized views fire on INSERT and incrementally update aggregate functions using AggregatingMergeTree.
Works well for simple aggregations but complex joins and multi-table views are limited.
No continuous CDC ingestion — data must be inserted into ClickHouse first.

ClickHouse materialized views are effective for append-only analytical workloads but lack the streaming database features (CDC, complex joins, cascading views) that RisingWave and Materialize provide.

When to Use Incremental Materialized Views

Use Them When:

Data freshness matters. You need query results updated within seconds of source data changing.
Queries are known in advance. You can define the materialized view SQL ahead of time.
Source data changes incrementally. Small batches of inserts, updates, or deletes arrive continuously.
Multiple consumers need the same aggregation. Pre-computing the result once is cheaper than running the query for every consumer.

Don't Use Them When:

Queries are ad-hoc and exploratory. Use an OLAP database (ClickHouse, Pinot) for flexible analytical queries.
Data arrives in large bulk batches. For infrequent, large batch loads, full refresh may be simpler and equally fast.
Freshness requirements are hours, not seconds. Scheduled batch refresh with dbt or Airflow may be sufficient.

Frequently Asked Questions

What is an incremental materialized view?

An incremental materialized view automatically updates its stored query results by computing only the changes caused by new, updated, or deleted source data — rather than recomputing the entire query from scratch. This provides sub-second data freshness at a fraction of the compute cost of full refresh.

Which database has the best incremental materialized views?

RisingWave offers the most complete incremental materialized view implementation for most use cases. It supports cascading views, complex joins across multiple streams, sub-second updates, PostgreSQL compatibility, and open-source licensing under Apache 2.0. Materialize offers stronger consistency guarantees but is cloud-only and source-available.

Does PostgreSQL support incremental materialized views?

PostgreSQL does not natively support incremental materialized views. The standard REFRESH MATERIALIZED VIEW command recomputes the entire view from scratch. The pg_ivm extension adds basic incremental support, but it is not production-ready, blocks writes during updates, and only works on self-managed PostgreSQL installations.

How are incremental materialized views different from dbt models?

dbt models are batch transformations that run on a schedule — typically hourly or daily. They recompute results from scratch each time (full refresh) or use incremental models that process only new rows but still run as batch jobs. Incremental materialized views in streaming databases like RisingWave update continuously and automatically as each row arrives, with sub-second latency and no scheduling required.

Can incremental materialized views handle joins?

Yes. Streaming databases like RisingWave and Materialize support incremental maintenance of complex joins, including multi-way joins across 10+ streams. The quality of join maintenance varies significantly between implementations — RisingWave handles multi-stream joins efficiently while some alternatives struggle with state management as join complexity increases.