Incremental materialized view

Historical defaults and stability

Sequential execution was the default for a long time, in part due to error handling complexities. Historically, a failure in one materialized view could prevent others from executing. Newer versions have improved this by isolating failures per block, but sequential execution still provides clearer failure semantics.

In general, enable `parallel_view_processing=1` when:

- You have multiple independent Materialized Views
- You're aiming to maximize insert performance
- You're aware of the system's capacity to handle concurrent view execution

Leave it disabled when:

- Materialized Views have dependencies on one another
- You require predictable, ordered execution
- You're debugging or auditing insert behavior and want deterministic replay

## Materialized views and Common Table Expressions (CTE)[​](#materialized-views-common-table-expressions-ctes "Direct link to Materialized views and Common Table Expressions (CTE)")

**Non-recursive** Common Table Expressions (CTEs) are supported in Materialized Views.

Note

Common Table Expressions **aren't** materialized

ClickHouse doesn't materialize CTEs; instead, it substitutes the CTE definition directly into the query, which can lead to multiple evaluations of the same expression (if the CTE is used more than once).

Consider the following example which computes daily activity for each post type.

```
CREATE TABLE daily_post_activity
(
    Day Date,
 PostType String,
 PostsCreated SimpleAggregateFunction(sum, UInt64),
 AvgScore AggregateFunction(avg, Int32),
 TotalViews SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (Day, PostType);

CREATE MATERIALIZED VIEW daily_post_activity_mv TO daily_post_activity AS
WITH filtered_posts AS (
    SELECT
 toDate(CreationDate) AS Day,
 PostTypeId,
 Score,
 ViewCount
    FROM posts
    WHERE Score > 0 AND PostTypeId IN (1, 2)  -- Question or Answer
)
SELECT
    Day,
    CASE PostTypeId
        WHEN 1 THEN 'Question'
        WHEN 2 THEN 'Answer'
    END AS PostType,
    count() AS PostsCreated,
    avgState(Score) AS AvgScore,
    sum(ViewCount) AS TotalViews
FROM filtered_posts
GROUP BY Day, PostTypeId;
```

While the CTE is strictly unnecessary here, for example purposes, the view will work as expected:

```
INSERT INTO posts
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
```

```
SELECT
    Day,
    PostType,
    avgMerge(AvgScore) AS AvgScore,
    sum(PostsCreated) AS PostsCreated,
    sum(TotalViews) AS TotalViews
FROM daily_post_activity
GROUP BY
    Day,
    PostType
ORDER BY Day DESC
LIMIT 10

┌────────Day─┬─PostType─┬───────────AvgScore─┬─PostsCreated─┬─TotalViews─┐
│ 2024-03-31 │ Question │ 1.3317757009345794 │          214 │       9728 │
│ 2024-03-31 │ Answer   │ 1.4747191011235956 │          356 │          0 │
│ 2024-03-30 │ Answer   │ 1.4587912087912087 │          364 │          0 │
│ 2024-03-30 │ Question │ 1.2748815165876777 │          211 │       9606 │
│ 2024-03-29 │ Question │ 1.2641509433962264 │          318 │      14552 │
│ 2024-03-29 │ Answer   │ 1.4706927175843694 │          563 │          0 │
│ 2024-03-28 │ Answer   │  1.601637107776262 │          733 │          0 │
│ 2024-03-28 │ Question │ 1.3530864197530865 │          405 │      24564 │
│ 2024-03-27 │ Question │ 1.3225806451612903 │          434 │      21346 │
│ 2024-03-27 │ Answer   │ 1.4907539118065434 │          703 │          0 │
└────────────┴──────────┴────────────────────┴──────────────┴────────────┘

10 rows in set. Elapsed: 0.013 sec. Processed 11.45 thousand rows, 663.87 KB (866.53 thousand rows/s., 50.26 MB/s.)
Peak memory usage: 989.53 KiB.
```

In ClickHouse, CTEs are inlined which means they're effectively copy-pasted into the query during optimization and **not** materialized. This means:

- If your CTE references a different table from the source table (i.e., the one the materialized view is attached to), and is used in a `JOIN` or `IN` clause, it will behave like a subquery or join, not a trigger.
- The materialized view will still only trigger on inserts into the main source table, but the CTE will be re-executed on every insert, which may cause unnecessary overhead, especially if the referenced table is large.

For example,

```
WITH recent_users AS (
  SELECT Id FROM stackoverflow.users WHERE CreationDate > now() - INTERVAL 7 DAY
)
SELECT * FROM stackoverflow.posts WHERE OwnerUserId IN (SELECT Id FROM recent_users)
```

In this case, the users CTE is re-evaluated on every insert into posts, and the materialized view won't update when new users are inserted - only when posts are.

Generally, use CTEs for logic that operates on the same source table the materialized view is attached to or ensure that referenced tables are small and unlikely to cause performance bottlenecks. Alternatively, consider [the same optimizations as JOINs with Materialized Views](/docs/materialized-view/incremental-materialized-view#join-best-practices).

- [Background](#background)- [Example](#example)
    - [A more complex example](#a-more-complex-example)- [Other applications](#other-applications)
      - [Filtering and transformation](#filtering-and-transformation)- [Lookup table](#lookup-table)- [Chaining / cascading materialized views](#chaining)- [Materialized views and JOINs](#materialized-views-and-joins)
        - [Example](#materialized-views-and-joins-example)- [Best practices for JOINs in materialized views](#join-best-practices)- [Using source table in filters and joins](#using-source-table-in-filters-and-joins-in-materialized-views)- [Use cases and caveats](#use-cases-and-caveats)- [Materialized views and unions](#materialized-views-and-unions)- [Parallel vs sequential processing](#materialized-views-parallel-vs-sequential)
            - [When to use parallel processing](#materialized-views-when-to-use-parallel)- [Materialized views and Common Table Expressions (CTE)](#materialized-views-common-table-expressions-ctes)

Was this page helpful?