Replacing Elasticsearch with ClickHouse : A 90% Cost-Reduction Migration

Keep OpenTelemetry (OTEL) as the instrumentation standard — zero application changes.
Change only the destination: swap Elasticsearch exporter → ClickHouse exporter in OTEL Collector.

All logs, traces, and metrics land in ClickHouse.
Grafana reads ClickHouse for dashboards and alerts.
AI agent queries logs/traces in plain English via the same LibreChat + MCP platform.

Application
    │
    ▼ OTEL SDK (instrumented)
OTEL Collector
    │
    ▼ Elasticsearch Exporter
Elasticsearch Cluster
    │
    ▼
Kibana                   ← only UI, no programmatic access

-- This query is IMPOSSIBLE in Elasticsearch:
-- "Which users were affected by the 500 errors on trading-service between 14:00-14:30?"

SELECT DISTINCT l.LogAttributes['user_id'] AS user_id,
       u.kyc_status,
       count() AS error_count
FROM otel_logs l
JOIN mart_users u ON l.LogAttributes['user_id'] = u.username
WHERE l.ServiceName = 'trading-service'
  AND l.SeverityText = 'ERROR'
  AND l.Timestamp BETWEEN '2026-04-17 14:00:00' AND '2026-04-17 14:30:00'
GROUP BY user_id, u.kyc_status
ORDER BY error_count DESC;

                    +------------------------------------------+
                    |         CRYPTO EXCHANGE APPS             |
                    |                                          |
                    | Trading Service  Wallet Service          |
                    | Risk Service     User Service            |
                    | API Gateway      Background Workers      |
                    +-------------------+----------------------+
                                        |
                           OTEL SDK (zero app code change)
                           Auto-instruments: HTTP, DB, Kafka, Redis
                                        |
                    +-------------------v----------------------+
                    |           OTEL COLLECTOR                 |
                    |                                          |
                    |  Receivers: OTLP gRPC/HTTP               |
                    |  Processors: batch, resource, filter     |
                    |  Exporters: clickhouseexporter           |
                    +-------------------+----------------------+
                                        |
                    +-------------------v----------------------+
                    |           CLICKHOUSE CLUSTER             |
                    |                                          |
                    |  database: otel                          |
                    |  ┌─────────────┬──────────┬──────────┐   |
                    |  │ otel_logs   │otel_traces│otel_metrics│|
                    |  │ (Logs)      │(Traces)  │(Metrics) │   |
                    |  └─────────────┴──────────┴──────────┘   |
                    |                                          |
                    |  database: marts (business data)         |
                    |  ┌────────────────────────────────────┐  |
                    |  │ mart_trades  mart_users  mart_wallets│|
                    |  └────────────────────────────────────┘  |
                    +---+------------------+-------------------+
                        |                  |
           +------------v---+     +--------v-----------+
           |    GRAFANA     |     |   LIBRECHAT + LLM  |
           |                |     |   (AI queries over |
           | Dashboards     |     |   logs in plain    |
           | Trace Viewer   |     |   English)         |
           | Log Explorer   |     |                    |
           | Alerts         |     |                    |
           +----------------+     +--------------------+

App → OTEL Collector (on same host) → ClickHouse directly

App → OTEL Agent (lightweight, on each host)
          │
          ▼
    OTEL Aggregator (central, 1–2 instances)
    - batches writes
    - retries on ClickHouse failure
    - filters/transforms before storage
          │
          ▼
    ClickHouse

# OTEL Agent config (on each service host — minimal, just forwards)
exporters:
  otlp:
    endpoint: otel-aggregator.internal:4317   # sends to aggregator, not ClickHouse

# OTEL Aggregator config (central — does batching, retry, filtering)
processors:
  batch:
    timeout: 10s
    send_batch_size: 50000
  retry_on_failure:
    enabled: true
    initial_interval: 5s
    max_elapsed_time: 300s

exporters:
  clickhouse:
    endpoint: tcp://clickhouse.internal:9000
    database: otel
    compress: lz4
    timeout: 10s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_elapsed_time: 300s

Application Code → OTEL SDK → OTEL Collector → [ANY BACKEND]
                                                   ↑
                              Swap this: Elasticsearch → ClickHouse

# otel-collector-config.yaml

receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

