How can we correlate logs, metrics, and traces across services without paying a fortune or losing query performance?

Quick Answer: You correlate logs, metrics, and traces efficiently by putting them on a single, high‑throughput analytical backend, normalizing identifiers (service, trace, span, tenant), and querying them with the same engine and language. With ClickHouse + ClickStack, you get millisecond joins across billions of events while using compression and columnar storage to keep costs under control.

Why This Matters

When your incidents span dozens of microservices, “just add more dashboards” stops working. What teams really need is to pivot across logs, metrics, and traces in one place—jumping from a spike in error rate to the exact traces and log lines in seconds, not minutes. Traditional observability stacks often make this either operationally fragile (indexes and hot shards) or financially painful (per‑GB ingest and per‑seat query pricing). A unified, columnar backend like ClickHouse lets you scale to petabytes of telemetry and still get millisecond queries, so you can investigate faster without throttling data or deleting history just to save money.

Key Benefits:

• End‑to‑end correlation: Join logs, metrics, and traces on shared IDs (service, trace_id, pod, customer) with simple SQL instead of stitching tools together.
• Millisecond queries at scale: Analyze billions of rows across telemetry types while keeping dashboards and ad‑hoc investigations fast.
• Cost‑efficient retention: Use columnar storage and compression to store more history (90–180 days+), reducing the pressure to sample or drop data.

Core Concepts & Key Points

Concept	Definition	Why it's important
Unified telemetry backend	Storing logs, metrics, and traces in the same analytical database (e.g., ClickHouse via ClickStack).	Eliminates cross‑system hops and enables direct SQL joins across telemetry types in real time.
Correlation identifiers	Stable fields like trace_id, span_id, service, pod, tenant_id, request_id emitted consistently across signals.	These IDs are the “join keys” that make cheap, fast correlation possible without complex heuristics.
Columnar OLAP for observability	Using a column‑oriented, vectorized, compressed engine (ClickHouse) for time‑series telemetry.	Handles high‑cardinality, high‑volume data with millisecond queries and much lower storage cost than row‑stores or index‑heavy search engines.

How It Works (Step‑by‑Step)

At a high level, you: (1) standardize telemetry schemas, (2) land all signals in ClickHouse (via ClickStack or your own pipelines), and (3) query everything with SQL. The goal is to transform “logs vs metrics vs traces” into “three tables you can join.”

1. Standardize your telemetry model

   Before you ingest a single event, decide on shared fields and naming:

   • Required across logs, metrics, traces:
     • timestamp
     • service_name
     • env (prod, staging)
     • cluster / region
     • tenant_id or customer_id (if multi‑tenant)
   • For correlation:
     • trace_id, span_id (for logs & traces)
     • http_target, rpc_method, db_system, etc.
     • k8s_pod, k8s_node, host, instance

   Example (JSON log):

   {
     "timestamp": "2026-04-12T08:01:23.456Z",
     "service_name": "payments-api",
     "env": "prod",
     "cluster": "us-central-1",
     "trace_id": "0f23b4d92f...",
     "span_id": "a91c0e2b3d...",
     "tenant_id": "merchant_123",
     "level": "ERROR",
     "message": "Charge failed: card declined",
     "error_code": "card_declined"
   }
   

   Those shared keys give you simple, cheap JOINs later.

