Five ClickHouse Decisions for LLM Trace Analytics

Start with the reads#

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.

The detail table: feature and time first#

The production logs table keeps one row per span, with Date materialized from TimestampMs:

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

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.

The day column has two jobs. PARTITION BY toYYYYMM(Date) gives retention a monthly boundary, and 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, and the timestamp condition gives the precise range. Secondary indexes support narrower filters such as organization name, user name, duration, token counts, and experiment ID. They supplement FeatureId and date constraints.

Why TraceId is not first#

TraceId first would make a known-trace lookup cheap. But support investigations begin with a feature and time window, and retention begins with time boundaries. A random trace ID at the start of each part scatters adjacent trace times across the primary index.

Trace lookups should carry the feature and date 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, so backfills and retries can write old partitions. Operators need a retention policy that accounts for replay.

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, so traces_aggregate stores mergeable values:

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);

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

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 merge rule, so define conflict resolution in the write model before choosing an aggregate function.

Child spans may also arrive before their root. The Redis coordination article covers how to choose a stable timestamp so one trace does not straddle ClickHouse partitions.

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, while SpanKind 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 but 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).

Merge materialized-view states at read time#

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

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.

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.

Store bounded display data#

ClickHouse holds truncated previews for quick inspection, and object storage holds complete prompts, completions, metadata, and variables. The search article covers that split.

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, which the payload delivery article covers.

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:

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.

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