olap trained

• The storage layout is the index. Columnar engines store each column in compressed blocks: DuckDB row groups of 122,880 rows, Parquet row groups, ClickHouse granules of 8,192 rows. Blocks carry min/max statistics. A query is fast when it reads few columns and skips most blocks. Skipping only works when the data is physically sorted by the columns you filter on. In ClickHouse, ORDER BY is that sort (primary indexes). In DuckDB and Parquet, zonemaps work best when rows were inserted sorted (indexing).
• Execution is vectorized. Operators process column batches: 2,048-value vectors in DuckDB, blocks in ClickHouse. Aggregation over a few columns of billions of rows is cheap. Point lookups, row updates, and high-concurrency small transactions are not. Keep those in Postgres (postgres).
• ClickHouse is write-once and merge-later. Every INSERT creates an immutable part. Background merges combine parts, and engine semantics run at merge time: Replacing dedup, Summing and Aggregating fold, and TTL. Until a merge happens, duplicates and partial aggregates are visible (parts, merges).
• DuckDB is an in-process library over files. It needs no server. It queries CSV, Parquet, JSON, Iceberg, and Delta in place, locally or over https/s3 with range reads (httpfs). It suits one machine with one writer.
• Lakehouse = Parquet files + a table format. Iceberg, Delta, and DuckLake add snapshots, schema evolution, and file-level statistics (lakehouse formats). The engine is replaceable, and the files are the contract.

The runnable two-engine lab holds the full scripts.

• Choose the ClickHouse ORDER BY from the queries you run. Put columns in low-to-high cardinality order, then time. Official docs: choosing a primary key and cardinality order. A common community pattern says "high cardinality first", and measurement says otherwise: symbol (3 values) first reads 35/95 granules against 95/95 with time first.
• Start without PARTITION BY, or keep it coarse (month). Partitions are for data lifecycle (drop or move), not query speed (partitioning keys). The community advice to partition by month or day by default risks a too-many-parts failure.
• Insert in big batches, roughly 10k-100k+ rows per insert. Use async_insert=1 for many small writers (insert strategy).
• Pre-aggregate with an incremental MV into Aggregating or SummingMergeTree. Query it with -Merge and GROUP BY (incremental MV). Use a projection when you only need a second sort order on the same table (MV vs projection).
• Pick the narrowest types: LowCardinality(String) for small vocabularies, Decimal for fixed-precision money, and no Nullable unless NULL means something (select data type).
• Avoid mutations (ALTER UPDATE/DELETE) and routine OPTIMIZE FINAL. Model changes as inserts (ReplacingMergeTree with a version column) and read with FINAL, or use argMax over the version (avoid mutations, avoid optimize final).
• Enrich with dictionaries (dictGet) instead of joining a small dimension (dictionaries).
• DuckDB: write Parquet sorted by the filter columns, with zstd. For big file-to-file jobs, SET preserve_insertion_order = false and cap memory_limit to 50-60% of RAM if you hit OOM (oom, tuning).
• Verify pruning with the plan, not wall-clock time. In ClickHouse, run EXPLAIN indexes = 1 and compare granules. In DuckDB, run EXPLAIN and look for filters inside PARQUET_SCAN, or read parquet_metadata() stats (explain).

• DuckDB: zero-ops, embedded in Python, Node, or the CLI. It is the best tool for ad-hoc analysis over files, notebooks, ETL steps, CI data checks, and feature building (feature-engineering, ml). It has friendly SQL (GROUP BY ALL, QUALIFY, arg_max, ASOF JOIN) and is the dbt-duckdb target in dbt.
• ClickHouse: a server for continuous ingest and many concurrent dashboards over billions of rows. It has incremental MVs, TTL, tiered storage, dictionaries, and very strong compression (DoubleDelta turned a 1.48 MiB ts column into 2.5 KiB). clickhouse local gives the same engine with no server.
• Parquet plus a table format keeps data engine-neutral. Both engines read each other's files; the round trip works in both directions.

• DuckDB: one writer process per database file, and it is not a multi-user server. Use MotherDuck or DuckLake to share it (motherduck, ducklake). Large joins can exceed memory (oom).
• ClickHouse: updates and deletes are expensive. Dedup is eventual. Joins are weaker than aggregation (minimize joins). A bad ORDER BY needs a table rewrite to fix. Running and replicating a cluster is real operations work.
• Both: neither is an OLTP store. Keep the source of truth in Postgres and ship changes to OLAP.