processors:
  batch:
    timeout: 5s
    send_batch_size: 10000
  memory_limiter:
    limit_mib: 512
  resource:
    attributes:
      - key: environment
        value: production
        action: upsert

exporters:
  # ─── BEFORE (Elasticsearch) ───────────────────────────────
  # elasticsearch:
  #   endpoints: [https://elastic-cluster:9200]
  #   logs_index: logs-%{+yyyy.MM.dd}
  #   traces_index: traces-%{+yyyy.MM.dd}

  # ─── AFTER (ClickHouse) ────────────────────────────────────
  clickhouse:
    endpoint: tcp://clickhouse:9000
    database: otel
    logs_table_name:    otel_logs
    traces_table_name:  otel_traces
    metrics_table_name: otel_metrics
    ttl:     90               # days — auto-delete old data
    compress: lz4
    timeout:  5s
    retry_on_failure:
      enabled:           true
      initial_interval:  5s
      max_interval:      30s
      max_elapsed_time:  300s

service:
  pipelines:
    logs:
      receivers:  [otlp]
      processors: [memory_limiter, batch, resource]
      exporters:  [clickhouse]
    traces:
      receivers:  [otlp]
      processors: [memory_limiter, batch, resource]
      exporters:  [clickhouse]
    metrics:
      receivers:  [otlp]
      processors: [memory_limiter, batch, resource]
      exporters:  [clickhouse]

# Add to JVM startup — auto-instruments HTTP, JDBC, Kafka, Redis
java -javaagent:opentelemetry-javaagent.jar \
     -Dotel.service.name=trading-service \
     -Dotel.exporter.otlp.endpoint=http://otel-collector:4317 \
     -Dotel.traces.exporter=otlp \
     -Dotel.metrics.exporter=otlp \
     -Dotel.logs.exporter=otlp \
     -jar trading-service.jar

# Add 3 lines at startup — auto-instruments HTTP, PostgreSQL, Redis
from opentelemetry.instrumentation.fastapi import FastAPIInstrumentor
from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor
from opentelemetry.instrumentation.requests import RequestsInstrumentor

FastAPIInstrumentor.instrument_app(app)
Psycopg2Instrumentor().instrument()
RequestsInstrumentor().instrument()

// tracing.js — load before anything else via --require
require('@opentelemetry/auto-instrumentations-node/register');
// Auto-instruments: express, pg, http, redis, mongoose, kafka

# fluent-bit.yaml — ships K8s pod logs directly to ClickHouse
[OUTPUT]
    Name          clickhouse
    Match         *
    Host          clickhouse.internal
    Port          8123
    Database      otel
    Table         fluent_logs
    # Critical: async inserts prevent too-small-batch errors
    async_insert  1
    flush         10s

