🪢 Langfuse 图解教程Langfuse Visual Guide 第二部分 · 前置基础Part 2 · Foundations 08 / 55
第二部分 · 前置基础Part 2 · Foundations

ClickHouse 与宽事件表ClickHouse & the wide-event tables

🧭 读前Before you start
先读 第7课 的双存储分工,本课深入 ClickHouse 的三张宽事件表。
Read Lesson 7 on the dual-store split; this lesson dives into ClickHouse's three wide-event tables.

上一课说遥测数据都进 ClickHouse。这一课打开引擎盖,看这三张宽事件表(traces / observations / scores)到底是怎么建的、为什么这么建。 这是整个存储层最该看懂的一课——一旦理解了表引擎、排序键、分区这几样,后面「为什么列表快、为什么按项目按时间查特别高效、为什么数据能边写边查」 这些问题都会迎刃而解。我们以 observations 表为例(建表 SQL 在 packages/shared/clickhouse/migrations/unclustered/0002_observations.up.sql)。

🔌 生活类比
把 ClickHouse 的表想成一座超大图书馆。分区(PARTITION)像按出版年月分大书架——找「2024 年 6 月的书」直接去那个架,别的架碰都不用碰; 排序键(ORDER BY)像每个架内的摆放顺序(先按学科、再按日期),于是「某学科某段时间的书」只要在架上扫连续一小段; ReplacingMergeTree 引擎则像一条上架规则:同一个书号来了新版本,用新版盖掉旧版(按版本号判断谁新)。 三者配合,海量藏书也能秒级定位与盘点。这一课就把这三条「上架与摆放规则」逐一拆开,让你看清 Langfuse 是怎么在上亿行里做到「边写边查、查得还快」的。
observations 一行的解剖(宽事件) 标识 / 关联id · trace_id · project_id · parent_observation_id · type · name 富属性(内联的宽列)provided_model_name · internal_model_id · total_cost · start_time · end_time · prompt_id/name/version Map 列(半结构化)metadata · usage_details · cost_details · provided_usage_details · provided_cost_details 大字段(ZSTD 压缩)input CODEC(ZSTD(3)) · output CODEC(ZSTD(3)) 版本控制event_ts · is_deleted
一行宽事件的五类列:标识/关联列(拼树、定位)、内联的富属性宽列(model、cost、prompt…)、半结构化的 Map 列(metadata、usage/cost 明细)、用 ZSTD 压缩的大字段(input/output)、以及版本控制列(event_ts、is_deleted)。一行就装下了第 3 课说的全部富属性。
一行 observations 的解剖:同一行,四类列 ⟵ 同一行(一次 LLM 调用 = 一条 observation)⟶ ① 关联列project_id proj_abtrace_id chat_a1b2id obs_7fparent_observation_idobs_3a(拼成调用树)→ 拼调用树 · 租户隔离 ② 业务列type GENERATIONname answerprovided_model_name gpt-4olevel DEFAULT · input/output {…}metadata {env: prod}→ 可过滤 · 可分组 ③ 数字列start_time / end_timeusage_details {in:512,out:88}cost_details {total:.0041}total_cost 0.0041→ 可聚合(SUM/AVG) ④ 版本列event_ts …T12:00is_deleted 0同 id 多次写入,查时取 event_ts 最新→ ReplacingMergeTree
一行宽事件 = 四类列各司其职(列名取自 clickhouse/migrations/.../0002_observations.up.sql;值为示例):关联列拼调用树并按 project_id 隔离;业务列供过滤/分组;数字列供聚合;版本列 event_ts/is_deleted 让 ReplacingMergeTree 在「同 id 多次写入」时保留最新。第 3 课的三支柱,物理上就是这一行。

ReplacingMergeTree:同 id 后写覆盖先写

三张表用的引擎都是 ReplacingMergeTree。它的建表声明长这样:

clickhouse/migrations/unclustered/0002_observations.up.sqlENGINE
ENGINE = ReplacingMergeTree(event_ts, is_deleted)
PARTITION BY toYYYYMM(start_time)
ORDER BY (project_id, type, toDate(start_time), id)

关键就在 ReplacingMergeTree(event_ts, is_deleted) 这一句。它的语义是:排序键完全相同的多行,会被「替换」成最新的一行—— 谁的 event_ts(版本时间戳)更大,就保留谁;is_deleted=1 则表示这行被软删除。这正好完美契合第 5、6 课的合并模型: 一个 observation 先 create、后 update,会产生两次写入(相同 id、不同 event_ts),ClickHouse 在后台合并时自动用 update 那条盖掉 create 那条。 你不需要先查出旧行、改完再写回——只管追加,覆盖交给引擎。这就是第 2 课「不可变 / 追加式事件」能成立的存储基础。