1. Binance spot klines switched from ms to µs epochs on 2025-01-01. A naive /1000 gives year 57218 in DuckDB. fromUnixTimestamp64Milli silently clamps to 9999-12-31 in ClickHouse. Detect the unit (>= 1e14 means µs).
2. An MV is an insert trigger. It saw 0 of 786,240 existing rows until an explicit INSERT ... SELECT backfill.
3. An AggregatingMergeTree target returns partial rows until merges finish. Always use -Merge and GROUP BY. Never SELECT *.
4. ReplacingMergeTree dedups at merge time. It returned 306,720 rows without FINAL and 262,080 with it.
5. Gorilla on decimal-string prices made storage worse: 1.46 MiB, against 1.19 MiB for LZ4, 845 KiB for ZSTD, and 597 KiB for Decimal64(2)+ZSTD. ALP reached 593 KiB, but it is beta in 26.10 and needs enable_alp_codec=1. Measure codecs on your own data.
6. Small parts are Compact, and system.columns then reports 0 bytes per column. Use SETTINGS min_bytes_for_wide_part = 0 on a lab table to measure codecs.
7. CREATE TABLE b AS a ENGINE = ... copies the codecs and the skip indexes. It is not a "default codecs" baseline.
8. lagInFrame returns the type default (0) on the first row, not NULL. That produces inf returns; DuckDB lag returns NULL. Guard with isFinite or pass a default.
9. In clickhouse local, --format sets the input format too. It broke INSERT ... VALUES with CANNOT_PARSE_INPUT. Use --output-format.
10. A skip index on a column uncorrelated with the sort key barely helps. minmax on trades still read 74/95 granules. Put hot filters in the key or a projection instead.
11. ClickHouse DateTime64(3,'UTC') becomes Parquet and then DuckDB TIMESTAMP WITH TIME ZONE. DuckDB's own TIMESTAMP stays naive. Types change across the round trip.
12. TTL is evaluated against now(). Historical backfills are deleted immediately: only 18,270 rows survived an 18-month TTL on 2024-25 data.
13. ClickHouse extract(_file, ...) and DuckDB filename = true expose the source path. Derive partition values like symbol from them, not from content.

The two-engine lab runs 786,240 one-minute rows for 3 symbols (2024-10 to 2025-03) through DuckDB 1.5.5 (python and duckdb-cli via uvx) and ClickHouse local 26.10.1.831.

1. The same OHLCV, VWAP, and window answers came out of both engines, and the MV rollup matched the raw rollup exactly.
2. ORDER BY impact: a one-week, one-symbol query read 2/15 granules with (symbol, ts) against 4/15 with ts. A symbol-only query read 35/95 against 95/95. A projection fixed the second table (35/95). Sorting also shrank the table on disk: 20.00 MiB against 21.63 MiB.
3. Codecs: DoubleDelta on ts gave 819x. On close, Gorilla was the worst option (the codec gotcha above).
4. Failure modes: the ms/µs epoch switch, the MV blind to existing rows, ReplacingMergeTree without FINAL, lagInFrame → inf, clickhouse local --format, and TTL deleting a backfill.
5. Parquet: DuckDB wrote it and ClickHouse read it (sum matched, 96.38M ETH). ClickHouse wrote it and DuckDB read it (ts became TIMESTAMPTZ). Sorted against shuffled Parquet meant 4/12 against 12/12 row groups to read for one symbol, and 28.6 against 31.8 MiB.
6. Remote: DuckDB counted the 2.96M-row NYC taxi Parquet over https with a filter in about 8 s, with httpfs autoloaded. The host load average sat around 180-200 on 11 cores, so wall-clock times are not benchmarks.

These entries come from the official docs, not from hands-on runs.

Modeling: use a star schema (facts plus conformed dimensions) for BI tools and dbt (dbt). Use one wide denormalized table for ClickHouse and Pinot serving, and resolve dimensions with dictionaries. Keep snowflake-normalized dimensions only when they are huge and shared (data modeling). Ingest by CDC or batch from Postgres (postgres) into Parquet or ClickHouse. Do not dual-write from the app.

Community lists: awesome-clickhouse, awesome-duckdb.