Putting Observability Data Into ClickHouse — Schema, Rollups, TTL, and Splitting the Work

• Introduction — When You Need to Scan 30 Days of Traces to Answer a Question
• Why Columnar Storage Fits Observability Data
• Trace Schema — The Sort Key Is Everything
• Log Schema — Should Attributes Be a Map or a JSON Type?
• Partitioning, TTL, and Tiered Storage
• Rollups With Materialized Views
• Sending From the Collector Into ClickHouse
• Dividing Roles Among Prometheus, OpenSearch, and ClickHouse
• Closing — Schema Decisions Are Hard to Reverse Later

Introduction — When You Need to Scan 30 Days of Traces to Answer a Question

"I want to see the tenant distribution of requests to a specific payment processor over the last 30 days that took longer than 3 seconds."

When a question like this comes in, most observability stacks choke. Prometheus doesn't know about individual requests. The trace backend only retains 7 days. If you stored it in a search engine, the cluster wobbles while it scans 30 days' worth.

Once observability data reaches multiple terabytes a day and questions like this start recurring, you need an analytical database. ClickHouse shows up in this spot often because the nature of observability data suits columnar storage so well.

This post designs a real schema. Verified against ClickHouse 26.5 stable; if you want a long-term-stable line, the 26.3 LTS series is the alternative. 25.8 LTS support ends in late August 2026, so it isn't recommended for new builds. The native JSON type became production-ready in 25.3.

Why Columnar Storage Fits Observability Data

Three things line up.

First, queries are narrow. Even if a trace table has 25 columns, a query for "p99 by service" only reads two of them: service name and duration. Row storage has to read all 25 columns off disk; columnar storage reads only two.

Second, values repeat. There are dozens of service names, hundreds of span names, and around ten status codes. When the same value runs in a row, compression gets extremely good. Timestamps shrink via delta encoding, and low-cardinality strings shrink via dictionary encoding.

Third, writes are append-only. Observability data is never updated. This matches exactly the workload MergeTree assumes.

The fastest way to feel the compression ratio is to measure it yourself.

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

-- You can also look at it column by column. Columns that don't compress well make up most of the cost
SELECT
    name,
    type,
    formatReadableSize(data_compressed_bytes) AS compressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio
FROM system.columns
WHERE database = 'otel' AND table = 'otel_traces'
ORDER BY data_compressed_bytes DESC
LIMIT 15;

If a column has a compression ratio close to 1, there are usually two causes: random strings (trace IDs, UUIDs), or free-form text. Trace IDs can't be helped, but free text can be split into a separate column where you have room to apply a different codec.

Trace Schema — The Sort Key Is Everything

The single most important decision in MergeTree is ORDER BY. Data is sorted on disk in this order, and queries use that order to skip blocks they don't need to read. Get the sort key wrong, and nothing else can make up for it.

The default table the OpenTelemetry ClickHouse exporter creates sorts traces by service name, span name, then timestamp. That's a choice optimized for queries that "scan by service and operation."

CREATE TABLE otel.otel_traces
(
    Timestamp          DateTime64(9) CODEC(Delta(8), 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) CODEC(ZSTD(1)),
    SpanKind           LowCardinality(String) CODEC(ZSTD(1)),
    ServiceName        LowCardinality(String) CODEC(ZSTD(1)),
    ResourceAttributes Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    ScopeName          String CODEC(ZSTD(1)),
    ScopeVersion       String CODEC(ZSTD(1)),
    SpanAttributes     Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    Duration           UInt64 CODEC(ZSTD(1)),
    StatusCode         LowCardinality(String) CODEC(ZSTD(1)),
    StatusMessage      String CODEC(ZSTD(1)),

    Events Nested (
        Timestamp  DateTime64(9),
        Name       LowCardinality(String),
        Attributes Map(LowCardinality(String), String)
    ) CODEC(ZSTD(1)),

    Links Nested (
        TraceId    String,
        SpanId     String,
        TraceState String,
        Attributes Map(LowCardinality(String), String)
    ) CODEC(ZSTD(1)),

    INDEX idx_trace_id       TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_res_attr_key   mapKeys(ResourceAttributes)   TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_key  mapKeys(SpanAttributes)       TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_duration       Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toDateTime(Timestamp))
TTL toDateTime(Timestamp) + toIntervalDay(30)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

