ClickHouse Under LLM Observability: Why Trace Backends Converged on One Engine

A team called me in because their LLM tracing had become the most expensive part of their AI product. Not the model calls — the observability. They were spending more on storing and querying traces than on the inference those traces described, and the trace UI still took eleven seconds to open a conversation. Somebody suggested sampling to 5%. That was the moment they got nervous enough to ask for help, because sampling an LLM system means throwing away exactly the rare weird outputs you built the tracing to find.

The instrumentation was fine. They had spans, token counts, model names, the lot. The problem was one layer down, in a place nobody had thought of as an architectural decision: they had put LLM traces in Postgres, because that is where the rest of the application lived.

This is the article about that layer. Not what to instrument — I've written that one — but where the data goes afterwards, why nearly every serious LLM observability tool now sits on ClickHouse, and what the people who got there first learned the expensive way.

Why do LLM traces break the database you already have?

LLM traces break row-oriented databases because the questions you ask of them are analytical while the rows themselves are enormous. You want p95 latency by model over the last week, or token spend grouped by customer, or every trace where the retrieval step returned nothing — all scans and aggregations across a huge time range. A row store answers those by reading whole rows, and in an LLM trace the bulk of the row is a prompt and a completion you did not ask for.

That last part is what makes this workload different from ordinary APM. A conventional HTTP span is small and structurally boring: method, route, status, duration, a handful of attributes. A few hundred bytes. An LLM span carries the prompt, the completion, the tool definitions, sometimes retrieved documents and a base64 image. Kilobytes at minimum, occasionally megabytes. The payload is the interesting data — you cannot debug a bad answer without seeing the text — but it is also dead weight on nine out of ten queries.

Look at the retention row for a second, because it is the one that surprises people. In classic APM, sampling is orthodoxy — you keep 1% of healthy traffic and all the errors, and nobody minds, because one successful checkout looks like every other successful checkout. LLM systems are non-deterministic. The same prompt can produce a fine answer a thousand times and a hallucinated refund policy on the thousand-and-first, and that one span is the entire reason the tracing exists. There is no error flag on it. It returned HTTP 200 with a beautifully formatted lie.

So you keep everything. Which means you have signed up for a high-volume, append-heavy, wide, analytically-queried dataset with fat text columns and unbounded cardinality — and you have arrived, whether you meant to or not, at a columnar OLAP problem.

What ClickHouse is actually good at here

ClickHouse is a columnar OLAP database that stores each column separately, sorted and compressed, and reads only the columns a query names. I've written the full mechanics in ClickHouse Internals, but three properties do the work in this specific workload.

Column pruning makes the payload problem disappear. A dashboard querying avg(latency_ms) BY model touches two columns. The prompt text sits in a different file on disk and is never opened. In Postgres that same query drags every prompt blob through the buffer cache to get at a number sitting next to it. This is the single biggest win, and it is structural rather than a matter of tuning.

Compression is unreasonably effective on trace data. Trace columns are repetitive by nature — the same model names, the same handful of prompt templates, the same tenant IDs, monotonically increasing timestamps. Sorted, similar values sit adjacent, and general-purpose compression eats them. Teams moving OpenTelemetry data from row-oriented stores to ClickHouse commonly report 5–10× better compression, and for the highly templated text in LLM prompts I'd expect the top of that range rather than the bottom.

Sparse indexing turns time-range queries into small reads. Because the primary index marks granules of roughly 8,192 rows rather than individual rows, a query scoped to one project over one day skips almost the entire table without touching it. Trace queries are essentially always scoped that way.

None of this is LLM-specific, which is rather the point. LLM observability did not need a new kind of database. It needed the kind that observability vendors had already been quietly migrating onto for years — and the tooling arrived ready-made.

The Langfuse migration, in the open

The most useful case study here is public, which is rare. Langfuse is an open-source LLM observability platform, and its engineers documented their move off Postgres in enough detail to learn from. ClickHouse acquired the company on 16 January 2026, keeping the project under its existing MIT license — so the architecture is not only documented, it is readable.

Their breaking point came in 2023. Ingestion API response times spiked to 50 seconds under bursty traffic, and dashboards degraded worst for the largest customers — the ones with the most data and therefore the most reason to want analytics. The failure had the shape every row-store analytics story has: it worked beautifully until it didn't, and then it got worse in proportion to success.

