Materialized Views

Incremental Materialized Views

An incremental materialized view is a stored query result that the database keeps correct by applying only the changes to the base data, never by re-running the whole query. This page explains how that works, where it does not fit, and how PostgreSQL, Snowflake, Databricks, ClickHouse, Materialize and RisingWave each handle it.

Last reviewed 2026-11-08. Vendor behavior is described from each vendor's public documentation; links are at the end of the comparison table.

Delta
Change-Driven Updates
Each insert, update or delete flows through the query plan and touches only the output rows it affects.
1s
Default Barrier Interval
RisingWave commits a consistent snapshot across all views every second by default.
Joins + Aggs
Full SQL Coverage
Multi-way joins, aggregations, OVER windows, time windows and views on views, all maintained incrementally.
S3
State on Object Storage
Operator state is checkpointed to S3-compatible storage, so view state is not limited by memory.

Definition

What is an incremental materialized view?

A materialized view stores the result of a query so that reading it is as cheap as reading a table. The hard part is keeping the stored result equal to what the query would return right now. There are two ways to do that.

Full refresh throws the old result away and runs the query again. PostgreSQL's REFRESH MATERIALIZED VIEW, Redshift's default refresh and ClickHouse's refreshable materialized views work this way. The cost of every refresh is proportional to the size of the base data, and between refreshes the view is stale.

Incremental maintenance keeps the old result and applies the delta. When a row is inserted, updated or deleted in a base table, the database computes which output rows that change affects and fixes only those. The research literature calls this incremental view maintenance (IVM). The cost of each update is proportional to the size of the change, not the size of the data, and the view is current to within the propagation delay.

FactorFull refreshIncremental maintenance
Work per refreshScan the whole base dataProcess the changed rows
Cost scales withTotal data sizeChange rate
FreshnessStale until the next refreshSeconds behind the source, or less
Operational piecesScheduler, retries, overlap handlingOne CREATE MATERIALIZED VIEW
Best whenMost rows change between refreshesFew rows change relative to the total

Mechanism

How does incremental view maintenance actually work?

Every SQL query compiles to a plan of operators: scans, filters, projections, joins, aggregations, window functions. In a batch database the plan runs once over a snapshot. In an incremental system the same plan runs continuously, and each operator is rewritten to consume changes and emit changes.

  • →A filter or projection is stateless. An input insert that passes the predicate becomes an output insert; a delete becomes a delete.
  • →An aggregation keeps one state entry per group (count, sum, or a small sketch). An insert adds to the group's state and emits an update for that one group. Nothing else is touched.
  • →A join keeps the rows it has seen on each side, indexed by the join key. A new row on the left is probed against the right-side state and emits exactly the matching output rows. This is why join state, not CPU, is usually the limiting resource.
  • →An update is processed as a delete of the old row followed by an insert of the new row, so every operator only has to understand inserts and deletes.
  • →A view on another view just consumes the first view's change stream. The chain can be as deep as the medallion architecture needs.

Two things separate production systems. The first is where the state lives. Trigger-based implementations keep it in the base tables and pay on every write. In-memory engines keep it in RAM and pay for large state in hardware. RisingWave keeps operator state in a log-structured store on object storage with a local cache, so state size is bounded by S3, not by memory.

The second is consistency across views. If two views refresh on their own schedules, a dashboard that reads both can show a total that does not match its own breakdown. RisingWave injects a barrier into every stream at a fixed interval (one second by default). All operators align on the barrier, state is checkpointed at the barrier, and a read always sees every view as of the same barrier.

A complete example: Kafka orders joined with CDC customers

The example below ingests orders from Kafka and customer attributes from PostgreSQL change data capture, joins them, aggregates, and stacks a second view on the first. Every statement is standard RisingWave SQL.

