ClickStack - Performance Tuning

Materializing frequently accessed attributes avoids this overhead by extracting the value at insert time and storing it as a first-class column.

Materialized columns:

- Are computed automatically during inserts
- Can't be explicitly set in INSERT statements
- Support any ClickHouse expression
- Allow type conversion from String to more efficient numeric or date types
- Enable skip indexes and primary key usage
- Reduce disk reads by avoiding full map access

Note

ClickStack automatically detects materialized columns extracted from maps and transparently uses them during query execution, even when users continue to query the original attribute path.

### Example[​](#materialize-column-example "Direct link to Example")

Consider the default ClickStack schema for traces, where Kubernetes metadata is stored in `ResourceAttributes`:

```
CREATE TABLE IF NOT EXISTS otel_traces
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `ParentSpanId` String CODEC(ZSTD(1)),
    `TraceState` String CODEC(ZSTD(1)),
    `SpanName` LowCardinality(String) CODEC(ZSTD(1)),
    `SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` String CODEC(ZSTD(1)),
    `SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `Duration` UInt64 CODEC(ZSTD(1)),
    `StatusCode` LowCardinality(String) CODEC(ZSTD(1)),
    `StatusMessage` String CODEC(ZSTD(1)),
    `Events.Timestamp` Array(DateTime64(9)) CODEC(ZSTD(1)),
    `Events.Name` Array(LowCardinality(String)) CODEC(ZSTD(1)),
    `Events.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    `Links.TraceId` Array(String) CODEC(ZSTD(1)),
    `Links.SpanId` Array(String) CODEC(ZSTD(1)),
    `Links.TraceState` Array(String) CODEC(ZSTD(1)),
    `Links.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    `__hdx_materialized_rum.sessionId` String MATERIALIZED ResourceAttributes['rum.sessionId'] CODEC(ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_rum_session_id __hdx_materialized_rum.sessionId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_res_attr_key mapKeys(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_key mapKeys(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_value mapValues(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_duration Duration TYPE minmax GRANULARITY 1,
    INDEX idx_lower_span_name lower(SpanName) TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 8
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toDateTime(Timestamp))
TTL toDate(Timestamp) + toIntervalDay(30)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;
```

A user may filter traces using Lucene syntax e.g. `ResourceAttributes.k8s.pod.name:"checkout-675775c4cc-f2p9c"`:

This results in a SQL predicate similar to:

```
ResourceAttributes['k8s.pod.name'] = 'checkout-675775c4cc-f2p9c'
```

Because this accesses a Map key, ClickHouse must read the full `ResourceAttributes` column for each matching row - potentially very large if the Map contains many keys.

If this attribute is queried frequently, it should be materialized as a top-level column.

To extract the pod name at insert time, add a materialized column:

```
ALTER TABLE otel_v2.otel_traces
ADD COLUMN PodName String
MATERIALIZED ResourceAttributes['k8s.pod.name']
```

From this point forward, **new** data will store the Pod name as a dedicated column, `PodName`.

Users can now query for pod names efficiently, using Lucene syntax e.g. `PodName:"checkout-675775c4cc-f2p9c"`

For newly inserted data, this avoids map access entirely and significantly reduces I/O.

However, even if users continue querying the original attribute path e.g. `ResourceAttributes.k8s.pod.name:"checkout-675775c4cc-f2p9c"`, **ClickStack will automatically rewrite the query** internally to use the materialized `PodName` column i.e. using the predicate:

```
PodName = 'checkout-675775c4cc-f2p9c'
```

This ensures users benefit from the optimization without changing dashboards, alerts, or saved queries.

Note

By default, materialized columns are excluded from `SELECT * queries`. This preserves the invariant that query results can always be reinserted into the table.

### Materializing historical data[​](#materializing-historical-data "Direct link to Materializing historical data")

Materialized columns only apply automatically to data inserted after the column is created. For existing data, queries against the materialized column will transparently fall back to reading from the original map.

If historical performance is critical, the column can be backfilled using a mutation e.g.

```
ALTER TABLE otel_v2.otel_traces
MATERIALIZE COLUMN PodName
```

This rewrites existing [parts](/docs/parts) to populate the column. Mutations are single-threaded per part and can take time on large datasets. To limit impact, mutations can be scoped to a specific partition:

```
ALTER TABLE otel_v2.otel_traces
MATERIALIZE COLUMN PodName
IN PARTITION '2026-01-02'
```

Mutation progress can be monitored using the `system.mutations` table e.g.

```
SELECT *
FROM system.mutations
WHERE database = 'otel'
  AND table = 'otel_traces'
ORDER BY create_time DESC;
```

Wait until `is_done = 1` for the corresponding mutation.

References

Mutations incur additional IO and CPU overhead and should be used sparingly. In many cases, it's sufficient to allow older data to age out naturally and rely on the performance improvements for newly ingested data.

## Optimization 2. Adding skip indices[​](#adding-skip-indices "Direct link to Optimization 2. Adding skip indices")

After materializing frequently queried attributes, the next optimization is to add data skipping indexes to further reduce the amount of data ClickHouse needs to read during query execution.

Skip indexes allow ClickHouse to avoid scanning entire blocks of data when it can determine that no matching values exist. Unlike traditional secondary indexes, skip indexes operate at the granule level and are most effective when query filters exclude large portions of the dataset. When used correctly, they can significantly accelerate filtering on high-cardinality attributes without changing query semantics.

Consider the default traces schema for ClickStack, which includes skip indices: