The last lesson ended at "the resolver calls a @langfuse/shared repository to read". This lesson opens that repository layer door and
shows how it pulls data out of ClickHouse. The core is a discipline: every analytical query funnels through one executor —
queryClickhouse(). It uniformly wraps each query with OTel tracing, query tags, backoff retry, and
friendly resource-error wrapping. Plus a key trick that makes cross-table JOINs cheap — the look-back window. Get this layer and
you get the "master valve" for all of Langfuse's reads.
🏬 Analogy
Think of ClickHouse as a vast warehouse. If every worker (each piece of query code) barges into the warehouse to rummage on their
own, nobody records who took what, and when things go wrong there's no trail. Langfuse instead opens one single dispatch window
(queryClickhouse): everyone hands in their slip here. The clerk logs who's collecting (query tags), stamps a tracking
ticket (OTel span), and waits and retries if a shelf is briefly busy (backoff). If your slip tries to haul off the whole warehouse
(out of memory), the clerk won't let the warehouse collapse — they politely hand back "narrow it down and come again" (resource error). One
window handles observability, accountability, and fault tolerance all at once.
The one dispatch window: queryClickhouse
No reading code connects to ClickHouse directly; it hands the query and a set of tags to queryClickhouse(). Around the actual
query, it uniformly wraps several cross-cutting concerns:
One window, four things unified: queryClickhouse() wraps each query with an OTel span, log_comment tags, backoff retry, and resource-error wrapping, then sends it to ClickHouse (big reads via queryClickhouseStream/WithProgress). Source: packages/shared/src/server/repositories/clickhouse.ts:639.
The heart of the read path: a parameterized column query (per packages/shared/src/server/repositories and 0002_observations; values illustrative): the repository always carries WHERE project_id = ? (tenant isolation), ORDER BY start_time, LIMIT, and adds FINAL to read the merged-latest rows from ReplacingMergeTree. The returned rows are then assembled into the shape the list/detail UI needs.
Of these four, the query tags deserve a special mention. sendClickhouseQuery stuffs the tags into ClickHouse's
log_comment setting (the query log's comment field): so every SQL hitting ClickHouse carries "which project, which surface (UI/public
API/worker), which route initiated it". When one slow query drags down the cluster, operators can locate the culprit by tag directly in ClickHouse's own
query log — a way to weld observability into the data-access layer that scattered raw queries can never achieve.
The same dispatch window also offers several pickup modes for different read scenarios — but they share the same tracing/tags/retry machinery:
queryClickhousefetch all at once
The common case: run the query, read the whole result into an array, return. Most lists, details, aggregations go through it.
queryClickhouseStreamstream as it reads
Big reads needn't buffer the whole result in memory; they stream row by row — batch exports (the exports mentioned in L12) and other "result might be huge" cases use it, with bounded memory.
queryClickhouseWithProgressstreaming with progress
For SSE streaming dashboards: pushes progress to the frontend as it queries, so big dashboard queries "give feedback, don't freeze".
Look-back windows: cross-table JOINs without full scans
Remember Lesson 8 — trace, observation, score are three independent wide tables, with no foreign keys, correlated by time. The
problem: to stitch a trace's observations and scores together, must we full-scan an observations table of months and billions of rows? Unacceptably slow. The
repository layer's answer is the look-back window: exploiting the fact that "a trace's observations almost all cluster in a short window after it
happened", it searches only within a bounded time range.
Bounded look-back, skip old partitions: anchored at the trace's timestamp, observations are found within start_time ≥ anchor − 2 days, scores within 1 hour. These constants are injected into WHERE clauses, with Lesson 8's monthly partitioning letting JOINs touch only a few partitions. Source: repositories/constants.ts:4,9.
😖 no window: full-table JOIN
To find a trace's observations, search the whole observations table (months, billions of rows). Every list query doing this would soon drag the cluster down — the classic crash of using an analytical store like a transactional one.
😀 look-back window: bounded scan
Inject start_time ≥ trace.timestamp − 2 days into WHERE, and with monthly partitioning the JOIN reads only those few relevant partitions. The scan shrinks from "months" to "two days" — queries fly.
The window has a cost, of course: a rare unusually long observation (ending more than 2 days late) could fall outside the window and be missed. This
is a deliberate trade-off — trading "letting go of a tiny long tail of correctness" for "JOINs no longer scanning the whole table". Per a documented distribution
(~96% of observations start within 2 minutes of the trace per repositories/README.md, with a very long tail), Langfuse sets the window to 2 days, leaving a huge safety margin, so it almost never truly misses.
Between correctness and cost, engineers drew a pragmatic line by the real data distribution.
what this layer adds
what it solves
OTel span
every query traceable, its latency visible — observability welded into the data layer
log_comment tags
every SQL carries project/surface/route, so slow queries can be traced by tag in CH's query log
backoff retry
retryable errors (network blips) auto-retry without bothering the caller
resource-error wrapping
memory/timeout become a friendly ClickHouseResourceError, handed to L21 for "narrow it" advice
look-back window
cross-table JOINs scan only bounded partitions, keeping full scans out
legacy guard + streaming variants
forbids reading legacy events; big reads use Stream, SSE dashboards use WithProgress
🎯 Design trade-off
Why cram all reads into one executor, instead of writing SQL wherever you need it? Because cross-cutting concerns most dread being
scattered. Observability, accountability, retry, resource protection — capabilities unrelated to "what business queries" yet needed by every query — if
a hundred raw queries each implement them, some will write them, some omit them, and inconsistently. Converge into one executor and they're
implemented once, in effect everywhere: add a capability (say a new query-tag dimension) and every query platform-wide gains it
together. The look-back window is another kind of wisdom: it accepts the cost of Lesson 8's "three independent wide tables, correlated by time" design
(JOINs have no foreign key to follow), and uses "the data's real temporal locality" to amortize that cost to near zero. One master valve for all
commonalities, one pragmatic timeline for all JOINs — that's the whole essence of the repository layer.
🎯 Key points
All ClickHouse reads funnel into one executor queryClickhouse(), uniformly wrapped with OTel span, query tags, backoff retry, resource-error wrapping.
Query tags go via log_comment into ClickHouse's query log, so every SQL carries project/surface/route — slow queries traceable by tag.
Resource errors (memory/timeout) are wrapped into ClickHouseResourceError, handed to L21's error middleware for "narrow the time range" advice.
Look-back windows: trace/observation/score are three independent wide tables correlated by time; JOINs anchor at the trace timestamp and search only within 2 days (obs) / 1 hour (scores), avoiding full scans.
Tradeoff: cross-cutting capabilities converge to one place "implemented once, everywhere"; the time window uses "real temporal locality" to amortize Lesson 8's split-table JOIN cost, trading a tiny long-tail of correctness for huge cost savings.
🧪 Self-test - think about the design
1. Langfuse routes all ClickHouse reads through one queryClickhouse() executor rather than writing queries everywhere. The core benefit?
because ClickHouse accepts only one connection
to make query syntax simpler
to hide the SQL from view
cross-cutting concerns (OTel tracing, query tags, backoff retry, resource-error wrapping) are implemented once and apply everywhere; add a capability and every query gains it, with no some-write-some-omit inconsistency
Show answer & explanation click to expand
Answer: D. Observability, accountability, retry, resource protection are unrelated to 'what business queries' yet needed by every query. Converged into one executor they're implemented once and reused; scattered raw queries inevitably drift and omit. In line with L06's 'converge complexity to one place'.
2. queryClickhouse tags every SQL via log_comment with project/surface/route. What's the practical use?
the tags speed up the query
the tags replace the WHERE clause
the tags enter ClickHouse's query log with the query; when a slow query drags the cluster down, operators can locate the culprit project/surface/route by tag right in the log — observability welded into the data-access layer
the tags encrypt the query
Show answer & explanation click to expand
Answer: C. sendClickhouseQuery stuffs tags into clickhouse_settings.log_comment, so every SQL hitting CH carries 'who/which surface/which route initiated it'. On a slow query or resource exhaustion, you can trace the culprit by tag in CH's own query log — impossible for scattered raw queries.
3. To stitch a trace's observations, Langfuse doesn't full-scan observations but searches only a bounded window (e.g. 2 days) around the trace timestamp. Why can it, and why must it?
an arbitrary limit
because data older than 2 days is auto-deleted
because ClickHouse can't query beyond 2 days
can: trace/observation/score are three independent wide tables correlated by time, and a trace's observations almost all cluster shortly after it; must: otherwise the cross-table JOIN scans months and billions of rows, crashing the cluster by using an analytical store like a transactional one
Show answer & explanation click to expand
Answer: D. L08 made the three tables independent and time-correlated, so JOINs have no FK to follow. Look-back windows (OBSERVATIONS_TO_TRACE_INTERVAL=2 days, SCORE=1 hour) injected into WHERE, with monthly partitioning, scan only a few relevant partitions. The cost is missing extreme-long-tail observations (~96% start within 2 min per the repo README, with a long tail; 2 days leaves margin) — a tiny correctness trade for huge cost savings.
💭 Open questions (no single right answer - just think or try)
'Converge all data access into one executor, uniformly adding tracing, tags, retry, resource protection' — can you apply this to your systems? If your code is full of raw SQL/raw HTTP calls, converging them into one executor, which three capabilities would you add first, and why?