Keeping a log facet query cheap at any time range

Beside the log stream in the Log Explorer sits the facet rail: the attribute keys in your logs, and for each one, its most common values with a count. Service, status, host, plus any key your logs carry, such as http.route or k8s.namespace.name. It re-renders whenever the query changes, so it runs as often as the log stream itself.

That makes it one of the most expensive things on the page. This post explains how we keep it cheap without making its numbers approximate, and where we deliberately didn't.

The query that grows with the window

Log management in Tracix stores logs in an OpenTelemetry-shaped ClickHouse table, otel_logs, with attributes in two maps: LogAttributes and ResourceAttributes. To list the keys in a window, the obvious query unpacks both maps on every row:

SELECT k, count() AS c
FROM (
  SELECT arrayJoin(arrayConcat(mapKeys(LogAttributes), mapKeys(ResourceAttributes))) AS k
  FROM otel_logs
  WHERE TimestampTime >= {from:DateTime} AND TimestampTime < {to:DateTime}
)
GROUP BY k
ORDER BY c DESC
LIMIT 60

It is correct, and its cost is proportional to the number of log lines in the window. Over the default window of the last 15 minutes that is fine. Over 30 days it reads every log line of the month, for a panel that is meant to feel instant.

A rollup in 15-minute buckets

The fix is to do the counting once, at insert time. A ClickHouse materialized view folds every key and value it sees into a small table, bucketed by 15 minutes:

CREATE TABLE logs_kv_rollup_15m
(
    bucket DateTime('UTC'),
    col    LowCardinality(String),
    key    String,
    value  String,
    count  UInt64
)
ENGINE = SummingMergeTree(count)
PARTITION BY toDate(bucket)
ORDER BY (col, key, value, bucket);

A SummingMergeTree adds up the counts of rows with the same key as it merges, so one busy service produces one row per key, value and bucket, not one per log line. The view writes two kinds of rows:

• col = 'a': attribute keys from both maps, plus the top-level string keys of JSON log bodies.
• col = 'f': the computed fields the rail always shows: service, status, source, dataset, namespace and host. Status is derived from the severity, the same way the explorer derives it.

It also has three deliberate limits. Values are cut at 256 characters, because no facet chip shows more and long values are where cardinality explodes. Empty values are dropped. And message and trace_id aren't rolled up at all: one is free text, the other is unique on almost every row, so a rollup of either would be as big as the logs.

Exact counts on windows that don't line up

Here is the catch. The rollup knows 09:00 to 09:15, 09:15 to 09:30, and so on. The window you are looking at almost never starts or ends on one of those boundaries: "the last hour" at 10:07:30 runs from 09:07:30. Reading every bucket the window touches would count up to 15 extra minutes at each end, and the numbers on the rail would be wrong in a way nobody would notice.

So the read is a hybrid. Full buckets inside the window come from the rollup, and the two partial slivers at the edges come from the raw logs:

export const KV_ROLLUP_BUCKET_MS = 900_000

/** Full rollup buckets inside [fromMs, toMs): null when none fit. */
export function kvRollupSpan(fromMs: number, toMs: number) {
  const bFromMs = Math.ceil(fromMs / KV_ROLLUP_BUCKET_MS) * KV_ROLLUP_BUCKET_MS
  const bToMs = Math.floor(toMs / KV_ROLLUP_BUCKET_MS) * KV_ROLLUP_BUCKET_MS
  return bToMs > bFromMs ? { bFromMs, bToMs } : null
}

/** The two sub-bucket edge slivers of [fromMs, toMs) around a rollup span. */
export function kvEdgeRanges(range: { fromMs: number; toMs: number }, span: { bFromMs: number; bToMs: number }) {
  const edges: Array<[number, number]> = []
  if (span.bFromMs > range.fromMs) edges.push([range.fromMs, span.bFromMs])
  if (range.toMs > span.bToMs) edges.push([span.bToMs, range.toMs])
  return edges
}

For "the last hour" at 10:07:30, the rollup answers 09:15 to 10:00, three buckets, and raw scans answer 09:07:30 to 09:15 and 10:00 to 10:07:30. The query is a UNION ALL of the three parts, summed by key. When a window is too short to hold a full bucket, as most 15-minute windows are, the whole read is raw, which is what it would have been anyway.

The important property is the bound. Each sliver is shorter than one bucket, so the raw part of the read is never more than 30 minutes of logs, whether the window is an hour or a month. The rest is a scan of a table whose size depends on how many distinct keys and values each 15 minutes holds, not on how many log lines arrived.

One more detail keeps the two halves consistent: the raw slivers apply the same 256-character cut to values before counting, so a long value lands on the same row of the rail whether it came from the rollup or the edge.

Where we kept the expensive path

A rollup answers one question well: how often each key and value occurred. Three things on the page ask other questions, and we let them pay full price rather than return a number that's quietly wrong.

Filtered facets. Once you filter, say service:checkout-api status:error, the rail has to count only the matching lines, and the rollup doesn't know which lines matched. Any filter sends the facet query to the raw logs.

Field statistics. The Field statistics tab counts how many documents carry each field and how many distinct values it has. Distinct counts don't add up across buckets, a value seen in two buckets is still one value, so this tab always reads raw logs. It only runs when you open it.

Selected values. When you select values in a facet, the counts for that facet are computed without your own selection, so the values you haven't picked keep showing what you would get by switching. The other facets narrow as usual.

The expensive paths still run under the same guardrails as every query in a workspace: a time budget, cancellation when you leave the page, and an error instead of partial counts when a query runs out of time. We wrote about those in Why every Tracix workspace gets its own ClickHouse database.

The same pattern for metrics

The Metrics Explorer had the same problem with its facets and its dimension list: ranking attribute keys over a 30-day window meant reading every series in that window. It now uses a sibling rollup, metrics_kv_rollup_15m, with the same hybrid read and the same bound. One difference: metric attribute values are often unique per point, round-trip times or byte counts, so that rollup keeps attribute keys only, and listing a key's values stays an on-demand raw query.

What it costs

Nothing here is free. The materialized view runs on every insert into otel_logs, which adds work at ingest. The rollup is another table to store, though a far smaller one than the logs it summarizes. And a materialized view only sees rows inserted after it exists, which matters when you add one to a table that already holds weeks of data.

For a panel that renders with every query, at any range, we think that's the right trade. If you want to see it, open the Log Explorer, set the range to 30 days and watch the rail. Logs and pipelines covers the rest of the explorer.