ClickStack - Performance Tuning

While the primary key also influences compression and storage layout, its primary purpose is query performance. In ClickStack, the out-of-the-box primary keys are already optimized for the most common observability access patterns and for strong compression. The default keys for logs, traces, and metrics tables are designed to perform well for typical workflows.

Filtering on columns that appear earlier in the primary key is more efficient than filtering on columns that appear later. While the default configuration is sufficient for most users, there are cases where modifying the primary key can improve performance for specific workloads.

A note on terminology

Throughout this document, the term "ordering key" is used interchangeably with "primary key." Strictly speaking, these differ in ClickHouse, but for ClickStack, they typically refer to the same columns specified in the table `ORDER BY` clause. For details, see the [ClickHouse documentation](/docs/engines/table-engines/mergetree-family/mergetree#choosing-a-primary-key-that-differs-from-the-sorting-key) on choosing a primary key that differs from the sorting key.

Before modifying any primary key, reading through our [guide to understand how primary indexes work](/docs/primary-indexes) in ClickHouse is strongly recommended:

Primary key tuning is table and data-type-specific. A change that benefits one table and data type may not apply to others. The goal is always to optimize for a particular data type e.g. logs.

**You will typically optimize the tables for logs and traces. It's rare that primary key changes need to be made to the other data types.**

Below are the default primary keys for the ClickStack tables for logs and metrics.

- Logs ([`otel_logs`](/docs/use-cases/observability/clickstack/ingesting-data/schemas#logs)) - `(ServiceName, TimestampTime, Timestamp)`
- Traces (['otel\_traces](/docs/use-cases/observability/clickstack/ingesting-data/schemas#traces)) - `(ServiceName, SpanName, toDateTime(Timestamp))`

See ["Tables and schemas used by ClickStack"](/docs/use-cases/observability/clickstack/ingesting-data/schemas) for the primary keys used by the tables for other data types. For example, trace tables are optimized for filtering by service name and span name, followed by timestamp and, trace ID. Log tables, conversely, are optimized for filtering by service name, then by date, and then by timestamp. Although the optimal order would be for the user to apply the filters in the order of the primary key, queries will still heavily benefit if filtering by any of these columns in any order, with ClickHouse [pruning data prior to reading](/docs/optimize/skipping-indexes).

When choosing a primary key, there are also other considerations for choosing an optimal ordering of the columns. See ["Choosing a primary key."](#choosing-a-primary-key)

**Primary keys should be changed in isolation per table. What makes sense for logs may not make sense for traces or metrics.**

### Choosing a primary key[​](#choosing-a-primary-key "Direct link to Choosing a primary key")

First, identify whether your access patterns differ substantially from the defaults for a specific table. For example, if you most commonly filter logs by Kubernetes node before service name, and this represents a dominant workflow, it may justify changing the primary key.

Modifying the default primary key

The default primary keys are sufficient in most cases. Changes should be made cautiously and only with a clear understanding of query patterns. Modifying a primary key can degrade performance for other workflows, so testing is essential.

Once you have extracted your desired columns, you can begin optimizing your ordering/primary key.

Some simple rules can be applied to help choose an ordering key. The following can sometimes be in conflict, so consider these in order. Aim to select a maximum of 4-5 keys from this process:

1. Select columns that align with your common filters and access patterns. If you typically start Observability investigations by filtering by a specific column e.g. pod name, this column will be used frequently in `WHERE` clauses. Prioritize including these in your key over those that are used less frequently.
2. Prefer columns that help exclude a large percentage of the total rows when filtered, thus reducing the amount of data that needs to be read. Service names and status codes are often good candidates - in the latter case, only if you filter by values which exclude most rows e.g. filtering by 200 codes will, in most systems, match most rows, in comparison to 500 errors, which will correspond to a small subset.
3. Prefer columns that are likely to be highly correlated with other columns in the table. This will help ensure these values are also stored contiguously, improving compression.
4. `GROUP BY` (aggregations for charts) and `ORDER BY` (sorting) operations for columns in the ordering key can be made more memory efficient.

On identifying the subset of columns for the ordering key, they must be declared in a specific order. This order can significantly influence both the efficiency of the filtering on secondary key columns in queries and the compression ratio for the table's data files. In general, it's best to order the keys in ascending order of cardinality. This should be balanced against the fact that filtering on columns that appear later in the ordering key will be less efficient than filtering on those that appear earlier in the tuple. Balance these behaviors and consider your access patterns. Most importantly, test variants. For further understanding of ordering keys and how to optimize them, reading ["Choosing a Primary Key."](/docs/best-practices/choosing-a-primary-key) is recommended for even deeper insights into primary key tuning and the internal data structures, see ["A practical introduction to primary indexes in ClickHouse."](/docs/guides/best-practices/sparse-primary-indexes)

### Changing the primary key[​](#changing-the-primary-key "Direct link to Changing the primary key")

If you're confident of your access patterns prior to data ingestion, simply drop and re-create the table for the relevant data type.

The example below shows a simple way to create a new logs table with the existing schema, but with a new primary key that includes the column `SeverityText` before the `ServiceName`.

Create new table

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

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

Ordering key vs primary key

Note in the above example, you're required to specify a `PRIMARY KEY` and `ORDER BY`.
In ClickStack, these are almost always the same.
The `ORDER BY` controls the physical data layout, while the `PRIMARY KEY` defines the sparse index.
In rare, very large workloads, these may differ, but most users should keep them aligned.

Exchange and drop table

#### Exchange and drop table[​](#exhange-and-drop-table "Direct link to Exchange and drop table")

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