id=o1 · event_ts=t0create:model, input(旧版) id=o1 · event_ts=t1(更大)update:+output, +usage(新版) 后台合并Replacing 保留 t1 这一条model+input+output+usage 查询时如果还没合并,可用 FINAL 或聚合保证读到最终态(细节见查询链路)
后写覆盖先写:相同排序键的两次写入,引擎按 event_ts 保留更新的那条,is_deleted 实现软删除。于是「改一条 observation」= 追加一条更大 event_ts 的记录,无需读-改-写。

排序键 + 分区:为什么按 project_id 和时间

再看那两行:PARTITION BY toYYYYMM(start_time) 和 ORDER BY (project_id, type, toDate(start_time), id)。 这两句决定了数据在磁盘上怎么分块、怎么排列,也就决定了查询能不能少扫数据。在上亿行的规模下,能不能「少扫」往往比单点「扫得多快」更决定性能。

📅 分区 PARTITION BY 月

按 toYYYYMM(start_time) 把数据切成一月一块。查「最近 7 天」时,引擎直接跳过不相关月份的整块数据(partition pruning),连读都不读。

🔑 排序键 project_id 打头

ORDER BY 第一列是 project_id,于是同一项目的数据在磁盘上物理相邻。多租户查询天生只看自己项目,扫一段连续区间即可,无需全表过滤。

查询「项目 P · 最近 7 天」→ 只扫绿色一小片 2024-04裁掉 2024-05裁掉 2024-06 项目 P 的连续区间 项目 A…项目 Z… ① 分区裁掉 4、5 月 → ② 排序键定位到 6 月里项目 P 那一段 → ③ 只读这一小片的几列 海量数据下,「少扫」比「扫得快」重要得多——这是列存 + 排序键 + 分区的合力 这也是第 2 课「围绕列式访问设计:时间窗口 + 排序键 + 数据剪枝」的实现
少扫才是关键:分区先按月裁掉无关数据,排序键(project_id 打头)再把目标项目的数据收拢成连续一段,最后列存只读你要的那几列。三者合力,让「按项目按时间」这类最高频查询在上亿行里也只碰一小片。

列存 + 压缩 + 索引:让「宽」也能快

宽事件行很宽,怎么保证不慢?靠三招,都写在建表 SQL 里:

手段建表里怎么写解决什么
列式存储MergeTree 家族天生列存查询只读用到的列,不为不相关的大字段买单
ZSTD 压缩input/output … CODEC(ZSTD(3))把又大又重复的 prompt/输出文本压到很小,省空间也省 IO
Map 列metadata/usage_details … Map(...)半结构化字段不必预先建列,灵活又可查
布隆过滤索引INDEX idx_id id TYPE bloom_filter()按 id / trace_id 精确查时,快速跳过不含目标的数据块

三张表的排序键略有不同,但都以 project_id 打头,体现「多租户隔离 = 排序键前缀」这一贯思路:

表ORDER BY
traces(project_id, toDate(timestamp), id)
observations(project_id, type, toDate(start_time), id)
scores(project_id, ...)

这种「三张表排序键高度一致」并非巧合,而是刻意为之:因为最常见的查询模式就是「某项目、某时间段」,把 project_id 和时间放在排序键最前, 等于把这类查询的成本压到最低。这也呼应第 3 课「三支柱 1:1 映射三张表」——三张表不仅结构对应,连物理排布的逻辑都一致,于是无论查 trace、查 observation 还是查 score, 引擎都能用同一套「裁分区 + 定区间」的本事。理解了 observations 这张,另外两张你就触类旁通了。

