Multi-table split vs. Null + materialized views

When a stream carries many record types and you want one table per type, the fan-out has to happen somewhere. The classic ClickHouse answer is a Null landing table plus one MATERIALIZED VIEW per type — the server fans out. The multi-sink split does it in the ETL: each record routes directly to its type's table. This rig asks whether moving that work off the (scarce, hard-to-scale) database earns its keep.

An in-process generator produces a skewed mix of typed rows; three arms consume the same stream through the real chain, sink pool, and runtime:

• single Null — one Null table, single sink. The throughput-ceiling control: rows are discarded after parse, so it measures the insert path with near-zero storage cost. (It runs the plain single-sink terminal, not a one-branch split, so it says nothing about split-terminal overhead.)
• Null + MV — one Null landing table + one materialized view per type into per-type MergeTree tables. ClickHouse fans out.
• ETL split — the pipeline routes each row to its type's MergeTree table directly, via the split terminal. No views run.

The two storage arms write the same rows to the same per-type tables; only who fans out differs. Throughput is read from etl_sink_records_total (durably-acked rows, summed across sinks and shards) over a 20 s window; chstats::capture_multi reads the whole-server INSERT CPU (which folds in MV execution for the MV arm), the views' own CPU, and part/merge shape across every target table.

Environment: Apple M5 Max (18-core), macos/aarch64, Rust release, ClickHouse 26.3 in Docker capped at 6 cores (--cpus=6) so the server is the scarce resource and the ETL has the rest of the host — the shape the split exists for. Harness: benchmarks/src/bin/multi_table_split.rs; raw JSONL in benchmarks/results/, reproduction in Methodology.

[!NOTE] Bottleneck discipline. The comparison only means something if ClickHouse is the binding constraint. Every arm records a limiter verdict; on the runs below both storage arms are sink-bound, so the numbers are the server's capacity, not the generator's. If an arm were generator- or budget-bound the result would be invalid — see the concept page.

Throughput: the split pulls away as types multiply

Throughput vs. type count (skewed mix, CH pinned to 6 cores)

Higher is better

At 6 server cores the MV arm flatlines (~2M rows/s) and drops as views multiply — each inserted block is re-scanned by every view. The split scales with the type count (3.5M → 6.3M) because it writes N independent tables in parallel and spends no CPU on view execution. The single-Null control is the parse-bound ceiling of one table's insert path.

single Null (ceiling)Null + MVETL split

Data table

Group	Series	Value	95% CI	n
4	single Null (ceiling)	1,994,664.8837342258 rows/s	—	—
4	Null + MV	2,279,845.4401581534 rows/s	—	—
4	ETL split	3,553,199.807890394 rows/s	—	—
8	single Null (ceiling)	2,199,768.677280386 rows/s	—	—
8	Null + MV	2,109,808.3075920637 rows/s	—	—
8	ETL split	4,339,844.400971299 rows/s	—	—
16	single Null (ceiling)	2,280,144.1995853814 rows/s	—	—
16	Null + MV	2,017,756.2525836457 rows/s	—	—
16	ETL split	6,304,452.672035339 rows/s	—	—

Apple M5 Max · commit 965be570f0 · 2026-07-11

The MV arm is pinned near 2M rows/s and falls as types grow (16 views cost more than 4). The split rises with the type count — 3.5M at 4 types to 6.3M at 16 — a +56%, +106%, +212% advantage. Some of that is honest parallelism (the split writes N tables through N×shards workers, where the MV arm funnels through one landing table), but the two metrics below are per-row normalized and do not depend on write concurrency.

Server CPU per row: the split spends less, and stays flat

Whole-server INSERT CPU per stored row (lower is better)

Lower is better

The MV arm's per-row server CPU climbs with view count (0.38 → 0.48 µs) — the fan-out is re-run per block, per view. The split holds flat near 0.32 µs: it spends 16–31% less server CPU per row, and every microsecond of that is INSERT, not view execution.

Null + MVETL split

Data table

Group	Series	Value	95% CI	n
4	Null + MV	0.38139173545047567 us	—	—
4	ETL split	0.3209594735085178 us	—	—
8	Null + MV	0.41847993815614637 us	—	—
8	ETL split	0.31836312602396766 us	—	—
16	Null + MV	0.4834518145225003 us	—	—
16	ETL split	0.3346946837735983 us	—	—

Apple M5 Max · commit 965be570f0 · 2026-07-11

The MV arm's ch_mv_cpu_us — the CPU the views alone burn — runs 5.7M to 8.4M µs over the window and grows with type count; the split's is zero. That is the CPU the split hands back to the database for queries and merges.

Part size: the split writes parts 4–10× larger

Mean rows per written part (higher is better)

Higher is better

The MV arm's parts shrink as types grow (65k → 16k rows) — each view writes a part per inserted block, and more views split the same blocks finer. The split's parts stay large (~170k–255k rows) because each table's part cadence is the ETL's batch/linger, decoupled from the landing INSERTs. Small parts are stored-up merge pressure; this is the problem the split is built to avoid.

Null + MVETL split

Data table

Group	Series	Value	95% CI	n
4	Null + MV	64,981.8 rows	—	—
4	ETL split	255,518.3 rows	—	—
8	Null + MV	32,599.8 rows	—	—
8	ETL split	210,186.9 rows	—	—
16	Null + MV	16,227.7 rows	—	—
16	ETL split	166,814.4 rows	—	—

Apple M5 Max · commit 965be570f0 · 2026-07-11

This is the core of it. With materialized views, a target's part size is coupled to the landing table's INSERT blocks — and the more types (views) share those blocks, the smaller each target's parts get (65k rows at 4 types, 16k at 16). The split sets each table's part cadence with its own batch/linger, so parts stay large regardless of type count. Fewer, bigger parts mean less merge work later.

Verdict: GO

Across every type count the ETL split beats the Null+MV approach on all three axes — throughput, server CPU per row, and part size — and the gap widens with the number of types, which is exactly the direction that matters (more types is when per-table tables are most worth having). Both storage arms are sink-bound, so the comparison is the server's capacity. The one honest caveat — the split's throughput edge is partly N-table write parallelism — does not touch the per-row CPU or the part-size results, which are the load-bearing wins.

The cost the split carries is checkpoint lag from per-table linger on rare tables (see the concept page); on an interleaved source that is the price of big rare-type parts. For a stream where the database is the scarce resource, it is a price worth paying.