ClickStack - Performance Tuning

For pre-aggregation and metric acceleration, ClickStack strongly prefers **explicit materialized views**, which give the application layer full control over view selection and usage.

For additional background, see:

- [Guide on projections](/docs/data-modeling/projections)
- [When to use projections](/docs/data-modeling/projections#when-to-use-projections)
- [Materialized views versus projections](/docs/managing-data/materialized-views-versus-projections)

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

Suppose your traces table is optimized for the default ClickStack access pattern:

```
ORDER BY (ServiceName, SpanName, toDateTime(Timestamp))
```

If you also have a dominant workflow that filters by TraceId (or frequently groups and filters around it), you can add a projection that stores rows sorted by TraceId and time:

```
ALTER TABLE otel_v2.otel_traces
ADD PROJECTION prj_traceid_time
(
    SELECT *
    ORDER BY (TraceId, toDateTime(Timestamp))
);
```

Use wildcards

In the example projection above, a wildcard (`SELECT *`) is used. While selecting a subset of columns can reduce write overhead, it also limits when the projection can be used, since only queries that can be fully satisfied by those columns are eligible. In ClickStack, this often restricts projection usage to very narrow cases. For this reason, it is generally recommended to use a wildcard to maximize applicability.

As with other data layout changes, the projection only affects newly written parts. To build it for existing data, materialize it:

```
ALTER TABLE otel_v2.otel_traces
MATERIALIZE PROJECTION prj_traceid_time;
```

Note

Materializing a projection can take a long time and consume significant resources. Because observability data typically expires via TTL, this should only be done when absolutely necessary. In most cases, it's sufficient to let the projection apply only to newly ingested data, allowing it to optimize the most frequently queried time ranges, such as the last 24 hours.

ClickHouse may choose the projection automatically when it estimates that the projection will scan fewer granules than the base layout. Projections are most reliable when they represent a straightforward reordering of the full row set (`SELECT *`) and the query filters strongly align with the projection’s `ORDER BY`.

Queries that filter on TraceId (especially equality) and include a time range would benefit from the above projection. For example:

```
-- Fetch a specific trace quickly
SELECT *
FROM otel_traces
WHERE TraceId = 'aeea7f401feb75fc5af8eb25ebc8e974'
  AND Timestamp >= now() - INTERVAL 1 DAY
ORDER BY Timestamp;

-- Trace-scoped aggregation
SELECT
  toStartOfMinute(Timestamp) AS t,
  count() AS spans
FROM otel_traces
WHERE TraceId = 'aeea7f401feb75fc5af8eb25ebc8e974'
  AND Timestamp >= now() - INTERVAL 1 DAY
GROUP BY t
ORDER BY t;
```

Queries that don't constrain `TraceId`, or that primarily filter on other dimensions that aren't leading in the projection’s ordering key, typically won't benefit (and may read via the base layout instead).

Note

Projections can also store aggregations (similar to materialized views). In ClickStack, projection-based aggregations aren't generally recommended because selection depends on the ClickHouse analyzer, and usage can be harder to control and reason about. Instead, prefer explicit materialized views that ClickStack can register and select intentionally at the application layer.

In practice, projections are best suited for workflows where you frequently pivot from a broader search to a trace-centric drill down (for example, fetching all spans for a specific TraceId).

### Costs and guidance[​](#projection-costs-and-guidance "Direct link to Costs and guidance")

- **Insert overhead**: A `SELECT *` projection with a different ordering key is effectively writing the data twice, which increases write I/O and can require additional CPU and disk throughput to sustain ingestion.
- **Use sparingly**: Projections are best reserved for genuinely diverse access patterns where a second physical ordering unlocks meaningful pruning for a large share of queries, for example, two teams querying the same dataset in fundamentally different ways.
- **Validate with benchmarks**: As with all tuning, compare real query latency and resource usage before and after adding and materializing a projection.

For a deeper background, see:

- [ClickHouse projections guide](/docs/data-modeling/projections#when-to-use-projections)
- [Materialized views vs projections](/docs/managing-data/materialized-views-versus-projections)

### Lightweight projections with `_part_offset`[​](#lightweight-projections "Direct link to lightweight-projections")

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

Lightweight projections are Beta for ClickStack

`_part_offset-based` lightweight projections aren't recommended for ClickStack workloads. While they reduce storage and write I/O, they can introduce more random access at query time, and their production behavior at the observability scale is still being evaluated. This recommendation may change as the feature matures and we gain more operational data.

Newer ClickHouse versions also support more lightweight projections that store only the projection sorting key plus a `_part_offset` pointer into the base table, rather than duplicating full rows. This can greatly reduce storage overhead, and recent improvements enable granule-level pruning, making them behave more like true secondary indexes. See:

- [Smarter storage with \_part\_offset](/docs/data-modeling/projections#smarter_storage_with_part_offset)
- [Blog explanation and examples](https://clickhouse.com/blog/projections-secondary-indices#example-combining-multiple-projection-indexes)

### Alternatives[​](#projection-alternatives "Direct link to Alternatives")

If you need multiple ordering keys, projections aren't the only option. Depending on operational constraints and how you want ClickStack to route queries, consider:

- Configuring your OpenTelemetry collector to write to two tables with different `ORDER BY` keys, and create separate ClickStack sources for each table.
- Create a materialized view as a copy pipeline i.e. attach a materialized view to the main table that selects raw rows into a secondary table with a different ordering key (a denormalization or routing pattern). Create a source for this target table. Examples can be found [here](/docs/materialized-view/incremental-materialized-view#filtering-and-transformation).

- [Introduction](#introduction)- [ClickHouse concepts](#clickhouse-concepts)- [Optimization 1. Materialize frequently queried attributes](#materialize-frequently-queried-attributes)
      - [Why materialize attributes](#why-materialize-attributes)- [Example](#materialize-column-example)- [Materializing historical data](#materializing-historical-data)- [Optimization 2. Adding skip indices](#adding-skip-indices)
        - [Bloom filters](#bloom-filters)- [Min-max indices](#min-max-indices)- [Materialize skip index](#materialize-skip-index)- [Evaluating skip index effectiveness](#evaluating-skip-index-effectiveness)- [When to add skip indexes](#when-to-add-skip-indexes)- [Optimization 3. Modifying the primary key](#modifying-the-primary-key)
          - [Choosing a primary key](#choosing-a-primary-key)- [Changing the primary key](#changing-the-primary-key)- [Optimization 4. Exploiting Materialized Views](#exploting-materialied-views)- [Optimization 5. Exploiting Projections](#exploting-projections)
              - [Example projections](#example-projections)- [Costs and guidance](#projection-costs-and-guidance)- [Lightweight projections with `_part_offset`](#lightweight-projections)- [Alternatives](#projection-alternatives)

Was this page helpful?