paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

August 06, 2023

Observability with ClickHouse: From First Principles to Production Scale

Why a columnar OLAP database has become the backbone of modern logs, metrics, and traces pipelines

Introduction

Every engineering team eventually hits the same wall: the observability stack that worked fine at ten services and a few gigabytes a day starts falling over at a hundred services and a few terabytes a day. Dashboards time out. Retention gets cut from 90 days to 7. Query costs creep past the compute budget for the actual product. The instinct is usually to buy a bigger vendor contract, but a growing number of engineering organizations - from Uber to Cloudflare to a long list of mid-size SaaS companies - have instead rebuilt parts of their observability pipeline on top of ClickHouse, a columnar OLAP database originally built for web analytics at Yandex.

This post is a working engineer's guide to that decision. It covers what ClickHouse actually is and why its architecture maps unusually well onto logs, metrics, and traces; how to design schemas, ingestion paths, and materialized views that hold up under real production load; and where the sharp edges are - because there are real trade-offs, and pretending otherwise does nobody any favors. The goal is not to convince you ClickHouse is the only right answer, but to give you enough grounded detail to evaluate it honestly against your own constraints.

Context: Why Observability Data Breaks Traditional Databases

Observability data has a specific and somewhat unusual shape. It is write-heavy and append-only - you almost never update a log line or a metric point after it's written. It is high-cardinality - trace IDs, pod names, request IDs, user IDs, and container hashes generate enormous numbers of distinct values. And the read pattern is dominated by aggregate queries over time windows: "P99 latency for service X over the last hour, grouped by region," not "fetch this one row by primary key." Traditional row-oriented OLTP databases like PostgreSQL or MySQL are optimized for the opposite pattern - fast lookups and updates on individual rows - and they buckle under the combination of high ingest volume and wide aggregate scans that observability workloads demand.

The usual response has been to reach for purpose-built systems: Elasticsearch for logs, Prometheus and its long-term-storage cousins (Thanos, Cortex, Mimir) for metrics, and Jaeger or Tempo for traces. This works, but it means running and paying for three or four separate storage systems, each with its own query language, retention policy, and operational quirks. Correlating a trace with the logs and metrics around it often means stitching together results from systems that were never designed to talk to each other.

ClickHouse's pitch is different: treat logs, metrics, and traces as what they structurally are - time-series-like, append-only, columnar data - and store all three in one system built specifically for fast aggregation over large volumes. This isn't a hypothetical; it's the architecture behind tools like SigNoz, and it underlies the design of the OpenTelemetry ClickHouse exporter that ships as part of the OpenTelemetry Collector contrib distribution. The convergence of OpenTelemetry as a vendor-neutral data format and ClickHouse as a storage engine has become a genuine pattern in the industry, not a niche experiment.

Deep Technical Explanation: How ClickHouse's Architecture Fits Observability

To understand why ClickHouse performs so well on this workload, you have to start with column-oriented storage. In a row-oriented database, all the fields of a single record are stored contiguously on disk, which is efficient when you need the whole row but wasteful when you only need a few columns out of many - the database still has to read past the columns you don't care about. ClickHouse stores each column separately. If your query only touches timestamp, service_name, and duration_ms out of a table with forty columns, ClickHouse reads only those three columns off disk. For observability queries, which are almost always "aggregate a couple of numeric or low-cardinality columns over millions of rows," this alone accounts for a large share of the performance advantage over row stores.

The second pillar is the MergeTree engine family, which is ClickHouse's default and most important table engine. MergeTree tables are organized into immutable parts on disk, each holding a sorted range of data according to a table's ORDER BY key. Background merge processes periodically combine smaller parts into larger ones, similar in spirit to LSM-tree compaction in systems like RocksDB or Cassandra. This design is what makes ClickHouse comfortable with extremely high insert rates - writes are cheap because they just create new parts - while still supporting fast reads, because the ORDER BY key acts as a sparse primary index that lets the query engine skip large chunks of data it doesn't need to scan.

Compression is the third pillar, and it matters more for observability than almost any other workload type. Because columns are stored separately and often contain repetitive or low-entropy values - log levels, HTTP status codes, service names, span kinds - ClickHouse's default codecs (LZ4, and ZSTD for colder data) achieve compression ratios that are frequently in the 10x-20x range for typical telemetry data, sometimes higher for highly repetitive fields. Specialized codecs like DoubleDelta and Gorilla (the same encoding Facebook's Gorilla time-series paper popularized) are built in specifically for monotonic timestamps and slowly-changing metric values, and ClickHouse lets you assign codecs per column rather than per table.

