Reference · Diagram Design

OLAP — Analytical Databases

The other half of the database world: how analytical engines differ from transactional ones, the four shapes they come in, and the mechanism that makes scanning millions of rows cheap.

Analytical OLAP databases compared with transactional OLTP A reference in three layers. The first contrasts OLTP, which answers questions about one record and optimizes for safe writes, with OLAP, which aggregates whole columns across millions of rows. The second covers four shapes of analytical database: embedded in-process engines, real-time OLAP engines, cloud warehouses, and lakehouses, each with its language, use case, engines and scale. The third explains why reads are cheap, comparing row-oriented and column-oriented storage side by side and listing columnar layout, compression, vectorized execution, data skipping and batch writes as the mechanisms. TWO DIFFERENT JOBS COVERED IN THE OTHER DIAGRAM OLTP — Transactional Answers: "What is this one customer's balance right now?" Shape: Many small reads and writes, one row at a time Optimized for: Writing safely, fetching single records fast Engines: PostgreSQL · MySQL · MongoDB · DynamoDB COVERED HERE OLAP — Analytical Answers: "Average revenue per region over three years?" Shape: Few huge reads scanning millions of rows, batch writes Optimized for: Aggregating whole columns, not fetching one row Engines: ClickHouse · DuckDB · Snowflake · BigQuery FOUR SHAPES OF ANALYTICAL DB EMBEDDED In-process Runs inside your app. No server. Language: SQL Use case: Notebooks, local analysis Engines: DuckDB, chDB Scale: One machine, GBs to TBs Think "SQLite, but for analytics". REAL-TIME OLAP Real-time Sub-second queries on fresh data. Language: SQL Use case: Dashboards, logs, events Engines: ClickHouse, Druid, Pinot Scale: Petabytes, 100s q/node Built for continuous ingest. CLOUD WAREHOUSE Warehouse Storage and compute scale apart. Language: SQL Use case: BI, company reporting Managed: Snowflake, BigQuery Scale: Petabytes, elastic You pay per query or per second. LAKEHOUSE Lakehouse Tables over files in a bucket. Language: SQL over Parquet Use case: One copy, many engines Stack: Iceberg, Delta, Databricks Scale: Whatever the bucket holds Storage is R2 / S3, not the engine. WHY READS ARE CHEAP Row-oriented (OLTP) One row sits together on disk. To read one column you still pull every column with it. Column-oriented (OLAP) One column sits together. Read only the column the query actually asked for. THE MECHANISM Five things that make scanning cheap · Columnar layout — a query touching 2 of 40 columns reads 2, not 40 · Compression — a column holds one data type, so it packs far tighter · Vectorized execution — batches of ~1024-4096 values per step, SIMD · Data skipping — min/max per block prunes whole files before reading · Batch writes — rows arrive in bulk; updates are async merges, not in-place This is exactly why OLAP is fast at aggregates and bad at single-row updates. Consistency is looser here: ClickHouse is atomic per insert but eventually consistent across replicas, and has no multi-table transactions. Analytical engines trade the ACID guarantees of OLTP for scan speed. Updates and deletes are asynchronous merges, not in-place edits. FOCAL — THE SIDE THIS DIAGRAM IS ABOUT WHAT ONE QUERY ACTUALLY READS FROM DISK