Five ClickHouse Decisions for LLM Trace Analytics

Start with the reads#

The previous articles established how the ingestion path normalizes analytical data before it reaches ClickHouse. This article starts with the reads that data must serve.

The useful starting point was a small query table:

Trace needs more than one physical shape. The detail page reads many span rows, while the analytics chart reads one pre-aggregated row per feature and day.

Each destination serves one read: span details, trace rollups, or daily aggregates.

The detail table: feature and time first#

The production logs table keeps one row per span. Its primary key is:

ENGINE = MergeTree
PARTITION BY toYYYYMM(Date)
ORDER BY (FeatureId, Date, TimestampMs)

Date is materialized from TimestampMs:

Date Date MATERIALIZED toDate(TimestampMs),
TimestampMs DateTime64(3),
FeatureId LowCardinality(UUID),
TraceId String,
ParentSpanId String

That order describes the contract. A reader starts inside one feature, narrows to a date range, then reads time-ordered spans. ClickHouse can skip large regions only when the query respects the order that was written.

MergeTree records sparse marks for ordered data parts and skips granules outside the key range. The key serves feature-scoped, time-bounded reads, so it omits TraceId.

The day column has two jobs. PARTITION BY toYYYYMM(Date) gives retention and partition management a monthly boundary. Date inside the sort key helps a normal product query narrow within that partition. A detail query should give ClickHouse both:

SELECT TraceId, SpanId, ParentSpanId, SpanName, TimestampMs, DurationMs
FROM logs
WHERE FeatureId = {feature:UUID}
  AND Date BETWEEN {from_day:Date} AND {to_day:Date}
  AND TimestampMs >= {from:DateTime64(3)}
  AND TimestampMs < {to:DateTime64(3)}
ORDER BY TimestampMs
LIMIT 200;

The Date condition enables partition and key pruning. The timestamp condition gives the precise range. Secondary indexes support narrower filters: organization name, user name, duration, token counts, and experiment ID. They supplement FeatureId and date constraints.

A trace needs a different row#

A trace list needs a stable summary: first timestamp, maximum duration, total tokens and cost, and bounded previews. Computing that from every matching span grows expensive with trace volume.

The traces_aggregate table stores mergeable values instead:

CREATE TABLE traces_aggregate
(
    FeatureId LowCardinality(UUID),
    Date Date MATERIALIZED toDate(TimestampMs),
    TraceId String,
    TimestampMs SimpleAggregateFunction(min, DateTime64(3)),
    DurationMs SimpleAggregateFunction(max, UInt64),
    InputTokens SimpleAggregateFunction(sum, UInt64),
    OutputTokens SimpleAggregateFunction(sum, UInt64),
    Cost SimpleAggregateFunction(sum, Decimal(19, 10)),
    IdealOutput SimpleAggregateFunction(max, String)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(Date)
ORDER BY (FeatureId, Date, TraceId);

min selects the trace’s earliest timestamp. max preserves the longest duration. Token and cost values sum. Each aggregation function encodes part of the trace summary’s meaning.

The aggregate row carries root metadata, while child projections write empty strings for root-only values. Given that write-side invariant, max retains the populated root value. A competing non-empty child value would make lexical max an invalid semantic merge rule. Define conflict resolution in the write model before choosing an aggregate function.

A later installment of this series examines the awkward case behind Date: child spans may arrive before their root. A Redis coordination model must choose a stable timestamp so one trace does not straddle ClickHouse partitions merely because messages arrived out of order.

Detect roots from the parent relationship#

Daily analytics counts traces and computes trace latency once per trace. An empty ParentSpanId identifies a root across the supported producers. SpanKind describes instrumentation semantics and varies between SDKs and legacy producers.

The current daily view uses scalar count(), countIf(), sum(), min(), and max() columns beside aggregate states. AggregatingMergeTree merges aggregate-state columns; it keeps an ordinary column’s first value when parts merge. Treating those scalar metrics as merged totals is a schema debt.

A replacement view should write aggregate states for every metric that needs a cross-part total:

CREATE MATERIALIZED VIEW analytics_daily_mv
ENGINE = AggregatingMergeTree()
ORDER BY (FeatureId, Date)
AS SELECT
    FeatureId,
    Date,
    countState() AS SpanCountState,
    countIfState(ParentSpanId = '') AS TraceCountState,
    sumState(Cost) AS TotalCostState,
    quantilesState(0.5, 0.95, 0.99)(DurationMs) AS LatencyQuantiles,
    avgIfState(DurationMs, ParentSpanId = '') AS AvgTraceLatencyState
FROM logs
WHERE ExperimentID = ''
GROUP BY FeatureId, Date;

The experiment view needs the same state functions and orders by (FeatureId, ExperimentID, Date) because experiment readers filter by a feature and experiment before comparing days.

Choose the ordering key for dependable queries#

TraceId first makes a known-trace lookup cheap:

SELECT *
FROM logs
WHERE TraceId = {trace_id:String};

Support investigations begin with a feature and time window. Retention and partition operations begin with time boundaries. A random trace ID at the start of each part scatters adjacent trace times across the primary index.

The key favors the dependable query:

(FeatureId, Date, TimestampMs) fits workloads whose dependable constraints are FeatureId and a time range. Trace lookups should carry both constraints from the route or surrounding UI. Otherwise, search a bounded set of relevant dates and charge that cost to the exceptional query.

Date follows the span timestamp. Backfills and retries can therefore write old partitions, so operators need a retention policy that accounts for replay.

Merge materialized-view states at read time#

AggregatingMergeTree stores aggregate states that merge as parts combine. Reader queries merge the states they select.

For an analytics range, merge state columns and group by every requested dimension:

SELECT
    Date,
    countMerge(SpanCountState) AS spans,
    countIfMerge(TraceCountState) AS traces,
    avgMerge(AvgTraceLatencyState) AS average_trace_latency_ms,
    quantilesMerge(0.5, 0.95, 0.99)(LatencyQuantiles) AS latency
FROM analytics_daily_mv
WHERE FeatureId = {feature:UUID}
  AND Date BETWEEN {from_day:Date} AND {to_day:Date}
GROUP BY Date
ORDER BY Date;

GROUP BY Date produces a daily chart. A range summary omits Date and merges across the selected range:

SELECT
    countMerge(SpanCountState) AS spans,
    countIfMerge(TraceCountState) AS traces,
    sumMerge(TotalCostState) AS cost,
    avgMerge(AvgTraceLatencyState) AS average_trace_latency_ms
FROM analytics_daily_mv
WHERE FeatureId = {feature:UUID}
  AND Date BETWEEN {from_day:Date} AND {to_day:Date};

Charts group by Date; range summaries merge all selected states. Summing daily averages weights a low-volume day as heavily as a high-volume day. quantilesMerge combines percentile states because averaging displayed p95 values cannot recover a weekly distribution.

Review the materialized-view definition and repository query together. The current daily views mix aggregate states with scalar values, so cross-part totals for counts, tokens, cost, and first/last seen need a schema replacement. Free-form prompt text remains outside them.

Store bounded display data#

ClickHouse holds truncated previews for quick inspection; object storage holds complete prompts, completions, metadata, and variables.

The storage split is:

A preview needs a named bound, and the payload store needs its own authorization and retrieval path. Dashboard code should fetch from object storage only when the user requests the complete payload.

Test representative query shapes#

Run representative statements against production-like data: many features, several months, varied trace sizes, and enough rows to expose a full scan.

For each important read, inspect the query plan and record three things:

Verify that query predicates match the table’s physical ordering: feature, day, and time. Measure and accept the cost of a screen that cannot use those constraints.

An organization-wide report is a separate workload. It may need a different aggregate, a constrained export job, or a product decision not to offer that view live.

Keep surrounding contracts explicit#

The producer must supply the correct trace timestamp. Date MATERIALIZED toDate(TimestampMs) derives a consistent date from that value.

Opaque OpenTelemetry trace IDs remain valid and non-local. The trace-ID locality article examines controlled, time-prefixed generators as an optional optimization.

Operators must define retention, benchmark index effectiveness, and decide how mutable results are updated. The service boundary must enforce authorization, retention, and preview limits.

I now use five rules:

1. Write down the query a product screen must answer.
2. Put its invariant filters at the front of the key.
3. Pre-aggregate only when the resulting row has a stable meaning.
4. Keep the full payload outside the analytical hot path.
5. Benchmark the query against representative data before calling the schema finished.