In Part 1 I followed one order from INSERT to merge, and I skipped what happens inside the server in between. This post is that in-between. It's the most source-code-heavy part of the series, and you don't need it to use ClickHouse well. I wrote it because I kept running into questions that only the source could answer: why a brand-new part already has level 1, why "block" seems to mean something different every time I see it, and what exactly the server writes before an INSERT returns.

The setup is the same as in Part 1: one container running ClickHouse 26.9 and the orders table.

docker run -d --name ch --ulimit nofile=262144:262144 clickhouse/clickhouse-server:26.9
docker exec -it ch clickhouse-client

In Part 1, I followed an order from INSERT through a merge. That answered two questions that came up during coffee breaks at work. This post tackles the third:

"Why doesn't my SELECT show the values I expect?"

I usually hear this question from people using SummingMergeTree, AggregatingMergeTree, or CollapsingMergeTree. In these engines, a merge can change the rows themselves: it sums them, collapses them, or removes them. As a result, the same query can return different results depending on how many merges have occurred.

I work as a data infrastructure engineer at a company where ClickHouse comes up at almost every coffee break. The questions I get are usually some version of these:

• "Why do I still see the old value? I'm using ReplicatedReplacingMergeTree."
• "Why are we getting Too many parts errors?"
• "Why doesn't my SELECT show the values I expect?"

At some point I noticed that all of them come from the same gap. The people asking had never seen the whole flow, from the moment a row is inserted to the moment it's merged, deduplicated or deleted.