SQL (RisingWave)
-- 1. Declare the streams. Nothing is recomputed on read.
CREATE SOURCE orders (
  order_id    BIGINT,
  customer_id BIGINT,
  amount      NUMERIC,
  status      VARCHAR,
  order_ts    TIMESTAMPTZ,
  WATERMARK FOR order_ts AS order_ts - INTERVAL '30 seconds'
) WITH (
  connector = 'kafka',
  topic = 'orders',
  properties.bootstrap.server = 'broker:9092'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE customers (
  customer_id BIGINT PRIMARY KEY,
  tier        VARCHAR,
  region      VARCHAR
) WITH (
  connector = 'postgres-cdc',
  hostname = 'pg', port = '5432',
  username = 'rw', password = '...',
  database.name = 'shop', schema.name = 'public',
  table.name = 'customers'
);

-- 2. A join plus an aggregation, maintained incrementally.
--    An order insert touches one group; a customer tier change
--    moves that customer's orders between groups. Nothing else moves.
CREATE MATERIALIZED VIEW revenue_by_tier_region AS
SELECT c.tier, c.region,
       COUNT(*)              AS orders,
       SUM(o.amount)         AS revenue,
       MAX(o.order_ts)       AS last_order
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.tier, c.region;

-- 3. A view on the view. Same consistency snapshot as its input.
CREATE MATERIALIZED VIEW top_regions AS
SELECT region, SUM(revenue) AS revenue
FROM revenue_by_tier_region
GROUP BY region;

-- 4. Read it like a table. No REFRESH statement exists or is needed.
SELECT * FROM top_regions ORDER BY revenue DESC LIMIT 5;

When a paid order arrives, one group in revenue_by_tier_region changes and one row in top_regions changes. When a customer is upgraded from silver to gold in PostgreSQL, the CDC update is processed as delete plus insert, that customer's past paid orders move from the silver group to the gold group, and both views stay consistent with each other at the next barrier. If you need results that never change once emitted, for example to write to an append-only sink, add EMIT ON WINDOW CLOSE to a time-windowed view.

Landscape

Which databases support incremental materialized views?

Most databases now offer something called a materialized view, but the maintenance model behind the name differs a great deal. The table summarizes each system's public documentation as of 2026-11-08. RisingWave is listed last and described in the same terms as the others.

SystemMaintenance modelFreshnessQuery coverageWatch out for
PostgreSQLFull recompute with REFRESH MATERIALIZED VIEW. Incremental only via the pg_ivm extension (synchronous triggers).Whatever the refresh schedule is; pg_ivm updates on each write.pg_ivm: inner joins, most aggregates, DISTINCT. No outer joins, window functions or CTEs.Trigger-based maintenance slows writes on the base tables.
OracleFast refresh using materialized view logs, the longest-standing production IVM implementation.On commit, or on demand or schedule.Broad, with documented restrictions per query type; some shapes fall back to complete refresh.Requires MV logs on every base table.
SnowflakeMaterialized views: background maintenance, single table only. Dynamic tables: incremental when the query shape allows, otherwise full refresh.Dynamic tables are defined by a target lag with a minimum of about one minute.Dynamic tables support joins, aggregations and chaining.Refresh cost depends on whether Snowflake can refresh incrementally for that query.
DatabricksMaterialized views run on Lakeflow Declarative Pipelines (formerly Delta Live Tables). Incremental refresh when the engine can prove it is safe, otherwise full recompute.Scheduled or triggered refresh; minutes in practice.Most SQL, with incremental refresh limited to supported shapes and to sources with change tracking enabled.Designed around the lakehouse batch and micro-batch model.
BigQueryAutomatic incremental refresh for a limited set of query shapes; others are recomputed.Background refresh; stale results can be served with a configurable staleness.Aggregations over a single table and some inner joins.Query shape restrictions are strict.
ClickHouseMaterialized views are insert triggers that transform each inserted block into a target table. Refreshable MVs recompute on a schedule.Immediate for inserts; refreshable MVs are periodic.Inserts only. Updates, deletes and right-side join changes are not propagated.Aggregation state lives in merging table engines rather than in the view.
MaterializeStreaming engine built on differential dataflow; every view is maintained incrementally.Sub-second.Joins, aggregations, recursion, with strict consistency.State is held in memory on compute clusters, which drives cost for large state.
RisingWaveStreaming engine; every CREATE MATERIALIZED VIEW is maintained incrementally. Optional refresh.interval.sec for periodic batch refresh.Sub-second; one-second barriers by default.Multi-way joins, aggregations, OVER windows, time windows, cascading views, EMIT ON WINDOW CLOSE.State is checkpointed to object storage (S3 and compatible), so it is not bounded by memory.

Sources: PostgreSQL REFRESH MATERIALIZED VIEW, pg_ivm, Oracle refresh, Snowflake dynamic tables, Databricks materialized views, BigQuery materialized views, ClickHouse materialized views, Materialize docs, RisingWave CREATE MATERIALIZED VIEW. If a vendor's behavior has changed, tell us and we will correct the row.

Limits

When is incremental maintenance the wrong choice?

Incremental maintenance wins when the change is small relative to the data. It does not win everywhere, and pretending otherwise leads to surprised capacity planning. Four cases to check before you commit a query to a streaming view:

  • →Queries whose output changes almost entirely on every input change. A global percentile or median over the full history is the classic case: the incremental state is as large as the data.
  • →Workloads where nearly all rows change between reads. If a nightly load rewrites 80 percent of a table, a batch refresh after the load costs about the same and is simpler.
  • →Exploratory analysis. A materialized view fixes the query up front. For questions you ask once, run a normal SELECT.
  • →Very large joins on keys with no locality where neither side is small. The join state is proportional to both inputs, so plan the state size before committing to it.

For the first two cases RisingWave lets you keep the same CREATE MATERIALIZED VIEW statement and add WITH (refresh.interval.sec = 600) to get a periodically recomputed view instead of an incremental one, so you can pick the maintenance model per query rather than per system.

Use Cases

Where do incremental materialized views pay off most?

The common thread is a query that is read far more often than its inputs change in bulk, and whose readers care about freshness measured in seconds.

  • →Operational dashboards and alerting on live KPIs, where a five-minute refresh is already too late.
  • →Feature serving for online models and AI agents: aggregates per user, per device or per account that must reflect the last event.
  • →Denormalized read models for APIs, joining several CDC streams into one queryable view instead of fanning out queries.
  • →Medallion-style lakehouse layers, with bronze, silver and gold views that stay mutually consistent and can be written to Iceberg.
  • →Fraud and risk signals that combine a sliding window over events with slowly changing reference data.

Frequently Asked Questions

What is incremental view maintenance (IVM)?
What is the difference between a regular view and a materialized view?
What is the difference between a materialized view and a table?
How does incremental maintenance reduce compute cost?
Does PostgreSQL support incremental materialized views?
How are ClickHouse incremental materialized views different?
Snowflake dynamic tables vs materialized views: which is incremental?
Can I build materialized views on top of other materialized views?
How does RisingWave keep incremental views consistent?

Try an incremental materialized view on your own stream

Connect Kafka or a Postgres CDC source, write one CREATE MATERIALIZED VIEW, and query it like a table.

Start Free
The engine for real-time streaming data.
Analyze, search, and decide.
GitHubXLinkedInSlackYouTube
Sign up for our to stay updated.