Three things are worth watching closely.

Where to use LowCardinality(String). Use it only on strings with roughly fewer than 10,000 distinct values. A dictionary gets built, which speeds up both compression and filtering. Attach it to a column like TraceId, where values are essentially unique, and the dictionary grows without bound and makes things worse instead.

The role of the bloom filter index. Since the sort key is service and span name, a query that looks up a single trace ID can't use the sort order. The bloom filter quickly rules out "this trace ID isn't in this block," cutting down the blocks that need to be read. But this is a secondary aid — it can't substitute for the sort key.

ttl_only_drop_parts = 1. This processes TTL expiry at the part level, not the row level. Since partitions are per-day, an entire part for an expired day disappears at once. Without this setting, TTL cleanup triggers part rewrites, which drives disk I/O way up.

If you want to change the sort key, start by looking at which queries dominate.

If both access patterns matter, create a second table. You pay double the storage cost, but both queries get fast. This is a common choice in ClickHouse.

Log Schema — Should Attributes Be a Map or a JSON Type?

The sort key for a log table is different. Since queries that narrow by time range and service dominate for logs, put time first, but don't slice it too finely.

CREATE TABLE otel.otel_logs
(
    Timestamp          DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    TraceId            String CODEC(ZSTD(1)),
    SpanId             String CODEC(ZSTD(1)),
    TraceFlags         UInt8,
    SeverityText       LowCardinality(String) CODEC(ZSTD(1)),
    SeverityNumber     UInt8,
    ServiceName        LowCardinality(String) CODEC(ZSTD(1)),
    Body               String CODEC(ZSTD(1)),
    ResourceAttributes Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    LogAttributes      Map(LowCardinality(String), String) CODEC(ZSTD(1)),

    -- Promote frequently filtered keys to physical columns
    HttpRoute    LowCardinality(String) MATERIALIZED LogAttributes['http.route'],
    HttpStatus   UInt16                 MATERIALIZED toUInt16OrZero(LogAttributes['http.response.status_code']),
    ErrorType    LowCardinality(String) MATERIALIZED LogAttributes['error.type'],

    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_severity  SeverityNumber TYPE set(16) GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, toStartOfFiveMinutes(Timestamp), Timestamp)
TTL toDateTime(Timestamp) + toIntervalDay(30)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

The MATERIALIZED column is an important device here. At insert time, it pulls a value out of the Map and stores it as a separate column. A Map lookup has to search for the key every time, but a physical column reads directly, so frequently used filters get much faster. Storage cost goes up, but low-cardinality columns compress well, so the actual increase is small.

There are two options for how to store attributes.

Map's biggest weakness is that it can't be partially read. Even to read a single LogAttributes['http.route'], you have to read the entire Map for that row off disk and decompress it. For a log with 40 attributes, that cost is significant. The JSON type stores each sub-key like its own separate column, so it doesn't have this problem.

-- Example JSON type usage — cap the number of sub-columns
CREATE TABLE otel.otel_logs_json
(
    Timestamp    DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    ServiceName  LowCardinality(String) CODEC(ZSTD(1)),
    SeverityText LowCardinality(String) CODEC(ZSTD(1)),
    TraceId      String CODEC(ZSTD(1)),
    Body         String CODEC(ZSTD(1)),
    Attributes   JSON(max_dynamic_paths = 512) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, toStartOfFiveMinutes(Timestamp), Timestamp)
TTL toDateTime(Timestamp) + toIntervalDay(30)
SETTINGS ttl_only_drop_parts = 1;

-- Accessing a sub-path with an explicit type lets indexes and statistics kick in
SELECT
    ServiceName,
    Attributes.http.route::LowCardinality(String) AS route,
    count() AS c,
    quantile(0.99)(Attributes.duration_ms::Float64) AS p99
FROM otel.otel_logs_json
WHERE Timestamp >= now() - INTERVAL 1 HOUR
  AND SeverityText = 'ERROR'
GROUP BY ServiceName, route
ORDER BY c DESC
LIMIT 20;

max_dynamic_paths plays the same role as a field-count cap in a search engine. Paths beyond the cap get pushed into a separate shared store — queries get slower, but the cluster doesn't collapse. It structurally mitigates incidents like mapping explosions.