🎯 设计取舍
ReplacingMergeTree 的覆盖是「最终」的,不是「立刻」的——后台合并需要时间,所以理论上你可能在两次写入都还在、还没合并的瞬间读到旧行。 这是它换来「只管追加、写入飞快」的代价。怎么补?查询侧用 FINAL 关键字强制合并后再读,或用聚合(按 id 取 event_ts 最大的那条)来保证读到最终态—— 这就把「去重」的成本从每次写入挪到了查询时(且可控)。第 2 课说「避免读时去重的隐藏开销」,Langfuse 的做法是:尽量让常规查询走聚合/最终态视图, 把代价显式化、可优化,而不是让每次写入都付出读-改-写的代价。追加换吞吐,去重挪到读侧并优化——这是高写入遥测系统的经典取舍。
🎯 本课要点
  • 引擎 = ReplacingMergeTree(event_ts, is_deleted):相同排序键,后写(event_ts 更大)覆盖先写,is_deleted 软删除——完美承接第 5、6 课 create+update 的合并模型。
  • 分区 = 按月(toYYYYMM),查时间窗口可整块裁剪;排序键 = project_id 打头,多租户查询只扫连续一段。
  • 「少扫」靠分区裁剪 + 排序键定位 + 列存只读所需列这三者合力,是海量数据下保持高效的关键。
  • 列存 + ZSTD 压缩 + Map 列 + bloom_filter 索引,让又宽又大的行也能查得快、存得省,不为不相关的大字段买单。
  • 取舍:覆盖是「最终」的,去重成本从写入挪到查询(FINAL/聚合),换来写入飞快——避免读时去重的隐藏开销。

🧪 自测 · 想一想为什么这么设计

1. ReplacingMergeTree(event_ts, is_deleted) 的核心语义是什么?它怎么承接“一个 observation 先 create 后 update”?
  1. 排序键相同的多行会被替换成 event_ts 最大的那条(is_deleted 软删除);create 与 update 是两次同 id 写入,后台合并时自动用 update 覆盖 create——只管追加,覆盖交给引擎
  2. 它会拒绝重复 id 的写入
  3. 它要求先删除旧行再写新行
  4. 它把两次写入都永久保留且都返回
看答案与解析 点击展开
答案:A。ReplacingMergeTree 让“改一条”退化成“追加一条更大 event_ts 的记录”,无需读-改-写。这正好承接 create+update 的合并模型,也是第 2 课“不可变/追加式事件”能落地的存储基础。
2. 为什么 observations 的 ORDER BY 以 project_id 打头、PARTITION BY 按月?这对“某项目最近 7 天”这类查询有什么用?
  1. 为了让写入更慢
  2. 因为 ClickHouse 不支持索引
  3. 月分区让查询直接裁掉无关月份,project_id 打头让目标项目数据物理相邻成一段;于是只扫一小片、再列存只读所需列——“少扫”是海量下高效的关键
  4. 为了让所有项目的数据混在一起
看答案与解析 点击展开
答案:C。分区裁剪 + 排序键定位 + 列存选列三者合力,让最高频的“按项目按时间”查询在上亿行里只碰一小片。这正是第 2 课“围绕列式访问设计:时间窗口+排序键+剪枝”。
3. ReplacingMergeTree 的覆盖是“最终”的(后台合并需要时间)。这带来什么取舍,Langfuse 怎么应对?
  1. 只能每天查询一次
  2. 未合并瞬间可能读到旧行;用 FINAL 或按 id 取最大 event_ts 的聚合保证最终态——把去重成本从“每次写入”挪到“查询时且可控”,换来写入飞快
  3. 没有任何取舍,永远强一致
  4. 必须停机合并
看答案与解析 点击展开
答案:B。追加换吞吐的代价是“最终一致”。查询侧用 FINAL/聚合读到最终态,把去重从每次写入挪到查询时并显式优化——正是第 2 课“避免读时去重的隐藏开销”的实际做法。
💭 发散思考(没有标准答案,动手或动脑想想)
  • ReplacingMergeTree 把“改”变成“追加 + 后台覆盖”。如果你自己的系统也常常“同一条记录被多次更新”,这种“只追加、读时取最新”的思路能怎么帮你?它的代价你能接受吗?

Last lesson said telemetry goes to ClickHouse. This lesson opens the hood on how the three wide-event tables (traces / observations / scores) are actually built and why. It's the must-understand storage lesson — once you grasp the table engine, ordering key and partitioning, later questions ("why are lists fast", "why is by-project + by-time so efficient", "why can data be queried while being written") all fall into place. We use the observations table as the example (CREATE TABLE in packages/shared/clickhouse/migrations/unclustered/0002_observations.up.sql).

