Best high-cardinality OpenTelemetry logs backend to replace Elasticsearch (fast investigations + long retention)

Teams that push OpenTelemetry logs hard—high cardinality, spiky traffic, and long retention—eventually hit the same wall with Elasticsearch: slow investigations and spiraling storage costs. The good news is that modern OLAP databases like ClickHouse can act as a purpose-built logs backend, giving you millisecond investigative queries over billions of events while keeping months or years of data online.

Quick Answer: The best high-cardinality OpenTelemetry logs backend to replace Elasticsearch for fast investigations and long retention is ClickHouse (typically via ClickStack, the open source observability stack powered by ClickHouse). Its column-oriented OLAP engine, compression, and vectorized execution deliver millisecond queries over billions of log rows at petabyte scale, while dramatically reducing storage footprint and hardware costs. With proper batching, schema design, and retention via TTLs, ClickHouse can serve as both your “speed layer” for live investigations and your long-term archive—without splitting data into separate systems.

Why This Matters

If your incident workflow depends on “zooming in” on a problem in seconds, slow queries are more than an annoyance—they’re risk. With Elasticsearch, high-cardinality labels (Kubernetes, microservices, tenants, feature flags) and long retention combine into a worst-case workload: heavy index overhead, hot shards, and sluggish queries just when you need them fastest.

ClickHouse flips that model. Instead of indexing every field up front, it stores logs in a compressed, columnar format and uses vectorized execution to scan and aggregate only the columns you need. That’s why it’s used to analyze billions of rows in real time with millisecond results, and why companies like Anthropic and Capital One rely on it for large-scale observability and cost-efficient analytics.

Key Benefits:

• Fast investigations at scale: Slice and dice billions of log events in milliseconds, even with high-cardinality labels, so on-call engineers can iterate quickly during incidents.
• Long retention without separate “cold” systems: Columnar compression plus TTL- and tiered-storage–based lifecycle controls make it feasible to keep months or years of logs online in the same cluster.
• Operationally predictable ingest: MergeTree tables, batching guidance, and system tables (system.parts, system.merges) help you avoid the “small writes + too many shards” failure modes common in Elasticsearch.

Core Concepts & Key Points

Concept	Definition	Why it's important
Column-oriented OLAP engine	ClickHouse stores data by column and executes queries in a vectorized fashion, scanning only the columns required.	This is the core reason you get millisecond investigations on billions of log rows instead of multi-second waits for index-heavy queries.
MergeTree storage & batching	MergeTree is the main table engine in ClickHouse, storing data in parts that are continuously merged in the background. Batching means inserting data in reasonably large row groups.	Healthy ingest (1,000–100,000 rows per insert) keeps part counts low, avoids “Too many parts” errors, and sustains high write throughput for noisy logs workloads.
TTL and tiered storage	Declarative rules that automatically move or remove data over time, often between SSD and object storage.	This is how you turn “we can’t afford 90 days of logs” into “we can keep a year online,” without manual re-indexing or separate cold clusters.

How It Works (Step-by-Step)

The basic pattern for using ClickHouse as your high-cardinality OpenTelemetry logs backend is:

1. Ingest OTLP logs into a ClickHouse table optimized for observability.
2. Query logs via SQL (directly or through an observability UI) with filters, aggregations, and joins.
3. Use TTL and tiered storage to keep costs predictable as retention grows.

1. Ingest: From OTLP to ClickHouse

You typically have three ingest choices:

• ClickStack (opinionated stack on ClickHouse):
  Use ClickStack, the open source observability stack powered by ClickHouse, which wires up ingestion, storage, and visualization for logs, metrics, and traces.
• OpenTelemetry Collector → ClickHouse:
  Configure the OTEL Collector to export logs to ClickHouse using the ClickHouse exporter or via an intermediary (e.g., Kafka, vector, Fluent Bit).
• Custom pipeline:
  Write logs to Kafka or another bus and batch them into ClickHouse using async inserts or your own writer.

A simple logs table might look like this:

CREATE TABLE otel_logs
(
    ts          DateTime64(3, 'UTC'),
    service     LowCardinality(String),
    level       LowCardinality(String),
    trace_id    String,
    span_id     String,
    body        String,
    attributes  Map(String, String)
)
ENGINE = MergeTree
ORDER BY (ts, service)
PARTITION BY toDate(ts);

Key design choices here:

• ORDER BY (ts, service) keeps related events close together on disk, which is ideal for time-range and service-scoped investigations.
• LowCardinality(String) on fields like service and level dramatically compresses high-cardinality-ish dimensions without giving up fast filtering.
• Map(String, String) for attributes lets you carry dynamic OTEL attributes without needing to re-ALTER the schema for every new key.

To keep ingest healthy:

• Aim for 1,000–100,000 rows per insert.
• Prefer async inserts for high-throughput pipelines:

SET async_insert = 1;
SET wait_for_async_insert = 0;

This allows ClickHouse to buffer and batch inserts internally, smoothing write spikes from log bursts.

2. Query: Fast investigations with SQL

Once data lands, ClickHouse’s OLAP engine takes over:

• It reads only the columns you request (e.g., ts, service, level, body).
• It can aggregate billions of rows using vectorized execution and compression-aware scans.

Common investigative queries:

Time-bounded filter:

