Columnar storage and OLAP

In short: Columnar databases store the values of a column together instead of row by row. This speeds up analyses over many rows enormously and compresses very well.

In more detail: OLTP systems (orders, bookings) read and change single rows, for which row storage is ideal. OLAP systems (reports, analyses) sum a few columns over millions of rows; there columnar storage reads only the necessary data.

In Depth

• Compression: Similar values next to each other pack strongly (run-length, dictionary).
• Vectorised processing: The CPU processes blocks of values with SIMD.
• Examples: ClickHouse, DuckDB, BigQuery, Parquet files.
• Data warehouse: Central collection from several sources for analysis.
• ETL/ELT: Extract, transform, load data.
• Drawback: Changing single rows or reading them whole is slower.

See also: B-tree and B+ tree, Query planner and EXPLAIN