The fourth pillar, and the one people underestimate most, is vectorized query execution. ClickHouse processes data in batches of column values using SIMD instructions rather than evaluating expressions row by row. Combined with a cost-based approach to skipping data via primary indexes, sparse indexes, and optional secondary structures like skip indexes and bloom filters on specific columns, this is why a well-modeled ClickHouse table can scan and aggregate billions of rows in a query that takes low single-digit seconds, on hardware that would take a row store minutes for the equivalent query.

Schema Design for Observability Data

Good performance in ClickHouse is earned at schema design time, not query time - this is the single biggest mental shift for engineers coming from OLTP backgrounds. The ORDER BY clause is not a suggestion the optimizer might use; it physically determines how data is sorted on disk, and it is the primary lever for query speed. For a logs table, a common and effective pattern is to order by (service_name, toDate(timestamp), timestamp) - grouping first by the field you filter on most (service), then by a coarse-grained time bucket, then by exact time. This lets ClickHouse skip entire ranges of data for queries scoped to one service without a full scan.

High cardinality fields - trace IDs, request IDs, arbitrary user-supplied tags - need different treatment than low-cardinality fields like log level or HTTP method. Putting a high-cardinality column early in the ORDER BY key destroys the benefit of the sparse index, because consecutive rows will rarely share a prefix. The standard approach is to keep such fields out of the primary sort key and instead index them with a bloom_filter or set type skip index, which lets ClickHouse quickly determine which granules of data could possibly contain a given trace ID without scanning everything.

For semi-structured attributes - the arbitrary key-value tags that come attached to spans and log lines - ClickHouse's Map(String, String) type or, in more recent versions, the native JSON column type, lets you avoid pre-defining a fixed schema for every possible attribute while still getting reasonable query performance through specialized functions like mapValues and JSONExtract. This is the same trade-off Elasticsearch's dynamic mapping solves for, but with a stricter, more predictable cost model.

CREATE TABLE otel_logs
(
    timestamp        DateTime64(9),
    trace_id         String,
    span_id          String,
    service_name     LowCardinality(String),
    severity_text    LowCardinality(String),
    body             String,
    resource_attrs   Map(LowCardinality(String), String),
    log_attrs        Map(LowCardinality(String), String)
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (service_name, severity_text, timestamp)
TTL toDateTime(timestamp) + INTERVAL 30 DAY DELETE
SETTINGS index_granularity = 8192;

ALTER TABLE otel_logs
ADD INDEX idx_trace_id trace_id TYPE bloom_filter GRANULARITY 4;

Notice the LowCardinality(String) wrapper on fields like service_name and severity_text. This is a dictionary-encoding hint - ClickHouse stores the distinct values once and references them by integer, which shrinks both storage and the working set the CPU touches during aggregation. It is one of the cheapest performance wins available and is criminally underused by teams migrating schemas from other systems without adapting them.

Implementation: Building an Ingestion and Query Pipeline

A realistic pipeline starts with instrumentation via OpenTelemetry, since it decouples your application code from any specific backend. The OpenTelemetry Collector receives spans, logs, and metrics from your services, batches them, and exports them to ClickHouse using the clickhouseexporter component. This separation matters operationally: you can point the same collector at ClickHouse today and at a different backend tomorrow without touching instrumented application code.

# otel-collector-config.yaml
receivers:
  otlp:
    protocols:
      grpc:
      http:

processors:
  batch:
    timeout: 5s
    send_batch_size: 10000

exporters:
  clickhouse:
    endpoint: tcp://clickhouse:9000?dial_timeout=10s
    database: otel
    ttl: 720h
    logs_table_name: otel_logs
    traces_table_name: otel_traces
    metrics_table_name: otel_metrics

service:
  pipelines:
    logs:
      receivers: [otlp]
      processors: [batch]
      exporters: [clickhouse]
    traces:
      receivers: [otlp]
      processors: [batch]
      exporters: [clickhouse]

Batching is not optional with ClickHouse - it's a hard operational requirement. Because each insert creates a new part on disk, sending thousands of tiny single-row inserts per second will overwhelm the background merge process and can degrade the whole cluster. The batch processor above, combined with client-side batching in the exporter itself, ensures ClickHouse receives a manageable number of larger inserts rather than a flood of small ones. As a rule of thumb, aim for inserts in the range of several thousand to tens of thousands of rows per batch, at a frequency of roughly one insert per second per table, per node.

On the query side, application code typically talks to ClickHouse through one of its official clients rather than raw HTTP. The following example uses the official Node.js client to run a P99 latency query - a bread-and-butter observability question - grouped by service and time bucket.

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: process.env.CLICKHOUSE_URL,
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
  database: 'otel',
});

