Elasticsearch to ClickHouse for Logs and Observability: When and How to Switch

Elasticsearch to ClickHouse is the migration we scope most often in observability engagements, and the reason is rarely a feature gap — it is an economics gap. Elasticsearch became the default log store the way most defaults happen: it was there, Kibana was pleasant, and the cluster was small. Then the platform grew, log volume compounded, and the cluster stopped being small. Today the most predictable infrastructure conversation we have is with a team whose Elasticsearch logging bill has crossed into six or seven figures and whose actual usage is: filter by service, filter by time, grep for a request ID, draw a rate graph.

That usage pattern is an analytics workload wearing a search costume. This post covers when log and observability workloads belong in ClickHouse, how to build the schema and ingestion pipeline, how to translate the query patterns, a seven-step Elasticsearch to ClickHouse cutover that avoids a migration cliff — and, because the honesty is the point, what Elasticsearch still does better.

Why Log Workloads Outgrow Elasticsearch Economics

Elasticsearch pays a structural tax that log workloads feel acutely:

• Index-everything by default. Inverted indexes on every mapped field mean the index can rival or exceed the raw data. You pay to index millions of fields per second that will never appear in a query.
• JVM heap gravity. Cluster capacity is bounded by heap as much as by disk. Scaling retention means scaling nodes, whether or not query load grew.
• Replica economics. Search-oriented replication doubles hot storage as table stakes.

Compression takes a back seat to search-time performance in a document store. Columnar storage inverts that: identical timestamps, repeated service names, and near-identical messages compress brutally well when stored by column. Order-of-magnitude compression on log data is normal in ClickHouse, before tiering — we walked through the pipeline mechanics and compression numbers in our ClickHouse Vector log pipeline post.

Add ClickHouse storage tiering (hot NVMe, cold S3-backed MergeTree with a local cache) and retention economics change category: keeping 13 months of logs queryable stops being a budget line worth a meeting. The market has noticed. ClickHouse Inc. acquired HyperDX in 2025 and Langfuse in January 2026, and ClickStack Cloud — its managed observability offering — is in private preview. Observability on ClickHouse is not a hack anymore; it is a product category, and the Elasticsearch to ClickHouse pattern is its most common on-ramp.

What Changed the Calculus: A Real Text Index

The historical objection was legitimate: ClickHouse could filter and aggregate, but free-text search meant LIKE '%...%' scans. That objection expired. The text index went experimental in ClickHouse 25.9, beta in 25.12, and GA in 26.2, with position-aware phrase search added in 26.7 (and roughly 2.5× faster regex in the same release for the patterns that still need it).

Practically: “find this trace ID anywhere in 90 days of logs” is now an index lookup, and multi-word phrase queries respect word order. Combined with columnar pruning — ClickHouse only reads the columns and time ranges the query touches — the common log-search patterns run comfortably at a fraction of the hardware. This is the single development that moved Elasticsearch to ClickHouse migrations from “viable with caveats” to the default recommendation for log workloads.

The query surface matters as much as the index structure. hasToken answers single-term membership, hasAllTokens requires every term to appear, and phrase search (26.7+) respects word order — which covers the match, terms, and phrase queries that dominate real Kibana usage. The text index also supports a direct-read path where qualifying queries are answered from the index without touching the message column at all; the ClickHouse documentation reports 45–89× speedups over column scans for those shapes. Treat vendor numbers as vendor numbers — the point is architectural: token-membership queries stop being scans, which removes the last workload class that used to justify keeping a search engine in the log path.

Elasticsearch to ClickHouse Schema Design for Logs

Schema is where an Elasticsearch to ClickHouse migration is won or lost, because the mapping habits that Elasticsearch tolerates are exactly the ones MergeTree punishes. Logs in ClickHouse reward a small amount of upfront design:

CREATE TABLE logs
(
    timestamp    DateTime64(3),
    service      LowCardinality(String),
    level        LowCardinality(String),
    trace_id     String,
    message      String,
    attributes   JSON,
    INDEX msg_text message TYPE text GRANULARITY 1,
    INDEX trace_bf trace_id TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (service, level, timestamp)
TTL timestamp + INTERVAL 14 DAY TO VOLUME 'cold',
    timestamp + INTERVAL 13 MONTH DELETE;

The decisions that matter:

• ORDER BY mirrors how humans investigate: which service, what severity, when. Most queries then read a thin slice of the table instead of all of it.
• LowCardinality on enumerable fields (service, level, environment, region) — dictionary encoding that makes both storage and GROUP BY dramatically cheaper.
• The JSON type for variable attributes. Production-ready since the 25.x line, it gives you schema-on-write flexibility for structured metadata without the mapping-explosion problem that plagues Elasticsearch when developers log creative field names. The ~3× speedup for deeply nested queries in 26.6 was aimed at exactly this workload.
• Targeted indexes, not indexed-everything: a text index on message, a bloom filter on trace_id. You index the two access paths that matter and let the sort order and partitioning carry the rest. This asymmetry — index two things instead of two thousand — is a large share of the Elasticsearch to ClickHouse cost difference.

Materialized views complete the picture: error-rate-per-service-per-minute rollups feed dashboards from tables thousands of times smaller than the raw stream, so dashboard refresh concurrency costs approximately nothing.

CREATE MATERIALIZED VIEW error_rates_1m
ENGINE = SummingMergeTree
ORDER BY (service, minute)
AS
SELECT
    toStartOfMinute(timestamp) AS minute,
    service,
    countIf(level = 'ERROR')   AS errors,
    count()                    AS total
FROM logs
GROUP BY minute, service;

After a week of production traffic, verify the compression claim against your own data rather than taking ours on faith — system.columns is the measurement source:

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 1) AS ratio
FROM system.columns
WHERE database = 'observability'
  AND table = 'logs'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

For deeper part-level diagnostics — fragmentation, partition sizing, capacity forecasting — see our system.parts diagnostic guide.

Building the Ingestion Pipeline: Kafka and OpenTelemetry

Most estates arrive at an Elasticsearch to ClickHouse migration with logs already flowing through Kafka or an OpenTelemetry Collector, which is exactly what makes the dual-write pattern cheap to set up. If the pipeline is Kafka-based, the ClickHouse side is a Kafka engine table plus a materialized view — the pattern we detail in our ClickHouse Kafka real-time analytics guide:

CREATE TABLE logs_kafka_queue
(
    timestamp    DateTime64(3),
    service      String,
    level        String,
    trace_id     String,
    message      String,
    attributes   String
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list   = 'kafka-1:9092,kafka-2:9092,kafka-3:9092',
    kafka_topic_list    = 'otel-logs',
    kafka_group_name    = 'clickhouse_logs_consumer',
    kafka_format        = 'JSONEachRow',
    kafka_num_consumers = 4;

CREATE MATERIALIZED VIEW logs_kafka_mv TO logs
AS
SELECT
    timestamp,
    service,
    level,
    trace_id,
    message,
    CAST(attributes, 'JSON') AS attributes
FROM logs_kafka_queue;

Because this consumer joins its own consumer group, Elasticsearch keeps consuming the same topic untouched — the tee costs one CREATE TABLE. If the pipeline is OpenTelemetry Collector-based instead, add the ClickHouse exporter alongside the existing Elasticsearch exporter (we covered collector architecture in depth in our OpenTelemetry Collector guide):

exporters:
  elasticsearch:
    endpoints: ["https://es-cluster:9200"]     # existing path, unchanged
  clickhouse:
    endpoint: tcp://clickhouse-lb:9000
    database: observability
    logs_table_name: logs
    timeout: 10s
    retry_on_failure:
      enabled: true

service:
  pipelines:
    logs:
      receivers: [otlp, filelog]
      processors: [batch]
      exporters: [elasticsearch, clickhouse]   # dual-write is one line

Two ingestion rules save most of the tuning pain later: batch inserts in the 10,000–100,000 row range, and use async inserts for any producer that cannot batch. The full tuning treatment — batch sizing, async_insert settings, and the system tables that measure ingestion health — is in our ClickHouse ingestion performance post.

Translating Elasticsearch Query Patterns to ClickHouse SQL

The query translation layer is the part of an Elasticsearch to ClickHouse migration teams overestimate. Every log investigation decomposes into a handful of moves, and each has a direct equivalent:

Concretely, “grep for a request ID across 90 days” — the query that used to require keeping 90 days hot in Elasticsearch — becomes:

SELECT
    timestamp,
    service,
    level,
    message
FROM logs
WHERE hasToken(message, 'a7f3c2e1d94b')
  AND timestamp >= now() - INTERVAL 90 DAY
ORDER BY timestamp
LIMIT 100;

And the error-rate dashboard panel, served from the rollup rather than the raw table:

SELECT
    minute,
    service,
    round(sum(errors) / sum(total) * 100, 2) AS error_pct
FROM error_rates_1m
WHERE minute >= now() - INTERVAL 6 HOUR
GROUP BY minute, service
ORDER BY minute;

Do not take index effectiveness on faith either. EXPLAIN indexes = 1 shows exactly which granules the primary key, partition pruning, and skip indexes eliminated — if the text or bloom filter index is not listed with a meaningful granule reduction, the index is not earning its keep:

EXPLAIN indexes = 1
SELECT count()
FROM logs
WHERE service = 'api'
  AND hasToken(message, 'timeout')
  AND timestamp >= now() - INTERVAL 1 HOUR;

Analysts stop learning a query DSL and use SQL, which every BI tool, notebook, and LLM already speaks. For the front end, Grafana works well, and the ClickStack line (from the HyperDX acquisition) exists precisely to replace the Kibana experience. Diagnostics improved too: EXPLAIN ANALYZE, added in 26.7, tells you exactly what a slow investigation query actually did.

Operating the New Log Store: Replication, HA, and the Telemetry That Matters

An Elasticsearch to ClickHouse move also swaps operational models, and pretending otherwise is how migrations acquire their first 2 a.m. surprise. Elasticsearch couples durability and query capacity in one mechanism: replicas serve searches and protect data, which is why hot storage doubles by default. ClickHouse separates the two. ReplicatedMergeTree replicates parts for durability and failover, coordinated through ClickHouse Keeper — a three-node Keeper quorum is the baseline for production — while query capacity scales independently through added replicas or, since the 24.x line, parallel replicas fanning a single query across the cluster.

For a log store, a two-replica setup per shard is typically sufficient: the cold S3 tier carries its own eleven-nines object durability, so you replicate the hot window rather than the entire 13-month retention. That asymmetry is another quiet line item in the Elasticsearch to ClickHouse cost delta.

Run the new store by measurement from day one. Three system tables answer almost every operational question a log platform raises. system.query_log gives investigation-latency percentiles — the metric that decides whether responders trust the new store:

SELECT
    toStartOfHour(event_time)                          AS hour,
    quantile(0.95)(query_duration_ms)                  AS p95_ms,
    quantile(0.99)(query_duration_ms)                  AS p99_ms,
    sum(read_bytes)                                    AS bytes_read,
    count()                                            AS queries
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind = 'Select'
  AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

system.merges tells you whether background merges keep pace with ingest — sustained merge backlog on a log table almost always traces back to undersized batches or partition-key mistakes — and system.parts carries the storage story: parts per partition, compression ratios, and the hot/cold split across volumes. Alert on parts-per-partition growth and on p95 investigation latency, not on CPU; both regressions surface days before users complain. During the dual-write window, run this telemetry side by side with the Elasticsearch cluster’s own metrics so the comparison that justifies the migration is measured, not remembered.

What Elasticsearch Still Does Better

Migrating everything is the wrong lesson, and an honest Elasticsearch to ClickHouse assessment names the workloads that should stay. Elasticsearch remains the better engine when:

• Relevance ranking is the product. BM25 scoring, analyzers, synonyms, stemming, fuzzy matching, boosting, highlighting — the machinery of ranked full-text retrieval is what Lucene has refined for two decades. ClickHouse’s text index answers “which rows contain these terms”; it does not order results by how well they match. If you are building site search, product search, or anything where result ordering is a quality metric, keep Elasticsearch (or a dedicated search engine).
• Document-centric workloads. Frequent in-place partial updates to individual documents fit a document store; ClickHouse mutations are the wrong tool for that access pattern.
• Percolation and saved-search alerting at fine grain, and teams deeply invested in the Elastic ecosystem’s security/SIEM content packs, have real switching costs that belong in the assessment.

The clean division we recommend: search stays where relevance matters; observability moves where volume matters. Logs, traces, and metrics are filter-and-aggregate workloads at enormous volume — that is ClickHouse’s shape. Ranked retrieval over curated corpora is Elasticsearch’s shape. Most organizations discover their Elasticsearch estate is 90% the former by volume and were paying search-engine prices for it.

The Elasticsearch to ClickHouse Cutover: 7 Proven Steps

Elasticsearch to ClickHouse migrations that go badly share one trait: a hard cutover date. The ones that go well share this sequence — dual-write first, move readers second, move retention last, and let evidence make every decision:

1. Audit actual usage. Every logging deployment has hundreds of saved objects and a dozen that matter. Pull the Kibana saved-object inventory and access logs; the ten dashboards and five alert rules that constitute daily usage define the migration’s real scope.
2. Design the schema and retention policy first. Sort key, partitioning, the two targeted indexes, TTL tiering — the section above. Schema retrofits on a populated log table are mutations at scale; get it right on an empty table.
3. Dual-write. Tee the pipeline — a second Kafka consumer group or one extra exporter line in the collector config. Backfill nothing: logs age out fast enough that the window closes on its own.
4. Rebuild the dashboards and alerts that matter in Grafana or ClickStack, reading from the rollup materialized views, not the raw stream.
5. Run both stores through one full incident cycle. Nothing validates a log store like a real outage: responders run the same investigation in both systems, and disagreements get root-caused before anything is decommissioned.
6. Shorten Elasticsearch retention step by step. Each reduction shrinks the old cluster and its bill; each step is trivially reversible by pausing the drawdown.
7. Decommission — or right-size to what genuinely needs relevance-ranked search. Often that is nothing; sometimes it is one important thing. Both outcomes are wins, because both were chosen on evidence.

Note the blast radius of this sequence: through step 5, ClickHouse is a shadow system — if it disappoints, you delete a consumer group and an exporter line, and nothing user-facing ever noticed. The first irreversible action is the final retention drawdown in step 7, and by then the new store has survived a real incident with its latency percentiles on record. A migration whose rollback path stays open until the last step is the only kind we are willing to run against a production observability stack.

Standing caveat: validate every schema, setting, and pipeline change in staging against a representative traffic sample before applying it to production, and keep your DR posture intact throughout the drawdown — the old cluster is your fallback until step 7 completes.

Where ChistaDATA Fits

ChistaDATA has moved observability stacks from Elasticsearch to ClickHouse end to end — schema design, pipeline migration, dashboard and alert translation — and operates the result with full-stack ClickHouse consulting, 24×7 support, and managed services. If your Elasticsearch logging bill has become a line item worth a meeting, talk to us.