Traditional databases (PostgreSQL, MySQL) store data row-by-row on disk (OLTP). This is great for looking up a single user, but terrible for aggregating a billion rows. Columnar databases store data column-by-column (OLAP), revolutionizing analytics.

Module 1: The Magic of Columnar Layouts

If you query SELECT SUM(salary) FROM employees, a row-based DB must load the entire row (name, age, address) into memory just to get the salary. A columnar DB only loads the tightly packed salary column from disk.

Why Columnar Wins for Analytics

  • Massive Compression: Storing identical data types together allows for aggressive compression like Run-Length Encoding (RLE).
  • Reduced Disk I/O: Queries only read the specific columns requested.
  • Vectorized Execution: CPU SIMD instructions can process chunks of column arrays simultaneously.

Module 2: The Trade-offs

While reads are incredibly fast, columnar databases generally struggle with heavy concurrent updates and single-row inserts.

ClickHouse_Engine.sqlsql
CREATE TABLE user_analytics (
    event_date Date,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);

To solve the insert problem, engines like ClickHouse use an LSM-tree-like structure (MergeTree) where inserts are written as immutable parts in the background and continuously merged.

Module 3: The Modern Data Lake

Instead of loading all data into a rigid data warehouse, modern architectures rely on data lakes using open columnar file formats like Apache Parquet, Iceberg, and Delta Lake on cheap object storage (S3), queried dynamically by engines like Trino or Athena.