The decision rule is simple. If attribute keys are mostly predictable and few in number, a Map is enough. If the key set differs by service and keeps growing, the JSON type is better.

Partitioning, TTL, and Tiered Storage

A daily partition is the default. There's a temptation to slice it finer, but it's better to resist. More partitions means more parts, and more parts means heavier merge load and metadata overhead. If daily data runs into multiple terabytes, consider hourly partitions, but check first whether TTL and the sort key already solve the problem.

TTL is used for moving data, not just deleting it. Keep recent data on fast disks, and older data on slower, cheaper storage.

-- Define a storage policy (in config.xml or a separate config file)
-- hot: local NVMe, cold: object storage

ALTER TABLE otel.otel_traces
MODIFY TTL
    toDateTime(Timestamp) + INTERVAL 3 DAY  TO VOLUME 'hot',
    toDateTime(Timestamp) + INTERVAL 14 DAY TO VOLUME 'cold',
    toDateTime(Timestamp) + INTERVAL 90 DAY DELETE;

There's one thing you must confirm when using move TTL. Queries against data moved to object storage incur a network round trip and get much slower. You need to tell users up front that "90-day retention" doesn't mean "queryable at the same speed for 90 days." Otherwise, someday someone runs a full scan over data from 60 days ago and paralyzes the cluster.

Also verify that TTL is actually running.

-- Size per partition and the oldest data
SELECT
    table,
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows,
    min(min_time) AS oldest
FROM system.parts
WHERE database = 'otel' AND active
GROUP BY table, partition
ORDER BY partition
LIMIT 10;

-- Merges pending and in progress
SELECT table, elapsed, progress, num_parts, formatReadableSize(memory_usage) AS mem
FROM system.merges
WHERE database = 'otel';

-- If the part count is high, inserts start getting rejected
SELECT table, count() AS parts
FROM system.parts
WHERE database = 'otel' AND active
GROUP BY table
ORDER BY parts DESC;

Part count is worth watching. Frequent small inserts make parts explode, and if merges can't keep up, inserts themselves get rejected. The standard response is to increase the exporter's batch size and turn on async inserts.

Rollups With Materialized Views

Retaining raw traces for 30 days is expensive. But most dashboard queries need aggregates, not raw data. If a materialized view builds the aggregate at insert time, you can keep the raw data short-lived and the aggregate long-lived.

-- 1) The table that will hold the aggregated results
CREATE TABLE otel.trace_rollup_1m
(
    Bucket      DateTime,
    ServiceName LowCardinality(String),
    SpanName    LowCardinality(String),
    SpanKind    LowCardinality(String),
    Calls       AggregateFunction(count),
    Errors      AggregateFunction(countIf, UInt8),
    DurationQ   AggregateFunction(quantiles(0.5, 0.9, 0.99), Float64),
    DurationSum AggregateFunction(sum, Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(Bucket)
ORDER BY (ServiceName, SpanName, SpanKind, Bucket)
TTL Bucket + toIntervalDay(400);

-- 2) Automatically aggregate as data is inserted into the source table
CREATE MATERIALIZED VIEW otel.trace_rollup_1m_mv TO otel.trace_rollup_1m AS
SELECT
    toStartOfMinute(Timestamp) AS Bucket,
    ServiceName,
    SpanName,
    SpanKind,
    countState()                                   AS Calls,
    countIfState(StatusCode = 'Error')             AS Errors,
    quantilesState(0.5, 0.9, 0.99)(Duration / 1e6) AS DurationQ,
    sumState(Duration / 1e6)                       AS DurationSum
FROM otel.otel_traces
GROUP BY Bucket, ServiceName, SpanName, SpanKind;

-- 3) Merge the states together at query time
SELECT
    ServiceName,
    SpanName,
    countMerge(Calls)                          AS calls,
    countIfMerge(Errors)                       AS errors,
    round(countIfMerge(Errors) / countMerge(Calls), 4) AS error_ratio,
    arrayElement(quantilesMerge(0.5, 0.9, 0.99)(DurationQ), 3) AS p99_ms
FROM otel.trace_rollup_1m
WHERE Bucket >= now() - INTERVAL 30 DAY
GROUP BY ServiceName, SpanName
ORDER BY calls DESC
LIMIT 30;

There are three traps here.

First, a materialized view is an insert trigger. It doesn't process data already sitting in the source table. If you want to backfill historical data after creating the view, you have to run a separate INSERT SELECT.

