> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```sql theme={null}
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](/sql/commands/sql-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. Serverless backfilling is not supported for databases in non-default resource groups. See [Serverless backfilling](/processing/serverless-backfill).
* See [troubleshooting](/troubleshoot/troubleshoot-oom#oom-when-creating-materialized-views) for more details. |

<Note>
  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](/sql/identifiers).
</Note>

<Note>
  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.
</Note>

## 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](/sql/commands/sql-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](/operate/view-configure-runtime-parameters).

4. To offload the backfill phase to dedicated serverless backfiller nodes, enable [serverless backfilling](/processing/serverless-backfill). 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)`. Serverless backfilling is not supported for databases in non-default resource groups.

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.

   ```sql theme={null}
   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:

   ```sql theme={null}
   SELECT * FROM rw_catalog.rw_fragment_backfill_progress;
   ```

   To inspect the structure of the running streaming job:

   ```sql theme={null}
   DESCRIBE FRAGMENTS <mv name>;
   ```

   To dump a `dot` formatted graph of your backfill orders:

   ```sql theme={null}
   EXPLAIN (backfill, format dot) CREATE MATERIALIZED VIEW ...;
   ```

   <Note>
     * 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.
   </Note>

## See also

<CardGroup>
  <Card title="Overview of data processing" icon="database" href="/processing/overview" horizontal />
</CardGroup>