ClickHouse vs Elasticsearch for log analytics at high cardinality—query speed, storage cost, and retention

Click-heavy log analytics backends eventually run into the same wall: high-cardinality labels, hot dashboards, and data science teams asking for 90-day retention—without a 5x cost blowup. Elasticsearch and ClickHouse can both power this workload, but they behave very differently once you’re at billions of events per day and millions of unique label combinations.

Quick Answer: For high-cardinality log analytics, ClickHouse typically delivers faster exploratory queries at lower storage cost than Elasticsearch, especially beyond the “few TB” stage. Elasticsearch can be easier if you’re already all-in on the Elastic ecosystem, but you’ll pay more in heap, replica, and index overhead as cardinality and retention grow. ClickHouse’s columnar storage, compression, and vectorized execution give it a structural advantage for high-cardinality aggregations, time-range scans, and long-term retention.

Why This Matters

High-cardinality logs aren’t just “more data.” They fundamentally stress how your database stores, indexes, and aggregates information. Once you’re tagging everything with service, version, region, k8s_pod, trace_id, feature_flag, and ten more labels, your log system becomes the investigative backbone for outages, cost analysis, and security. If queries are slow, incident MTTR goes up. If storage is expensive, finance kills your retention.

Choosing between Elasticsearch and ClickHouse at this stage is choosing between two very different cost and performance envelopes:

• Elasticsearch: document-oriented, heavy indexing, JVM heap bound, often tuned to keep short retention and roll over indices aggressively.
• ClickHouse: column-oriented OLAP engine, built to scan and aggregate billions of rows with millisecond results and best-in-class compression.

Key Benefits:

• Query speed at high cardinality: ClickHouse’s columnar layout, late materialization, and vectorized execution keep exploratory GROUP BY and “slice & dice” queries fast, even when you add multiple high-cardinality dimensions.
• Lower storage and infra cost: Compression and sparse indexing in ClickHouse reduce raw and replica storage overhead, making 30–180 days of logs economically realistic.
• Operational simplicity at scale: ClickHouse’s MergeTree engine prefers large batched inserts and uses background merges instead of heavy per-field inverted indexing, avoiding the index-heap spiral common in large Elastic clusters.

Core Concepts & Key Points

Concept	Definition	Why it's important
High-cardinality log analytics	Log workloads where many fields (labels/tags) have very large numbers of distinct values (services, pod IDs, user IDs, trace IDs, feature flags).	Stress-tests both storage (index size) and query execution (complex aggregations), revealing whether your system can handle real-world observability workloads.
Columnar vs document storage	Columnar (ClickHouse) stores values of each column together; document (Elasticsearch) stores JSON-like documents plus inverted indexes for fields.	Columnar favors large scans and aggregations over many rows; document + inverted indexes favors targeted search but gets expensive as you index more high-cardinality fields.
Retention & cost tradeoffs	How long you keep logs online and at what resolution (raw vs sampled), constrained by storage + compute cost.	Determines whether you can answer 90-day questions (“When did this regression start?”) without an archive restore or huge bill; ClickHouse’s compression typically extends feasible retention windows.

How It Works (Step-by-Step)

At a high level, both systems ingest log events, index them in various ways, and serve queries. The details—especially around indexing and execution—drive the difference in speed and cost.

1. Ingestion & Indexing

Elasticsearch

• You send JSON documents to an index, often via Beats, Fluent Bit, or Logstash.
• Each field you mark as indexed builds or updates an inverted index; doc values and stored fields also consume space.
• High-cardinality fields (user_id, trace_id) amplify:
  • index size
  • heap usage for segment metadata
  • merge IO when segments are compacted

Large Elastic log clusters often resort to:

• Dropping fields from _source
• Turning off indexing for “expensive” fields
• Aggressive index lifecycle management (ILM) with hot/warm/cold tiers

ClickHouse

• You typically define a MergeTree-family table (e.g., ReplacingMergeTree, MergeTree) with explicit columns for parsed log fields.
• Ingest is batched—ideally 1,000–100,000 rows per insert—via Kafka, Fluent Bit, Vector, or custom pipelines.
• ClickHouse creates:
  • A sparse primary key index (min/max + granules)
  • Optional secondary data-skipping indexes (e.g., minmax, set, tokenbf_v1) on select columns

Because it’s columnar:

• Non-filtered columns can remain highly compressed “on disk” without bloating index structures.
• High-cardinality fields don’t automatically create huge inverted indexes; you pay mainly for raw column data + compression.

Example log table in ClickHouse:

