ClickStack - Materialized Views

Note

This query may process large volumes of data and can be resource-intensive. Always validate available CPU, memory, and I/O capacity before running a backfill. A useful technique is to first execute the query with `FORMAT Null` to estimate runtime and resource usage.

If the query itself is expected to run for many hours, this approach **isn't recommended**.

Note how the following query adds a `WHERE` clause to limit the aggregation to data older than the earliest timestamp present in the view:

```
INSERT INTO otel_traces_1m
SELECT
    toStartOfMinute(Timestamp) AS Timestamp,
    ServiceName,
    StatusCode,
    count() AS count,
    avgState(Duration) AS avg__Duration,
    maxSimpleState(Duration) AS max__Duration,
    quantilesState(0.95, 0.99)(Duration) AS quantiles__Duration
FROM otel_traces
WHERE Timestamp < (
    SELECT min(Timestamp) FROM otel_traces_1m
)
GROUP BY
    Timestamp,
    ServiceName,
    StatusCode;
```

#### Incremental backfilling using a Null table[​](#incremental-backfill-null-table "Direct link to Incremental backfilling using a Null table")

For larger datasets or more resource-intensive aggregation queries, a direct backfill using a single `INSERT INTO SELECT` can be impractical or unsafe. In these cases, an **incremental backfill** approach is recommended. This method more closely mirrors how incremental materialized views normally operate, processing data in manageable blocks rather than aggregating the entire historical dataset at once.

This approach is appropriate when:

- The backfill query would otherwise run for many hours.
- Peak memory usage of a full aggregation is too high.
- You want to tightly control CPU and memory consumption during backfill.
- You need a more resilient process that can be restarted safely if interrupted.

The key idea is to use a [**Null table**](/docs/engines/table-engines/special/null) as an ingestion buffer. While the Null table doesn't store data, any materialized views attached to it will still execute, allowing aggregation states to be computed incrementally as data flows through.

Create a Null table for backfilling

##### Create a Null table for backfilling[​](#create-null-table "Direct link to Create a Null table for backfilling")

Create a lightweight Null table that contains only the columns required by the materialized view's aggregation. This minimizes I/O and memory usage.

```
CREATE TABLE otel_traces_backfill
(
    Timestamp DateTime64(9),
    ServiceName LowCardinality(String),
    StatusCode LowCardinality(String),
    Duration UInt64
)
ENGINE = Null;
```

Attach a materialized view to the Null table

##### Attach a materialized view to the Null table[​](#attach-mv-to-null-table "Direct link to Attach a materialized view to the Null table")

Next, create a materialized view on the Null table that targets the same aggregation table used by your primary materialized view.

```
CREATE MATERIALIZED VIEW otel_traces_1m_mv_backfill
TO otel_traces_1m
AS
SELECT
    toStartOfMinute(Timestamp) AS Timestamp,
    ServiceName,
    StatusCode,
    count() AS count,
    avgState(Duration) AS avg__Duration,
    maxSimpleState(Duration) AS max__Duration,
    quantilesState(0.95, 0.99)(Duration) AS quantiles__Duration
FROM otel_traces_backfill
GROUP BY
    Timestamp,
    ServiceName,
    StatusCode;
```

This materialized view will execute incrementally as rows are inserted into the Null table, producing aggregation states in small blocks.

Backfill data incrementally

##### Backfill data incrementally[​](#incremental-backfill "Direct link to Backfill data incrementally")

Finally, insert historical data into the Null table. The materialized view will process the data block by block, emitting aggregation states into the target table without persisting the raw rows.

```
INSERT INTO otel_traces_backfill
SELECT
    Timestamp,
    ServiceName,
    StatusCode,
    Duration
FROM otel_traces
WHERE Timestamp < (
    SELECT min(Timestamp) FROM otel_traces_1m
);
```

Because the data is processed incrementally, memory usage remains bounded and predictable, closely resembling normal ingestion behavior.

Note

For additional safety, consider directing the backfill materialized view to a temporary target table (for example, `otel_traces_1m_v2`). Once the backfill completes successfully, [partitions can be moved](/docs/sql-reference/statements/alter/partition#move-partition-to-table) to the primary target table e.g. `ALTER TABLE otel_traces_1m_v2 MOVE PARTITION '2026-01-02' TO otel_traces_1m`. This allows for easy recovery if the backfill is interrupted or fails due to resource limits.

For further details on tuning this process, including improving insert performance and reducing and controlling resources, see ["Backfilling."](/docs/data-modeling/backfilling#tuning-performance--resources)

## Recommendations[​](#recommendations "Direct link to Recommendations")

The following recommendations summarize best practices for designing and operating materialized views in ClickStack. Following these guidelines will help ensure materialized views are effective, predictable, and cost-efficient.

### Granularity selection and alignment[​](#granularity-selection-and-alignment "Direct link to Granularity selection and alignment")

Materialized views are only used when the visualization or alert granularity is an **exact multiple** of the view's granularity. How this granularity is determined depends on the chart type:

- **Time charts** (line or bar charts with time on the x-axis):
  The chart's explicit granularity must be a multiple of the materialized view granularity.
  For example, a 10-minute chart can use materialized views with 10, 5, 2, or 1-minute granularity, but not 20-minute, or 3-minute views.
- **Non-time charts** (number, table, or summary charts):
  The effective granularity is derived as `(time range / 80)`, rounded up to the nearest HyperDX-supported granularity. This derived granularity must also be a multiple of the materialized view granularity.

Because of these rules:

- **Don't create materialized views with a 10-minute granularity**.
  ClickStack supports 15-minute granularity for charts and alerts, but not 10-minute. A 10-minute materialized view would therefore be incompatible with common 15-minute visualizations and alerts.
- Prefer **1-minute** or **1-hour** granularities, which compose cleanly with most chart and alert configurations.

Higher granularity (for example, 1 hour) produces smaller views and lower storage overhead, while lower granularity (for example, 1 minute) provides more flexibility for fine-grained analysis. Choose the smallest granularity that supports your critical workflows.

### Limit and consolidate materialized views[​](#limit-and-consolidate-materialized-views "Direct link to Limit and consolidate materialized views")

Each materialized view introduces additional insert-time overhead and contributes to part and merge pressure.
The following guidelines are recommended:

- **No more than 20 materialized views** per source.
- **Around 10 materialized views** is typically optimal.
- Consolidate multiple visualizations into a single view when they share common dimensions.

Where possible, compute multiple metrics and support multiple charts from the same materialized view.

### Choose dimensions carefully[​](#choose-dimensions-carefully "Direct link to Choose dimensions carefully")

Include only dimensions that are commonly used for grouping or filtering:

- Every additional grouping column increases the size of the view.
- Balance query flexibility against storage and insert-time cost.
- Filters on columns not present in the view will cause ClickStack to fall back to the source table.

Tip

A common and almost always useful baseline is a materialized view grouped by **service name with a count metric**, which enables fast histograms and service-level overviews in search and dashboards.

### Naming conventions for aggregation columns[​](#naming-conventions-for-aggregation-columns "Direct link to Naming conventions for aggregation columns")