Materialized Views in RisingWave: A Complete Guide

Introduction

You have a dashboard that shows real-time order totals, active user counts, or fraud alerts. Every time someone loads the page, the system runs the same expensive aggregation query against a growing dataset. The query gets slower as data scales, and your users notice.

Materialized views in RisingWave solve this problem by precomputing query results and keeping them incrementally updated as new data arrives. Unlike traditional databases where materialized views require manual or scheduled refreshes, RisingWave maintains them continuously: every insert, update, or delete is reflected in the materialized view within seconds.

This guide covers everything you need to know about materialized views in RisingWave, from basic syntax to advanced patterns like cascading views and streaming joins. Every SQL example is tested against RisingWave v2.8.0 and ready to run.

What Is a Materialized View?

A materialized view is a database object that stores the result of a query physically, rather than recomputing it on every read. Think of it as a cached query result that the database keeps in sync with the underlying data.

In traditional databases like PostgreSQL, materialized views are snapshots. You run REFRESH MATERIALIZED VIEW on a schedule, and the view is stale between refreshes. This approach falls apart when you need fresh data: a 10-minute refresh cycle means your dashboard could be showing data that is 10 minutes old.

RisingWave takes a fundamentally different approach. It uses incremental maintenance, which means it processes only the changes (deltas) in the source data rather than recomputing the entire query. When a new row arrives in the source table, RisingWave propagates only that change through the query plan and updates the materialized view result. The view stays fresh with sub-second latency.

Why Incremental Maintenance Matters

Consider an aggregation query that counts orders by status across 100 million rows. In a traditional database, refreshing this materialized view means scanning all 100 million rows every time. In RisingWave, inserting one new order triggers a single increment to the relevant counter. The cost is proportional to the change, not the total data size.

This makes materialized views in RisingWave practical for use cases that would be prohibitively expensive with batch refreshes:

• Real-time dashboards with sub-second freshness
• Streaming aggregations over event data
• Continuously updated feature stores for ML pipelines
• Alerting systems that react to patterns as they happen

Creating Materialized Views: Syntax and Examples

Basic Syntax

The syntax for creating a materialized view in RisingWave follows standard SQL:

CREATE MATERIALIZED VIEW view_name AS
  SELECT ...
  FROM source_or_table
  WHERE ...
  GROUP BY ...;

Let's walk through practical examples with realistic data. First, create a source table for e-commerce orders:

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product VARCHAR,
    amount DECIMAL,
    status VARCHAR,
    region VARCHAR,
    created_at TIMESTAMP
);

Insert some sample data:

INSERT INTO orders VALUES
    (1, 101, 'Laptop', 1200.00, 'completed', 'us-east', '2026-03-28 10:00:00'),
    (2, 102, 'Mouse', 25.00, 'completed', 'us-west', '2026-03-28 10:05:00'),
    (3, 103, 'Keyboard', 75.00, 'pending', 'eu-west', '2026-03-28 10:10:00'),
    (4, 101, 'Monitor', 450.00, 'completed', 'us-east', '2026-03-28 10:15:00'),
    (5, 104, 'Headphones', 150.00, 'cancelled', 'us-west', '2026-03-28 10:20:00'),
    (6, 105, 'Webcam', 80.00, 'completed', 'eu-west', '2026-03-28 10:25:00'),
    (7, 102, 'USB Hub', 35.00, 'pending', 'us-east', '2026-03-28 10:30:00'),
    (8, 106, 'Tablet', 600.00, 'completed', 'us-west', '2026-03-28 10:35:00');

Example 1: Simple Aggregation

Create a materialized view that tracks order totals by status:

CREATE MATERIALIZED VIEW mv_order_summary AS
SELECT
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY status;

Query the view just like a regular table:

SELECT * FROM mv_order_summary ORDER BY status;

Expected output:

   status   | order_count | total_amount
------------+-------------+--------------
 cancelled  |           1 |       150.00
 completed  |           5 |      2355.00
 pending    |           2 |       110.00

Example 2: Filtered Aggregation by Region

Create a view that shows revenue per region, but only for completed orders:

CREATE MATERIALIZED VIEW mv_revenue_by_region AS
SELECT
    region,
    COUNT(*) AS completed_orders,
    SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY region;

SELECT * FROM mv_revenue_by_region ORDER BY revenue DESC;

Expected output:

 region  | completed_orders | revenue
---------+------------------+---------
 us-east |                2 | 1650.00
 us-west |                2 |  625.00
 eu-west |                1 |   80.00

Example 3: Cascading Materialized Views

One powerful feature of RisingWave is that materialized views can reference other materialized views, creating a computation pipeline. This is called cascading.

Let's build a view on top of mv_revenue_by_region that identifies the top-performing region:

CREATE MATERIALIZED VIEW mv_top_region AS
SELECT
    region,
    revenue
