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.
Definition
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.
| Factor | Full refresh | Incremental maintenance |
|---|---|---|
| Work per refresh | Scan the whole base data | Process the changed rows |
| Cost scales with | Total data size | Change rate |
| Freshness | Stale until the next refresh | Seconds behind the source, or less |
| Operational pieces | Scheduler, retries, overlap handling | One CREATE MATERIALIZED VIEW |
| Best when | Most rows change between refreshes | Few rows change relative to the total |
Mechanism
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.
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.
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.
-- 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
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.
| System | Maintenance model | Freshness | Query coverage | Watch out for |
|---|---|---|---|---|
| PostgreSQL | Full 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. |
| Oracle | Fast 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. |
| Snowflake | Materialized 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. |
| Databricks | Materialized 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. |
| BigQuery | Automatic 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. |
| ClickHouse | Materialized 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. |
| Materialize | Streaming 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. |
| RisingWave | Streaming 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
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:
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
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.
Connect Kafka or a Postgres CDC source, write one CREATE MATERIALIZED VIEW, and query it like a table.
Start Free