You have a dashboard that shows total revenue by product category. Every time a new order comes in, the underlying aggregation query runs against millions of rows. Response times creep up. Users complain. Someone suggests caching the results in a materialized view, but now the data goes stale unless you manually refresh it, and each refresh recomputes the entire result set from scratch.
This is the fundamental tension with traditional materialized views: you trade freshness for performance. Incremental materialized views eliminate that tradeoff. Instead of recomputing everything on each refresh, they update only the portion of the result affected by new or changed data. One new order? The system adjusts the running totals for that customer and category without touching the rest.
In this article, you will learn what incremental materialized views are, how they differ from the traditional full-refresh approach, and how RisingWave maintains them automatically. You will build working examples covering aggregations, joins, and cascading materialized views, all verified against RisingWave 2.8.0.
What Are Incremental Materialized Views?
A materialized view is a precomputed query result stored as a physical table. When you query the materialized view, the database reads the stored result instead of re-executing the original query. This makes reads fast, but the stored data can become outdated as the underlying tables change.
Traditional materialized views solve staleness through periodic full refresh. The database re-runs the entire defining query and replaces the old result with the new one. For a query that aggregates 100 million rows, this means processing all 100 million rows even if only 10 rows changed since the last refresh.
An incremental materialized view takes a different approach. It tracks changes (inserts, updates, deletes) to the underlying tables and applies only the necessary modifications to the stored result. If 10 new rows arrive, the system computes how those 10 rows affect the result and applies the delta. The other 99,999,990 rows are never touched.
Full Refresh vs. Incremental Maintenance
| Aspect | Full Refresh | Incremental Maintenance |
| Update method | Recompute entire query | Apply delta from changed rows |
| Cost per update | Proportional to total data size | Proportional to change volume |
| Freshness | Stale between refreshes | Continuously up to date |
| Trigger | Manual or scheduled (e.g., cron) | Automatic on data change |
| Consistency | Snapshot at refresh time | Eventually consistent (typically sub-second) |
| Complexity | Simple to implement | Requires change propagation engine |
The incremental approach is particularly valuable when the ratio of changes to total data is small, which is the common case in production systems. A table with 500 million rows might receive 10,000 new rows per second. Reprocessing 500 million rows every few seconds is not feasible. Processing 10,000 deltas per second is straightforward.
How RisingWave Maintains Incremental Materialized Views
RisingWave is a streaming database purpose-built for incremental computation. Unlike traditional databases that bolt materialized view support onto a batch query engine, RisingWave treats every materialized view as a continuously running streaming pipeline.
When you execute CREATE MATERIALIZED VIEW in RisingWave, the system:
- Parses the query and builds a streaming execution plan (a directed acyclic graph of operators).
- Deploys stream operators for each part of the query: filters, joins, aggregations, projections.
- Processes the initial data (called "backfill") to compute the current result.
- Listens for changes on all upstream tables and materialized views.
- Propagates deltas through the operator graph whenever upstream data changes.
Each operator in the graph knows how to update its output incrementally. A SUM operator, for example, does not re-sum all values when a new row arrives. It adds the new value to the running total. A JOIN operator does not re-join all rows; it probes the new row against the other side's existing state and emits matching pairs.
This architecture means there is no REFRESH MATERIALIZED VIEW command in RisingWave, because there is nothing to refresh. The views are always current. You can read more about this in the RisingWave materialized views documentation.
Consistency Guarantees
RisingWave provides barrier-based checkpoint consistency. The system periodically injects barriers into the data stream. All operators process data between barriers atomically, ensuring that materialized views reflect a consistent snapshot, not a half-processed state. This means you will never query a materialized view and see partial results from an in-flight batch of changes.
Practical Example: Aggregation-Based Materialized View
Let's build a concrete example. You will create tables, insert data, define a materialized view with aggregations, and observe how it updates incrementally.
All SQL in this article is tested against RisingWave 2.8.0 running locally.
Set Up the Base Tables
CREATE TABLE blog_orders (
order_id INT,
customer_id INT,
product_id INT,
quantity INT,
unit_price NUMERIC,
order_time TIMESTAMP
);
CREATE TABLE blog_products (
product_id INT,
product_name VARCHAR,
category VARCHAR
);
Insert sample data:
INSERT INTO blog_products VALUES
(1, 'Wireless Mouse', 'Electronics'),
(2, 'USB-C Cable', 'Electronics'),
(3, 'Notebook', 'Office Supplies'),
(4, 'Desk Lamp', 'Office Supplies'),
(5, 'Coffee Beans', 'Food & Beverage');
INSERT INTO blog_orders VALUES
(1001, 201, 1, 2, 29.99, '2026-03-15 10:30:00'),
(1002, 202, 3, 5, 4.99, '2026-03-15 11:00:00'),
(1003, 201, 2, 1, 12.99, '2026-03-15 11:30:00'),
(1004, 203, 5, 3, 18.50, '2026-03-15 12:00:00'),
(1005, 202, 4, 1, 45.00, '2026-03-15 12:30:00'),
(1006, 204, 1, 4, 29.99, '2026-03-15 13:00:00'),
(1007, 201, 5, 2, 18.50, '2026-03-15 13:30:00');
Create the Aggregation Materialized View
This materialized view computes per-customer order totals:
CREATE MATERIALIZED VIEW blog_mv_order_totals AS
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(quantity * unit_price) AS total_spent
FROM blog_orders
GROUP BY customer_id;
Query the result immediately:
SELECT * FROM blog_mv_order_totals ORDER BY customer_id;
customer_id | total_orders | total_spent
-------------+--------------+-------------
201 | 3 | 109.97
202 | 2 | 69.95
203 | 1 | 55.50
204 | 1 | 119.96
(4 rows)
No REFRESH command needed. The view computed its initial state automatically during creation.
Observe Incremental Updates
Now insert a new order:
INSERT INTO blog_orders VALUES
(1008, 205, 3, 10, 4.99, '2026-03-15 14:00:00');
Query the materialized view again:
SELECT * FROM blog_mv_order_totals ORDER BY customer_id;
customer_id | total_orders | total_spent
-------------+--------------+-------------
201 | 3 | 109.97
202 | 2 | 69.95
203 | 1 | 55.50
204 | 1 | 119.96
205 | 1 | 49.90
(5 rows)
Customer 205 appeared instantly. RisingWave did not re-aggregate all seven previous orders. It processed only the new row, computed COUNT(*) = 1 and SUM(10 * 4.99) = 49.90, and inserted a new entry into the materialized result.
Practical Example: Join-Based Materialized View
Materialized views that join multiple tables benefit even more from incremental maintenance. A full refresh of a join over two large tables is expensive. Incrementally, when a new row arrives on one side, the system only needs to probe it against the other side's existing data.
CREATE MATERIALIZED VIEW blog_mv_enriched_orders AS
SELECT
o.order_id,
o.customer_id,
p.product_name,
p.category,
o.quantity,
o.unit_price,
o.quantity * o.unit_price AS line_total,
o.order_time
FROM blog_orders o
JOIN blog_products p ON o.product_id = p.product_id;
SELECT * FROM blog_mv_enriched_orders ORDER BY order_id;
order_id | customer_id | product_name | category | quantity | unit_price | line_total | order_time
----------+-------------+----------------+-----------------+----------+------------+------------+---------------------
1001 | 201 | Wireless Mouse | Electronics | 2 | 29.99 | 59.98 | 2026-03-15 10:30:00
1002 | 202 | Notebook | Office Supplies | 5 | 4.99 | 24.95 | 2026-03-15 11:00:00
1003 | 201 | USB-C Cable | Electronics | 1 | 12.99 | 12.99 | 2026-03-15 11:30:00
1004 | 203 | Coffee Beans | Food & Beverage | 3 | 18.50 | 55.50 | 2026-03-15 12:00:00
1005 | 202 | Desk Lamp | Office Supplies | 1 | 45.00 | 45.00 | 2026-03-15 12:30:00
1006 | 204 | Wireless Mouse | Electronics | 4 | 29.99 | 119.96 | 2026-03-15 13:00:00
1007 | 201 | Coffee Beans | Food & Beverage | 2 | 18.50 | 37.00 | 2026-03-15 13:30:00
(7 rows)
Each order row is now enriched with product details. When a new order arrives, RisingWave looks up the matching product and emits the joined result without re-joining the entire dataset.
Notice that order 1008 (which we inserted earlier for customer 205) also appears when queried:
SELECT * FROM blog_mv_enriched_orders WHERE customer_id = 205;
order_id | customer_id | product_name | category | quantity | unit_price | line_total | order_time
----------+-------------+--------------+-----------------+----------+------------+------------+---------------------
1008 | 205 | Notebook | Office Supplies | 10 | 4.99 | 49.90 | 2026-03-15 14:00:00
(1 row)
The join MV picked up the new order automatically because it subscribes to changes on both blog_orders and blog_products.
Practical Example: Cascading Materialized Views
One of the most powerful patterns with incremental materialized views is cascading: building one materialized view on top of another. RisingWave treats the output of one MV as a stream of changes that feeds into downstream MVs, creating a multi-layer pipeline where changes propagate through all levels automatically.
Layer 1: Enriched Orders (Join)
We already created blog_mv_enriched_orders above, which joins orders with product details.
Layer 2: Category Revenue (Aggregation on top of Join)
CREATE MATERIALIZED VIEW blog_mv_category_revenue AS
SELECT
category,
COUNT(*) AS order_count,
SUM(line_total) AS total_revenue,
AVG(line_total) AS avg_order_value
FROM blog_mv_enriched_orders
GROUP BY category;
SELECT * FROM blog_mv_category_revenue ORDER BY total_revenue DESC;
category | order_count | total_revenue | avg_order_value
-----------------+-------------+---------------+-----------------
Electronics | 3 | 192.93 | 64.31
Office Supplies | 3 | 119.85 | 39.95
Food & Beverage | 2 | 92.50 | 46.25
(3 rows)
This MV does not query the base tables at all. It reads from blog_mv_enriched_orders and aggregates by category. When a new order arrives, the change flows: blog_orders -> blog_mv_enriched_orders -> blog_mv_category_revenue.
Layer 3: High-Value Categories (Filter on top of Aggregation)
CREATE MATERIALIZED VIEW blog_mv_high_value_categories AS
SELECT
category,
total_revenue,
avg_order_value
FROM blog_mv_category_revenue
WHERE total_revenue > 80;
SELECT * FROM blog_mv_high_value_categories ORDER BY total_revenue DESC;
category | total_revenue | avg_order_value
-----------------+---------------+-----------------
Electronics | 192.93 | 64.31
Office Supplies | 119.85 | 39.95
Food & Beverage | 92.50 | 46.25
(3 rows)
All three categories currently exceed the threshold. The three-layer pipeline looks like this:
graph LR
A[blog_orders] --> C[blog_mv_enriched_orders]
B[blog_products] --> C
C --> D[blog_mv_category_revenue]
D --> E[blog_mv_high_value_categories]
When you insert a single row into blog_orders, the change propagates through all three materialized views automatically. There is no scheduling, no orchestration code, and no refresh commands to manage.
Why Cascading MVs Matter
In traditional databases, building a pipeline of dependent materialized views requires careful orchestration. You need to refresh them in the correct order (leaf views first, then views that depend on them), handle failures at each stage, and accept that the pipeline might take minutes or hours to complete. With incremental maintenance in RisingWave, the entire pipeline updates in sub-second latency as a single streaming computation.
This pattern is common in production systems:
- Layer 1: Clean and enrich raw event data (joins with dimension tables)
- Layer 2: Compute business metrics (aggregations, window functions)
- Layer 3: Apply business rules (filters, thresholds, alerts)
Each layer reads from the one below it, and the entire stack stays consistent without manual intervention.
Incremental Materialized Views vs. Traditional Database Approaches
Different databases handle materialized views in different ways. Here is how RisingWave's approach compares to other common options.
PostgreSQL
PostgreSQL supports materialized views but only with full refresh (REFRESH MATERIALIZED VIEW). There is no built-in incremental maintenance. You can achieve something similar with triggers and manually maintained summary tables, but this requires significant custom code and is error-prone for complex queries.
Oracle and SQL Server
Both offer some form of incremental refresh (Oracle calls them "fast refresh" materialized views, SQL Server uses "indexed views"). However, these come with significant restrictions on the queries you can use. Complex joins, certain aggregation functions, and subqueries often disqualify a view from incremental maintenance. You must also manually create materialized view logs (Oracle) or meet strict determinism requirements (SQL Server).
Apache Flink
Flink supports continuous queries with incremental state maintenance, similar in concept to RisingWave. The key difference is deployment complexity. Flink requires a separate cluster, a JVM-based runtime, and a different programming model (DataStream API or Flink SQL). RisingWave offers the same incremental computation through a PostgreSQL-compatible interface, so you can use existing SQL tools, drivers, and workflows.
Materialize
Materialize also provides incrementally maintained views with a SQL interface. RisingWave and Materialize share similar goals but differ in storage architecture. RisingWave uses a cloud-native shared storage layer that separates compute and storage, while Materialize ties compute and storage together. RisingWave is also open source under the Apache 2.0 license.
Performance Characteristics of Incremental Maintenance
The efficiency of incremental maintenance depends on the query operators involved:
- Filters (WHERE): Near-zero cost. The system evaluates the filter condition against the new row and either emits or drops it.
- Projections (SELECT columns): Trivial. Just map the new row to the output schema.
- Aggregations (GROUP BY, SUM, COUNT, AVG): The system maintains per-group state. A new row looks up its group, updates the running aggregate, and emits the changed group. Cost is proportional to the number of groups affected, not the total number of rows.
- Joins: The system indexes both sides by the join key. A new row on one side probes the other side's index. Cost depends on how many rows match on the other side.
- Window functions: These require maintaining ordered state and can be more expensive for operations like
RANK()that may cause cascading updates within a window partition.
For most production workloads, the incremental cost per event is measured in microseconds to low milliseconds, making it feasible to maintain dozens of materialized views simultaneously on a single RisingWave instance. You can monitor performance using the RisingWave system catalog tables.
What Are the Limitations of Incremental Materialized Views?
Incremental materialized views are not a universal solution. There are scenarios where they are less effective or require careful consideration:
- Non-monotonic queries: Some queries, such as those using
EXCEPTor certain correlated subqueries, are difficult to maintain incrementally because changes in one part of the query can affect arbitrary rows in the result. - State size: Each materialized view maintains internal state (hash maps for joins, accumulators for aggregations). If a join has a very high cardinality on both sides, the state can consume significant memory and storage.
- Retraction cost: When a row is deleted or updated in a base table, the system must "retract" its contribution from all downstream views. For deeply nested cascading views, retraction can be more expensive than insertion.
- Complex expressions: While RisingWave supports a wide range of SQL, some expressions (like non-deterministic functions) cannot be maintained incrementally because their output is not a pure function of their input.
Understanding these tradeoffs helps you decide which queries to materialize and which to leave as regular views or ad-hoc queries.
What Is the Difference Between a Materialized View and an Incremental Materialized View?
A standard materialized view stores a precomputed query result that you refresh manually or on a schedule. Each refresh recomputes the entire query from scratch, regardless of how much data changed. This approach is simple but wasteful when only a small fraction of the underlying data has been modified.
An incremental materialized view also stores a precomputed result, but it updates that result continuously by processing only the changes (deltas) to the underlying data. When a new row is inserted, updated, or deleted, the system computes how that change affects the materialized result and applies the minimal necessary modification. This makes incremental materialized views suitable for real-time applications where data freshness matters.
When Should You Use Incremental Materialized Views?
You should use incremental materialized views when your application requires low-latency access to derived data that changes frequently. Common use cases include:
- Real-time dashboards where metrics must reflect the latest data without manual refresh
- Event-driven applications that need to react to state changes within seconds
- Data enrichment pipelines where raw events must be joined with dimension tables continuously
- Pre-aggregated APIs where endpoint response times must stay under a few milliseconds regardless of data volume
If your data changes infrequently (once per day or less) and query latency requirements are relaxed, a traditional materialized view with scheduled refresh may be simpler and sufficient. For more details on getting started, see the RisingWave quickstart guide.
How Do Incremental Materialized Views Handle Joins Across Multiple Tables?
When a materialized view joins two or more tables, the system maintains an index for each side of the join. When a new row arrives on one side, the system probes the index on the other side to find matching rows and emits the joined result. This is the same hash-join algorithm used in batch processing, but applied incrementally to each arriving row rather than to the full dataset.
RisingWave supports inner joins, left joins, right joins, and full outer joins in materialized views. For multi-way joins (three or more tables), the system chains the join operators. A new row propagates through each join stage, and only the matching rows at each stage are processed. See the RisingWave SQL reference for joins for the full list of supported join types.
Conclusion
Incremental materialized views fundamentally change how you think about data freshness and query performance. Instead of choosing between fast reads (materialized, possibly stale) and fresh data (re-query everything), you get both.
Key takeaways:
- Incremental materialized views update only the portion of the result affected by new data, making the cost proportional to the change volume rather than the total data size.
- RisingWave maintains these views automatically through a streaming execution engine, with no manual refresh commands needed.
- Cascading materialized views let you build multi-layer data pipelines where changes propagate through all layers automatically.
- The pattern works well for aggregations, joins, and filters, covering the vast majority of analytical query patterns.
- Understanding the tradeoffs (state size, retraction cost, query restrictions) helps you design effective streaming pipelines.
Ready to try incremental materialized views yourself? Get started with RisingWave in 5 minutes. Quickstart ->
Join our Slack community to ask questions and connect with other stream processing developers.
