Incremental Materialized View

Background

Incremental Materialized Views (Materialized Views) allow users to shift the cost of computation from query time to insert time, resulting in faster SELECT queries.

Unlike in transactional databases like Postgres, a ClickHouse Materialized View is just a trigger that runs a query on blocks of data as they are inserted into a table. The result of this query is inserted into a second "target" table. Should more rows be inserted, results will again be sent to the target table where the intermediate results will be updated and merged. This merged result is the equivalent of running the query over all of the original data.

The principal motivation for Materialized Views is that the results inserted into the target table represent the results of an aggregation, filtering, or transformation on rows. These results will often be a smaller representation of the original data (a partial sketch in the case of aggregations). This, along with the resulting query for reading the results from the target table being simple, ensures query times are faster than if the same computation was performed on the original data, shifting computation (and thus query latency) from query time to insert time.

Materialized views in ClickHouse are updated in real time as data flows into the table they are based on, functioning more like continually updating indexes. This is in contrast to other databases where Materialized Views are typically static snapshots of a query that must be refreshed (similar to ClickHouse Refreshable Materialized Views).

Example

For example purposes we'll use the Stack Overflow dataset documented in "Schema Design".

Suppose we want to obtain the number of up and down votes per day for a post.

This is a reasonably simple query in ClickHouse thanks to the toStartOfDay function:

This query is already fast thanks to ClickHouse, but can we do better?

If we want to compute this at insert time using a Materialized View, we need a table to receive the results. This table should only keep 1 row per day. If an update is received for an existing day, the other columns should be merged into the existing day's row. For this merge of incremental states to happen, partial states must be stored for the other columns.

This requires a special engine type in ClickHouse: the SummingMergeTree. This replaces all the rows with the same ordering key with one row which contains summed values for the numeric columns. The following table will merge any rows with the same date, summing any numerical columns:

To demonstrate our Materialized View, assume our votes table is empty and have yet to receive any data. Our Materialized View performs the above SELECT on data inserted into votes, with the results sent to up_down_votes_per_day:

The TO clause here is key, denoting where results will be sent to i.e. up_down_votes_per_day.

We can repopulate our votes table from our earlier insert:

On completion, we can confirm the size of our up_down_votes_per_day - we should have 1 row per day:

We've effectively reduced the number of rows here from 238 million (in votes) to 5000 by storing the result of our query. What's key here, however, is that if new votes are inserted into the votes table, new values will be sent to the up_down_votes_per_day for their respective day where they will be automatically merged asynchronously in the background - keeping only one row per day. up_down_votes_per_day will thus always be both small and up-to-date.

Since the merging of rows is asynchronous, there may be more than one vote per day when a user queries. To ensure any outstanding rows are merged at query time, we have two options:

• Use the FINAL modifier on the table name. We did this for the count query above.
• Aggregate by the ordering key used in our final table i.e. CreationDate and sum the metrics. Typically this is more efficient and flexible (the table can be used for other things), but the former can be simpler for some queries. We show both below:

This has sped up our query from 0.133s to 0.004s – an over 25x improvement!

info

Important: ORDER BY = GROUP BY

In most cases the columns used in the GROUP BY clause of the Materialized Views transformation, should be consistent with those used in the ORDER BY clause of the target table if using the SummingMergeTree or AggregatingMergeTree table engines. These engines rely on the ORDER BY columns to merge rows with identical values during background merge operations. Misalignment between GROUP BY and ORDER BY columns can lead to inefficient query performance, suboptimal merges, or even data discrepancies.

A more complex example

The above example uses Materialized Views to compute and maintain two sums per day. Sums represent the simplest form of aggregation to maintain partial states for - we can just add new values to existing values when they arrive. However, ClickHouse Materialized Views can be used for any aggregation type.

Suppose we wish to compute some statistics for posts for each day: the 99.9th percentile for the Score and an average of the CommentCount. The query to compute this might look like:

As before, we can create a Materialized View which executes the above query as new posts are inserted into our posts table.

For the purposes of example, and to avoid loading the posts data from S3, we will create a duplicate table posts_null with the same schema as posts. However, this table will not store any data and simply be used by the Materialized View when rows are inserted. To prevent storage of data, we can use the Null table engine type.

The Null table engine is a powerful optimization - think of it as /dev/null. Our Materialized View will compute and store our summary statistics when our posts_null table receives rows at insert time - it's just a trigger. However, the raw data will not be stored. While in our case, we probably still want to store the original posts, this approach can be used to compute aggregates while avoiding storage overhead of the raw data.

