ClickHouse Observability at Scale: 7 Costly Schema Mistakes We Measured

Why telemetry is different

What breaks first in ClickHouse observability at scale

Telemetry is a peculiar workload, and ClickHouse observability at scale fails in its own ways. Volume grows on three axes at once: more teams ship services, each service emits more signals, and each signal carries more attributes. None of those growth curves is under the database team’s control. Meanwhile the readers want the same experience they had at a tenth of the volume: dashboards that load instantly, trace lookups in seconds, alerts that do not lag.

Three forces pull against each other in every design choice: storage and compute cost, query latency, and the experience of whoever is asking the question, which increasingly includes automated agents as well as engineers. Optimise one and the other two move. A raw table with no rollups is cheap to write and expensive to read. A dozen materialized views make dashboards fast and inserts slow. The job is to find the balance for your access patterns and then hold it as the data grows.

The second lesson is that an OpenTelemetry ClickHouse stack is a pipeline, not a database. Most of the failures we are called in for start upstream of the table: in how the SDKs name things, in how often collectors flush, in what the gateway forwards. Figure 1 is the map we use to decide where each fix belongs.

The lab behind every measurement: ClickHouse 26.9.2.8 on a 2 vCPU node, 20 million synthetic log records spread over 48 hours from 24 services, with OpenTelemetry-shaped columns (Timestamp, TraceId, ServiceName, SeverityText, Body and two attribute maps). Seven of the 24 services behave like legacy code: no trace context, and their useful identifiers exist only inside the message text. Query timings are medians of three runs with the query condition cache off; granule and byte counts are exact.

Mistake 2

ClickHouse logs schema mistake: leaving structure inside strings and maps

OpenTelemetry ClickHouse schemas keep attributes in Map columns, which is the right default: the set of keys is open and changes with every deploy. The cost shows up when a dashboard filters on the same key thousands of times a day. A map lookup has to read the whole map column for every candidate row, and nothing in the primary key or the skip indexes knows about the key inside it. Free text is worse. When the only place an identifier exists is the log message, every lookup is a substring scan over the largest column in the table.

The fix is to do the expensive work once, at insert time, instead of on every query. We built a shaped copy of the service-first table: two attributes promoted to typed columns with MATERIALIZED expressions, a bloom filter on TraceId and a set index on the status code. A second pair of tables did the same for the legacy services, whose order ids exist only inside the message text.

-- OpenTelemetry ClickHouse shaping at ingest: same rows, structure promoted once at insert time
CREATE TABLE obs.logs_shaped
(
    Timestamp           DateTime64(9, 'UTC') CODEC(Delta(8), ZSTD(1)),
    TraceId             String CODEC(ZSTD(1)),
    SpanId              String CODEC(ZSTD(1)),
    SeverityText        LowCardinality(String),
    ServiceName         LowCardinality(String),
    Body                String CODEC(ZSTD(1)),
    ResourceAttributes  Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    LogAttributes       Map(LowCardinality(String), String) CODEC(ZSTD(1)),

    -- computed on INSERT, stored as real columns, invisible to SELECT *
    HttpStatus  UInt16                 MATERIALIZED toUInt16OrZero(LogAttributes['http.status_code']),
    PodName     LowCardinality(String) MATERIALIZED ResourceAttributes['k8s.pod.name'],

    -- skip indexes for the lookups the sort key cannot serve
    INDEX idx_trace  TraceId    TYPE bloom_filter(0.01) GRANULARITY 1,   -- point lookups by trace
    INDEX idx_status HttpStatus TYPE set(16)            GRANULARITY 4    -- few distinct values per block
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, Timestamp);

-- ClickHouse logs schema for legacy services: the order id lives only in the message, so extract it once
ALTER TABLE obs.legacy_shaped
    ADD COLUMN IF NOT EXISTS OrderId UInt64
        MATERIALIZED toUInt64OrZero(extract(Body, 'order_id=(\\d+)'));
ALTER TABLE obs.legacy_shaped
    ADD INDEX IF NOT EXISTS idx_order OrderId TYPE bloom_filter(0.01) GRANULARITY 1;

Finding one order in the legacy services’ logs meant a LIKE over every message: 1,254.2 MB, all 2,442 granules and 974 ms. With the order id extracted into a column at insert time, the same lookup read 1.0 MB from 14 granules in 18 ms. The trace lookup on the service-first table went from all 2,442 granules and 413 ms to 25 granules and 43 ms, purely from the bloom filter. Filtering HTTP 500s over six hours went from 43.7 MB read through the map to 1.8 MB through the promoted column and its set index.

