再看那两行:PARTITION BY toYYYYMM(start_time) 和 ORDER BY (project_id, type, toDate(start_time), id)。
这两句决定了数据在磁盘上怎么分块、怎么排列,也就决定了查询能不能少扫数据。在上亿行的规模下,能不能「少扫」往往比单点「扫得多快」更决定性能。
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.
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.
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:
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.
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.
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:
Move
In the schema
Solves
Columnar storage
the MergeTree family is columnar
queries read only the columns used, not paying for irrelevant big fields
ZSTD compression
input/output … CODEC(ZSTD(3))
shrinks big, repetitive prompt/output text — saves space and IO
Map columns
metadata/usage_details … Map(...)
semi-structured fields need no predefined columns — flexible yet queryable
Bloom-filter index
INDEX 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:
Table
ORDER 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'?
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
It rejects writes with duplicate ids
It requires deleting the old row before writing the new one
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?
To make writes slower
Because ClickHouse has no indexes
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
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?
You may only query once a day
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
No trade-off at all, always strongly consistent
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?