How do we migrate from Elasticsearch to ClickHouse using ClickStack for logs and traces?

Most teams outgrow Elasticsearch for logs and traces the same way: dashboards creep past 5 seconds, hot nodes run at 80–90% CPU, and storage bills climb while retention shrinks. Using ClickStack to migrate that workload to ClickHouse gives you millisecond investigative queries on billions of events, with far better compression and predictable cost.

Quick Answer: You migrate from Elasticsearch to ClickHouse with ClickStack by standing up a ClickHouse-based ClickStack cluster alongside your existing Elasticsearch, dual-writing logs/traces into both, backfilling historical data into ClickHouse, then gradually cutting queries and UIs over. The key steps are: define target schemas in ClickHouse, set up ingestion (agents, ClickPipes, or connectors) into ClickStack, validate query parity, and finally decommission Elasticsearch indices once retention windows overlap.

Why This Matters

Elasticsearch was designed as a text search engine and can work well for smaller log volumes, but it becomes expensive and slow when you’re handling high-cardinality labels, dense metrics, and trace spans at scale. ClickHouse, especially when used via ClickStack, is built for analytical workloads: columnar storage, vectorized execution, and aggressive compression give you millisecond queries on billions of rows—even under concurrency.

Migrating with ClickStack instead of building your own logging stack from scratch lets you keep the familiar “logs/metrics/traces” mental model while swapping in a database that’s optimized for OLAP and observability.

Key Benefits:

• Blazing fast investigations: Sub-second queries on high-cardinality fields and long time ranges, thanks to columnar storage and vectorized execution in ClickHouse.
• Cost-effective retention: Best-in-class compression and efficient OLAP scans let you keep more days/weeks of logs and traces on the same or lower budget.
• Observability-native tooling: ClickStack wraps ClickHouse with the pieces you expect (ingestion, schema, example dashboards) so your migration path from Elasticsearch is structured rather than ad hoc.

Core Concepts & Key Points

Concept	Definition	Why it's important
ClickStack	A ClickHouse-based observability stack for logs, metrics, and traces that provides schemas, ingestion patterns, and query examples on top of ClickHouse.	Gives you a reference architecture for replacing Elasticsearch with ClickHouse without reinventing ingestion, schema, and dashboards.
Dual-writing	Temporarily sending logs and traces to both Elasticsearch and ClickHouse during migration.	Lets you validate queries, alerting, and dashboards in ClickHouse before cutting over, with no data loss risk.
MergeTree health	The state of your ClickHouse storage engine based on part counts, merge activity, and ingest patterns.	Healthy MergeTrees (well-batched inserts, controlled partitions) are what keep ClickHouse fast at petabyte scale; ignoring them leads to “Too many parts” and degraded performance.

How It Works (Step-by-Step)

At a high level, a safe migration from Elasticsearch to ClickHouse using ClickStack looks like this:

1. Stand up ClickHouse (Cloud, OSS, or Local for development) and deploy ClickStack for logs/traces.
2. Design schemas that map Elasticsearch index fields to ClickHouse tables (e.g., log events, spans).
3. Configure agents or pipelines (Vector, Fluent Bit, OpenTelemetry Collector, ClickPipes, etc.) to dual-write to Elasticsearch and ClickHouse.
4. Backfill historical data from Elasticsearch into ClickHouse.
5. Validate query parity, dashboards, and alerts.
6. Gradually shift read traffic to ClickStack and then turn off Elasticsearch writes.

Below is a more operational walkthrough from that sequence.

1. Choose your ClickHouse deployment

Deploy your way based on your constraints:

• ClickHouse Cloud

  • Best when you want managed operations: automatic scaling, backups, and cluster management handled for you.
  • Good fit for production logs/traces at scale, especially when you don’t want to run your own stateful infrastructure.
  • Use ClickStack against a Cloud service to get a fully managed observability backend.

• Open-source ClickHouse (self-managed)

  • Best when you have an existing Kubernetes or bare-metal footprint and need full control or air-gapped deployments.
  • You’ll manage replicas, disks, backups, and upgrades yourself.
  • Ideal for consolidating internal analytics plus observability on a shared cluster.

• ClickHouse Local

  • Run queries against local Parquet/CSV dumps of your Elasticsearch data for prototyping schema, queries, and transformations.
  • No server; useful during early design or while testing transformations of historical indices.

In practice, many teams prototype with ClickHouse Local or a small OSS cluster, then stand up ClickHouse Cloud as the production home for logs and traces.

2. Design your target schemas in ClickHouse

With Elasticsearch, your schema often “emerges” from index templates and dynamic mappings. ClickHouse is SQL-first, so you’ll want explicit schemas.

A minimal log table in ClickStack style might look like:

CREATE TABLE logs
(
    ts             DateTime64(3, 'UTC'),
    level          LowCardinality(String),
    service_name   LowCardinality(String),
    logger         LowCardinality(String),
    trace_id       UUID,
    span_id        UUID,
    message        String,
    attributes     Map(String, String),
    host           LowCardinality(String),
    k8s_namespace  LowCardinality(String),
    k8s_pod        String,
    ingestion_time DateTime64(3, 'UTC') DEFAULT now64(3)
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (service_name, ts, trace_id);

Key decisions that differ from Elasticsearch:

• Partitioning

  • Follow ClickHouse guidance: partition primarily for lifecycle/retention, not per-tenant or per-service.
  • Common pattern: PARTITION BY toDate(ts) or toYYYYMM(ts) for logs and traces.
  • Avoid high-cardinality partitions (e.g., per service_name), which can explode part counts and slow merges.

• Ordering key

  • Use fields you filter/sort by most often: typically (service_name, ts) or (ts, service_name) plus trace_id.
  • Ordering drives how data is stored on disk and scanned; it’s the closest analog to your Elasticsearch index’s primary sort.

• LowCardinality and compression

  • Use LowCardinality(String) for labels/tags like service_name, level, host to leverage dictionary encoding and compression.
  • This is a big part of why ClickHouse can keep more data with less storage than Elasticsearch.

If you’re migrating traces, a typical spans table might look like:

CREATE TABLE spans
(
    ts_start       DateTime64(6, 'UTC'),
    ts_end         DateTime64(6, 'UTC'),
    trace_id       UUID,
    span_id        UUID,
    parent_span_id UUID,
    service_name   LowCardinality(String),
    operation      LowCardinality(String),
    status         LowCardinality(String),
    attributes     Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toDate(ts_start)
ORDER BY (service_name, ts_start, trace_id, span_id);

3. Wire ingestion: dual-writing from your existing pipeline

Most Elasticsearch-based log/trace stacks ingest via:

• Beats/Fluentd/Fluent Bit
• Vector
• OpenTelemetry Collector
• Custom services that write directly to Elasticsearch

To migrate with minimal disruption:

1. Enable dual-write on your agents (where possible):

   • Add ClickHouse as a second sink in your log/trace agents. For example, in Vector:

     [sinks.clickhouse_logs]
     type = "clickhouse"
     inputs = ["kubernetes_logs"]
     endpoint = "https://<your-clickhouse-host>"
     database = "default"
     table = "logs"
     compression = "lz4"
     encoding.codec = "json"
     

   • Keep the existing Elasticsearch sink active while you validate.

2. Use ClickStack ingestion presets where available:

   • ClickStack gives you reference configurations for logs and traces (e.g., OTLP over HTTP/gRPC to an ingest gateway that writes into ClickHouse).
   • This keeps your OpenTelemetry or logging agent config focused on standard protocols, not vendor-specific details.

3. Batch smartly to keep MergeTree healthy:

   • Target 1,000–100,000 rows per insert into ClickHouse.
   • Avoid a pattern where each log line is inserted individually; this creates too many small parts and triggers “Too many parts” errors.
   • If you’re using async batching sinks (like Vector), tune buffer sizes/timeouts to meet this.

Monitor system.parts as you ramp up:

SELECT
    table,
    count() AS parts,
    sum(rows) AS rows,
    sum(bytes_on_disk) AS bytes
FROM system.parts
WHERE database = 'default' AND table IN ('logs', 'spans') AND active
GROUP BY table;

If you see parts per table climbing into the tens of thousands under steady state, adjust batching and/or partitioning before scaling further.

4. Backfill historical data from Elasticsearch

You don’t have to move all history, but most teams want at least a few weeks/months migrated so that they can shut down hot Elasticsearch indices quickly.

Common backfill patterns:

• Export from Elasticsearch to files, then load with ClickHouse:

  • Use scroll/search_after or snapshot APIs to dump NDJSON/Parquet to object storage.

  • Use ClickHouse’s file engines or ClickPipes to ingest:

    INSERT INTO logs
    SELECT
        parseDateTimeBestEffort(timestamp) AS ts,
        level,
        service_name,
        logger,
        trace_id,
        span_id,
        message,
        attributes,
        host,
        k8s_namespace,
        k8s_pod
    FROM s3(
        'https://bucket/path/logs-*.ndjson',
        'AWS_ACCESS_KEY_ID',
        'AWS_SECRET_ACCESS_KEY',
        'JSONEachRow'
    );
    

• Stream via intermediate tooling (Kafka, Debezium-style CDC, etc.):

  • For organizations already using Kafka-based pipelines, you can replay topics into ClickHouse using a Kafka engine table or a connector, while Elasticsearch still reads from the same topics.

Keep in mind:

• Use larger batch sizes for historical backfills (100k–1M rows per insert) where possible; they compress and merge more efficiently.
• Run backfills during lower-traffic hours or in a separate ClickHouse cluster if your production queries are latency-sensitive.

5. Validate queries, dashboards, and alerts

Before you cut over, prove to yourself that ClickStack + ClickHouse answers the same questions your Elasticsearch-backed systems do, just faster and cheaper.

Focus on:

• Query parity:

  • Rewrite your top N Kibana/Lens queries into SQL. For example, a frequency count of errors by service:

    SELECT
        service_name,
        count() AS error_count
    FROM logs
    WHERE ts >= now() - INTERVAL 1 HOUR
      AND level = 'error'
    GROUP BY service_name
    ORDER BY error_count DESC
    LIMIT 50;
    

  • Use system.query_log to measure real latency:

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

• Trace exploration:

  • Recreate “find this trace ID, then expand spans” flows using joins on trace_id and span_id.
  • Ensure span trees render correctly from the ClickStack UI or your trace UI using ClickHouse as the backend.

• Alerting parity:

  • Re-implement key alerts—error rate thresholds, latency SLOs, traffic drops—against ClickHouse queries.
  • Validate that they fire at similar times and with similar sensitivity compared to Elasticsearch.

Use this phase to tune:

• max_threads, max_memory_usage, and max_bytes_before_external_group_by so heavy aggregations don’t interfere with low-latency queries.
• async_insert and related settings if you want to decouple client latency from ingest durability under spikes.

6. Cut over traffic and retire Elasticsearch

Once ClickHouse has at least the same retention as your Elasticsearch hot indices and you’re satisfied with query parity:

1. Switch read paths

   • Point your dashboards (Grafana, custom UIs, internal tools) to ClickHouse/ClickStack instead of Elasticsearch.
   • Keep Elasticsearch available read-only for a short overlap window.

2. Turn off Elasticsearch writes

   • Update ingestion agents to remove the Elasticsearch sink; keep only the ClickHouse/ClickStack path.

   • Monitor ingest throughput and MergeTree health in system.merges and system.parts:

     SELECT
         table,
         count() AS active_merges,
         sum(total_size_bytes_compressed) AS bytes_merging
     FROM system.merges
     WHERE database = 'default'
     GROUP BY table;
     

3. Decommission or shrink Elasticsearch

   • Drop or close old indices once you’re comfortable there are no remaining dependencies.
   • Scale down the cluster or turn it off, redirecting the infrastructure budget to ClickHouse where it buys more retention and lower latency.

Common Mistakes to Avoid

• Treating partitioning as a query optimization trick

  • Partitioning in ClickHouse is primarily for lifecycle and data management, not a magic way to speed up arbitrary queries.
  • Avoid per-service or per-tenant partitions for logs/traces unless you have a very small, fixed set of tenants; they explode part counts and make merges expensive. Stick to date-based partitions for most observability workloads.

• Inserting one log/trace per request

  • This is the fastest way to hit “Too many parts” and degraded performance in MergeTree tables.
  • Always batch: let agents or collectors accumulate events (at least 1,000 rows per insert, ideally 10,000–100,000) before flushing to ClickHouse.

Real-World Example

When we migrated a high-cardinality logs and traces backend from Elasticsearch to ClickHouse, our Elasticsearch cluster was running hot: P95 investigative queries over 24 hours of logs routinely took 5–10 seconds, and a week of retention on hot nodes was already pushing disk limits. We introduced ClickStack on a new ClickHouse Cloud cluster and dual-wrote logs/traces from our Kubernetes workloads via Vector.

We designed a single logs table partitioned by day and ordered by (service_name, ts, trace_id), and a spans table for traces using similar patterns. For ingest, we tuned Vector’s batch size to ~10k events per insert, monitoring system.parts to keep active part counts in the low thousands. Historical backfill pulled the last 30 days of Elasticsearch indices into S3 as NDJSON and then into ClickHouse via s3() and INSERT SELECT.

Within a week, our core investigative queries—like “all 5xx responses by endpoint over the last 12 hours”—dropped from ~6 seconds in Kibana to under 200 ms in ClickHouse, even as we extended log retention from 7 to 30 days. Once we validated alerting parity and trace exploration flows, we switched dashboards to query ClickHouse and turned off Elasticsearch writes, ultimately decommissioning half of the original Elasticsearch nodes.

Pro Tip: During dual-write and early cutover, watch system.query_log and system.parts together—if latency starts creeping up while part counts grow, fix your batching before adding more tenants or extending retention. It’s much easier to correct ingestion patterns early than to untangle a fragmented MergeTree at petabyte scale.

Summary

Migrating from Elasticsearch to ClickHouse using ClickStack for logs and traces is a staged process: deploy ClickHouse (Cloud or self-managed), design OLAP-friendly schemas, dual-write from your existing agents, backfill historical Elasticsearch indices, validate query and alert parity, then switch dashboards and decommission Elasticsearch. ClickHouse’s columnar storage, vectorized execution, and compression give you millisecond queries and longer retention on the same budget—but you unlock that by respecting MergeTree fundamentals: sensible partitioning, well-chosen ordering keys, and batched inserts.

If you follow these patterns and keep an eye on system tables like system.parts, system.merges, and system.query_log, the migration can be incremental and low-risk while delivering a major upgrade in performance and cost profile for your observability stack.

Next Step

Get Started