ClickStack - Performance Tuning

The `EXCHANGE` statement is used to swap the names of the tables [atomically](/docs/concepts/glossary#atomicity). The temporary table (now the old default table), can be dropped.

```
EXCHANGE TABLES otel_logs_temp AND otel_logs
DROP TABLE otel_logs_temp
```

However, **the primary key can't be modified on an existing table**. Changing it requires creating a new table.

The following process can be used to ensure the old data can be retained and still queried transparently (using its existing key in HyperDX, if required, while new data is exposed through a new table optimized for the users' access patterns. This approach ensures ingest pipelines don't need to be modified, with data still sent to the default table names, and all changes are transparent to users.

Note

Backfilling existing data into a new table is rarely worthwhile at scale. The compute and IO cost is usually high, and doesn't justify the performance benefits. Instead, allow older data to expire [via TTL](/docs/use-cases/observability/clickstack/ttl) while newer data benefits from the improved key.

Create new table

#### Create new table[​](#create-new-table-with-key-2 "Direct link to Create new table")

Create the new table with the desired primary key. Note the `_23_01_2025` suffix - adapt this to be the current date. e.g.

```
CREATE TABLE otel_logs_23_01_2025 AS otel_logs
PRIMARY KEY (SeverityText, ServiceName, TimestampTime)
ORDER BY (SeverityText, ServiceName, TimestampTime)
```

Create a Merge table

#### Create a Merge table[​](#create-merge-table "Direct link to Create a Merge table")

The [Merge engine](/docs/engines/table-engines/special/merge) (not to be confused with MergeTree) doesn't store data itself, but allows reading from any number of other tables simultaneously.

```
CREATE TABLE otel_logs_merge
AS otel_logs
ENGINE = Merge(currentDatabase(), 'otel_logs*')
```

Note

`currentDatabase()` assumes the command is run in the correct database. Otherwise, specify the database name explicitly.

You can now query this table to confirm it returns data from `otel_logs`.

Update HyperDX to read from the merge table

#### Update HyperDX to read from the merge table[​](#update-hyperdx-to-read-from-merge-tree "Direct link to Update HyperDX to read from the merge table")

Configure HyperDX to use `otel_logs_merge` as the table for the logs data source.

At this point, writes continue to `otel_logs` with the original primary key, while reads use the merge table. There is no visible change for users or impact on ingestion.

Exchange the tables

#### Exchange the tables[​](#exchange-the-tables "Direct link to Exchange the tables")

An `EXCHANGE` statement is now used to swap the names of the `otel_logs` and `otel_logs_23_01_2025` tables atomically.

```
EXCHANGE TABLES otel_logs AND otel_logs_23_01_2025
```

Writes now go to the new `otel_logs` table with the updated primary key. Existing data remains in `otel_logs_23_01_2025` and is still accessible via the merge table. The suffix indicates the date the change was applied and represents the latest timestamp contained in that table.

This process allows primary key changes with no ingest interruption and no user-visible impact.

This process can be adapted should further changes be required to primary keys. For example, if you decide that actually the `SeverityNumber` should be part of the primary key one week later, rather than the `SeverityText`. The following process can be adapted as many times as primary key changes are required.

Create new table

#### Create new table[​](#create-new-table-with-key-3 "Direct link to Create new table")

Create the new table with the desired primary key.
In the example below `30_01_2025` is used as our suffix to denote the date of the table. e.g.

```
CREATE TABLE otel_logs_30_01_2025 AS otel_logs
PRIMARY KEY (SeverityNumber, ServiceName, TimestampTime)
ORDER BY (SeverityNumber, ServiceName, TimestampTime)
```

Exchange the tables

#### Exchange the tables[​](#exchange-the-tables-v2 "Direct link to Exchange the tables")

An `EXCHANGE` statement is now used to swap the names of the `otel_logs` and `otel_logs_30_01_2025` tables atomically.

```
EXCHANGE TABLES otel_logs AND otel_logs_30_01_2025
```

Writes now go to the new `otel_logs` table with the updated primary key. The old data remains in `otel_logs_30_01_2025`, accessible via the merge table.

Redundant tables

If TTL policies are in place, which is recommended, tables with older primary keys that are no longer receiving writes will gradually empty as data expires. They should be monitored and periodically cleaned up once they contain no data. At present, this cleanup process is manual.

## Optimization 4. Exploiting Materialized Views[​](#exploting-materialied-views "Direct link to Optimization 4. Exploiting Materialized Views")

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

ClickStack can exploit [Incremental Materialized Views](/docs/materialized-view/incremental-materialized-view) to accelerate visualizations that rely on aggregation-heavy queries, such as computing average request duration per minute over time. This feature can dramatically improve query performance and is typically most beneficial for larger deployments, around 10 TB per day and above, while enabling scaling into the petabytes-per-day range. Incremental Materialized Views are in Beta and should be used with care.

For details on using this feature in ClickStack, see our dedicated guide ["ClickStack - Materialized Views."](/docs/use-cases/observability/clickstack/materialized_views)

## Optimization 5. Exploiting Projections[​](#exploting-projections "Direct link to Optimization 5. Exploiting Projections")

Projections represent a final, advanced optimization that can be considered once materialized columns, skip indexes, primary keys, and materialized views have been evaluated. While projections and materialized views may appear similar, in ClickStack, they serve different purposes, and are best used in different scenarios.

Practically, a **projection can be thought of as an additional, hidden copy of the table** that stores the same rows in a **different physical order**. This gives the projection its own primary index, distinct from the base table's `ORDER BY` key, allowing ClickHouse to prune data more effectively for access patterns that don't align with the original ordering.

Materialized views can achieve a similar effect by explicitly writing rows into a separate target table with a different ordering key. The key difference is that **projections are maintained automatically and transparently** by ClickHouse, while materialized views are explicit tables that must be registered and selected intentionally by ClickStack.

When a query targets the base table, ClickHouse evaluates the base layout and any available projections, samples their primary indexes, and selects the layout that can produce the correct result while reading the fewest granules. This decision is made automatically by the query analyzer.

In ClickStack, projections are therefore best suited for **pure data reordering**, where:

- Access patterns are fundamentally different from the default primary key
- It's impractical to cover all workflows with a single ordering key
- You want ClickHouse to transparently choose the optimal physical layout

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