Part 1 · 2 chapters · ~12 min

Columnar Storage: Parquet, ClickHouse, DuckDB

Column layout, encodings (dictionary, run-length, delta, bit-packing), compression, row groups and statistics for predicate pushdown, vectorised execution, a measured comparison with DuckDB, ClickHouse MergeTree tables, and DuckDB as an analytics tool on a laptop.

Measured with DuckDB

code

# reproduce: pip install duckdb, then in Python
con.execute("CREATE TABLE tx AS SELECT i AS id, (i % 200000) AS account_id, (random()*5000000)::BIGINT AS amount_kobo, "
            "['NGN','NGN','NGN','USD','GHS','KES'][1 + (i % 6)] AS currency, "
            "['completed','completed','completed','failed','reversed'][1 + (i % 5)] AS status, "
            "TIMESTAMP '2026-01-01' + to_seconds(i % 23000000) AS created_at, 'narration for transfer ' || i AS narration "
            "FROM range(10000000) t(i)")
con.execute("COPY tx TO 'tx.csv' (HEADER)"); con.execute("COPY tx TO 'tx.parquet' (FORMAT parquet, COMPRESSION zstd)")
q = "SELECT currency, sum(amount_kobo), count(*) FROM {} WHERE status = 'completed' GROUP BY 1"
# measured (best of 3): read_csv_auto('tx.csv') 345 ms · 'tx.parquet' 24 ms · sizes 862 MB vs 88 MB

Vectorised execution: column engines process batches of thousands of values per operator call, which suits CPU caches and SIMD (Computers course). That, plus reading less data, explains most of the gap.

COLUMNAR STORAGE, MEASURED

10,000,000 transfer rows in DuckDB 1.5.6 on an Apple-silicon laptop

swipe the figure sideways, or tap expand for full screen

1/4

size

The same 10 million rows: 862 MB as CSV, 88 MB as Parquet with zstd. Columns of similar values (status, currency) compress extremely well with dictionary and run-length encoding.

9.7× smallerdictionary + RLE + zstd

ClickHouse in one table

code

CREATE TABLE transfers (
  created_at DateTime, account_id UInt64, currency LowCardinality(String),
  status LowCardinality(String), amount_kobo Int64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (account_id, created_at);          -- sort key = sparse primary index; queries by account and time are fast
-- MergeTree writes immutable parts and merges them in the background, like an LSM tree (Algorithms P3)