2. Ingest logs, metrics, and traces into ClickHouse

   You can either use:

   • ClickStack (ClickHouse‑powered observability stack):
     An open‑source stack for logs, metrics, and traces built on ClickHouse. It gives you:

     • Ingestion from common agents/collectors (e.g., OpenTelemetry).
     • Predefined MergeTree schemas tuned for observability.
     • Out‑of‑the‑box dashboards and correlation flows.

   • Custom pipelines into ClickHouse Cloud or OSS ClickHouse:

     • Use OTLP exporters → Collector → ClickHouse.
     • Or send JSON/Protobuf logs directly via HTTP or Native protocol.
     • For ad‑hoc or local testing, use ClickHouse Local to query files without a running server.

   Example: a simplified table for logs (MergeTree):

   CREATE TABLE logs
   (
       timestamp     DateTime64(3),
       service_name  LowCardinality(String),
       env           LowCardinality(String),
       cluster       LowCardinality(String),
       tenant_id     String,
       trace_id      String,
       span_id       String,
       level         LowCardinality(String),
       message       String,
       error_code    LowCardinality(String)
   )
   ENGINE = MergeTree
   PARTITION BY toDate(timestamp)
   ORDER BY (service_name, env, timestamp);
   

   And for spans (traces):

   CREATE TABLE spans
   (
       timestamp_start DateTime64(3),
       timestamp_end   DateTime64(3),
       trace_id        String,
       span_id         String,
       parent_span_id  String,
       service_name    LowCardinality(String),
       env             LowCardinality(String),
       cluster         LowCardinality(String),
       tenant_id       String,
       name            String,
       status_code     LowCardinality(String)
   )
   ENGINE = MergeTree
   PARTITION BY toDate(timestamp_start)
   ORDER BY (service_name, env, timestamp_start, trace_id);
   

   Metrics typically land in a schema like:

   CREATE TABLE metrics
   (
       timestamp     DateTime64(3),
       service_name  LowCardinality(String),
       env           LowCardinality(String),
       cluster       LowCardinality(String),
       tenant_id     String,
       metric_name   LowCardinality(String),
       value         Float64,
       attributes    Map(String, String)
   )
   ENGINE = MergeTree
   PARTITION BY toDate(timestamp)
   ORDER BY (service_name, env, metric_name, timestamp);
   

   Design notes from production:

   • Partition by date, not by high‑cardinality fields like tenant_id or service_name. Partitioning is primarily for lifecycle (retention) and data management, not for micro‑optimizing queries.
   • Keep partitions coarse (daily is a good default) to avoid “Too many parts” and expensive merges.
   • Use LowCardinality for tags/labels with limited distinct values (environment, region, level).

3. Query and correlate with millisecond SQL

   Once data lands in ClickHouse, correlation becomes a few SQL statements instead of a new product.

   • Metrics → traces:

     -- Find traces during an error-rate spike
     WITH
       toDateTime('2026-04-12 08:00:00') AS t_start,
       toDateTime('2026-04-12 08:05:00') AS t_end
     SELECT
         s.trace_id,
         s.service_name,
         s.name,
         s.status_code,
         s.timestamp_start
     FROM metrics m
     INNER JOIN spans s
         ON m.trace_id = s.trace_id
     WHERE
         m.metric_name = 'http_server_requests_seconds_count'
         AND m.env = 'prod'
         AND m.service_name = 'payments-api'
         AND m.timestamp BETWEEN t_start AND t_end
         AND m.attributes['status_code'] = '500'
     LIMIT 100;
     

   • Traces → logs:

     -- Given a trace, show all associated logs
     SELECT
         l.timestamp,
         l.service_name,
         l.level,
         l.message
     FROM logs l
     WHERE l.trace_id = '0f23b4d92f...'
     ORDER BY l.timestamp;
     

   • Metrics → logs (without trace IDs):

     -- Correlate latency spike with error logs by tenant & pod
     WITH
       toDateTime('2026-04-12 08:00:00') AS t_start,
       toDateTime('2026-04-12 08:10:00') AS t_end
     SELECT
         l.timestamp,
         l.service_name,
         l.tenant_id,
         l.k8s_pod,
         l.level,
         l.message
     FROM metrics m
     INNER JOIN logs l
         ON m.tenant_id = l.tenant_id
        AND m.attributes['k8s_pod'] = l.k8s_pod
        AND l.timestamp BETWEEN m.timestamp - INTERVAL 5 SECOND
                             AND m.timestamp + INTERVAL 5 SECOND
     WHERE
         m.metric_name = 'http_request_duration_seconds_bucket'
         AND m.attributes['le'] = '1.0'
         AND m.value > 0  -- slow bucket
         AND m.env = 'prod'
         AND m.service_name = 'checkout-api'
         AND m.timestamp BETWEEN t_start AND t_end
     LIMIT 200;
     

   Under the hood, ClickHouse’s columnar storage and vectorized execution scan only the needed columns and compress the rest. That’s why even multi‑billion‑row joins can stay in the tens of milliseconds on modern hardware.

