Batch Analytics

How many users signed up last month by country? What is the average order value by product category over the last year? These questions require scanning millions or billions of rows, too slow for a transactional database, perfect for an analytics engine.

Batch analytics uses columnar storage (Parquet, ORC), distributed query engines (Spark SQL, Presto, BigQuery), and materialized views to answer complex questions over massive datasets. The key insight: analytics queries scan many rows but few columns, so columnar storage (which groups column values together) dramatically reduces I/O.

The single most important idea in batch analytics is that storage layout determines query speed, and the right layout depends on what you are asking.

A row store (Postgres, MySQL) keeps all columns of a row together on disk. Reading one user's full profile is fast because every column is contiguous. But scanning the email column across 200 million users is slow because you have to read every byte of every row to extract the one column you want.

A column store (Parquet, BigQuery, Redshift, ClickHouse) keeps all values of one column together. Scanning 200 million emails reads only the email column, skipping the other 39 columns entirely. For a typical analytics query that selects 3 columns out of 40, the column store reads 92 percent fewer bytes.

Why compression matters on top of this. Column values are the same type and often repeat. A country column with 200 distinct values across 500 million rows compresses to almost nothing with dictionary encoding. The Parquet spec documents 10x compression ratios as normal for low-cardinality string columns. A row store cannot compress as well because adjacent bytes have different types and no predictable repetition.

Amazon's Redshift team published benchmarks showing a 400 TB reporting workload dropped from 4 minute queries on row-oriented Postgres to 8 second queries on columnar Redshift. The data was identical. The schema was identical. Only the storage layout changed.