The Materialized View thus becomes:

Note how we append the suffix State to the end of our aggregate functions. This ensures the aggregate state of the function is returned instead of the final result. This will contain additional information to allow this partial state to merge with other states. For example, in the case of an average, this will include a count and sum of the column.

Partial aggregation states are necessary to compute correct results. For example, for computing an average, simply averaging the averages of sub-ranges produces incorrect results.

We now create the target table for this view post_stats_per_day which stores these partial aggregate states:

While earlier the SummingMergeTree was sufficient to store counts, we require a more advanced engine type for other functions: the AggregatingMergeTree. To ensure ClickHouse knows that aggregate states will be stored, we define the Score_quantiles and AvgCommentCount as the type AggregateFunction, specifying the function source of the partial states and the type of their source columns. Like the SummingMergeTree, rows with the same ORDER BY key value will be merged (Day in the above example).

To populate our post_stats_per_day via our Materialized View, we can simply insert all rows from posts into posts_null:

In production, you would likely attach the Materialized View to the posts table. We have used posts_null here to demonstrate the null table.

Our final query needs to utilize the Merge suffix for our functions (as the columns store partial aggregation states):

Note we use a GROUP BY here instead of using FINAL.

Materialized Views and JOINs

Refreshable Materialized Views

The following applies to Incremental Materialized Views only. Refreshable Materialized Views execute their query periodically over the full target dataset and fully support JOINs. Consider using them for complex JOINs if a reduction in result freshness can be tolerated.

Incremental Materialized views in ClickHouse fully support JOIN operations, but with one crucial constraint: the Materialized View only triggers on inserts to the source table (the left-most table in the query). Right-side tables in JOINs do not trigger updates, even if their data changes. This behavior is especially important when building Incremental Materialized Views, where data is aggregated or transformed during insert time.

When an Incremental Materialized View is defined using a JOIN, the left-most table in the SELECT query acts as the source. When new rows are inserted into this table, ClickHouse executes the Materialized View query only with those newly inserted rows. Right-side tables in the JOIN are read in full during this execution, but changes to them alone do not trigger the view.

This behavior makes JOINs in Materialized Views similar to a snapshot join against static dimension data.

This works well for enriching data with reference or dimension tables. However, any updates to the right-side tables (e.g., user metadata) will not retroactively update the Materialized View. To see updated data, new inserts must arrive in the source table.

Example

Let's walk through a concrete example using the Stack Overflow dataset. We'll use a Materialized View to compute daily badges per user, including the display name of the user from the users table.

As a reminder, our table schemas are:

We'll assume our users table is pre-populated:

The Materialized View and its associated target table are defined as:

Grouping and Ordering Alignment

The GROUP BY clause in the Materialized View must include DisplayName, UserId, and Day to match the ORDER BY in the SummingMergeTree target table. This ensures rows are correctly aggregated and merged. Omitting any of these can lead to incorrect results or inefficient merges.

If we now populate the badges, the view will be triggered - populating our daily_badges_by_user table.

Suppose we wish to view the badges achieved by a specific user, we can write the following query:

Now, if this user receives a new badge and a row is inserted, our view will be updated:

danger

Notice the latency of the insert here. The inserted user row is joined against the entire users table, significantly impacting insert performance. We propose approaches to address this below in "Using Source Table in Filters and Joins".

Conversely, if we insert a badge for a new user, followed by the row for the user, our Materialized View will fail to capture the users' metrics.

The view, in this case, only executes for the badge insert before the user row exists. If we insert another badge for the user, a row is inserted, as is expected:

Note, however, that this result is incorrect.

Best Practices for JOINs in Materialized Views

• Use the left-most table as the trigger. Only the table on the left side of the SELECT statement triggers the Materialized View. Changes to right-side tables will not trigger updates.

• Pre-insert joined data. Ensure that data in joined tables exists before inserting rows into the source table. The JOIN is evaluated at insert time, so missing data will result in unmatched rows or nulls.

• Limit columns pulled from joins. Select only the required columns from joined tables to minimize memory use and reduce insert-time latency (see below).

• Evaluate insert-time performance. JOINs increase the cost of inserts, especially with large right-side tables. Benchmark insert rates using representative production data.

• Prefer dictionaries for simple lookups. Use Dictionaries for key-value lookups (e.g., user ID to name) to avoid expensive JOIN operations.