It was not free. The shaped table is 917.2 MiB compressed against 804.9 MiB for the same rows without the extra columns and indexes, 14% more storage, and it took 85.1 s to load against 82.0 s. The extracted order id was cheaper: 757.9 MiB against 739.2 MiB, 2.5%. Promote the handful of keys that dashboards and alerts filter on every minute. Leave the long tail in the map, where it costs nothing until someone asks.

Mistake 4

ClickHouse observability at scale on the write path: materialized views are not free

An incremental materialized view runs on every INSERT into its source table, inside the same insert, and writes its own parts. That is what makes it fast to read and what makes it a cost on the write path. We sent 2.4 million records in 24 batches of 100,000 into a table with zero, one, two and three views attached.

-- ClickHouse observability at scale, rollup 1: errors per service per minute (24 services: aggregates well)
CREATE TABLE obs.errors_per_min
(
    minute       DateTime,
    ServiceName  LowCardinality(String),
    errors       UInt64
)
ENGINE = SummingMergeTree
ORDER BY (ServiceName, minute);

CREATE MATERIALIZED VIEW obs.mv_errors_per_min TO obs.errors_per_min AS
SELECT toStartOfMinute(Timestamp) AS minute, ServiceName, count() AS errors
FROM obs.mv_base
WHERE SeverityText = 'ERROR'
GROUP BY minute, ServiceName;

-- ClickHouse high cardinality warning, rollup 2: requests per pod and status per minute (thousands of pods: aggregates badly)
CREATE TABLE obs.pod_status_per_min
(
    minute    DateTime,
    PodName   LowCardinality(String),
    status    UInt16,
    requests  UInt64
)
ENGINE = SummingMergeTree
ORDER BY (PodName, status, minute);

CREATE MATERIALIZED VIEW obs.mv_pod_status_per_min TO obs.pod_status_per_min AS
SELECT toStartOfMinute(Timestamp)                        AS minute,
       ResourceAttributes['k8s.pod.name']                AS PodName,
       toUInt16OrZero(LogAttributes['http.status_code']) AS status,
       count()                                           AS requests
FROM obs.mv_base
GROUP BY minute, PodName, status;

-- ClickHouse log storage index, rollup 3: one row per trace id (grows with traffic)
CREATE TABLE obs.trace_index
(
    TraceId   String,
    first_ts  SimpleAggregateFunction(min, DateTime64(9, 'UTC')),
    last_ts   SimpleAggregateFunction(max, DateTime64(9, 'UTC')),
    services  AggregateFunction(groupUniqArray, LowCardinality(String))
)
ENGINE = AggregatingMergeTree
ORDER BY TraceId;

CREATE MATERIALIZED VIEW obs.mv_trace_index TO obs.trace_index AS
SELECT TraceId, min(Timestamp) AS first_ts, max(Timestamp) AS last_ts,
       groupUniqArrayState(ServiceName) AS services
FROM obs.mv_base
WHERE TraceId != ''
GROUP BY TraceId;

The error rollup was effectively free: throughput stayed at 190.9 thousand rows per second and the view added 4,896 rows. The pod-and-status rollup was not. It wrote 1.75 million rows for 2.4 million input rows, because its grouping key barely groups anything inside a 100,000-row block. With all three views, throughput fell 24% to 145.8 thousand rows per second, insert CPU rose 28%, and four parts were created per INSERT instead of one, each of them a future merge.

Before adding a view, estimate its reduction ratio: rows in the target per block divided by rows in the block. A view that keeps more than a few percent of its input is a second copy of the table with extra steps. Some of those rollups are cheaper to compute in the gateway, where they never touch the merge pipeline. The ClickHouse guide to incremental materialized views covers the mechanics.

Mistake 6

One retention policy for all ClickHouse log storage

Telemetry loses value quickly, and ClickHouse log storage should follow that curve. The last hour is queried constantly, last week occasionally, last quarter almost never, and a single tier of fast storage for all of it is the expensive default. ClickHouse can move parts between volumes and delete them with table TTLs. We gave the lab a two-volume storage policy and a table that moves data to the cold volume after one day and deletes it after thirty.

<!-- config.d/storage.xml: ClickHouse log storage tiers, a hot volume on the default disk, a cold volume on a second disk -->
<clickhouse>
    <storage_configuration>
        <disks>
            <cold_disk>
                <type>local</type>
                <path>/var/lib/clickhouse-cold/</path>   <!-- larger, cheaper disk -->
            </cold_disk>
        </disks>
        <policies>
            <hot_cold>
                <volumes>
                    <hot><disk>default</disk></hot>
                    <cold><disk>cold_disk</disk></cold>
                </volumes>
                <move_factor>0.1</move_factor>   <!-- also move when hot has under 10% free -->
            </hot_cold>
        </policies>
    </storage_configuration>
</clickhouse>

-- ClickHouse log storage with retention tiers: hot for a day, cold until day 30, then gone
CREATE TABLE obs.logs_tiered
(
    Timestamp           DateTime64(9, 'UTC') CODEC(Delta(8), ZSTD(1)),
    TraceId             String CODEC(ZSTD(1)),
    SpanId              String CODEC(ZSTD(1)),
    SeverityText        LowCardinality(String),
    ServiceName         LowCardinality(String),
    Body                String CODEC(ZSTD(1)),
    ResourceAttributes  Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    LogAttributes       Map(LowCardinality(String), String) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, Timestamp)
TTL toDateTime(Timestamp) + INTERVAL 1 DAY  TO VOLUME 'cold',
    toDateTime(Timestamp) + INTERVAL 30 DAY DELETE
SETTINGS storage_policy = 'hot_cold',
         ttl_only_drop_parts = 1;   -- drop whole parts at expiry instead of rewriting them

-- ClickHouse log storage: where does each day live now?
SELECT partition, disk_name, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE database = 'obs' AND table = 'logs_tiered' AND active
GROUP BY partition, disk_name
ORDER BY partition;

Twenty seconds after loading, both day partitions were on the cold volume.

Seven of the 40 new parts were written there directly, because their rows were already past the TTL at insert time. The other 33 were moved, the slowest move taking 70 ms. We loaded at 00:18 on 9 October, so even the newest rows of 8 October were not yet a full day old. Their whole partition still landed on the cold volume within those twenty seconds. Moves and deletes act on whole parts, not rows, so the partition key and part sizes decide how precise retention can be. Test the boundary behaviour on your own data before promising a tenant an exact hot window.

We also compared codecs for the cold tier on one day of data, 10 million rows. The right half of Figure 5 has the numbers: ZSTD(1) halved the message column against LZ4, from 227.4 MB to 118.8 MB. ZSTD(9) saved only another 6% and took more than twice as long to load and merge, 52.6 s against 24.4 s. Our cold tiers stop at ZSTD(3). The ClickHouse documentation on MergeTree TTL and storage policies covers the full syntax, including object storage disks.

Shared platforms

Multi-tenant ClickHouse log storage on one cluster

A platform team serving many internal teams, or a vendor serving many customers, has to decide where tenants are separated. Separate clusters per tenant give the cleanest isolation of performance and data, and cost the most to run. A shared cluster is cheaper, and then isolation has to come from the database. In ClickHouse that means row policies for visibility and quotas for fairness, with every tenant reaching the data through a role rather than a shared account.

-- OpenTelemetry ClickHouse tenancy: one tenant's view of a shared ClickHouse logs schema: its rows only, and a bounded share of the cluster
CREATE ROLE IF NOT EXISTS tenant_payments;

CREATE ROW POLICY IF NOT EXISTS rp_payments ON obs.logs_shaped
    FOR SELECT USING ServiceName = 'payments'      -- in a real platform: a TenantId column in the sort key
    TO tenant_payments;

CREATE QUOTA IF NOT EXISTS q_tenant_payments
    FOR INTERVAL 1 hour MAX queries = 2000, read_bytes = 50000000000
    TO tenant_payments;

GRANT SELECT ON obs.logs_shaped TO tenant_payments;

CREATE USER IF NOT EXISTS payments_ro
    IDENTIFIED WITH sha256_password BY '${TENANT_PASSWORD}'
    DEFAULT ROLE tenant_payments;

-- Verified in the lab: as payments_ro, a GROUP BY ServiceName returns only 'payments' (833,151 rows)

Put the tenant column first in the sort key, so that a tenant’s queries prune to its own granules instead of filtering everyone’s. Quotas cap the damage a single runaway dashboard can do, but they do not stop one tenant’s ingestion spike from slowing everyone’s merges. When a tenant’s volume or its service levels justify it, giving that tenant its own storage is the honest answer. The ClickHouse reference for row policies lists the full syntax.

Design review

The ClickHouse observability at scale checklist we use

Test every ClickHouse logs schema change in this post on a copy of your own telemetry before applying it to production, and keep backups and a rollback for every ALTER. A sort key cannot be changed in place: it means a new table and a backfill.

Further reading

Related ChistaDATA guides and sources

From our blog: ClickHouse performance observability on 26.9, which uses the system tables referenced here, and ClickHouse high availability on 26.9, for running the same telemetry store across replicas.

ClickHouse documentation: asynchronous inserts, data skipping indexes, incremental materialized views, MergeTree, TTL and storage policies and row policies.

Measurements: ChistaDATA lab, one 2 vCPU node, ClickHouse 26.9.2.8, 20,000,000 synthetic OpenTelemetry-style log records over 48 hours from 24 services, October 2026. Query times are medians of three runs with the query condition cache off; granule and byte counts are from system.query_log. Results on real telemetry will differ.