FROM mv_revenue_by_region
ORDER BY revenue DESC
LIMIT 1;

SELECT * FROM mv_top_region;

Expected output:

 region  | revenue
---------+---------
 us-east | 1650.00

When new completed orders arrive, mv_revenue_by_region updates first, then mv_top_region updates automatically. RisingWave handles the dependency chain for you.

Streaming Joins in Materialized Views

Materialized views become especially powerful when they involve joins. RisingWave supports streaming joins that stay incrementally maintained as either side of the join receives new data.

Create a customers table:

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR,
    tier VARCHAR
);

INSERT INTO customers VALUES
    (101, 'Alice', 'gold'),
    (102, 'Bob', 'silver'),
    (103, 'Charlie', 'bronze'),
    (104, 'Diana', 'silver'),
    (105, 'Eve', 'gold'),
    (106, 'Frank', 'bronze');

Now create a materialized view that joins orders with customers to calculate spending by customer tier:

CREATE MATERIALIZED VIEW mv_spending_by_tier AS
SELECT
    c.tier,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.tier;

SELECT * FROM mv_spending_by_tier ORDER BY total_spent DESC;

Expected output:

  tier  | order_count | total_spent
--------+-------------+-------------
 gold   |           3 |     1730.00
 bronze |           2 |      675.00
 silver |           3 |      210.00

This view updates whenever a new order is placed or a customer's tier changes. Both sides of the join are monitored.

Performance Considerations

Memory and State Management

Every materialized view maintains internal state. For aggregation views, this includes the intermediate aggregation results. For join views, RisingWave keeps both sides of the join in memory to process new arrivals efficiently.

Key factors that affect resource usage:

• Number of groups: A GROUP BY with high cardinality (millions of distinct keys) requires more memory than one with a few hundred keys.
• Join state: A join between two large tables keeps both sides in state storage. Use filters in the WHERE clause to reduce the state size.
• Cascading depth: Each level of cascading adds processing overhead. Keep the chain shallow when possible (2-3 levels is typical).

Best Practices

1. Filter early: Place WHERE clauses to reduce the data flowing into materialized views. Processing fewer rows means lower latency and less resource usage.

2. Choose keys wisely: The columns in GROUP BY determine how state is partitioned. Use columns with bounded cardinality when possible.

3. Monitor with EXPLAIN: Use EXPLAIN CREATE MATERIALIZED VIEW to inspect the query plan before creating a view. This helps catch inefficient plans early.

4. Drop unused views: Materialized views consume resources even when nobody queries them, because they still process incoming data. Drop views you no longer need with DROP MATERIALIZED VIEW view_name;.

For a deeper dive into query optimization, see the RisingWave performance tuning guide.

Materialized Views vs. Traditional Approaches

RisingWave combines the low operational overhead of a database with the streaming capabilities of a dedicated stream processor. You write standard SQL, and the system handles the incremental computation automatically. For more details on how RisingWave compares to other stream processors, see the RisingWave architecture overview.

FAQ

What is a materialized view in RisingWave?

A materialized view in RisingWave is a precomputed query result that is automatically and incrementally maintained as the underlying data changes. Unlike traditional databases that require manual refresh, RisingWave updates materialized views continuously with sub-second latency.

Can materialized views in RisingWave reference other materialized views?

Yes, RisingWave supports cascading materialized views where one view reads from another. The system automatically manages the dependency chain, updating downstream views when upstream views change. This lets you build multi-stage streaming pipelines entirely in SQL.

How much memory do materialized views use?

Memory usage depends on the query type and data volume. Aggregation views store one result per group key, so a GROUP BY region with 10 regions is lightweight. Join views maintain both sides of the join in state, which can be more memory-intensive. Use filters and bounded keys to control resource usage.

When should I use a materialized view instead of a regular query?

Use a materialized view when you have a query that runs frequently and needs fresh results, but the computation is expensive (aggregations, joins, or complex filters). The view precomputes the result, so reads are fast. If a query runs rarely or the underlying data changes infrequently, a regular query may be simpler.

Conclusion

Materialized views in RisingWave provide a practical way to maintain fresh query results over streaming data without the complexity of traditional stream processing frameworks. Key takeaways:

• RisingWave uses incremental maintenance, processing only data changes rather than full recomputation
• You create and query materialized views with standard SQL, no custom APIs or SDKs needed
• Cascading views let you build multi-stage pipelines where each stage feeds the next
• Streaming joins stay up to date as either side receives new data
• Performance tuning comes down to filtering early, choosing bounded group keys, and monitoring with EXPLAIN

For more on streaming SQL concepts, explore the RisingWave SQL reference for CREATE MATERIALIZED VIEW.

Ready to try this yourself? Get started with RisingWave in 5 minutes. Quickstart →

Join our Slack community to ask questions and connect with other stream processing developers.