ClickHouse Query Design for the Observations Table#

Overview#

Langfuse stores LLM trace observations in a ClickHouse table using the ReplacingMergeTree engine. Because ClickHouse's merging is asynchronous, querying this table requires deliberate choices around deduplication, time-window filtering, and when to apply the FINAL modifier. Getting these wrong leads to either duplicate rows (from un-merged parts) or missed observations (from overly narrow time filters).

Table Schema#

The observations table is defined in 0002_observations.up.sql as:

ENGINE = ReplacingMergeTree(event_ts, is_deleted)
PARTITION BY toYYYYMM(start_time)
ORDER BY (project_id, type, toDate(start_time), id)

Key points 1:

• Deduplication key: (project_id, type, toDate(start_time), id) — rows with the same key are merged, keeping the one with the highest event_ts.
• Soft deletes: is_deleted column in the ReplacingMergeTree signature allows logical deletion without a hard DELETE.
• Partitioning: toYYYYMM(start_time) — partitioning by month means time-based filters prune partitions efficiently.
• Skip indexes: Bloom filters on id, trace_id, and project_id for fast point lookups 2.

Deduplication: FINAL vs. ORDER BY + LIMIT 1 BY#

Because background merges are not instantaneous, un-merged duplicate rows can coexist in different parts. Two strategies are used to deduplicate:

1. FINAL modifier#

Applies ClickHouse's merge logic at query time, guaranteeing a single latest row per dedup key. Used in correctness-critical paths such as:

• getObservationMetricsForPrompts 3
• getLatencyAndTotalCostForObservations 4
• getLatencyAndTotalCostForObservationsByTraces 5
• getObservationsGroupedByTraceId 6
• getObservationsForBlobStorageExport 7

Cost: FINAL is slow — it forces ClickHouse to read and merge all parts, bypassing skip indexes.

2. ORDER BY event_ts DESC + LIMIT 1 BY id, project_id#

A lighter-weight alternative that achieves deduplication by sorting candidates and keeping the latest. Used in getObservationsForTrace 8 and similar per-trace lookups. This can use skip indexes, making it faster for targeted queries with good predicates.

A CTE wrapper is also used to avoid FINAL while still deduplicating — see getCostForTraces, where the inline comment notes: "Wrapping the query in a CTE allows us to skip FINAL which allows ClickHouse to use skip indexes."

Conditional FINAL: OTel Projects and the shouldSkipObservationsFinal Function#

For OpenTelemetry (OTel) projects, spans are immutable — they are never updated after ingestion. Deduplication is therefore unnecessary. The shouldSkipObservationsFinal function encapsulates this logic:

1. Environment override: If LANGFUSE_API_CLICKHOUSE_DISABLE_OBSERVATIONS_FINAL=true, FINAL is always skipped 9.
2. OTel project detection: Redis tracks which projects are OTel users. If the project is detected as OTel, FINAL is skipped 10.

This is applied in getObservationsForTrace and getObservationsTableInternal. When skipping dedup, the ORDER BY event_ts DESC / LIMIT 1 BY clauses are also omitted 8:

${skipDedup ? "" : "ORDER BY event_ts DESC"}
${skipDedup ? "" : "LIMIT 1 BY id, project_id"}

The Traces API also uses shouldSkipObservationsFinal when building the observation_stats CTE, conditionally adding FINAL only when metrics or observation filters are requested 11.

TRACE_TO_OBSERVATIONS_INTERVAL: The 1-Hour Time Window#

When querying observations for a known trace, queries use start_time >= traceTimestamp - INTERVAL 1 HOUR to narrow the scan to a relevant time window. This constant is defined in constants.ts:

// observation.start_time > t.timestamp - 1 hour
export const TRACE_TO_OBSERVATIONS_INTERVAL = "INTERVAL 1 HOUR";

This is used, for example, in getObservationsForTrace 12:

AND start_time >= {traceTimestamp: DateTime64(3)} - INTERVAL 1 HOUR

Rationale: The observations table is partitioned and ordered by toDate(start_time). Filtering by start_time allows ClickHouse to prune partitions and use the primary index. Without this filter, querying by trace_id alone would force a full-table scan across all partitions.

Trade-off: If an observation arrives more than 1 hour before the trace timestamp (late-arriving or out-of-order events), it will be excluded by this filter. The 1-hour window is a deliberate performance vs. completeness trade-off.

The complementary constant for the reverse direction (joining observations → traces) is:

// t.timestamp > observation.start_time - 2 days
export const OBSERVATIONS_TO_TRACE_INTERVAL = "INTERVAL 2 DAY";

This wider 2-day window is used when the left-hand join key is an observation's start_time and the right-hand lookup is for the parent trace 13. The asymmetry reflects that trace timestamps are less precisely bounded relative to the collection of their observations.

Observations Table View in getObservationsTableInternal#

The UI's observations table query (getObservationsTableInternal) applies several layered strategies 14:

• Conditional trace join: The traces table is only joined if filters or ordering reference trace columns, avoiding expensive joins when not needed 15.
• LIMIT 1 BY o.id, o.project_id: Applied for row queries (not count queries) when dedup is not skipped, placed before the outer LIMIT/OFFSET 16.
• Date-first ordering: When ordering by startTime, a toDate(o.start_time) prefix sort is prepended so ClickHouse can read in primary-key order without a separate sort step 17.
• Score CTE: Score aggregations are pre-computed in a separate CTE and joined only when score filters are active 18.

Key Source Files#