🔌 Analogy
Picture a ClickHouse table as a giant library. Partitioning is like big shelves by publish month — for "books from June 2024" you go straight to that shelf and never touch the others; the ordering key is the arrangement within a shelf (by subject, then date), so "a subject over a time range" is just a short contiguous scan; the ReplacingMergeTree engine is a shelving rule: a new edition of the same call number replaces the old (decided by version number). Together, even a vast collection is located and counted in milliseconds.
anatomy of one observations row (wide event) identity / linkageid · trace_id · project_id · parent_observation_id · type · name rich attributes (inlined wide columns)provided_model_name · internal_model_id · total_cost · start_time · end_time · prompt_id/name/version Map columns (semi-structured)metadata · usage_details · cost_details · provided_usage_details · provided_cost_details big fields (ZSTD compressed)input CODEC(ZSTD(3)) · output CODEC(ZSTD(3)) versioningevent_ts · is_deleted
Five kinds of columns in one wide-event row: identity/linkage (tree, lookup), inlined rich wide columns (model, cost, prompt…), semi-structured Map columns (metadata, usage/cost details), ZSTD-compressed big fields (input/output), and version columns (event_ts, is_deleted). One row holds all of Lesson 3's rich attributes.
Anatomy of one observations row: same row, four kinds of column ⟵ one row (one LLM call = one observation) ⟶ ① link columnsproject_id proj_abtrace_id chat_a1b2id obs_7fparent_observation_idobs_3a (builds the tree)→ call tree · tenant isolation ② business columnstype GENERATIONname answerprovided_model_name gpt-4olevel DEFAULT · input/output {…}metadata {env: prod}→ filter · group by ③ numeric columnsstart_time / end_timeusage_details {in:512,out:88}cost_details {total:.0041}total_cost 0.0041→ aggregate (SUM/AVG) ④ version columnsevent_ts …T12:00is_deleted 0same id written N times,read keeps latest event_ts→ ReplacingMergeTree
One wide row = four kinds of column, each with a job (column names from clickhouse/migrations/.../0002_observations.up.sql; values illustrative): link columns build the call tree and isolate by project_id; business columns filter/group; numeric columns aggregate; version columns event_ts/is_deleted let ReplacingMergeTree keep the latest when the same id is written more than once. Lesson 3's three pillars are physically this one row.

ReplacingMergeTree: later write of the same id wins

All three tables use the ReplacingMergeTree engine. The declaration looks like:

clickhouse/migrations/unclustered/0002_observations.up.sqlENGINE
ENGINE = ReplacingMergeTree(event_ts, is_deleted)
PARTITION BY toYYYYMM(start_time)
ORDER BY (project_id, type, toDate(start_time), id)

The key is ReplacingMergeTree(event_ts, is_deleted). Its semantics: multiple rows with the identical ordering key are "replaced" down to the latest one — whoever has the larger event_ts (version timestamp) wins; is_deleted=1 marks a soft delete. This perfectly fits Lessons 5 and 6's merge model: an observation that's created then updated produces two writes (same id, different event_ts), and ClickHouse automatically lets the update overwrite the create during background merge. You needn't read the old row, edit, and write back — just append; overwrite is the engine's job. This is the storage basis that makes Lesson 2's "immutable / append-oriented events" viable.

id=o1 · event_ts=t0create: model, input (old) id=o1 · event_ts=t1 (larger)update: +output, +usage (new) bg mergeReplacing keep the t1 rowmodel+input+output+usage if not yet merged at query time, FINAL or aggregation guarantees the final state (see the read path)
Later write wins: for two writes with the same ordering key, the engine keeps the newer event_ts; is_deleted implements soft delete. So "edit an observation" = append a record with a larger event_ts, no read-modify-write.

Ordering key + partition: why by project_id and time

Now the other two lines: PARTITION BY toYYYYMM(start_time) and ORDER BY (project_id, type, toDate(start_time), id). They decide how data is chunked and arranged on disk — and therefore whether a query can scan less.

📅 PARTITION BY month

toYYYYMM(start_time) cuts data into one chunk per month. For "last 7 days", the engine simply skips whole irrelevant months (partition pruning) — never even reads them.

🔑 ORDER BY project_id first

The first ORDER BY column is project_id, so one project's data is physically adjacent on disk. Multi-tenant queries naturally see only their project — a contiguous range, no full-table filtering.

query "project P · last 7 days" → scan only the green slice 2024-04pruned 2024-05pruned 2024-06 project P contiguous range project A…project Z… (1) prune Apr/May → (2) ordering key locates project P in June → (3) read only a few columns of that slice at scale, "scan less" beats "scan fast" — the combined effect of columnar + ordering key + partition this realizes Lesson 2's "columnar access: time windows + ordering keys + data pruning"
Scanning less is the point: partitions prune irrelevant data by month, the ordering key (project_id first) clusters the target project into a contiguous range, and columnar storage reads only the columns you want. Together, "by project, by time" — the hottest query — touches only a small slice even across hundreds of millions of rows.

Columnar + compression + indexes: making "wide" fast too

Wide rows are wide — how stay fast? Three moves, all in the CREATE TABLE:

