ClickStack - Performance Tuning

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

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

This guide focuses on the most common and effective performance optimizations for ClickStack, sufficient to optimize the majority of real-world observability workloads, typically up to tens of terabytes of data per day.

The optimizations are presented in a deliberate order, starting with the simplest and highest impact techniques and progressing toward more advanced and specialized tuning. Early optimizations should be applied first and will often deliver substantial gains on their own. As data volumes grow and workloads become more demanding, the latter techniques become increasingly worthwhile to explore.

## ClickHouse concepts[​](#clickhouse-concepts "Direct link to ClickHouse concepts")

Before applying any of the optimizations described in this guide, it's important to be familiar with a few core ClickHouse concepts.

In ClickStack, each **data source maps directly to one or more ClickHouse tables**. When using OpenTelemetry, ClickStack creates and manages a set of default tables that store logs, traces, and metrics data. If you're using custom schemas or managing your own tables, you may already be familiar with these concepts. However, if you're simply sending data via the OpenTelemetry Collector, these tables are created automatically, and are where all optimizations described below will be applied.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Data type Table|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Logs [otel\_logs](/docs/use-cases/observability/clickstack/ingesting-data/schemas#logs)| Traces [otel\_traces](/docs/use-cases/observability/clickstack/ingesting-data/schemas#traces)| Metrics (gauges) [otel\_metrics\_gauge](/docs/use-cases/observability/clickstack/ingesting-data/schemas#gauge)| Metrics (sums) [otel\_metrics\_sum](/docs/use-cases/observability/clickstack/ingesting-data/schemas#sum)| Metrics (histogram) [otel\_metrics\_histogram](/docs/use-cases/observability/clickstack/ingesting-data/schemas#histogram)| Metrics (Exponential histograms) [otel\_metrics\_exponentialhistogram](/docs/use-cases/observability/clickstack/ingesting-data/schemas#exponential-histograms)| Metrics (summary) [otel\_metrics\_summary](/docs/use-cases/observability/clickstack/ingesting-data/schemas#summary-table)| Sessions [hyperdx\_sessions](/docs/use-cases/observability/clickstack/ingesting-data/schemas#sessions) | | | | | | | | | | | | | | | | | |

Tables are assigned to [databases](/docs/sql-reference/statements/create/database) in ClickHouse. By default, the `default` database is used - this can be [modified in the OpenTelemetry collector](/docs/use-cases/observability/clickstack/config#otel-collector).

Focus on logs and traces

In most cases, performance tuning focuses on the logs and trace tables. While metrics tables can be optimized for filtering, their schemas are intentionally opinionated for Prometheus-style workloads and typically don't require modification for standard charting. Logs and traces, by contrast, support a wider range of access patterns, and therefore benefit most from tuning. Session data has a fixed user experience, and its schema rarely needs to be modified.

At a minimum, you should understand the following ClickHouse fundamentals:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Concept Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Tables** How data sources in ClickStack correspond to underlying ClickHouse tables. Tables in ClickHouse mainly use the [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) engine.| **Parts** How data is written in immutable parts and merged over time.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | **Partitions** Partitions group the data parts of a table into organised logical units. These units are easier to manage, query, and optimize.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **Merges** The internal process that merges parts together to ensure that there are a lower number of parts to query. Essential for maintaining query performance.|  |  |  |  | | --- | --- | --- | --- | | **Granules** The smallest unit of data that ClickHouse reads and prunes during query execution.|  |  | | --- | --- | | **Primary (ordering) keys** How the `ORDER BY` key determines on-disk data layout, compression, and query pruning. | | | | | | | | | | | | | |

These concepts are central to ClickHouse performance. They determine how data is written, how it's structured on disk, and how efficiently ClickHouse can skip reading data at query time. Every optimization in this guide, whether materialized columns, skip indexes, primary keys, projections, or materialized views, builds on these core mechanisms.

You're recommended to review the following ClickHouse documentation before undertaking any tuning:

- [Creating tables in ClickHouse](/docs/guides/creating-tables) - A simple introduction to tables.
- [Parts](/docs/parts)
- [Partitions](/docs/partitions)
- [Merges](/docs/merges)
- [Primary keys/indexes](/docs/primary-indexes)
- [How ClickHouse stores data: parts and granules](/docs/guides/best-practices/sparse-primary-indexes) - More advanced guide on how data is structured and queried in ClickHouse, covering granules and primary keys in detail.
- [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree)- Advanced MergeTree reference guide useful for commands and for internal specifics.

All optimizations described below can be applied directly to the underlying tables using standard ClickHouse SQL, either through the [ClickHouse Cloud SQL console](/docs/integrations/sql-clients/sql-console) or via the [ClickHouse client](/docs/interfaces/cli).

## Optimization 1. Materialize frequently queried attributes[​](#materialize-frequently-queried-attributes "Direct link to Optimization 1. Materialize frequently queried attributes")

The first and simplest optimization for ClickStack users is to identify commonly queried attributes in `LogAttributes`, `ScopeAttributes`, and `ResourceAttributes`, and promote them to top-level columns using materialized columns.

This optimization alone is often sufficient to scale ClickStack deployments to tens of terabytes per day and should be applied before considering more advanced tuning techniques.

### Why materialize attributes[​](#why-materialize-attributes "Direct link to Why materialize attributes")

ClickStack stores metadata such as Kubernetes labels, service metadata, and custom attributes in `Map(String, String)` columns. While this provides flexibility, querying map subkeys has an important performance implication.

When querying a single key from a Map column, ClickHouse must read the entire map column from disk. If the map contains many keys, this results in unnecessary IO and slower queries compared to reading a dedicated column.

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")