Common Mistakes to Avoid

• Treating logs, metrics, and traces as three different products

  If logs live in a search engine, metrics in a TSDB, and traces in a SaaS APM, correlation means “click the integration button and hope data arrives.” This adds latency, cardinality limits, and per‑product pricing.

  How to avoid it:
  Standardize your schema around ClickHouse (or ClickStack) as the unified backend. Use OpenTelemetry to emit consistent IDs and attributes and send all three signal types through a single ingestion path.

• Over‑partitioning and tiny inserts

  It’s tempting to partition by service_name, tenant_id, or cluster to “speed queries up.” In practice, that explodes the number of parts, makes merges expensive, and slows queries due to many small files. Frequent tiny inserts (e.g., every second per pod) compound the problem and lead to “Too many parts” errors.

  How to avoid it:

  • Partition by a time function (toDate(timestamp) is a solid default).

  • Batch inserts to at least 1,000 rows, ideally 10,000–100,000 rows per insert.

  • Monitor system.parts and system.merges to ensure the number of active parts per partition stays reasonable and merges keep up:

    -- Check parts per table
    SELECT
        table,
        partition,
        count(*) AS parts
    FROM system.parts
    WHERE active
    GROUP BY table, partition
    ORDER BY parts DESC
    LIMIT 20;
    

    -- See if merges are lagging
    SELECT
        database,
        table,
        result_part_name,
        partition_id,
        elapsed,
        progress
    FROM system.merges
    ORDER BY elapsed DESC
    LIMIT 10;
    

Real‑World Example

On one migration, we replaced an Elasticsearch + Prometheus + vendor APM setup with ClickHouse Cloud as the single backend for logs, metrics, and traces. The original system:

• Breached its storage budget at ~90 days of history for logs and metrics.
• Needed heavy sampling to keep APM trace costs under control.
• Delivered “slow query” dashboards in 5–15 seconds during incidents.

We moved to:

• ClickStack for ingesting OTEL telemetry into ClickHouse.
• Standardized fields (service_name, env, trace_id, tenant_id, k8s_pod) across all signals.
• ClickHouse MergeTree tables partitioned daily, batched inserts at ~50,000 rows per batch.

Operational changes:

• Engineers pivoted from a latency SLO burn‑rate metric to the exact slow traces in seconds, then to correlated logs with a single SQL query.
• Investigative dashboards dropped from 5+ seconds to well under 500 ms even at peak ingest.
• Storage costs fell enough that we comfortably kept 180 days of logs and 365 days of metrics, with unsampled traces for key services.

Pro Tip: Start by making one high‑value workflow (e.g., “from 500 error rate spike → traces → logs in < 10 seconds”) excellent. Design your schemas, IDs, and indexes around that flow first, then generalize. It forces you to choose the right correlation keys instead of collecting everything “just in case.”

Summary

Correlating logs, metrics, and traces across services without blowing your budget or sacrificing performance comes down to three decisions:

1. Use a unified, columnar backend like ClickHouse (or ClickStack) instead of three separate products.
2. Normalize correlation IDs and tags across all signals so simple SQL joins answer the hard questions.
3. Operate ClickHouse with observability‑friendly practices: time‑based partitioning, large batched inserts, and active monitoring via system.parts and system.merges.

With this setup, you can run millisecond investigative queries over billions of events, keep months of history online, and power real‑time debugging for agentic systems, microservices, and high‑cardinality workloads—without paying SaaS‑style penalties for every new signal or tag.

Next Step

Get Started