CREATE TABLE logs
(
    ts           DateTime64(3),
    level        LowCardinality(String),
    service      LowCardinality(String),
    region       LowCardinality(String),
    host         String,
    pod          String,
    trace_id     String,
    span_id      String,
    message      String,
    attrs        Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (ts, service, region);

• LowCardinality reduces dictionary-heavy storage for medium-cardinality fields (like service, region).
• High-cardinality fields like trace_id and pod are just columns; they don’t explode an inverted index structure as in Elastic.

2. Query Execution

Elasticsearch

• Search and aggregations rely heavily on:
  • Inverted indexes
  • Doc values
  • JVM-based execution
• Queries like “error rate per service per region in the last 15 minutes” translate into:
  • Filter by time range using the time-based index
  • Apply term filters
  • Use aggregations (terms, date_histogram)
• As cardinality grows, terms aggregations become particularly expensive:
  • More buckets
  • Higher memory and CPU use
  • More pressure on heap and GC

ClickHouse

• Performs vectorized, columnar execution:
  • Reads only the columns referenced in the query
  • Applies filters early using the primary key index and data-skipping indexes
  • Executes GROUP BY and aggregations in CPU-efficient, cache-friendly batches

Example investigative query:

SELECT
    service,
    region,
    count(*)         AS errors,
    uniqExact(trace_id) AS unique_traces
FROM logs
WHERE
    ts >= now() - INTERVAL 15 MINUTE
    AND level = 'ERROR'
GROUP BY
    service, region
ORDER BY errors DESC
LIMIT 50;

• Only ts, level, service, region, trace_id columns are read.
• Compression + vectorized execution allow this to stay in the millisecond to low-second range even over billions of rows.

You can verify behavior via system tables:

SELECT
    read_rows,
    read_bytes,
    result_rows,
    query_duration_ms
FROM system.query_log
WHERE query LIKE '%FROM logs%'
ORDER BY event_time DESC
LIMIT 5;

This makes performance claims inspectable and tunable instead of “best-effort.”

3. Retention & Lifecycle

Elasticsearch

• Common pattern: daily indices like logs-2026.04.12, managed via ILM:
  • Hot (SSD, lots of replicas)
  • Warm (slower storage)
  • Cold/frozen
• Longer retention often means:
  • Rolling over indices more frequently to avoid shard bloat
  • Moving to slower nodes and/or snapshotting to S3 and restoring on demand
  • Complex shard count and ILM tuning per index pattern

ClickHouse

• Retention is often implemented via:
  • PARTITION BY on a time period (day, week, month)
  • ALTER TABLE ... DROP PARTITION or TTL clauses for automatic data removal

Example with TTL:

CREATE TABLE logs
(
    ts        DateTime64(3),
    -- other columns
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (ts)
TTL ts + INTERVAL 90 DAY DELETE;

• No per-index shard juggling.
• Dropping old partitions is a metadata operation that removes large swaths of data efficiently.
• Partitioning is primarily for data lifecycle, not for query speed; over-partitioning (e.g., partition by ts, service, region) can create too many parts and hurt performance.

You can watch partitions and parts via:

SELECT
    partition,
    count() AS parts,
    sum(rows) AS rows
FROM system.parts
WHERE table = 'logs'
  AND active
GROUP BY partition
ORDER BY partition DESC
LIMIT 10;

Use this to guard against “Too many parts” caused by overly granular partition schemes or tiny batched inserts.

Common Mistakes to Avoid

• Treating partitioning as a free speed hack: In ClickHouse, partitioning is for retention and maintenance first. Over-partitioning on high-cardinality fields (e.g., service, tenant_id) creates many small parts, triggers “Too many parts” errors, and slows merges. Prefer time-based partitioning (toYYYYMM or toYYYYMMDD) and keep partition cardinality low (ideally < 100–1,000 values).
• Ingesting tiny rows at high frequency: Both Elasticsearch and ClickHouse suffer if you ingest one log line at a time. In ClickHouse, always batch inserts—target at least 1,000 rows per insert, ideally 10,000–100,000—and consider async_insert to accumulate batches. In Elasticsearch, use bulk indexing and tune flush/refresh intervals to avoid constant segment creation.

Real-World Example

In my last role, we ran a mixed observability backend—metrics in Prometheus, traces in a vendor tool, and logs in Elasticsearch. Logs were the pain point:

• ~10B log events per day
• Hundreds of services, each with multiple env, region, cluster, pod, and version labels
• SREs needed 90 days of retention to trace intermittent incidents

On Elasticsearch (8.x) with time-based indices:

• Aggregation-heavy Kibana views (“error rate per service per region over 24 hours”) routinely took 5–20 seconds or timed out under load.
• Storage costs grew quickly:
  • Multiple replicas for availability
  • Large index sizes due to many indexed fields and doc values
  • Additional “summary” indices for dashboards
• We fought constant heap pressure and GC tuning as cardinality grew.

We migrated logs to ClickHouse (self-managed first, then ClickHouse Cloud):

• Schema: MergeTree table with time-based partitioning (toYYYYMM), ORDER BY (ts, service, region).
• Ingest: Flows from Kafka into ClickHouse in 20–50k-row batches.
• Query workloads: SRE dashboards, ad-hoc incident investigations, security anomaly hunting.

Outcomes:

• Incident dashboards dropping from 5+ seconds to under 500 ms even at high cardinality and across tens of billions of rows.
• Storage reduced by >50% thanks to compression and avoiding large per-field inverted indexes.
• Retention extended from 14 days (Elastic, hot + warm) to 90 days in ClickHouse at similar or lower total cost.

We validated performance with system.query_log and watched merges with system.merges to keep ingest healthy under peak traffic.

Pro Tip: In ClickHouse, always design your ORDER BY around the most common filter and group-by patterns (ts first, then a couple of key dimensions like service, region). Then use system.query_log to confirm your real queries align with that design and adjust before it’s locked in at petabyte scale.

Summary

For high-cardinality log analytics at serious scale, the tradeoffs are structural:

• Elasticsearch’s document + inverted index model is excellent for targeted search and full-text queries, but index and heap costs climb quickly when you index many high-cardinality fields over long retention windows.
• ClickHouse’s columnar, OLAP-first design excels at high-volume, high-cardinality aggregations and time-range analytics—the core of modern log-based observability—while keeping storage and compute costs manageable via compression and vectorized execution.

If your primary workloads are:

• “Slice and dice billions of log lines by multiple labels, in real time”
• “Keep 30–180 days of detailed logs online”
• “Run millisecond queries at petabyte scale without runaway infra cost”

then ClickHouse is usually the better foundation, either as your main log store or as a speed layer alongside existing systems.

Next Step

Get Started