What they built instead is worth studying, because the interesting part is not "they used ClickHouse." It is the write path around it.

graph LR
  SDK["Application SDK
batched spans"] --> API["Ingestion API
auth + validate only"]
  API --> S3[("S3
raw events, source of truth")]
  API --> R[("Redis
queue of references")]
  R --> W["Worker"]
  S3 --> W
  W --> CH[("ClickHouse
traces, observations, scores")]
  CH --> UI["Dashboards
trace browser"]

That missing arrow is the design. An LLM span is not written once — a trace gets updated as it completes, then again when an evaluation scores it, then again when a user leaves a thumbs-down. Roughly 90% of updates land within ten seconds of the original write, so a naive implementation reads the current row, merges, and writes back. In ClickHouse that read is either wrong or ruinously slow, because you'd need strong consistency settings to reliably see your own recent write.

Keeping raw events in S3 sidesteps it. The worker has the full history of an object in object storage, so it can compute the new state without asking the database anything. ClickHouse only ever receives inserts. If I had to name one idea to steal from this architecture wholesale, it's that one: make object storage the source of truth and the analytical database a derived, write-only projection. It is a good pattern well beyond observability.

The update problem, and why FINAL is a trap

ClickHouse does not do UPDATE in the way a transactional database does. The idiomatic answer for mutable rows is ReplacingMergeTree, which is what Langfuse used for traces, observations and scores: you write a new row with the same sorting key and a higher version, and background merges eventually collapse the duplicates.

The word doing all the work in that sentence is eventually. Deduplication happens when parts merge, on no schedule you control. Until then, both rows are there — and a query that naively counts traces will over-count.

The obvious fix is the FINAL modifier, which forces the merge at query time. It is also the thing I most often find quietly destroying performance in ClickHouse deployments that grew past their prototype.

There are cheaper ways to get the latest version, and choosing between them is a real engineering decision rather than a style preference:

In practice LIMIT 1 BY is where I start, because it is the one that stays fast as the table grows:

-- Latest version of each observation in a project's last 24h.
-- Cheap: the sorting key prunes granules, and no query-time merge happens.
SELECT
    id,
    trace_id,
    model,
    total_tokens,
    latency_ms
FROM observations
WHERE project_id = {project:String}
  AND start_time >= now() - INTERVAL 1 DAY
ORDER BY id, event_version DESC
LIMIT 1 BY id;

The catch — and there is always one — is that this only works if your ORDER BY key lets the filter prune before the dedup. Get the sorting key wrong and you are deduplicating the whole table to answer a one-day question. That decision is covered properly in Part 2 of the ClickHouse series, and it matters more here than almost anywhere else.

The wide-immutable turn

Here's where it gets genuinely interesting, because Langfuse's next move was to conclude that the mutable model was the mistake.

Their v4 architecture — in preview on Langfuse Cloud from 10 March 2026, with a self-hosted preview following in June — moves to an observation-centric data model built on a wide, mostly immutable table. No read-time joins between traces, observations and scores. No ReplacingMergeTree deduplication in the query path. One denormalized row, written once.

The numbers ClickHouse reported for the redesign are roughly three times less memory usage and up to twenty times faster analytical queries. Those are vendor figures for their own product and I'd treat the "up to" with the usual suspicion, but the direction is not in doubt, and the reasoning is sound enough to reproduce.

graph TD
  subgraph V3["v3 — normalized and mutable"]
    T["traces
ReplacingMergeTree"] --> J["JOIN at read time"]
    O["observations
ReplacingMergeTree"] --> J
    S["scores
ReplacingMergeTree"] --> J
    J --> D1["Deduplicate
then aggregate"]
  end
  subgraph V4["v4 — wide and immutable"]
    E["events
one wide row per observation"] --> D2["Aggregate directly"]
  end
  D1 -.->|"3x memory, 20x latency"| D2

The general principle underneath is older than LLMs and worth stating plainly: normalization is a write-side optimization, and analytical systems are read-side systems. Third normal form exists to stop update anomalies. If your rows are immutable facts about things that already happened, there are no update anomalies to prevent, and you are paying joins for a guarantee you don't need.

Which raises the obvious question — how do you handle late-arriving evaluations in an immutable model? You append them as separate event rows and resolve at read time within a single table, or you accept a bounded delay and assemble the wide row after the trace closes. Both beat a cross-table join against a deduplicating engine. The design tension doesn't vanish; it moves somewhere cheaper.

A schema I'd actually start from

If you are building this yourself rather than adopting a tool, here is the shape I'd begin with. It is deliberately boring.

CREATE TABLE llm_spans
(
    -- Identity and scoping
    project_id      LowCardinality(String),
    trace_id        String,
    span_id         String,
    parent_span_id  String,

    -- Time
    start_time      DateTime64(3),
    end_time        DateTime64(3),
    latency_ms      UInt32,

    -- The dimensions you filter and group by, every single day
    span_kind       LowCardinality(String),   -- llm | retrieval | tool | agent
    model           LowCardinality(String),
    provider        LowCardinality(String),
    prompt_version  LowCardinality(String),
    environment     LowCardinality(String),
    status          LowCardinality(String),

    -- The numbers you aggregate
    input_tokens    UInt32,
    output_tokens   UInt32,
    cached_tokens   UInt32,
    cost_usd        Decimal(12, 6),

    -- High-cardinality identifiers: strings, not enums
    user_id         String,
    session_id      String,

    -- Payloads live elsewhere; keep pointers and cheap previews
    input_ref       String,        -- s3://bucket/key
    output_ref      String,
    input_preview   String,        -- first ~500 chars, for the trace list
    error_message   String,

    -- Escape hatch for attributes the schema doesn't know about yet
    attributes      Map(LowCardinality(String), String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(start_time)
ORDER BY (project_id, start_time, trace_id, span_id)
TTL start_time + INTERVAL 90 DAY;

Four decisions in there are load-bearing, and the rest is detail.

LowCardinality on the dimensions, plain String on the identifiers. LowCardinality builds a dictionary per part, which is a large win for model names and environments and a net loss for user IDs — a dictionary the size of the data helps nobody. The heuristic I use: under roughly ten thousand distinct values, dictionary-encode it; above that, don't.

The sorting key leads with project_id then start_time. Every query in this system is scoped to a tenant and a time window, so those two prefixes let the sparse index prune before anything else happens. Putting trace_id first would feel natural — it's the thing you look up — and would be a serious mistake, because it destroys pruning for every aggregate query. If you need fast trace lookup, add a skip index; don't reorder the key.

Payloads by reference. input_ref points at object storage; input_preview holds enough text to render a list view. The trace browser reads previews and stays fast; opening a single trace fetches two objects from S3 and nobody notices the round trip. Keeping full prompts in the hot table is the mistake I see most often, and it taxes every query you will ever write against it.

A Map for the unknown. Which brings us to the standards problem.

The semantic conventions are not settled yet

OpenTelemetry's GenAI semantic conventions define how LLM operations should be represented as spans — attributes like gen_ai.request.model, gen_ai.usage.input_tokens, and span types for inference, retrieval and tool calls. They are the right thing to build on. They are also, as of mid-2026, still in Development status rather than stable, with an OTEL_SEMCONV_STABILITY_OPT_IN environment variable deciding whether an instrumentation library emits the old attribute shape or the current one.

What that means in practice: two services in your own stack, using different SDK versions, can emit differently-named attributes for the same concept. I've seen a token-cost dashboard read as a 40% drop in spend that was purely a library upgrade renaming an attribute. Nothing alerted, because nothing was broken — one series just stopped receiving data while another started.

The same discipline applies to cost. Prices change, and a cost_usd computed at write time freezes an assumption you may want to revisit. Keep the token counts, which are facts, alongside the derived cost, which is an opinion. I go further into that split in LLM FinOps and token costs.

Where the money actually goes

The team I opened with had assumed their cost problem was ClickHouse-shaped. It wasn't — they weren't running ClickHouse. But the diagnosis generalizes, because LLM observability spend has three components and people usually guess the wrong one.

That third row deserves emphasis because it is the failure mode ClickHouse newcomers hit hardest. Writing spans one at a time creates one part per insert, background merges can't keep up, and you hit the too many parts error — at which point ingestion stops. Batch on the client, or use async inserts and let the server batch for you. The whole failure mode and its fixes are in Part 3 of the series.

On query compute: the thing I check first is always dashboard defaults. A "last 30 days" panel refreshing every 30 seconds on eight open browser tabs is a surprisingly effective way to scan a large table 23,000 times a day for the benefit of nobody, and it never appears in anyone's mental model of their costs.

When I wouldn't reach for this

Adopting ClickHouse means running ClickHouse, and that is a real operational commitment — replication, merges, memory limits, version upgrades. It is not a database you install and forget, and I'd be doing you a disservice pretending the ops burden is trivial.

So: below roughly a million spans a month, Postgres is fine and the migration is premature. Use a hosted tool if tracing is not your product — the entire point of this article is that the storage layer is a solved problem, not that you should go solve it again yourself. And if your traces genuinely are small and structured, with no large payloads and no analytical queries, then you have an ordinary APM problem and ordinary APM tools will serve you better.

The argument for owning this layer gets strong at scale, when you want traces joined against your own business data, or when data residency means the payloads cannot leave your infrastructure. Those are good reasons. "It seemed more serious" is not one.

What to carry away

LLM traces are an analytical workload wearing an application-data costume — wide rows, fat text payloads, unbounded cardinality, and a retention requirement that forbids the sampling you'd normally lean on. Put them in a row store and every aggregate query pays for prompt blobs it never displays.

Columnar storage fixes that structurally rather than by tuning, which is why the tooling converged on ClickHouse rather than on anything LLM-specific. Two design choices then decide whether your implementation is fast or merely correct: keep payloads in object storage behind a reference, and prefer an immutable append-only model over mutable rows that make you pay for deduplication on every read. Langfuse arrived at both the expensive way and published the receipts.

And treat the GenAI semantic conventions as a moving target for now — keep the raw attribute map next to your typed columns, because the cheapest schema migration is the one you can run against data you already kept.

Frequently asked questions

Why do LLM observability tools use ClickHouse?

LLM traces are append-heavy, wide, and queried analytically over large time ranges — scans and aggregations across billions of spans rather than single-row lookups. That is exactly what columnar engines are built for, and ClickHouse additionally compresses the large, repetitive text payloads that dominate LLM trace volume. A row store has to read whole rows, including multi-kilobyte prompt blobs, to answer a question that only touches latency or token counts.

Why is the FINAL modifier expensive in ClickHouse?

FINAL forces a query-time merge across all parts sharing a sorting key so ReplacingMergeTree duplicates collapse to the latest version. It is correct but costly: memory rises and data-skipping indexes become less effective, because the engine cannot safely prune parts it may still need for deduplication. On large trace tables, argMax or ORDER BY … LIMIT 1 BY is usually much cheaper.

Should I store prompts and completions in ClickHouse?

Store metadata, identifiers, token counts, costs and latencies in ClickHouse, and keep the full prompt and completion payloads in object storage behind a reference, with a short preview column for list views. Payload columns are read whenever they are selected, so multi-kilobyte strings in a hot table make every trace-browsing query pay for data it usually doesn't show.

Are the OpenTelemetry GenAI semantic conventions stable?

No. As of mid-2026 they remain in Development status, with an opt-in environment variable controlling whether an instrumentation emits the older or newer attribute shape. Keep raw attributes in a Map column alongside your parsed typed columns so you can re-derive them when the convention moves.

Can I use ClickHouse for LLM traces and OpenTelemetry data together?

Yes, and it's the common arrangement — GenAI spans are ordinary OpenTelemetry spans with additional attributes, so they flow through the same collector pipeline into the same backend. Keeping them in one store is the main practical advantage: you can join an LLM span to the HTTP request that triggered it without correlating across two systems.

References

• Langfuse — From Zero to Scale: Infrastructure Evolution (the Postgres-to-ClickHouse migration, in their own words)
• Langfuse Engineering Handbook — ClickHouse (deduplication strategies, batching, granule effects)
• ClickHouse — ClickHouse welcomes Langfuse (16 January 2026)
• ClickHouse — Scaling LLM observability for the agentic era (3 February 2026; source of the 3× memory / 20× query figures)
• OpenTelemetry — Generative AI semantic conventions
• ClickHouse Docs — Integrating OpenTelemetry