SELECT ts, service, level, body
FROM otel_logs
WHERE ts >= now() - INTERVAL 15 MINUTE
  AND service = 'api-gateway'
  AND level IN ('ERROR', 'WARN')
ORDER BY ts DESC
LIMIT 200;

High-cardinality breakdown (e.g., per tenant):

SELECT attributes['tenant_id'] AS tenant,
       count(*) AS errors
FROM otel_logs
WHERE ts >= now() - INTERVAL 5 MINUTE
  AND level = 'ERROR'
GROUP BY tenant
ORDER BY errors DESC
LIMIT 50;

These run fast because:

• Columnar storage plus compression means scanning billions of rows is CPU- and IO-efficient.
• LowCardinality keeps dictionary-encoded dimensions compact while allowing fast GROUP BY.

To verify query behavior, you can inspect system.query_log:

SELECT
    query_duration_ms,
    read_rows,
    read_bytes
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%FROM otel_logs%'
ORDER BY event_time DESC
LIMIT 10;

This gives you concrete proof of performance—latency, rows scanned, and bytes read—rather than relying on intuition.

3. Retention: TTL and tiered storage

ClickHouse treats partitioning as primarily a data management technique, not a magic performance knob. For logs, that’s a feature. You can declare lifecycle rules directly on the table:

ALTER TABLE otel_logs
MODIFY TTL
    ts + INTERVAL 30 DAY TO DISK 'ssd',
    ts + INTERVAL 365 DAY DELETE;

This example:

• Keeps the first 30 days on SSD.
• After 30 days, moves data to a cheaper disk or object-backed volume.
• After 365 days, deletes it automatically.

With tiered storage, this becomes a unified hot/warm/cold strategy without multiple clusters or re-indexing jobs. The tradeoff is clear:

• Pros: Simple, predictable lifecycle; no separate “cold” backend.
• Cons: Very long retention on a single partition key can increase part counts if ingest isn’t batched and managed well.

This is where system tables are your friends:

SELECT
    partition,
    count() AS parts,
    sum(rows) AS total_rows
FROM system.parts
WHERE table = 'otel_logs'
  AND active
GROUP BY partition
ORDER BY partition;

If you see partitions with thousands of active parts, it’s a signal to:

• Improve batching.
• Revisit PARTITION BY granularity (e.g., daily vs. monthly), staying within a low-cardinality range (hundreds, not millions, of partitions).
• Monitor system.merges to ensure merges keep up.

Common Mistakes to Avoid

• Treating partitioning as a performance silver bullet:
  Over-partitioning logs (e.g., by service, tenant, or pod_id) can hurt performance by creating many small parts and forcing cross-partition scans. Use time-based, low-cardinality partitions (daily or monthly) for lifecycle management, and let the ORDER BY and filters handle performance.
• Small, frequent inserts that create “Too many parts” errors:
  Ingesting log events one-by-one (or in tiny batches) is the easiest way to overload MergeTree with parts. Always batch inserts to at least 1,000 rows, ideally 10,000–100,000, and enable async_insert for spiky workloads.

Real-World Example

At one organization, our Elasticsearch-based logs stack was failing the two tests that matter to on-call engineers:

1. Investigations weren’t interactive.
   Queries like “show me all 500s for api-gateway in the last 30 minutes, grouped by customer” regularly took 5–15 seconds, or worse when we hit hot shards.
2. Retention was constrained by cost.
   We could barely afford 14–30 days of logs before resorting to a separate cold system, which meant broken investigations when issues stretched back weeks.

We moved high-cardinality logs to ClickHouse behind an OTEL Collector pipeline, using a schema like the otel_logs example above and ClickStack for visualization.

After tuning:

• Median investigation queries landed under 300–500 ms on billions of rows, even during incident spikes.
• Storage footprint dropped by more than half thanks to columnar compression and LowCardinality encodings.
• Retention extended from ~30 days to 180 days in the hot+higher-cost tier, and a year in cheaper storage, all in one logical cluster.

Operationally, the guardrails that mattered most were:

• Enforcing batched inserts from the Collector → Kafka → ClickHouse writer path.
• Watching system.parts and system.merges to catch misconfiguration early.
• Using TTL with tiered storage instead of trying to manually juggle “hot” vs “cold” indices.

Pro Tip: Before migrating fully off Elasticsearch, mirror a slice of your OTEL logs into ClickHouse (e.g., 5–10% of traffic or a subset of services) and run your real investigative queries side-by-side. Use system.query_log to compare actual latency and scan volumes—this gives you hard evidence of how ClickHouse handles your real high-cardinality patterns.

Summary

For teams searching for the best high-cardinality OpenTelemetry logs backend to replace Elasticsearch—without sacrificing fast investigations or long retention—ClickHouse is purpose-built for the job. Its column-oriented OLAP engine delivers millisecond queries over billions of rows, even with noisy labels and dense attributes. MergeTree’s design, combined with batching and async_insert, keeps ingest stable at scale, while TTL and tiered storage provide a clean path to months or years of retention in a single logical system.

If you pair ClickHouse with ClickStack or your own OTEL Collector pipeline, you can evolve from “we can barely keep 14 days of logs” to “we can query a year of history interactively” and prove it with real query logs and SQL.

Next Step

Get Started