-- Custom schema for Fluent Bit (33x compression)
CREATE TABLE otel.fluent_logs
(
    timestamp               DateTime64(9)             CODEC(Delta, ZSTD(1)),
    pod_name                LowCardinality(String),
    namespace               LowCardinality(String),
    container_name          LowCardinality(String),
    severity                LowCardinality(String),
    message                 String                    CODEC(ZSTD(1)),
    kubernetes_labels       Map(LowCardinality(String), String) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (namespace, pod_name, timestamp)
TTL toDateTime(timestamp) + INTERVAL 90 DAY;

CREATE TABLE otel.otel_logs (
    Timestamp           DateTime64(9)              CODEC(Delta, ZSTD(1)),
    TraceId             String                     CODEC(ZSTD(1)),
    SpanId              String                     CODEC(ZSTD(1)),
    TraceFlags          UInt32,
    SeverityText        LowCardinality(String),    -- ERROR, WARN, INFO, DEBUG
    SeverityNumber      Int32,
    ServiceName         LowCardinality(String),
    Body                String                     CODEC(ZSTD(1)),
    ResourceSchemaUrl   String                     CODEC(ZSTD(1)),
    ResourceAttributes  Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ScopeSchemaUrl      String                     CODEC(ZSTD(1)),
    ScopeName           String                     CODEC(ZSTD(1)),
    ScopeVersion        String                     CODEC(ZSTD(1)),
    ScopeAttributes     Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    LogAttributes       Map(LowCardinality(String), String) CODEC(ZSTD(1)),

    -- Skip indexes for fast filtering (replaces Elasticsearch inverted index for common patterns).
    -- Body uses tokenbf_v1 here as the safe default — it's stable, no flags, works on every CH 23+.
    -- See §8 for the newer `text` index, which is the recommended upgrade once you're on a recent
    -- ClickHouse build (better selectivity, S3-friendly). You'd swap idx_body for the text index there.
    INDEX idx_trace_id   TraceId     TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_body       Body        TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
    INDEX idx_service    ServiceName TYPE set(100)             GRANULARITY 4,
    INDEX idx_severity   SeverityText TYPE set(10)             GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, toUnixTimestamp(Timestamp))
TTL toDateTime(Timestamp) + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

CREATE TABLE otel.otel_traces (
    Timestamp           DateTime64(9)              CODEC(Delta, ZSTD(1)),
    TraceId             String                     CODEC(ZSTD(1)),
    SpanId              String                     CODEC(ZSTD(1)),
    ParentSpanId        String                     CODEC(ZSTD(1)),
    TraceState          String                     CODEC(ZSTD(1)),
    SpanName            LowCardinality(String),
    SpanKind            LowCardinality(String),    -- SERVER, CLIENT, PRODUCER, CONSUMER
    ServiceName         LowCardinality(String),
    ResourceAttributes  Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    SpanAttributes      Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    Duration            Int64,                     -- nanoseconds
    StatusCode          LowCardinality(String),    -- STATUS_CODE_OK, STATUS_CODE_ERROR
    StatusMessage       String,
    Events              Nested (
        Timestamp       DateTime64(9),
        Name            LowCardinality(String),
        Attributes      Map(LowCardinality(String), String)
    ),
    Links               Nested (
        TraceId         String,
        SpanId          String,
        TraceState      String,
        Attributes      Map(LowCardinality(String), String)
    ),

    INDEX idx_trace_id   TraceId     TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_span_name  SpanName    TYPE set(50)             GRANULARITY 4,
    INDEX idx_service    ServiceName TYPE set(50)             GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp))
TTL toDateTime(Timestamp) + INTERVAL 90 DAY;

