ClickStack - Materialized Views

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/clickstack/managing/materialized_views.md)

Beta feature. [Learn more.](/docs/beta-and-experimental-features#beta-features)

## Introduction[​](#introduction "Direct link to Introduction")

ClickStack can exploit [Incremental Materialized Views (IMV)](/docs/materialized-view/incremental-materialized-view) to accelerate visualizations that rely on aggregation-heavy queries, such as computing average request duration per minute over time. This feature can dramatically improve query performance and is typically most beneficial for larger deployments, around 10 TB per day and above, while enabling scaling into the petabytes-per-day range. Incremental materialized views are in Beta and should be used with care.

Note

Alerts can also benefit from materialized views, and will exploit them automatically.
This can reduce the computational overhead of running many alerts, especially since these typically run very frequently.
Reducing the execution time can be beneficial with respect to both responsiveness, and resource consumption.

## What are incremental materialized views[​](#what-are-incremental-materialized-views "Direct link to What are incremental materialized views")

Incremental materialized views allow you to shift the cost of computation from query time to insert time, resulting in significantly faster `SELECT` queries.

Unlike transactional databases such as Postgres, a ClickHouse materialized view isn't a stored snapshot. Instead, it acts as a trigger that runs a query on blocks of data as they're inserted into a source table. The output of this query is written into a separate target table. As additional data is inserted, new partial results are appended and merged into the target table. The merged result is equivalent to running the aggregation over the entire original dataset.

The primary motivation for using materialized views is that the data written to the target table represents the result of an aggregation, filtering, or transformation. In ClickStack, they're used exclusively for aggregations. These results are typically much smaller than the raw input data, often representing partial aggregation states. Combined with the simplicity of querying the pre-aggregated target table, this leads to substantially lower query latency compared to performing the same computation on raw data at query time.

Materialized views in ClickHouse are updated continuously as data flows into the source table, behaving more like always-up-to-date indexes. This differs from many other databases, where materialized views are static snapshots that must be periodically refreshed, similar to ClickHouse [Refreshable Materialized Views](/docs/materialized-view/refreshable-materialized-view).

Incremental materialized views compute only the changes to the view as new data arrives, pushing computation to insert time. Because ClickHouse is highly optimized for ingestion, the incremental cost of maintaining the view for each inserted block is small relative to the savings achieved during query execution. The cost of computing the aggregation is amortized across inserts rather than paid repeatedly on every read. Querying the pre-aggregated results is therefore far less expensive than recomputing them, resulting in lower operational cost and near real-time performance for downstream visualizations, even at petabyte scale.

This model differs fundamentally from systems that recompute entire views on each update or rely on scheduled refreshes. For a deeper explanation of how materialized views work and how to create them, refer to the linked guide above.

Each materialized view introduces additional insert-time overhead, so they should be used selectively.

Tip

Create views only for the most common dashboards and visualizations.
Limit usage to fewer than 20 views while the feature is in beta.
This threshold is expected to increase in future releases.

Note

A single materialized view can compute multiple metrics for different groupings, for example, minimum, maximum, and p95 duration per service name over one-minute buckets. This allows a single view to serve many visualizations rather than just one. Consolidating metrics into shared views is therefore important to maximize the value of each view and ensure it's reused across dashboards and workflows.

Before proceeding further, you're recommended to familiarize yourself with materialized views in ClickHouse in more depth.
See our guide on [Incremental materialized views](/docs/materialized-view/incremental-materialized-view) for additional details.

## Selecting visualizations for acceleration[​](#selecting-visualizatons-for-acceleration "Direct link to Selecting visualizations for acceleration")

Before creating any materialized views, it's important to understand which visualizations you want to accelerate and which workflows are most critical to your users.

In ClickStack, materialized views are designed to **accelerate aggregation-heavy visualizations**, meaning queries that compute one or more metrics over time. Examples include **average request duration per minute**, **request counts per service**, or **error rates over time**. A materialized view must always contain an aggregation and a time-based grouping, since it's intended to serve time series visualizations.

In general, the following is recommended:

### Identify high-impact visualizations[​](#identify-high-impact-visualizations "Direct link to Identify high-impact visualizations")

The best candidates for acceleration typically fall into one of the following categories:

- Dashboard visualizations that refresh frequently and are continuously displayed, such as high-level monitoring dashboards shown on wall displays.
- Diagnostic workflows used in runbooks, where specific charts are repeatedly consulted during incident response, and need to return results quickly.
- Core HyperDX experiences, including:
  - Histogram views on the search page.
  - Visualizations used in preset dashboards, such as APM, Services, or Kubernetes views.

These visualizations are often executed repeatedly across users and time ranges, making them ideal targets for shifting computation from query time to insert time.

### Balance benefit against insert-time cost[​](#balance-benefit-against-insert-time-cost "Direct link to Balance benefit against insert-time cost")

Materialized views introduce additional work at insert time, so they should be created selectively and deliberately. Not every visualization benefits from pre-aggregation, and accelerating rarely used charts is usually not worth the overhead. You should keep the total number of materialized views below a maximum of 20.

Note

Prior to moving to production, always validate the resource overhead introduced by materialized views, particularly CPU usage, disk I/O, and [merge activity](/docs/tips-and-tricks/too-many-parts). Each materialized view increases insert-time work and contributes additional parts, so it's important to ensure merges can keep up and part counts remain stable. This can be monitored via [system tables](/docs/operations/system-tables/tables) and the [built-in observability dashboard](/docs/operations/monitoring#built-in-advanced-observability-dashboard) in open-source ClickHouse or using the built-in metrics and [monitoring dashboards in ClickHouse Cloud](/docs/cloud/manage/monitor/advanced-dashboard). See [Too many parts](/docs/knowledgebase/exception-too-many-parts) for guidance on diagnosing and mitigating excessive part counts.

Once you have identified the visualizations that matter most, the next step is consolidation.

### Consolidate visualizations into shared views[​](#consolidate-visualizations-into-shared-views "Direct link to Consolidate visualizations into shared views")