ClickStack - Materialized Views

- `otel_traces_1m`, grouped by minute, `ServiceName`, and `StatusCode`
- `otel_traces_1m_v2`, grouped by minute, `ServiceName`, `StatusCode`, and `SpanName`

The second view contains additional grouping keys and therefore produces more rows and scans more data.

If a visualization requests **average duration per service over time**, both views are technically valid. ClickStack issues an [`EXPLAIN ESTIMATE`](/docs/sql-reference/statements/explain#explain-estimate) query for each candidate and compares the estimated granule counts i.e.:

```
EXPLAIN ESTIMATE
SELECT
    toStartOfHour(Timestamp) AS hour,
    ServiceName,
    avgMerge(avg__Duration) AS avg__Duration
FROM otel_v2.otel_traces_1m
GROUP BY
    hour,
    ServiceName
ORDER BY hour DESC

┌─database─┬─table──────────┬─parts─┬──rows─┬─marks─┐
│ otel_v2  │ otel_traces_1m │     1 │ 49385 │     6 │
└──────────┴────────────────┴───────┴───────┴───────┘

1 row in set. Elapsed: 0.009 sec.

EXPLAIN ESTIMATE
SELECT
    toStartOfHour(Timestamp) AS hour,
    ServiceName,
    avgMerge(avg__Duration) AS avg__Duration
FROM otel_v2.otel_traces_1m_v2
GROUP BY
    hour,
    ServiceName
ORDER BY hour DESC

┌─database─┬─table─────────────┬─parts─┬───rows─┬─marks─┐
│ otel_v2  │ otel_traces_1m_v2 │     1 │ 212519 │    26 │
└──────────┴───────────────────┴───────┴────────┴───────┘

1 row in set. Elapsed: 0.004 sec.
```

Because `otel_traces_1m` is smaller and scans fewer granules, it's selected automatically.

Both materialized views still outperform querying the base table directly, but selecting the smallest sufficient view yields the best performance.

### Alerts[​](#alerts "Direct link to Alerts")

Alert queries automatically use materialized views when compatible. The same optimization logic applies, providing faster alert evaluation.

## Backfilling a materialized view[​](#backfilling-a-materialized-view "Direct link to Backfilling a materialized view")

As noted earlier, incremental materialized views only contain data inserted **after the view is created** and aren't automatically backfilled. This design keeps views lightweight and inexpensive to maintain, but it also means they can't be used for queries that require data earlier than the view's minimum timestamp.

In most cases, this is acceptable. Common ClickStack workloads focus on recent data, such as the last 24 hours, meaning a newly created view becomes fully usable within a day of creation. However, for queries spanning longer time ranges, the view may remain unusable until enough time has passed.

In these cases, users may consider **backfilling** the materialized view with historical data.

Backfilling can be **computationally expensive**. Under normal operation, materialized views are populated incrementally as data arrives, spreading the compute cost evenly over time.

Backfilling compresses this work into a much shorter period, **significantly increasing CPU and memory usage per unit time.**

Depending on the dataset size and retention window, this may require temporarily scaling the cluster, either vertically, or horizontally in ClickHouse Cloud, to complete the backfill in a reasonable time frame.

If additional resources aren't provisioned, backfilling can negatively impact production workloads, including query latency, and ingestion throughput. For **very large datasets or long historical ranges, backfilling may be impractical**, or infeasible altogether.

In summary, backfilling is often not worth the cost and operational risk. It should be considered only in exceptional cases where historical acceleration is critical. If you choose to proceed, it is recommended to follow the controlled approach outlined below to balance performance, cost, and production impact.

### Backfilling approaches[​](#backfilling-approaches "Direct link to Backfilling approaches")

Avoid POPULATE

Using the [POPULATE](/docs/sql-reference/statements/create/view#materialized-view) command isn't recommended for backfilling materialized views for anything other than small datasets where ingest is paused. This operator can miss rows inserted into its source table, with the materialized view created after the populate hash is finished. Furthermore, this populate runs against all data and is vulnerable to interruptions or memory limits on large datasets.

Suppose you want to backfill a materialized view corresponding to the following aggregation, which computes per-minute metrics grouped by service name and status code:

```
SELECT
    toStartOfMinute(Timestamp),
    ServiceName,
    StatusCode,
    count() AS count,
    avg(Duration),
    max(Duration),
    quantiles(0.95, 0.99)(Duration)
FROM otel_traces
GROUP BY
    time,
    ServiceName,
    StatusCode
```

As discussed earlier, incremental materialized views aren't backfilled automatically. The following processes are recommended to safely backfill historical data while preserving incremental behavior for new data.

#### Direct backfill using `INSERT INTO SELECT`[​](#direct-backfill "Direct link to direct-backfill")

This approach is best suited for **smaller datasets** or **relatively lightweight aggregation queries** where the full backfill can be completed in a reasonable amount of time without exhausting cluster resources. It's typically appropriate when the backfill query can run in minutes, or at most a few hours, and when temporary increases in CPU, and I/O usage are acceptable. For larger datasets or more expensive aggregations, consider the incremental, or block-based backfilling approaches below instead.

Determine the current coverage of the view

##### Determine the current coverage of the view[​](#determine-current-coverage-of-view "Direct link to Determine the current coverage of the view")

Before attempting any backfill, first establish what data the materialized view already contains. This is done by querying the minimum timestamp present in the target table:

```
SELECT min(Timestamp)
FROM otel_traces_1m;
```

This timestamp represents the earliest point from which the view can satisfy queries. Any query from ClickStack requesting data earlier than this timestamp will fall back to the base table.

Decide whether backfilling is necessary

##### Decide whether backfilling is necessary[​](#decide-whether-backfilling-is-neccessary "Direct link to Decide whether backfilling is necessary")

In most ClickStack deployments, queries focus on recent data, such as the last 24 hours. In these cases, newly created views become fully usable shortly after creation, and backfilling is unnecessary.

If the timestamp returned in the previous step is sufficiently old for your use cases, no backfill is required. Backfilling should only be considered when:

- Queries frequently span long historical ranges.
- The view is critical for performance across those ranges.
- The dataset size and aggregation cost make backfilling feasible.

Backfill missing historical data

##### Backfill missing historical data[​](#backfill-missing-historical-data "Direct link to Backfill missing historical data")

If backfilling is required, populate the materialized view's target table for timestamps earlier than the current minimum using the query from the view modified to only read data older than the timestamp recorded above. Because the target table uses AggregatingMergeTree, the backfill query **must insert aggregation states, not final values**.

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: