Equivalent concepts in ClickStack and Elastic

### Deduplication and routing[​](#deduplication-and-routing "Direct link to Deduplication and routing")

Elasticsearch de-duplicates documents based on their `_id`, routing them to shards accordingly. ClickHouse doesn't store a default row identifier but supports **insert-time deduplication**, allowing users to retry failed inserts safely. For more control, `ReplacingMergeTree` and other table engines enable deduplication by specific columns.

Index routing in Elasticsearch ensures specific documents are always routed to specific shards. In ClickHouse, you can define **shard keys** or use `Distributed` tables to achieve similar data locality.

### Aggregations and execution model[​](#aggregations-execution-model "Direct link to Aggregations and execution model")

While both systems support the aggregation of data, ClickHouse offers significantly [more functions](/docs/sql-reference/aggregate-functions/reference), including statistical, approximate, and specialized analytical functions.

In observability use cases, one of the most common applications for aggregations is to count how often specific log messages or events occur (and alert in case the frequency is unusual).

The equivalent to a ClickHouse `SELECT count(*) FROM ... GROUP BY ...` SQL query in Elasticsearch is the [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html), which is an Elasticsearch [bucket aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket.html).

ClickHouse's `GROUP BY` with a `count(*)` and Elasticsearch's terms aggregation are generally equivalent in terms of functionality, but they differ widely in their implementation, performance, and result quality.

This aggregation in Elasticsearch [estimates results in "top-N" queries](https://www.elastic.co/docs/reference/aggregations/search-aggregations-bucket-terms-aggregation#terms-agg-doc-count-error) (e.g., top 10 hosts by count), when the queried data spans multiple shards. This estimation improves speed but can compromise accuracy. You can reduce this error by [inspecting `doc_count_error_upper_bound`](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#terms-agg-doc-count-error) and increasing the `shard_size` parameter — at the cost of increased memory usage and slower query performance.

Elasticsearch also requires a [`size` setting](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-size) for all bucketed aggregations — there's no way to return all unique groups without explicitly setting a limit. High-cardinality aggregations risk hitting [`max_buckets` limits](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-settings.html#search-settings-max-buckets) or require paginating with a [composite aggregation](https://www.elastic.co/docs/reference/aggregations/bucket/composite-aggregation), which is often complex and inefficient.

ClickHouse, by contrast, performs exact aggregations out of the box. Functions like `count(*)` return accurate results without needing configuration tweaks, making query behavior simpler and more predictable.

ClickHouse imposes no size limits. You can perform unbounded group-by queries across large datasets. If memory thresholds are exceeded, ClickHouse [can spill to disk](https://clickhouse.com/docs/en/sql-reference/statements/select/group-by#group-by-in-external-memory). Aggregations that group by a prefix of the primary key are especially efficient, often running with minimal memory consumption.

#### Execution model[​](#execution-model "Direct link to Execution model")

The above differences can be attributed to the execution models of Elasticsearch and ClickHouse, which take fundamentally different approaches to query execution and parallelism.

ClickHouse was designed to maximize efficiency on modern hardware. By default, ClickHouse runs a SQL query with N concurrent execution lanes on a machine with N CPU cores:

On a single node, execution lanes split data into independent ranges allowing concurrent processing across CPU threads. This includes filtering, aggregation, and sorting. The local results from each lane are eventually merged, and a limit operator is applied, in case the query features a limit clause.

Query execution is further parallelized by:

1. **SIMD vectorization**: operations on columnar data use [CPU SIMD instructions](https://en.wikipedia.org/wiki/Single_instruction,_multiple_data) (e.g., [AVX512](https://en.wikipedia.org/wiki/AVX-512)), allowing batch processing of values.
2. **Cluster-level parallelism**: in distributed setups, each node performs query processing locally. [Partial aggregation states](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states#working-with-aggregation-states) are streamed to the initiating node and merged. If the query's `GROUP BY` keys align with the sharding keys, merging can be [minimized or avoided entirely](/docs/operations/settings/settings#distributed_group_by_no_merge).

  

This model enables efficient scaling across cores and nodes, making ClickHouse well-suited for large-scale analytics. The use of *partial aggregation states* allows intermediate results from different threads and nodes to be merged without loss of accuracy.

Elasticsearch, by contrast, assigns one thread per shard for most aggregations, regardless of how many CPU cores are available. These threads return shard-local top-N results, which are merged at the coordinating node. This approach can underutilize system resources and introduce potential inaccuracies in global aggregations, particularly when frequent terms are distributed across multiple shards. Accuracy can be improved by increasing the `shard_size` parameter, but this comes at the cost of higher memory usage and query latency.

In summary, ClickHouse executes aggregations and queries with finer-grained parallelism and greater control over hardware resources, while Elasticsearch relies on shard-based execution with more rigid constraints.

For further details on the mechanics of aggregations in the respective technologies, we recommend the blog post ["ClickHouse vs. Elasticsearch: The Mechanics of Count Aggregations"](https://clickhouse.com/blog/clickhouse_vs_elasticsearch_mechanics_of_count_aggregations#elasticsearch).

### Data management[​](#data-management "Direct link to Data management")

Elasticsearch and ClickHouse take fundamentally different approaches to managing time-series observability data — particularly around data retention, rollover, and tiered storage.

#### Index lifecycle management vs native TTL[​](#lifecycle-vs-ttl "Direct link to Index lifecycle management vs native TTL")

In Elasticsearch, long-term data management is handled through **Index Lifecycle Management (ILM)** and **Data Streams**. These features allow you to define policies that govern when indices are rolled over (e.g. after reaching a certain size or age), when older indices are moved to lower-cost storage (e.g. warm or cold tiers), and when they're ultimately deleted. This is necessary because Elasticsearch does **not support re-sharding**, and shards can't grow indefinitely without performance degradation. To manage shard sizes and support efficient deletion, new indices must be created periodically and old ones removed — effectively rotating data at the index level.

ClickHouse takes a different approach. Data is typically stored in a **single table** and managed using **TTL (time-to-live) expressions** at the column or partition level. Data can be **partitioned by date**, allowing efficient deletion without the need to create new tables or perform index rollovers. As data ages and meets the TTL condition, ClickHouse will automatically remove it — no additional infrastructure is required to manage rotation.

#### Storage tiers and hot-warm architectures[​](#storage-tiers "Direct link to Storage tiers and hot-warm architectures")