DATA SYSTEMS · INTERACTIVE LAB

Explore MergeTree layout, data skipping and incremental materialized views.

The useful question is not “which database wins?” It is “what work did the system avoid, defer, store or repeat?”

Checking isolated teaching runtimes…

ROWS10K
100K
1Mone deterministic sales model

START WITH ONE QUESTION

How do column layout, sort order and inserted blocks reduce repeated work?

Recommended sequence

Select 100,000 rows and run Raw columnar aggregate as the ClickHouse baseline.
Run Column pruning. Confirm that the query requests fewer columns even though the logical dataset is unchanged.
Run Data skipping and inspect the reported read scope; connect the result to sort order and searchable ranges.
Run Stored aggregate states. Notice that ClickHouse merges prepared states rather than scanning every original event.
Run Insert through incremental MV. One event enters as a new block, and the materialized-view pipeline transforms that inserted block into aggregate state.

What this runtime actually demonstrates

ClickHouse incremental MV is insert-triggered transformation, not PostgreSQL pg_ivm and not a full-refresh snapshot.

Read the timing carefully

The displayed value is observed lab latency, not a universal database benchmark. PostgreSQL reuses an open PDO connection; DuckDB starts a subprocess for each request; ClickHouse communicates over HTTP and reports its own engine elapsed time. At only 10K–1M rows, much of the dataset may also remain in cache. Compare query plans, rows and bytes read, freshness and maintenance cost before comparing milliseconds.

CONCEPT

Read fewer columns, then skip more ranges.

MergeTree stores columns independently and organizes parts by an ORDER BY key. A predicate aligned with that key can skip marks and parts; an incremental materialized view transforms each arriving insert block into reusable aggregate state.

inserted block→MergeTree pipeline→aggregate states

BOUNDED EXPERIMENT

Dataset size

Whitelist only · 25 s timeout · 2 CPU / 2 GB ClickHouse ceiling · one operation at a time

—execution time

—rows scanned

—bytes read

—rows returned

SQL

Select a bounded experiment.

Result

No result yet.

Explain / query plan

The physical plan appears after execution.

What happened?

Run an experiment, then connect the measurement to physical work.

Incremental does not mean identical

MergeTree order

The ORDER BY expression is a physical promise. It shapes sparse indexes and locality; it is not merely presentation order. Queries misaligned with it may still scan widely.

Aggregate states

AggregatingMergeTree stores mergeable states such as sumState. Reads finalize them with sumMerge. Background merges may combine parts later, so queries must preserve correct merge semantics.

Inserted blocks

The materialized view sees newly inserted blocks. It does not automatically notice arbitrary historical mutations in the way a full PostgreSQL REFRESH rereads the source. That distinction is the lesson.