olap
the query accountant
The physical layout is the index. Columnar engines win by reading few columns and skipping blocks, and blocks can only be skipped when the data is sorted by what you filter on. Design the sort key, the types, and the ingestion around the real queries. Then prove the pruning with the plan, not with wall-clock time.
Use when designing, loading, querying, or tuning analytical (OLAP) databases -- DuckDB (embedded, Parquet, httpfs, CLI/python), ClickHouse (MergeTree family, ORDER BY / primary key, partitions, projections, materialized views, ReplacingMergeTree/AggregatingMergeTree, dictionaries, codecs, TTL, clickhouse local, chDB), Parquet/Iceberg/Delta/DuckLake lakehouse files, star schemas and wide tables, time-series or OHLCV analytics, EXPLAIN and pruning, ingestion, or choosing among DuckDB, ClickHouse, BigQuery, Snowflake, Pinot, Druid, StarRocks, MotherDuck. Postgres OLTP goes to postgres.
methodology
- Pick the engine by shape. Use DuckDB for single-node, file, or embedded work. Use ClickHouse for continuous ingest and concurrent dashboards. Use a warehouse or lakehouse when data must be shared across engines or teams. OLTP stays in Postgres.
- Write the three to five real queries first. Derive the ORDER BY from their filters: low cardinality first, then time. Partition coarsely or not at all.
- Load a real sample with narrow types. Check units and timestamps at the boundary before trusting any aggregate.
- Verify with EXPLAIN indexes = 1 (granules) or DuckDB EXPLAIN and parquet_metadata (row groups). Measure codecs on your own data.
- Pre-aggregate with a materialized view or projection only after step 4. Create the view before inserts and backfill explicitly.
- Write each surprise into the trained layer, with the versions that produced it.
contents
- trainedlearned layer: model, gotchas, troubleshooting, practiced cases. Read first
- best-practicesofficial ClickHouse best practices (primary key, partitioning, inserts, MVs, mutations)
- mergetreeofficial MergeTree family: Replacing, Aggregating, Summing, TTL, settings
- create-tableofficial codecs and column DDL; materialized views under projections
- projectionsofficial projections vs MVs, plus skip indexes
- performanceofficial DuckDB performance, OOM, indexing, file formats
- parquetofficial Parquet read/write, httpfs remote files
- lakehouse-formatsofficial Iceberg, Delta, DuckLake overview
- clickhouse-local-helpclickhouse local --help, duckdb CLI help
- binance-klinesrunnable two-engine lab with measured results
- clickhouse-best-practicesClickHouse official agent rules (schema, query, insert)
- clickhouse-architecture-advisorClickHouse architecture decisions (ingest, preaggregation, late upserts, joins)
- chdb-sqlchDB: ClickHouse embedded in Python
- duckdb-queryDuckDB official skills: query, read-file, convert-file, s3-explore, docs, install
- ecc-clickhouse-iocommunity ClickHouse patterns. The trained layer refines two of them
- sourcesthe curated source list: awesome-clickhouse and awesome-duckdb
Related gurus: postgres (OLTP, CDC source), dbt (modeling), feature-engineering, ml.