CREATE MATERIALIZED VIEW

Create streaming materialized views in RisingWave that are incrementally updated in real time. Supports backfill control, background DDL, and cascading materialized views. PostgreSQL-compatible SQL syntax.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] mv_name
[ WITH ( parameter = value [, ... ] ) ]
AS select_query;

Parameters

Parameter or clause	Description
mv_name	The name of the materialized view to be created.
select_query	A SELECT query that retrieves data for the materialized view. See SELECT for the syntax and examples of the SELECT command.
WITH clause	Specifies optional parameters for the materialized view. Common options include:

• source_rate_limit: Throttle ingestion rate during backfill to prevent OOM
• backfill_order: Control the backfill sequence across upstream relations (technical preview)
• cloud.serverless_backfill_enabled: Run the initial backfill on dedicated serverless backfiller nodes for one statement. In RisingWave Cloud, enable serverless backfilling in the Cloud console first. See Serverless backfilling.
• See troubleshooting for more details. |

Names and unquoted identifiers are case-insensitive. Therefore, you must double-quote any of these fields for them to be case-sensitive. See also Identifiers.

The ORDER BY clause in the CREATE MATERIALIZED VIEW statement is allowed but not considered as part of the definition of the materialized view. It’s only used in the initial creation of the materialized view and not during refreshes.

Backfill behavior and controls

1. CREATE MATERIALIZED VIEW will first backfill historical data from the referenced relations, and completion time varies based on the volume of data to be backfilled.

2. To perform the operations in the background, you can execute SET BACKGROUND_DDL=true; before running the CREATE MATERIALIZED VIEW statement. See details in SET BACKGROUND_DDL.

3. Snapshot backfill is enabled by default to improve isolation between the backfill phase and streaming phase. This helps prevent resource contention between backfilling historical data and processing new streaming data. To disable it if needed, set SET streaming_use_snapshot_backfill=false; before creating the materialized view. For more details, see View and configure runtime parameters.

4. To offload the backfill phase to dedicated serverless backfiller nodes, enable serverless backfilling. You can enable it for the current session with SET enable_serverless_backfill = true; or for one statement with WITH (cloud.serverless_backfill_enabled = true).

5. You can specify backfill_order in the WITH clause to control the backfill order for different upstream relations. Use the -> operator to define dependencies between tables. The left relation will be fully backfilled before the right relation begins.

   CREATE MATERIALIZED VIEW m1
   WITH (backfill_order = FIXED(t1 -> t2, t2 -> t3))
   AS
   SELECT v1 FROM t1
   UNION
   SELECT v1 FROM t2
   UNION
   SELECT v1 FROM t3;
   
   -- The backfill will follow this order:
   -- 1. t1
   -- 2. t2
   -- 3. t3
   

   To view progress of each backfill fragment:

   SELECT * FROM rw_catalog.rw_fragment_backfill_progress;
   

   To inspect the structure of the running streaming job:

   DESCRIBE FRAGMENTS <mv name>;
   

   To dump a dot formatted graph of your backfill orders:

   EXPLAIN (backfill, format dot) CREATE MATERIALIZED VIEW ...;
   
   • The backfill_order feature is currently in technical preview stage.
   • It is supported only for MATERIALIZED VIEW.
   • Backfill order recovery is not supported for background ddl. If a backfilling job restarts, the specified order will be lost, and all tables will backfill concurrently.
   • Cross-database scans inside the backfill_order clause are not supported.

See also

Overview of data processing