async function getP99LatencyByService(hoursBack: number) {
  const query = `
    SELECT
      service_name,
      toStartOfMinute(timestamp) AS minute,
      quantile(0.99)(duration_ms) AS p99_latency_ms,
      count() AS request_count
    FROM otel_traces
    WHERE timestamp >= now() - INTERVAL {hours:UInt32} HOUR
      AND parent_span_id = ''
    GROUP BY service_name, minute
    ORDER BY minute DESC
    LIMIT 500
  `;

  const resultSet = await client.query({
    query,
    query_params: { hours: hoursBack },
    format: 'JSONEachRow',
  });

  return resultSet.json();
}

For teams building custom ingestion outside the OpenTelemetry Collector - say, backfilling historical logs from S3 or another warehouse - the Python client is the more common choice, and it supports efficient bulk inserts via insert with a list of rows or a pandas DataFrame, rather than row-by-row execution.

import clickhouse_connect
from datetime import datetime

client = clickhouse_connect.get_client(
    host='clickhouse.internal',
    port=8443,
    username='ingest_user',
    password=os.environ['CLICKHOUSE_PASSWORD'],
    secure=True,
)

def backfill_logs(rows: list[dict]) -> None:
    columns = ['timestamp', 'trace_id', 'service_name', 'severity_text', 'body']
    data = [[r['timestamp'], r['trace_id'], r['service'], r['level'], r['message']] for r in rows]
    client.insert('otel_logs', data, column_names=columns)

Materialized Views and Pre-Aggregation

Raw event tables answer ad-hoc questions well, but dashboards that refresh every ten seconds and re-scan billions of raw spans are wasteful and, at sufficient scale, simply too slow. ClickHouse's materialized views solve this by incrementally aggregating data as it's inserted, rather than at query time. A materialized view in ClickHouse is not a cached query result recomputed on a schedule - it's a trigger that runs on every insert into the source table and writes the transformed result into a separate target table, usually one using the AggregatingMergeTree or SummingMergeTree engine.