MoveIn the schemaSolves
Columnar storagethe MergeTree family is columnarqueries read only the columns used, not paying for irrelevant big fields
ZSTD compressioninput/output … CODEC(ZSTD(3))shrinks big, repetitive prompt/output text — saves space and IO
Map columnsmetadata/usage_details … Map(...)semi-structured fields need no predefined columns — flexible yet queryable
Bloom-filter indexINDEX idx_id id TYPE bloom_filter()for exact id/trace_id lookups, quickly skip blocks not containing the target

The three tables' ordering keys differ slightly but all start with project_id, expressing the consistent "multi-tenant isolation = ordering-key prefix" idea:

TableORDER BY
traces(project_id, toDate(timestamp), id)
observations(project_id, type, toDate(start_time), id)
scores(project_id, ...)
🎯 Design trade-off
ReplacingMergeTree's overwrite is "eventual", not "immediate" — background merge takes time, so in principle you could read the old row in the instant both writes exist but aren't merged yet. That's the price of "just append, write fast". The fix: on the query side use the FINAL keyword to force-merge before reading, or use aggregation (take the max-event_ts row per id) to guarantee the final state — moving the "dedup" cost from every write to query time (and controllable). Lesson 2 says "avoid read-time dedup's hidden cost"; Langfuse's approach is to route regular queries through aggregation/final-state views, making the cost explicit and optimizable rather than paying read-modify-write on every write. Append for throughput, move dedup to the read side and optimize it — a classic write-heavy-telemetry trade.
🎯 Key points
  • Engine = ReplacingMergeTree(event_ts, is_deleted): same ordering key, later write (larger event_ts) overwrites earlier, is_deleted soft-deletes — perfectly absorbing the create+update merge model.
  • Partition = by month (toYYYYMM), so time-window queries prune whole chunks; ordering key = project_id first, so multi-tenant queries scan one contiguous range.
  • "Scan less" via partition pruning + ordering-key locating + columnar column-selection is the key to scale efficiency.
  • Columnar + ZSTD + Map columns + bloom_filter indexes keep wide, big rows fast to query and cheap to store.
  • Tradeoff: overwrite is "eventual"; dedup cost moves from writes to queries (FINAL/aggregation), buying fast writes — avoiding read-time dedup's hidden cost.

🧪 Self-test - think about the design

1. What's the core semantic of ReplacingMergeTree(event_ts, is_deleted)? How does it absorb 'an observation created then updated'?
  1. Rows with the same ordering key collapse to the largest event_ts (is_deleted soft-deletes); create and update are two writes of the same id, and background merge auto-overwrites create with update — just append, the engine overwrites
  2. It rejects writes with duplicate ids
  3. It requires deleting the old row before writing the new one
  4. It keeps both writes forever and returns both
Show answer & explanation click to expand
Answer: A. ReplacingMergeTree turns 'edit one' into 'append one with a larger event_ts', no read-modify-write. It absorbs the create+update merge model and is the storage basis for L02's 'immutable/append-oriented events'.
2. Why does observations' ORDER BY start with project_id and PARTITION BY by month? What does that do for a 'project, last 7 days' query?
  1. To make writes slower
  2. Because ClickHouse has no indexes
  3. Monthly partitions prune irrelevant months; project_id-first clusters the target project into a contiguous range; so only a small slice is scanned and columnar reads only needed columns — 'scan less' is the key at scale
  4. To mix all projects' data together
Show answer & explanation click to expand
Answer: C. Partition pruning + ordering-key locating + columnar column-selection make the hottest 'by project, by time' query touch only a small slice across hundreds of millions of rows. This is L02's 'columnar access: time windows + ordering keys + pruning'.
3. ReplacingMergeTree's overwrite is 'eventual' (background merge takes time). What trade-off does this bring, and how does Langfuse handle it?
  1. You may only query once a day
  2. Before merge you might read an old row; use FINAL or aggregate (max event_ts per id) for the final state — moving dedup cost from 'every write' to 'query time, controllable', buying fast writes
  3. No trade-off at all, always strongly consistent
  4. You must take downtime to merge
Show answer & explanation click to expand
Answer: B. Append-for-throughput costs 'eventual consistency'. The read side uses FINAL/aggregation for the final state, moving dedup from every write to query time and optimizing it explicitly — exactly L02's 'avoid read-time dedup's hidden cost'.
💭 Open questions (no single right answer - just think or try)
  • ReplacingMergeTree turns 'edit' into 'append + background overwrite'. If your own system also 'updates the same record many times', how could this 'append-only, read-the-latest' idea help you? Can you accept its cost?