CREATE TABLE otel.otel_metrics_sum (
    ResourceAttributes      Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ResourceSchemaUrl       String CODEC(ZSTD(1)),
    ScopeName               String CODEC(ZSTD(1)),
    ScopeVersion            String CODEC(ZSTD(1)),
    ScopeAttributes         Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ScopeDroppedAttrCount   UInt32 CODEC(ZSTD(1)),
    ScopeSchemaUrl          String CODEC(ZSTD(1)),
    ServiceName             LowCardinality(String) CODEC(ZSTD(1)),   -- materialized from ResourceAttributes['service.name']
    MetricName              LowCardinality(String) CODEC(ZSTD(1)),
    MetricDescription       String CODEC(ZSTD(1)),
    MetricUnit              String CODEC(ZSTD(1)),
    Attributes              Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    StartTimeUnix           DateTime64(9) CODEC(Delta, ZSTD(1)),
    TimeUnix                DateTime64(9) CODEC(Delta, ZSTD(1)),
    Value                   Float64 CODEC(ZSTD(1)),
    Flags                   UInt32 CODEC(ZSTD(1)),
    Exemplars               Nested (
        FilteredAttributes  Map(LowCardinality(String), String),
        TimeUnix            DateTime64(9),
        Value               Float64,
        SpanId              String,
        TraceId             String
    ) CODEC(ZSTD(1)),
    AggregationTemporality  Int32 CODEC(ZSTD(1)),    -- 1=Delta, 2=Cumulative
    IsMonotonic             Bool CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(TimeUnix)
ORDER BY (ServiceName, MetricName, Attributes, toUnixTimestamp(TimeUnix))
TTL toDateTime(TimeUnix) + INTERVAL 90 DAY
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

Browser → [traceparent header injected]
              │
              ▼
[1] Nginx               → Span: http.server (method, url, status, duration)
              │           propagates traceparent to upstream
              ▼
[2] API Gateway         → Span: route handling
              │
              ▼
[3] Trading Service     → Span: business logic
              │
        ┌─────┴──────┐
        ▼             ▼
[4] PostgreSQL      [5] ClickHouse    → Span: db.query (SQL text, duration, rows)
(OTEL JDBC auto)    (native OTEL)
        │
        ▼
[6] Kafka Producer      → Span: messaging.publish (topic, partition)

All spans → OTEL Collector → ClickHouse otel_traces table
                                         │
                                         ▼
                                   Grafana Trace UI
                                   (full waterfall)

# nginx.conf
load_module modules/ngx_otel_module.so;

http {
    otel_exporter {
        endpoint otel-collector:4317;
    }
    otel_service_name  "api-gateway";
    otel_trace         on;
    otel_trace_context propagate;    # injects traceparent into upstream request

    server {
        location /api/ {
            otel_trace      on;
            otel_span_name  "$request_method $uri";
            otel_span_attr  http.method  $request_method;
            otel_span_attr  http.url     $uri;
            otel_span_attr  http.status  $status;
            proxy_pass      http://backend;
        }
    }
}

from opentelemetry import trace
from opentelemetry.propagate import inject
import clickhouse_connect

tracer = trace.get_tracer("trading-service")

def get_user_risk_profile(username: str):
    with tracer.start_as_current_span("clickhouse.mart_user_risk_profile") as span:
        span.set_attribute("db.system",    "clickhouse")
        span.set_attribute("db.name",      "marts")
        span.set_attribute("db.operation", "SELECT")
        span.set_attribute("db.statement",
            "SELECT * FROM mart_user_risk_profile WHERE username = ?")

        # Inject current trace context into ClickHouse HTTP headers.
        # IMPORTANT: clickhouse_connect caches `http_headers` on the client object — they
        # are reused for every subsequent query on this client. To get a *unique* traceparent
        # per query, either (a) create a fresh client per request (as below), or (b) pass the
        # traceparent via the per-query `settings={"opentelemetry_traceparent": ...}` argument
        # so each query carries its own trace ID.
        headers = {}
        inject(headers)

        client = clickhouse_connect.get_client(
            host="clickhouse",
            http_headers=headers    # ClickHouse reads traceparent from here
        )
        result = client.query(
            "SELECT * FROM mart_user_risk_profile WHERE username = {username:String}",
            parameters={"username": username}
        )
        span.set_attribute("db.rows_returned", len(result.result_rows))
        return result.result_rows

import (
    "github.com/ClickHouse/clickhouse-go/v2"
    "go.opentelemetry.io/otel"
    "go.opentelemetry.io/otel/attribute"
    "go.opentelemetry.io/otel/propagation"
)

tracer := otel.Tracer("trading-service")

func getTopTraders(ctx context.Context) ([]Trader, error) {
    ctx, span := tracer.Start(ctx, "clickhouse.mart_trades_futures")
    defer span.End()

    span.SetAttributes(
        attribute.String("db.system",    "clickhouse"),
        attribute.String("db.operation", "SELECT"),
        attribute.String("db.statement", "SELECT username, SUM(quantity*price) FROM mart_trades_futures ..."),
    )

    // clickhouse-go/v2 does NOT auto-inject the OTEL traceparent into the
    // native protocol. Extract it explicitly from ctx and pass it via the
    // `opentelemetry_traceparent` query setting — ClickHouse server reads
    // that and emits the correct trace_id into system.opentelemetry_span_log.
    carrier := propagation.MapCarrier{}
    otel.GetTextMapPropagator().Inject(ctx, carrier)

    conn, _ := clickhouse.Open(&clickhouse.Options{
        Addr: []string{"clickhouse:9000"},
        Settings: clickhouse.Settings{
            "opentelemetry_traceparent": carrier["traceparent"],
            // optional, only if your propagator emits it:
            "opentelemetry_tracestate":  carrier["tracestate"],
        },
    })

    rows, err := conn.QueryContext(ctx, `
        SELECT username, SUM(quantity * price) as volume
        FROM mart_trades_futures
        WHERE transaction_time >= today()
        GROUP BY username
        ORDER BY volume DESC
        LIMIT 10
    `)
    return parseRows(rows), err
}

-- ClickHouse internal spans for a specific trace.
-- NOTE: in modern ClickHouse, system.opentelemetry_span_log.trace_id is type UUID,
-- not String — direct hex-string comparison fails. Convert with lower(hex(trace_id))
-- or build the UUID from the hex form.
SELECT
    lower(hex(trace_id))                     AS trace_id_hex,
    lower(hex(span_id))                      AS span_id_hex,
    lower(hex(parent_span_id))               AS parent_span_id_hex,
    operation_name,
    (finish_time_us - start_time_us) / 1000  AS duration_ms,
    attribute['clickhouse.query']             AS sql_query
FROM system.opentelemetry_span_log
WHERE lower(hex(trace_id)) = '4bf92f3577b34da6a3ce929d0e0e4736'
ORDER BY start_time_us;

-- Auto-export ClickHouse internal spans to otel_traces
CREATE MATERIALIZED VIEW otel.clickhouse_spans_mv TO otel.otel_traces AS
SELECT
    fromUnixTimestamp64Micro(start_time_us)  AS Timestamp,
    lower(hex(trace_id))                     AS TraceId,
    lower(hex(span_id))                      AS SpanId,
    lower(hex(parent_span_id))               AS ParentSpanId,
    ''                                       AS TraceState,
    operation_name                           AS SpanName,
    'SPAN_KIND_SERVER'                       AS SpanKind,
    'clickhouse'                             AS ServiceName,
    map()                                    AS ResourceAttributes,
    attribute                                AS SpanAttributes,
    (finish_time_us - start_time_us) * 1000  AS Duration,   -- convert to nanoseconds
    'STATUS_CODE_OK'                         AS StatusCode,
    ''                                       AS StatusMessage,
    []                                       AS `Events.Timestamp`,
    []                                       AS `Events.Name`,
    []                                       AS `Events.Attributes`,
    []                                       AS `Links.TraceId`,
    []                                       AS `Links.SpanId`,
    []                                       AS `Links.TraceState`,
    []                                       AS `Links.Attributes`
FROM system.opentelemetry_span_log;

-- Full waterfall for a single trade request
SELECT
    SpanName,
    ServiceName,
    Duration / 1e6                          AS duration_ms,
    SpanAttributes['http.method']           AS http_method,
    SpanAttributes['http.route']            AS route,
    SpanAttributes['db.statement']          AS sql,
    SpanAttributes['db.rows_affected']      AS rows,
    StatusCode,
    Timestamp
FROM otel.otel_traces
WHERE TraceId = '4bf92f3577b34da6a3ce929d0e0e4736'
ORDER BY Timestamp ASC;

SpanName                          Service              duration_ms
──────────────────────────────    ──────────────────── ───────────
http.server POST /api/trade       api-gateway   245.3 ms
  └─ trade.execute                trading-service      241.1 ms
       ├─ db.query (risk check)   trading-service       18.4 ms  ← SELECT mart_user_risk_profile
       ├─ db.query (insert trade) trading-service        4.2 ms  ← INSERT INTO mart_trades_futures
       ├─ SELECT (internal CH)    clickhouse             3.1 ms  ← ClickHouse internal span
       └─ kafka.produce           trading-service        2.1 ms

SET allow_experimental_full_text_index = 1;

-- Add the text index to the Body column.
-- `tokenizer = 'default'` splits on non-alphanumeric characters (good general default).
-- `case_sensitive = false` lowercases tokens at index *build time* — query the raw column.
ALTER TABLE otel.otel_logs
    ADD INDEX body_text_idx(Body)
    TYPE text(tokenizer = 'default', case_sensitive = false)
    GRANULARITY 1;

-- Materialize the index on existing data
ALTER TABLE otel.otel_logs MATERIALIZE INDEX body_text_idx;

-- Uses new text index — 7.4x faster than full scan
SELECT Timestamp, ServiceName, SeverityText, Body
FROM otel.otel_logs
WHERE hasToken(Body, 'OutOfMemoryError')
  AND Timestamp >= now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC
LIMIT 100;

-- LIKE also uses the text index automatically
SELECT Timestamp, ServiceName, SeverityText, Body
FROM otel.otel_logs
WHERE Body LIKE '%OutOfMemoryError%'
  AND Timestamp >= now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC
LIMIT 100;

-- Near O(1) — bloom_filter index makes this very fast
SELECT *
FROM otel.otel_logs
WHERE TraceId = 'abc123def456'
ORDER BY Timestamp;

-- Filter on OTEL log attributes — common in structured logging
SELECT ServiceName, count() AS error_count
FROM otel.otel_logs
WHERE LogAttributes['http.status_code'] = '500'
  AND Timestamp >= today()
GROUP BY ServiceName
ORDER BY error_count DESC;

-- "All ERROR logs from trading-service in last 2 hours that mention user_id"
SELECT
    Timestamp,
    SeverityText,
    Body,
    LogAttributes['user_id']    AS user_id,
    LogAttributes['error_code'] AS error_code,
    TraceId
FROM otel.otel_logs
WHERE ServiceName  = 'trading-service'
  AND SeverityText = 'ERROR'
  AND Timestamp    >= now() - INTERVAL 2 HOUR
  AND hasToken(Body, 'user_id')     -- uses text index
ORDER BY Timestamp DESC
LIMIT 200;

-- The text index was built with case_sensitive = false, so tokens are lowercased
-- at index time. Query the raw column — wrapping it in lower() defeats the index.
SELECT Timestamp, ServiceName, Body
FROM otel.otel_logs
WHERE hasToken(Body, 'OutOfMemoryError')   -- matches OOM/oom/Oom case-insensitively
  AND Timestamp >= now() - INTERVAL 1 HOUR;

# Install ClickHouse datasource plugin
grafana-cli plugins install grafana-clickhouse-datasource

# Or in docker-compose:
environment:
  - GF_INSTALL_PLUGINS=grafana-clickhouse-datasource

# grafana/provisioning/datasources/clickhouse.yaml
apiVersion: 1
datasources:
  - name: ClickHouse-OTEL
    type: grafana-clickhouse-datasource
    uid: clickhouse-otel
    jsonData:
      host: clickhouse
      port: 9000
      database: otel
      username: grafana_readonly
    secureJsonData:
      password: ${GRAFANA_CH_PASSWORD}

-- Error rate per service — last 1 hour, 1-minute buckets
SELECT
    toStartOfMinute(Timestamp)   AS time,
    ServiceName,
    countIf(SeverityText = 'ERROR') AS errors,
    count()                          AS total,
    errors / total * 100             AS error_rate_pct
FROM otel.otel_logs
WHERE Timestamp >= now() - INTERVAL 1 HOUR
GROUP BY time, ServiceName
ORDER BY time ASC;

-- HTTP endpoint latency percentiles — last 30 minutes
SELECT
    SpanAttributes['http.route']          AS endpoint,
    quantile(0.50)(Duration) / 1e6        AS p50_ms,
    quantile(0.95)(Duration) / 1e6        AS p95_ms,
    quantile(0.99)(Duration) / 1e6        AS p99_ms,
    count()                               AS request_count
FROM otel.otel_traces
WHERE SpanKind    = 'SPAN_KIND_SERVER'
  AND Timestamp  >= now() - INTERVAL 30 MINUTE
GROUP BY endpoint
ORDER BY p99_ms DESC;

-- Slowest ClickHouse queries in last 1 hour
SELECT
    SpanAttributes['db.statement']     AS sql,
    count()                             AS calls,
    avg(Duration) / 1e6                 AS avg_ms,
    max(Duration) / 1e6                 AS max_ms,
    quantile(0.99)(Duration) / 1e6      AS p99_ms
FROM otel.otel_traces
WHERE SpanAttributes['db.system'] IN ('clickhouse', 'postgresql')
  AND Timestamp >= now() - INTERVAL 1 HOUR
GROUP BY sql
ORDER BY p99_ms DESC
LIMIT 20;

-- Live log tail with filtering — wire to Grafana Logs panel
SELECT
    Timestamp,
    ServiceName,
    SeverityText,
    Body,
    TraceId,
    LogAttributes
FROM otel.otel_logs
WHERE Timestamp >= $__timeFrom()    -- Grafana time variable
  AND Timestamp <= $__timeTo()
  AND ServiceName = '$service'      -- Grafana template variable
  AND SeverityText IN ($severity)
ORDER BY Timestamp DESC
LIMIT 1000;

-- Grafana panel: "Affected users for errors in selected time range"
-- This is impossible in Kibana — requires joining log data with business data

SELECT
    l.LogAttributes['user_id']    AS user_id,
    u.kyc_status,
    count()                        AS error_count,
    max(l.Timestamp)               AS last_error
FROM otel.otel_logs l
JOIN marts.mart_users u ON l.LogAttributes['user_id'] = u.username
WHERE l.ServiceName  = '$service'
  AND l.SeverityText = 'ERROR'
  AND l.Timestamp    BETWEEN $__timeFrom() AND $__timeTo()
GROUP BY user_id, u.kyc_status
ORDER BY error_count DESC
LIMIT 50;

-- Delete logs older than 90 days — runs automatically in background
ALTER TABLE otel.otel_logs
    MODIFY TTL toDateTime(Timestamp) + INTERVAL 90 DAY;

-- Different retention per severity.
-- ClickHouse evaluates TTL expressions in order and applies the FIRST one whose
-- WHERE clause matches a row. Put the most-specific (longest-retained) classes
-- first; the no-WHERE clause acts as the default fallback.
ALTER TABLE otel.otel_logs
    MODIFY TTL
        toDateTime(Timestamp) + INTERVAL 365 DAY DELETE WHERE SeverityText = 'CRITICAL',  -- critical: 1 year
        toDateTime(Timestamp) + INTERVAL  90 DAY DELETE WHERE SeverityText = 'ERROR',     -- errors:   90 days
        toDateTime(Timestamp) + INTERVAL   7 DAY DELETE;                                  -- default:   7 days

-- Storage policy: SSD (0-7 days) → S3 (7-90 days) → delete (90+ days)
-- Define in storage_configuration in config.xml:

-- hot:  /var/lib/clickhouse/data  (local SSD, fast)
-- cold: s3://clickhouse-cold-tier/ (S3, cheap)

ALTER TABLE otel.otel_logs
    MODIFY TTL
        toDateTime(Timestamp) + INTERVAL 7 DAY TO VOLUME 'cold',    -- move to S3
        toDateTime(Timestamp) + INTERVAL 90 DAY DELETE;             -- delete

User: "Which services had the most errors in the last hour?"
→ SQL: SELECT ServiceName, count() FROM otel_logs WHERE SeverityText='ERROR'
        AND Timestamp >= now()-INTERVAL 1 HOUR GROUP BY ServiceName ORDER BY count() DESC

User: "Show me all traces where ClickHouse queries took more than 500ms today"
→ SQL: SELECT TraceId, ServiceName, Duration/1e6 AS ms, SpanAttributes['db.statement'] AS sql
        FROM otel_traces WHERE SpanAttributes['db.system']='clickhouse'
        AND Duration > 500000000 AND Timestamp >= today() ORDER BY Duration DESC

User: "Which users were affected by the trading-service errors between 2pm and 3pm today?"
→ SQL: SELECT l.LogAttributes['user_id'], u.kyc_status, count() as errors
        FROM otel_logs l JOIN mart_users u ON l.LogAttributes['user_id'] = u.username
        WHERE l.ServiceName='trading-service' AND l.SeverityText='ERROR'
        AND l.Timestamp BETWEEN today()+toIntervalHour(14) AND today()+toIntervalHour(15)
        GROUP BY 1,2 ORDER BY errors DESC

# Observability terms
- "error logs" = otel_logs WHERE SeverityText = 'ERROR'
- "slow query" = otel_traces WHERE SpanAttributes['db.system'] IN ('clickhouse','postgresql') AND Duration > 500000000
- "HTTP 5xx" = otel_logs WHERE LogAttributes['http.status_code'] LIKE '5%'
- "trade service" = ServiceName = 'trading-service'
- "trace" = otel_traces WHERE TraceId = '<id>'
- "latency" = Duration / 1e6 (milliseconds)
- "p99" = quantile(0.99)(Duration) / 1e6
- "error rate" = countIf(SeverityText='ERROR') / count() * 100

Without BindPlane:
  Edit otel-collector.yaml on server 1
  Edit otel-collector.yaml on server 2
  Edit otel-collector.yaml on server N
  Restart each collector
  ...

With BindPlane:
  Add ClickHouse destination once in BindPlane UI
  Roll out to entire fleet in one click

# BindPlane destination config for self-managed ClickHouse
destination:
  type: otlp
  name: clickhouse-self-managed
  config:
    endpoint: tcp://clickhouse.internal:9000
    headers:
      - key: x-clickhouse-database
        value: otel
    tls:
      insecure: false