At ClickHouse, we are constantly thinking about our getting started experience. ClickHouse is a complex and powerful piece of software that introduces new concepts for many, which can lead to mistakes. In this post, we highlight the most common 13 mistakes we see our new users encounter, why they occur and the correct approach.

For users encountering challenges managing ClickHouse at scale, [ClickHouse Cloud](https://clickhouse.cloud/signUp) automatically handles many of the common getting-started and subsequent scaling challenges.

## 1. Too many parts

An often-seen ClickHouse error:

```
DB::Exception: Too many parts
```

This error will often be experienced when inserting data and will be present in ClickHouse logs or in a response to an INSERT request.  To understand this error, users need to understand the concept of a part in ClickHouse.

A table in ClickHouse consists of data parts sorted by the user's specified primary key (by default, the ORDER BY clause on table creation but see [Index Design](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design) for the details). When data is inserted in a table, separate [data parts](https://clickhouse.com/docs/parts) are created, and each of them is lexicographically sorted by primary key. For example, if the primary key is `(CounterID, Date)`, the data in the part is sorted first by `CounterID`, and within each `CounterID` value by `Date`. In the background, ClickHouse merges data parts for more efficient storage, similar to a [Log-structured merge tree](https://en.wikipedia.org/wiki/Log-structured_merge-tree). Each part has its own primary index to allow efficient scanning and identification of where values lie within the parts. When parts are merged, then the merged part's primary indexes are also merged.

![sins-01-parts.png](https://clickhouse.com/uploads/sins_01_parts_db7108e636.png)

As the number of parts increases, queries invariably will slow as a result of the need to evaluate more indices and read more files. Users may also experience slow startup times in cases where the part count is high. The creation of too many parts thus results in more internal merges and "pressure" to keep the number of parts low and query performance high. While merges are concurrent, in cases of misuse or misconfiguration, the number of parts can exceed internal configurable limits ([parts_to_throw_insert](https://clickhouse.com/docs/en/operations/settings/merge-tree-settings/#parts_to_throw_insert), [max_parts_in_total](https://clickhouse.com/docs/en/operations/settings/merge-tree-settings/#max_parts_in_total)). While these limits can be adjusted, at the expense of query performance, the need to do so will more often point to issues with your usage patterns. As well as causing query performance to degrade, high part counts can also place greater pressure on ClickHouse Keeper in replicated configurations.

So, how is it possible to have too many of these parts?

### Poorly chosen partitioning key

A common reason is using a partition key with excessive [cardinality](https://en.wikipedia.org/wiki/Cardinality). On creating a table, users can optionally specify a column as a partition key by which data will be separated. A new file system directory will be created for every key value. This is typically a data management technique, allowing users to cleanly separate data logically in a table. Operations such as DROP PARTITION subsequently allow fast deletion of data subsets. This powerful feature can, however, easily be misused, with users interpreting it as a simple optimization technique for queries. 

Importantly, parts belonging to different partitions are never merged. If a key of high cardinality, e.g., `date_time_ms`, is chosen as a partition key then parts spread across thousands of folders will never be merge candidates - exceeding preconfigured limits and causing the "Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts" error on subsequent INSERTs. Addressing this problem is simple: choose a [partition key](https://clickhouse.com/docs/optimize/partitioning-key) with cardinality < 1000.

![sins-02-partitioning.png](https://clickhouse.com/uploads/sins_02_partitioning_14b984c2bf.png)

### Many small inserts

As well as the poor selection of a partition key, this issue can manifest itself as a result of many small inserts. Each INSERT into ClickHouse results in an insert block being converted to a part. To keep the number of parts manageable, users should therefore buffer data client-side and insert data as [batches](https://clickhouse.com/docs/optimize/bulk-inserts) - at a minimum 1,000 rows per insert, although batch sizes of 10,000 to 100,000 rows are optimal. If client-side buffering is not possible, users can defer this task to ClickHouse through [async inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts). In this case, ClickHouse will buffer inserts in memory before flushing them as a single batched part into the underlying table. The flush is triggered when a configurable threshold is met: a buffer size limit (`async_insert_max_data_size`, default 1MB), a time threshold (`async_insert_busy_timeout_ms`, default 1 second), or a maximum number of queued queries (`async_insert_max_query_number`, default 100). Since data is held in memory until flush, it is important to set `wait_for_async_insert=1` (the default) so that the client receives acknowledgement only after data has been safely written to disk, avoiding silent data loss in the event of a server crash before a flush.

![sins-03-async_inserts.png](https://clickhouse.com/uploads/sins_03_async_inserts_f51b9d0035.png)

[Buffer tables](https://clickhouse.com/docs/en/engines/table-engines/special/buffer/) exist as a legacy alternative. Their unique advantage is that data is queryable while still in the buffer, before flush to the destination table. However, Buffer tables have significant drawbacks: they are not replicated, are not compatible with FINAL or SAMPLE, and can lose data on abnormal server restarts. In ClickHouse Cloud with SharedMergeTree, each node maintains independent buffer state, adding further complexity. For nearly all use cases, async inserts are the recommended approach. Buffer tables should only be considered in rare scenarios where querying in-buffer data before flush is a hard requirement.

### Excessive materialized views

Other possible causes of this error are excessive materialized views. Materialized views are, in effect, a trigger that runs when a block is inserted into a table. They transform the data e.g., through a GROUP BY, before inserting the result into a different table. This technique is often used to accelerate certain queries by precomputing aggregations at INSERT time. Users can create these materialized views, potentially resulting in many parts. Generally, we recommended that users create views while being aware of the costs and consolidate them where possible.

![sins-04-mvs.png](https://clickhouse.com/uploads/sins_04_mvs_1f872af2fa.png)

The above list is not an exhaustive cause of this error. For example, mutations (as discussed below) can also cause merge pressure and an accumulation of parts. Finally, we should note that this error, while the most common, is only one manifestation of the above misconfigurations. For example, users can experience other issues as a result of a poor partitioning key. These include, but are not limited to, "no free inodes on the filesystem", backups taking a long time, and delays on replication (and high load on ClickHouse Keeper).

## 2. Going horizontal too early

We often have new self-managed users asking us to provide recommendations around orchestration and how to scale to dozens, if not hundreds, of nodes. While technologies such as Kubernetes have made the deployment of multiple instances of stateless applications relatively simple, this pattern should, in nearly all cases, not be required for ClickHouse. Unlike other databases, which may be restricted to a machine size due to inherent limits, e.g., JVM heap size, ClickHouse was designed from the ground up to utilize the full resources of a machine. We commonly find successful deployments with ClickHouse deployed on servers with hundreds of cores, terabytes of RAM, and petabytes of disk space. Most analytical queries have a sort, filter, and aggregation stage. Each of these can be parallelized independently and will, by default, use as many [threads as cores](https://clickhouse.com/docs/operations/settings/settings#max_threads), thus utilizing the full machine resources for a query.

![sins-05-vertical_scale.png](https://clickhouse.com/uploads/sins_05_vertical_scale_d36926028c.png)

Scaling vertically first has a number of benefits, principally cost efficiency, lower cost of ownership (with respect to operations), and better query performance due to the minimization of data on the network for operations such as JOINs. Of course, users need redundancy in their infrastructure, but two machines should be sufficient for all but the largest use cases.

ClickHouse Cloud supports both vertical scaling (increasing replica size) and horizontal scaling (adding more replicas), with its compute-storage separation architecture ([SharedMergeTree](https://clickhouse.com/docs/cloud/reference/shared-merge-tree)) making both approaches seamless. For details on scaling options, see the [Cloud scaling documentation](https://clickhouse.com/docs/manage/scaling). We strongly recommend considering vertical scaling first, before horizontal. In summary, go vertical before going horizontal!

For users encountering challenges managing ClickHouse at scale, ClickHouse Cloud automatically handles many of the common getting-started and subsequent scaling challenges.

Get started today

## 3. Mutation Pain

While rare in OLAP use cases, the need to modify data is sometimes unavoidable. ClickHouse [performs best on immutable data](https://clickhouse.com/docs/optimize/avoid-mutations), and any design pattern which requires data to be updated post-insert should be reviewed carefully. That said, ClickHouse provides two mechanisms for modifying data in place:

* **Classic mutations** via [ALTER TABLE ... UPDATE](https://clickhouse.com/docs/en/sql-reference/statements/alter/update/) — these rewrite entire data parts and are suited to infrequent, bulk changes.
* **Lightweight updates** via [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update) powered by **patch parts** — these write only the changed column values as compact delta parts, making them far more efficient for frequent or targeted updates.

### Classic mutations

Classic mutations work by rewriting whole data parts containing the affected columns. This process relies on the same thread pool as merges. In self-managed replicated environments, each replica must apply the mutation independently. For this reason, mutations are both CPU and IO-intensive and should be scheduled cautiously with permission to run limited to administrators. Resource pressure as a result of mutations manifests itself in several ways. Typically, normally scheduled merges accumulate, which in turn causes our earlier "too many parts" issue. Furthermore, users may experience replication delays. The [system.mutations](https://clickhouse.com/docs/en/operations/system-tables/mutations#system_tables-mutations) table should give administrators an indication of currently scheduled mutations. Note that mutations can be cancelled, but not rolled back, with the [KILL MUTATION ](https://clickhouse.com/docs/en/sql-reference/statements/kill#kill-mutation)query.

![sins-06-mutations.png](https://clickhouse.com/uploads/sins_06_mutations_647f7d67d9.png)

### Lightweight updates with patch parts

[Lightweight updates](https://clickhouse.com/docs/sql-reference/statements/update) take a fundamentally different approach. Instead of rewriting entire data parts, a lightweight update creates a small, compact "patch part" containing only the changed column values and metadata to locate the affected rows. These patches are applied on-the-fly during reads (so changes are visible immediately) and materialized efficiently during regular background merges — piggybacking on work ClickHouse is already doing. This can be [up to 1,000× faster](https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates) than classic mutations for many workloads. The same mechanism can be used for deletes, which set a `_row_exists = 0` mask via a patch part rather than rewriting columns. For a deep dive into how patch parts work, see our series on fast UPDATEs in ClickHouse: [Part 1 — purpose-built engines](https://clickhouse.com/blog/updates-in-clickhouse-1-purpose-built-engines) and [Part 2 — SQL-style updates](https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates).

### Mutations in ClickHouse Cloud

In [ClickHouse Cloud](https://clickhouse.cloud/signUp), tables use the [SharedMergeTree](https://clickhouse.com/docs/cloud/reference/shared-merge-tree) engine, which stores data in shared object storage with metadata coordinated through ClickHouse Keeper. This architecture changes the mutation picture: since replicas don't communicate directly with each other and all data lives in shared storage, mutations don't need to be independently applied on each replica — the rewritten parts are written to shared storage and become visible to all replicas through metadata updates. This leads to faster mutation execution and eliminates the replication delays that self-managed clusters can experience during heavy mutation workloads. Both classic mutations and lightweight updates with patch parts are available in ClickHouse Cloud.

### Deduplication

We often see users needing to schedule merges as a result of duplicate data. Typically we suggest users address this issue upstream and deduplicate prior to insertion into ClickHouse. If this is not possible, users have a number of options: deduplicate at query time or utilize a [ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/).

Deduplicating at query time can be achieved by grouping the data on the fields, which uniquely identify a row, and using the [argMax](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/argmax/) function with a date field to identify the last value for other fields. [ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/) allows rows with the same sorting key (ORDER BY key) to be deduplicated on merges. Note this is "best effort" only: sometimes parts will not be merged with the merge process scheduled at non-deterministic intervals. It, therefore, does not guarantee the absence of duplicates. Users can also utilize the [FINAL](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree#query-time-de-duplication--final) modifier to force this deduplication at `SELECT` time. While FINAL does add overhead, it has received significant performance improvements in recent versions including multi-threaded processing, and is suitable for many production workloads.

In the case where data needs to be deleted from ClickHouse e.g., for compliance or deduplication reasons, users can also utilize lightweight deletes instead of mutations. These take the form of a [DELETE statement](https://clickhouse.com/docs/en/sql-reference/statements/delete/) which accepts WHERE clause to filter rows. This marks rows as deleted only. These marks will be used to filter rows out at query time and will be removed when parts are merged.

![sins-07-lightweight_deletes.png](https://clickhouse.com/uploads/sins_07_lightweight_deletes_e11f6a6988.png)

Lightweight deletes are a production-ready feature and are more efficient than using a mutation in most cases, with the exception of if you are doing a large-scale bulk delete. Note that lightweight deletes are not currently compatible with tables that have projections.

## 4. Working with complex and semi-structured data

As well as supporting the usual primitive types, ClickHouse has rich support for complex types such as [Nested](https://clickhouse.com/docs/en/sql-reference/data-types/nested-data-structures/nested/#nestedname1-type1-name2-type2-), [Tuple](https://clickhouse.com/docs/en/sql-reference/data-types/tuple), [Map](https://clickhouse.com/docs/en/sql-reference/data-types/map), and [JSON](https://clickhouse.com/docs/en/sql-reference/data-types/json). The general principle is simple: if you know your data structure, explicitly defined columns will always give you the best compression, insert performance, and query speed. But when your data is genuinely semi-structured or schema-evolving, ClickHouse's native JSON type is purpose-built for the job.

### The JSON type for semi-structured data

ClickHouse provides a [native JSON data type](https://clickhouse.com/docs/en/sql-reference/data-types/json) built to [optimise storage and processing of JSON data](https://clickhouse.com/blog/json-bench-clickhouse-vs-mongodb-elasticsearch-duckdb-postgresql). Under the hood, ClickHouse stores JSON paths as dedicated subcolumns — transparently flattening your JSON into real columns with full compression and vectorized processing.

[By default, up to 1,024 unique paths are stored as dedicated subcolumns](https://clickhouse.com/docs/sql-reference/data-types/newjson#reaching-the-limit-of-dynamic-paths-inside-json). Paths beyond this limit are [stored in an efficient shared data structure](https://clickhouse.com/blog/json-data-type-gets-even-better).

JSON subcolumns can be used in sorting key expressions and data-skipping indexes, enabling the same query optimizations available to regular columns.

That said, the JSON type is not a substitute for schema design. For columns with a known, stable structure, explicit column definitions will always provide the best performance. The right approach is to use explicit columns for the parts of your data you understand, and the JSON type for the parts that are genuinely dynamic.

## 5. Overuse of Nullable

We often see users reaching for the [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable) type, which allows columns to store a Null value. Under the hood, this creates a separate column of UInt8 type. This additional column has to be processed every time a user works with a Nullable column. This leads to additional storage space used and almost always negatively affects performance.

In most cases, we recommend to [avoid using Nullable columns](https://clickhouse.com/docs/optimize/avoid-nullable-columns) entirely. Instead, use a default value that represents a Null, e.g., an empty string for String columns. 

## 6. Deduplication at insert time

New users to ClickHouse are often surprised by ClickHouse's [deduplication strategy](https://clickhouse.com/docs/guides/developer/deduplication). This usually occurs when identical inserts appear to not have any effect. For example, consider the following:

<pre><code type='click-ui' language='sql'>
CREATE TABLE temp
(
   `timestamp` DateTime,
   `value` UInt64
)
ENGINE = MergeTree
ORDER BY tuple()

INSERT INTO temp VALUES ('2022-10-21', 10), ('2022-10-22', 20), ('2022-10-23', 15), ('2022-10-24', 18)
INSERT INTO temp VALUES ('2022-10-21', 10), ('2022-10-22', 20), ('2022-10-23', 15), ('2022-10-24', 18)

clickhouse-cloud :) SELECT * FROM temp

SELECT *
FROM temp

┌───────────timestamp─┬─value─┐
│ 2022-10-21 00:00:00 │    10 │
│ 2022-10-22 00:00:00 │    20 │
│ 2022-10-23 00:00:00 │    15 │
│ 2022-10-24 00:00:00 │    18 │
└─────────────────────┴───────┘
</pre></code>

Notice that, in the example above, 8 rows were inserted, but only 4 rows are shown by the SELECT. A new user might be surprised by the result here. This behavior is the result of the [`replicated_deduplication_window`](https://clickhouse.com/docs/operations/settings/merge-tree-settings#replicated_deduplication_window) setting.

When data is inserted into ClickHouse, it creates [one or more](https://clickhouse.com/docs/operations/settings/settings#max_insert_block_size) blocks (parts). In replicated environments, such as [ClickHouse Cloud](https://clickhouse.cloud/signUp), a hash is also written in ClickHouse Keeper. Subsequent inserted blocks are compared against these hashes and ignored if a match is present. This is useful since it allows clients to safely retry inserts in the event of no acknowledgement from ClickHouse e.g., because of a network interruption. This requires blocks to be identical i.e., the same size with the same rows in the same order. These hashes are stored for only the most recent 100 blocks, although this can be [modified](https://clickhouse.com/docs/operations/settings/merge-tree-settings#replicated_deduplication_window). Note higher values will slow down inserts due to the need for more comparisons.

![sins-08-deduplication.png](https://clickhouse.com/uploads/sins_08_deduplication_c9f39896d5.png)

This same behavior can be enabled for non-replicated instances via the setting [`non_replicated_deduplication_window`](https://clickhouse.com/docs/operations/settings/merge-tree-settings#non_replicated_deduplication_window). In this case, the hashes are stored on a local disk.

## 7. Poor Primary Key Selection

Users new to ClickHouse often struggle to fully understand its primary key concepts. Unlike [B(+)-Tree]((https://en.wikipedia.org/wiki/B%2B_tree))-based OLTP databases, which are optimized for fast location of specific rows, [ClickHouse utilizes a sparse index](https://clickhouse.com/docs/optimize/sparse-primary-indexes) designed for millions of inserted rows per second and petabyte-scale datasets.  In contrast to OLTP databases, this index relies on the data on disk being sorted for fast identification of groups of rows that could possibly match a query - a common requirement in analytical queries. The index, in effect, allows the matching sections of part files to be rapidly identified before they are streamed into the processing engine. For more detail on the layout of the data on disk, we highly [recommend this guide](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#data-is-stored-on-disk-ordered-by-primary-key-columns).

![sins-09-primary_index.png](https://clickhouse.com/uploads/sins_09_primary_index_d3f166fa7d.png)

The effectiveness of this approach, for both query performance and compression, relies on the user [selecting good primary key](https://clickhouse.com/docs/best-practices/choosing-a-primary-key) columns via the ORDER BY clause when creating a table. In general, users should select columns for which they will often filter tables with more than 2 to 3 columns rarely required. The order of these columns is critical and can affect the compression and filtering by columns other than the first entry. For both the efficient filtering of secondary key columns in queries and the compression ratio of a table's column files, it is optimal to order the columns in a primary key by their cardinality in ascending order. A full explanation of the reasoning can be found [here](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-cardinality).

In this post, we highlight the most common 13 mistakes we see our new users encounter, why they occur and the correct approach.

large_13mistakesblog.jpg

medium_13mistakesblog.jpg

small_13mistakesblog.jpg

thumbnail_13mistakesblog.jpg

13mistakesblog.jpg

Getting started with ClickHouse? 13 mistakes and how to avoid them

![Faster root cause for slow traces with ClickStack Event Deltas](/uploads/Using_Click_Stack_Event_Deltas_for_outlier_attribution_in_observability_data_1138_fe2cd9a5f6.jpg)

One of the lesser-known features in ClickStack is **Event Deltas**. When users first open HyperDX, the ClickStack interface, and begin exploring their data, we often hear the same questions: *How do Event Deltas work? When should I use them? What insights can they reveal?*

In this post, we’ll unpack what Event Deltas are, how they can help accelerate root cause analysis for identifying why subsets of traces are slow, and how to interpret their visualizations effectively. We’ll also provide some general recommendations on using them effectively and share details on recent improvements that make the feature more powerful and flexible than ever.

> [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) is a high-performance observability stack bringing the power, speed, and flexibility of ClickHouse to logs, metrics, and traces - all in an open-source package that anyone can use. Event Deltas continue that mission by making it even simpler to identify the root cause of an issue.

## What problem does Event Deltas solve?

Before diving into what Event Deltas are, let’s understand the problem they are designed to address. Observability data, especially trace data, is often high-volume and noisy, making it challenging to visualize patterns or isolate anomalies effectively.

When analyzing traces, **users want to know why certain traces exhibit higher latency**. This can be explored using traditional visualization techniques, **but getting to the root cause is difficult and slow**. It often involves a manual process: filtering spans with higher latency, grouping them by different attributes, and then comparing distributions to spot which attributes appear more frequently in the slow spans than in the faster ones.

This iterative process is time-consuming and repetitive, requiring constant filtering, visualization, and comparison. 

<div class="my-6"><div class="rounded-lg bg-white/10 p-4"><h3 class="text-inherit undefined Typography_suiTitleh3__JlLe2  group/mdHeader toc-ignore" id="test">Get started with ClickStack</h3><p>Discover the world’s fastest and most scalable open source observability stack, in seconds.</p><a class=" w-full" target="_self" href="https://clickhouse.com/docs/use-cases/observability/clickstack/getting-started?loc=blog-o11y-global-cta&amp;utm_source=clickhouse&amp;utm_medium=web&amp;utm_campaign=blog"><button class="styles_button__3smpn mt-6 w-full font-medium" data-type="primary"><span class="flex items-center whitespace-nowrap">Try now</span></button></a></div></div>


## How do Event Deltas work?

ClickStack's Event Deltas feature is designed to completely remove the need for this manual, repetitive analysis. 

**Event Deltas surfaces key attribute differences between fast and slow traces automatically**. As an exploratory tool it is designed to help users quickly understand how and why performance changes occur within their traces. Rather than relying on pre-trained models or static alerting, **Event Deltas analyze trace data dynamically** - comparing the properties of *normal* and *slow* traces to highlight what has changed when performance regresses.

By contrasting the latency distributions of these trace groups, **ClickStack automatically identifies which attributes are most correlated with slower behavior**, whether that’s a new deployment version, a specific endpoint, or a particular user segment.

Instead of manually filtering and inspecting individual traces, Event Deltas instantly surface the key factors driving latency differences. This makes it easier to diagnose regressions, pinpoint their likely root causes, and visually connect them to the affected traces. The result is a fast, interactive way to explore observability data that fits the core spirit of ClickStack - helping users move from observation to insight with minimal effort.

## How are Event Deltas unique?

Many observability platforms use machine learning to detect and explain latency anomalies, typically by modeling latency or other KPIs over time against a partition key such as service endpoint, deployment version, or customer region. The model learns normal behavior, flags deviations, and may even suggest likely causes by identifying which attributes are most common in the anomalous data.

These approaches can be powerful for continuous monitoring, but they come with trade-offs. They require users to define areas of interest in advance, demand significant compute for model training and maintenance, and can generate false positives if not carefully tuned. Because they often run as scheduled jobs rather than interactively, they also sit apart from the real-time investigative workflow of SREs. While effective for automated alerting, they’re less suited for exploratory root cause analysis - precisely where Event Deltas shine.

## How do I use Event Deltas?

Suppose an SRE is investigating elevated latency in a specific service. In this example, we'll use our OpenTelemetry-based demo, available at [play-clickstack.clickhouse.com](https://play-clickstack.clickhouse.com). Let’s assume we’ve been alerted to high latency in a payment service, either through user reports or a traditional threshold-based alert.

From here, we open **HyperDX**, the ClickStack interface, and navigate to the **Traces** view for the payment service. Using the search filters, we narrow the dataset to only include traces from the payment service for the last day. 

![hyperdx_traces.png](/uploads/hyperdx_traces_e1f401c4cf.png)

In the default view, traces appear in a table where each row represents a span. Instead of manually building visualizations to uncover the cause of the latency spike, we can simply switch to the **Event Deltas** tab in the left navigation.

![hyperdx_event_deltas.png](/uploads/hyperdx_event_deltas_5621f88387.png)

We're presented with a **density heatmap** showing span durations over time. The x-axis represents time, while the y-axis shows duration. Color intensity is, by default, based on the number of spans in a bucket (a simple count) with **yellow** areas showing higher concentrations of spans and **blue** representing fewer.

This visualization provides an immediate three-dimensional view of system behavior - how durations vary over time, across duration buckets, and by density within those buckets.

![event_deltas_stable_system.png](/uploads/event_deltas_stable_system_bc5a0d0d3c.png)

> For a stable system, such as that shown above, spans should be concentrated in a narrow band. Whether this system is healthy depends on whether the latencies are within the expected range.

![event_deltas_problem_system.png](/uploads/event_deltas_problem_system_47bdfd8ad1.png)

Conversely in the above example, while the system appears mostly stable - most response times cluster tightly along the x-axis indicating generally consistent performance - we can see a cluster of spans sitting above this band. These represent slower responses that warrant investigation.

Our objective is straightforward: **are there any common attributes among these higher-latency events?** If so, they could point us toward the underlying cause.

This is exactly where **Event Deltas** come in. We can simply select the area of interest by drawing a bounding box around the slower spans in the heatmap. We’ll refer to these as the **outliers**. Event Deltas will then identify the columns and key values most associated with those outlier spans compared to the rest of the dataset.

![event_deltas_selection.png](/uploads/event_deltas_selection_f3cb128ea3.png)

For each attribute, ClickStack identifies values that are disproportionately represented in the selected outlier subset. In other words, if a value appears far more often in the slower spans than in the overall dataset, it’s highlighted as significant. Beneath the density plot, ClickStack renders a series of bar charts - one for each attribute, showing the distribution of values across the selected subsets. Attributes with the strongest bias are listed first, surfacing the factors most closely associated with anomalous spans and distinguishing them from normal behavior. 

![event_deltas_example.png](/uploads/event_deltas_example_480d8cdfc4.png)

> Event Deltas work by issuing two queries: one for the selected outlier area and one for the inlier area. Each query is limited to the appropriate duration and time window. A sample of events from both result sets is then inspected, and columns for which a high concentration of values appears predominantly in the outliers are identified. If interested in the queries, you can open your browser devtools to see the queries being issued.

In the example above, the `SpanAttributes.app.payment.card_type` field has surfaced. The Event Delta's analysis reveals that **24%** of inliers use **MasterCard**, while only **1%** of outliers do. Conversely, **99%** of the outliers use **Visa**, compared to **70%** of the inliers. This indicates that the Visa card type is strongly correlated with the higher-latency traces, while MasterCard appears only in the normal subset.

Conversely, values that appear only in the inliers can also provide useful insights. In this example, the error “Visa. Cache full” value appears exclusively in the inlier spans and is entirely absent from the outliers. Whenever this error occurs, latency remains below roughly **50 milliseconds**, suggesting that this condition is actually linked to faster responses rather than elevated latency.

We can use this information to dig deeper, filtering to these event subsets in the Traces view and examining specific traces to confirm the hypothesis.

Importantly, Event Deltas integrate naturally into existing exploratory workflows. They do not replace the usual investigation process but serve as an additional tool for SREs and developers to accelerate analysis and pinpoint issues more efficiently.

## How to best make use of Event Deltas

Event Deltas are a powerful tool for SREs exploring observability data, but like any analytical method, their effectiveness depends on how they are used. To get the most value, it helps to apply a few simple best practices.

### Focus on a single service

Event Deltas produce the best results when analysis is limited to one service at a time. Latency can vary significantly across services, which makes it harder to identify which attributes truly correlate with outliers. For example, the following is challenging for Event Deltas to analyze:

![multiple_services.png](/uploads/multiple_services_a1125f8419.png)

Before enabling Event Deltas, filter spans to a set where latency distributions are expected to be similar - often a single service. You’ll get clearer, more meaningful results by targeting areas where wide variation is unusual rather than expected.

### Select a clean subset for comparison

When defining an area of interest, aim for a clear separation between faster and slower spans. This allows Event Deltas to more accurately distinguish which attributes are driving the performance difference. 

![single_service.png](/uploads/single_service_1259d4a193.png)

### Ensure sufficient sample size

For meaningful comparisons, make sure both the inlier and outlier groups contain enough spans to reflect real patterns rather than random noise. Very small samples can exaggerate correlations or produce misleading results, while larger, well-balanced subsets help Event Deltas surface truly significant attributes and trends.

## Recent improvements - more flexibility

Until recently, Event Deltas were limited to analyzing traces using the `Duration` column on the y-axis, with color intensity based on count i.e. number in the bucket. In most cases, this remains the most effective way to visualize performance data. However, based on user feedback, we added the ability to **customize both the y-axis and the color intensity metric**, providing more flexibility for advanced analysis.

While this is an advanced feature, several use cases have already emerged. A common scenario involves traces that include additional latency-related columns beyond the standard span duration. For example, in our **[public ClickPy demo](https://clickpy.clickhouse.com)**, which provides analytics for Python packages and includes over **2 trillion rows**, OpenTelemetry tracing is used to capture and debug user issues.

Each chart in the application issues multiple queries to ClickHouse. While the overall request duration is captured in the duration column, we are often more interested in the ClickHouse response time itself. High response times might indicate temporary load on the system, but they could also highlight specific queries that consistently run slower.

The field `db.response_time_ms` within the `SpanAttributes` map stores the query response time. We can now adjust the `value` column for Event Deltas directly by modifying the expression above the density heatmap. To analyze this String value we cast it to a UInt64, allowing Event Deltas to analyze response time instead of span duration.

![custom_event_deltas.png](/uploads/custom_event_deltas_3bef4a03e6.png)

In the resulting visualization, we can clearly see periodic clusters of higher latencies. As before, we can select these regions to perform an Event Delta's analysis and identify which attributes are contributing to this pattern.

![custom_event_deltas_columns.png](/uploads/custom_event_deltas_columns_472c53124f.png)

From our analysis we can clearly see it's one specific type of query that is causing these high latencies connected to the visual `getPackageRanking` - this computes how a package ranks within the Python ecosystem for total downloads.

While count-based color intensity is the default and most intuitive way to visualize spans, other aggregate metrics can reveal different dimensions of performance if used to determine the density. Using fields such as status_code, request_size_bytes, cpu_time_ms, or query_cost for color intensity can highlight error frequency, payload size, resource usage, or query complexity in relation to latency. Choosing the right metric allows Event Deltas to move beyond raw counts and uncover deeper correlations within trace data.

> Important, the color intensity column must be an aggregation function. For example, `avg`, `sum`, `max`, `quantile` or even custom expressions such as `countDistinct(field)`.

![custom_density_event_deltas.png](/uploads/custom_density_event_deltas_3f03425028.png)

In this example, we analyze ClickPy spans to see whether overall span duration (the time it takes for visualizations to load) correlates with database response time. Using `Duration` on the y-axis and `db.response_time_ms` for color intensity quickly reveals that slower visualizations align with higher database latency, shown by stronger intensity in upper buckets. This points to the database as the likely source of slowdown, allowing us to focus analysis on specific queries rather than unrelated causes.

## Conclusion

In this post, we’ve explored the motivation behind Event Deltas, the problem they solve, and how they provide a faster, more intuitive way to identify the root causes of performance regressions in observability data. We covered how to use them effectively, outlined best practices for analysis, and highlighted recent improvements that make them more flexible than ever. Event Deltas are a useful tool in SRE’s investigation workflow - helping teams move quickly from identifying outliers to understanding why they occur and taking action to resolve them.




Read how ClickStack's improved Event Deltas make it effortless to pinpoint the root causes of performance outliers in observability data - turning complex trace analysis into instant, actionable insight.

large_Using ClickStack Event Deltas for outlier attribution in observability data #1138.jpg

medium_Using ClickStack Event Deltas for outlier attribution in observability data #1138.jpg

small_Using ClickStack Event Deltas for outlier attribution in observability data #1138.jpg

thumbnail_Using ClickStack Event Deltas for outlier attribution in observability data #1138.jpg

Faster root cause for slow traces with ClickStack Event Deltas

Learn how giving logs a structured format can unlock 170x compression and make large-scale log storage practical.

large_How to achieve 170x logs compression #1110.jpg

medium_How to achieve 170x logs compression #1110.jpg

small_How to achieve 170x logs compression #1110.jpg

thumbnail_How to achieve 170x logs compression #1110.jpg

How to achieve 170x logs compression #1110.jpg

Compressing nginx logs 170x with column storage

Can open table formats like Iceberg and Delta Lake really power observability at scale, and if not, what is missing today and which innovations could unlock low cost open observability?

large_lakehouses-o11y.png

medium_lakehouses-o11y.png

small_lakehouses-o11y.png

thumbnail_lakehouses-o11y.png

lakehouses-o11y.png

Are open-table-formats + lakehouses the future of observability?

See how easy it is to instrument your NextJS app with OpenTelemetry and ClickStack

large_instrumenting-app.png

medium_instrumenting-app.png

small_instrumenting-app.png

thumbnail_instrumenting-app.png

instrumenting-app.png

Instrumenting your NextJS application with OpenTelemetry and ClickStack

Instrument LibreChat and Clickhouse MCP server with ClickStack and OpenTelemetry to capture LLM traces, metrics, and token usage.

Dale McDiarmid

Blog posts by Dale McDiarmid

Getting started with ClickHouse? 13 mistakes and how to avoid them

Faster root cause for slow traces with ClickStack Event Deltas

Compressing nginx logs 170x with column storage

Are open-table-formats + lakehouses the future of observability?

Instrumenting your NextJS application with OpenTelemetry and ClickStack

LLM observability with ClickStack, OpenTelemetry, and MCP