• Align GROUP BY and ORDER BY for merge efficiency. When using SummingMergeTree or AggregatingMergeTree, ensure GROUP BY matches the ORDER BY clause in the target table to allow efficient row merging.

• Use explicit column aliases. When tables have overlapping column names, use aliases to prevent ambiguity and ensure correct results in the target table.

• Consider insert volume and frequency. JOINs work well in moderate insert workloads. For high-throughput ingestion, consider using staging tables, pre-joins, or other approaches such as Dictionaries and Refreshable Materialized Views.

Using Source Table in Filters and Joins

When working with Materialized Views in ClickHouse, it's important to understand how the source table is treated during the execution of the Materialized View's query. Specifically, the source table in the Materialized View's query is replaced with the inserted block of data. This behavior can lead to some unexpected results if not properly understood.

Example Scenario

Consider the following setup:

Explanation

In the above example, we have two Materialized Views mvw1 and mvw2 that perform similar operations but with a slight difference in how they reference the source table t0.

In mvw1, table t0 is directly referenced inside a (SELECT * FROM t0) subquery on the right side of the JOIN. When data is inserted into t0, the Materialized View's query is executed with the inserted block of data replacing t0. This means that the JOIN operation is performed only on the newly inserted rows, not the entire table.

In the second case with joining vt0, the view reads all the data from t0. This ensures that the JOIN operation considers all rows in t0, not just the newly inserted block.

The key difference lies in how ClickHouse handles the source table in the Materialized View's query. When a Materialized View is triggered by an insert, the source table (t0 in this case) is replaced by the inserted block of data. This behavior can be leveraged to optimize queries but also requires careful consideration to avoid unexpected results.

Use Cases and Caveats

In practice, you may use this behavior to optimize Materialized Views that only need to process a subset of the source table's data. For example, you can use a subquery to filter the source table before joining it with other tables. This can help reduce the amount of data processed by the Materialized View and improve performance.

In this example, the set built from the IN (SELECT id FROM t0) subquery has only the newly inserted rows, which can help to filter t1 against it.

Example with Stack Overflow

Consider our earlier Materialized View example to compute daily badges per user, including the user's display name from the users table.

This view significantly impacted insert latency on the badges table e.g.

Using the approach above, we can optimize this view. We'll add a filter to the users table using the user ids in the inserted badge rows:

Not only does this speed up the initial badges insert:

But also means future badge inserts are efficient:

In the above operation, only one row is retrieved from the users table for the user id 2936484. This lookup is also optimized with a table ordering key of Id.

Other applications

The above focuses primarily on using Materialized Views to incrementally update partial aggregates of data, thus moving the computation from query to insert time. Beyond this common use case, Materialized Views have a number of other applications.

Filtering and transformation

In some situations, we may wish to only insert a subset of the rows and columns on insertion. In this case, our posts_null table could receive inserts, with a SELECT query filtering rows prior to insertion into the posts table. For example, suppose we wished to transform a Tags column in our posts table. This contains a pipe delimited list of tag names. By converting these into an array, we can more easily aggregate by individual tag values.

We could perform this transformation when running an INSERT INTO SELECT. The Materialized View allows us to encapsulate this logic in ClickHouse DDL and keep our INSERT simple, with the transformation applied to any new rows.

Our Materialized View for this transformation is shown below:

Lookup table

Users should consider their access patterns when choosing a ClickHouse ordering key. Columns which are frequently used in filter and aggregation clauses should be used. This can be restrictive for scenarios where users have more diverse access patterns which cannot be encapsulated in a single set of columns. For example, consider the following comments table:

The ordering key here optimizes the table for queries which filter by PostId.

Suppose a user wishes to filter on a specific UserId and compute their average Score:

While fast (the data is small for ClickHouse), we can tell this requires a full table scan from the number of rows processed - 90.38 million. For larger datasets, we can use a Materialized View to lookup our ordering key values PostId for filtering column UserId. These values can then be used to perform an efficient lookup.

In this example, our Materialized View can be very simple, selecting only the PostId and UserId from comments on insert. These results are in turn sent to a table comments_posts_users which is ordered by UserId. We create a null version of the Comments table below and use this to populate our view and comments_posts_users table:

We can now use this View in a subquery to accelerate our previous query:

Chaining

Materialized views can be chained, allowing complex workflows to be established. For a practical example, we recommend reading this blog post.