CREATE TABLE service_latency_1m
(
    minute        DateTime,
    service_name  LowCardinality(String),
    p99_state     AggregateFunction(quantile(0.99), Float64),
    request_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
ORDER BY (service_name, minute);

CREATE MATERIALIZED VIEW service_latency_1m_mv
TO service_latency_1m AS
SELECT
    toStartOfMinute(timestamp) AS minute,
    service_name,
    quantileState(0.99)(duration_ms) AS p99_state,
    countState() AS request_count
FROM otel_traces
WHERE parent_span_id = ''
GROUP BY service_name, minute;

The dashboard then queries service_latency_1m directly - a table that's orders of magnitude smaller than the raw trace table - using quantileMerge to finalize the aggregate state. This pattern, sometimes called a "rollup" table in other systems, is what lets ClickHouse-backed dashboards stay fast even as raw retention grows into the trillions of rows, because the expensive aggregation work happens once at write time rather than repeatedly at every dashboard refresh.

Trade-offs and Pitfalls

None of this comes for free, and the first trade-off engineers run into is that ClickHouse is not a drop-in replacement for a document store like Elasticsearch when it comes to full-text search. ClickHouse does support token-based and n-gram bloom filter indexes for substring matching, and recent versions have added an experimental inverted index, but for teams whose primary log workflow is free-text search across unstructured messages - "find every log line containing this stack trace fragment" - Elasticsearch's inverted index and query DSL are still generally more mature and ergonomic for that specific job. ClickHouse earns its keep on structured aggregation, not full-text relevance ranking.

The second pitfall is the operational discipline mismatch: teams coming from managed services underestimate how much ClickHouse rewards (and punishes) schema and cluster design decisions made early. Choosing the wrong ORDER BY key, over-partitioning by putting too fine-grained a value in PARTITION BY (a common mistake is partitioning by hour instead of day, which explodes the number of parts and slows merges), or forgetting TTL clauses for data expiration are all mistakes that are expensive to reverse once you have terabytes of data on disk, because fixing them means rewriting the table.

Third, ClickHouse's eventual consistency model for distributed deployments - using ReplicatedMergeTree and asynchronous replication across nodes, coordinated historically via ZooKeeper and increasingly via ClickHouse Keeper - means there's a real window in which a write acknowledged on one replica hasn't yet propagated to others. This is rarely a problem for observability, where "eventually visible within a couple of seconds" is an acceptable trade-off, but it is a meaningfully different consistency guarantee than what teams get from a single-node Postgres instance, and it needs to be understood rather than assumed away.

Finally, there's a cost dimension worth naming honestly: ClickHouse is not a managed SaaS observability platform out of the box. Running it well - whether self-hosted or via ClickHouse Cloud - requires someone on the team who understands merge behavior, replication, and capacity planning. For a small team without that expertise, a hosted observability vendor may genuinely be cheaper in total cost of ownership even if the raw compute cost of ClickHouse is lower, because the engineering time spent operating it is a real cost too.

Best Practices for Production ClickHouse Observability

Set explicit TTL policies from day one rather than retrofitting them. Observability data has a natural decay curve - you need full-resolution data for a week or two for incident response, and much coarser aggregates for months after that. ClickHouse supports tiered TTL rules that can move data to cheaper storage (TTL ... TO VOLUME 'cold') or delete it outright, and setting this up before your disks fill up is far less stressful than doing it as an emergency.

Treat ORDER BY and partitioning keys as a schema migration decision, not a config tweak. Before committing to a schema in production, run representative queries - the actual dashboard queries your team will run, not synthetic benchmarks - against a realistic data volume, and iterate on the key design based on EXPLAIN output and query timing. The cost of getting this wrong compounds with every terabyte you ingest afterward.

Finally, invest in the ingestion path's resilience as much as the storage layer's performance. A ClickHouse cluster that's perfectly tuned but fed by an OpenTelemetry Collector with no backpressure handling or retry queue will silently drop data during traffic spikes - precisely the moments when observability data matters most. Configuring the collector's batch and queued_retry (or the newer exporterhelper sending queue) settings appropriately, and monitoring the collector's own internal metrics, is not optional polish; it's part of making the observability system itself observable.

Analogies and Mental Models

The cleanest mental model for ClickHouse's column store is a library organized by subject rather than by arrival date. If every book in a row-oriented database is shelved in the order it arrived, and you want "every mystery novel," you have to walk past every shelf checking each book's genre. A column store is like having pre-sorted galleries - all the mysteries in one wing, all the biographies in another - so pulling "everything in the mystery wing published after 2020" only requires walking through the relevant wing, not the whole library.

The MergeTree engine, meanwhile, behaves like a diligent filing clerk who never edits an existing folder, only adds new folders and periodically consolidates small folders into bigger ones overnight. Writes are fast because the clerk just drops a new folder in the inbox; reads stay fast because the clerk keeps folders labeled and sorted, and periodically merges them so there aren't too many folders to search through. This mental model explains both why ClickHouse loves high-throughput appends and why unbounded small inserts - folders arriving one page at a time - eventually overwhelm the clerk.

The 80/20 Insight

If you take away only a handful of ideas from ClickHouse's observability use case, make them these: get the ORDER BY key right for your dominant query pattern, because it does more for performance than any other single decision; use LowCardinality on every enum-like string column, because it's essentially free and meaningfully shrinks both storage and CPU cost; build materialized views for your top five dashboard queries rather than letting dashboards scan raw tables, because pre-aggregation is what keeps query latency flat as data grows; batch your inserts aggressively, because ClickHouse's entire performance model assumes large sequential writes, not a trickle of single rows; and set TTLs before you need them, because retention policy is far easier to define proactively than to retrofit onto an already-overflowing cluster. These five decisions account for the overwhelming majority of the performance and cost difference between a ClickHouse deployment that works and one that quietly becomes a maintenance burden.

Key Takeaways

Conclusion

ClickHouse didn't set out to be an observability database - it was built for web analytics, and its adoption in the logs, metrics, and traces space is a case of an architecture proving general enough to outgrow its original use case. The combination of column-oriented storage, the MergeTree engine family, aggressive compression, and vectorized execution happens to line up almost perfectly with what observability workloads need: fast writes for high-volume telemetry and fast aggregate reads over huge time ranges. That alignment is why the ecosystem around it - OpenTelemetry exporters, tools like SigNoz, and a growing body of production experience at companies operating at real scale - has matured as quickly as it has.

None of that makes ClickHouse a universal answer. It is not the right tool if your primary need is rich full-text search, and it demands real operational investment in schema design and cluster management that a hosted vendor would otherwise absorb. The honest framing is that ClickHouse trades convenience for control and cost efficiency at scale - a trade worth making for teams with the engineering capacity to do schema design properly, and a trade that may not pay off for teams without it. Understanding that trade-off, rather than treating ClickHouse as a drop-in replacement for whatever you're using today, is what separates observability pipelines that scale gracefully from ones that just move the pain somewhere new.

References