Second, the source's and the view's TTLs are independent. If your goal was to shrink the source to 7 days while keeping the view at 400, that's exactly what happens. But if you change the view's definition, the meaning of the aggregate before and after that point diverges, so it's safer to create a new view and a new table when changing the definition.

Third, quantiles must be stored as state. If you store a per-minute p99 as a plain number and then average it later, that isn't a p99 anymore. You have to store the intermediate state with quantilesState and merge it at query time with quantilesMerge for the quantile across multiple buckets to hold approximately.

The combination of source and rollup retention determines cost.

Sending From the Collector Into ClickHouse

Letting the exporter auto-create the schema is recommended only for development. In production, manage the DDL yourself and turn off auto-creation. The sort key and TTL need to differ by organization, and changing an auto-created schema later means recreating the table.

# otel-collector.yaml
exporters:
  clickhouse:
    endpoint: tcp://clickhouse.observability.svc:9000?dial_timeout=10s
    database: otel
    username: otel_writer
    password: ${env:CLICKHOUSE_PASSWORD}
    # Manage the DDL yourself in production
    create_schema: false
    logs_table_name: otel_logs
    traces_table_name: otel_traces
    compress: lz4
    async_insert: true
    timeout: 10s
    sending_queue:
      enabled: true
      num_consumers: 10
      queue_size: 10000
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_elapsed_time: 300s

processors:
  # ClickHouse likes large batches. Frequent small inserts make parts explode
  batch:
    timeout: 10s
    send_batch_size: 20000
    send_batch_max_size: 50000

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

Batch size is the key setting. ClickHouse is designed on the assumption that it receives tens of thousands of rows at a time. Hundreds of small inserts per second mass-produce parts and come back as merge load.

Here's a collection of failures you'll actually run into.

Check whether a query hits the sort key with EXPLAIN.

EXPLAIN indexes = 1
SELECT count()
FROM otel.otel_traces
WHERE ServiceName = 'checkout-api'
  AND Timestamp >= now() - INTERVAL 1 HOUR;
-- The number of marks read should be a tiny fraction of the total

Dividing Roles Among Prometheus, OpenSearch, and ClickHouse

Running all three looks wasteful, but each answers a different question. Attempts to consolidate into one usually end up losing the strength of one of them.

A realistic deployment looks like this.

• Alerting and SLO evaluation belong to Prometheus. It needs second-level evaluation and low-cost queries, and there's little reason to swap in another tool for this.
• Full-text search over recent logs belongs to OpenSearch. For queries that search text, like "logs containing this error message," an inverted index is overwhelmingly favorable. In exchange, keep retention short.
• Long-term retention and arbitrary analysis belong to ClickHouse. Raw traces, raw logs, and rollups all live here, and it handles questions answered by joining them.

When you run all three together, identifier consistency is non-negotiable. service.name, trace_id, and deployment.environment.name must hold the same value across all three systems, or you can't hop between tools. Normalize once in the collector, and have each exporter use that value as-is.

processors:
  transform/normalize:
    error_mode: ignore
    trace_statements:
      - context: resource
        statements:
          # Unify anything that came in under the old name to the current convention
          - set(attributes["deployment.environment.name"], attributes["deployment.environment"])
            where attributes["deployment.environment.name"] == nil
                  and attributes["deployment.environment"] != nil
          - delete_key(attributes, "deployment.environment")

Closing — Schema Decisions Are Hard to Reverse Later

In ClickHouse, what's easy to reverse and what's hard splits cleanly. Adding an index, changing TTL, adding a materialized view — you can do these while running. Changing the sort key or the partition key is effectively recreating the table.

So the adoption order looks like this. First, write down which queries will run thousands of times a day. Those queries' filter conditions should become the front of the sort key. Next, split retention between raw data and rollups. Finally, choose how to store attributes. Get just these three right early on, and everything else can be fixed later.

A check you can run right now is pulling the per-column compression ratio. If a column that doesn't compress is eating up half your storage cost, start by asking again whether you really need that column.

Further reading.

• ClickHouse: Observability schema design
• ClickHouse: MergeTree engine
• ClickHouse: Materialized View
• ClickHouse: JSON data type
• OpenTelemetry Collector: ClickHouse Exporter