An OLTP database stores one row at a time, optimised for fast inserts and indexed lookup. An analytics database stores one column at a time, optimised for scans over millions of rows that filter a few columns. The shapes are not interchangeable: a row store is too slow to scan for analytics; a column store is too slow to insert a single row transactionally.

This topic is the why and how of columnar storage — and the timeseries stores that layer on top.

Why Analytics Needs a Different Shape

The defining analytical query is “aggregate a few columns over many rows”:

SELECT region, SUM(revenue) AS total
  FROM sales
  WHERE sale_date BETWEEN '...' AND '...'
  GROUP BY region

The query touches three columns (region, revenue, sale_date) over millions of rows. A traditional row store reads every row’s full tuple, even though 90% of its bytes (other columns) are discarded. A columnar store reads only those three columns — typically 10–20× less disk I/O for the same analytical workload.

Column stores trade write speed for read speed — the right trade for an analytics workload.

Column-Major Layout on Disk

Each column is stored separately: sales.region in one file, sales.revenue in another, sales.sale_date in a third. Each file is laid out as a concatenation of fixed-width values (or variable-width with offsets). This buys two properties:

1. Only the touched columns are I/O’d — running an aggregate on three columns reads three column-files, not all rows. “Column pruning” is the simplest and largest analytical optimisation.
2. Values of the same type are contiguous on disk — integers next to integers, strings next to strings. The hardware prefetcher and the CPU cache love this; running aggregates rarely miss cache.

The cost: writing a single row requires appending to many files. Column stores batch inserts — typically via a write-ahead log and an LSM-style flush into immutable column “chunks” — to amortise the cost across thousands of rows at a time.

Compression and Late Materialisation

A column of similar values compresses extremely well. A column of country codes (US, US, US, GB, GB, …) runs through run-length encoding (RLE):

US × 10000, GB × 5000, DE × 3000, ...

Total storage: 3 numbers for 18,000 rows. Bit-packing compresses a TINYINT that uses 4 distinct values to 2 bits per value. Dictionary encoding stores each unique string once, with the column holding integer indices. Compression rates of 5–50× are normal on analytical data.

Late materialisation is the query-engine move that exploits this: keep values encoded for as long as possible, decode only the rows that survived the WHERE filter, and only when needed for the projection. A query that filters WHERE region = 'US' filters at the encoded level (mask of region='US' rows); it only decompresses the revenue values for those rows just before the SUM accumulator runs. The compression amortises through every operator, not just at storage.

Vectorised Execution

A traditional row-at-a-time executor processes one row, then the next, then the next — with the function-call overhead dominant over the actual work. A vectorised executor processes a vector of N rows at a time (typically N ≈ 1024), calling each operator once per batch.

When a column is stored as 1024 consecutive int32 values, the CPU’s vector instructions (AVX2) can SUM the batch in 4 instructions instead of 1024. Modern analytical engines (ClickHouse, DuckDB, Apache Arrow, Velox) are built on vectorised execution end-to-end.

Timeseries Databases

A timeseries workload is columnar-analytical with two extras:

1. Time is always the primary axis — queries are range-over-time (WHERE time > now() - INTERVAL '1 hour') and aggregations over time windows (GROUP BY time_bucket('5 minutes', ts)).
2. Writes are append-only and ordered — events arrive in roughly time-monotonic order; updates and deletes are rare.

The shape that suits: time-partitioned columns — each partition covers a time range (one_day.p_2024_03_15), within which the structural layout is columnar. Cold partitions are evicted to cold storage; hot partitions stay in memory or on local SSD.

The choice depends on the workload: Prometheus for infrastructure metrics, TimescaleDB for SQL/Postgres-faithful teams, ClickHouse for event-y high-throughput logs, InfluxDB for the most time-native data model.

Practice Trajectory

1. Take an analytical query you’ve written recently. Count the columns referenced vs the columns in the table; estimate the column-pruning ratio. Re-execute on a column store if you have access; measure.
2. Pick a single-column dataset; compress with RLE and dictionary encoding. Compare the compression ratios.
3. Load a million-row CSV into DuckDB and the equivalent row store (e.g., sqlite); run the same aggregate. Compare time and disk I/O.
4. Sketch a timeseries table for an HTTP request log. Choose a partition granularity (hourly, daily); justify what queries that choice optimises.
5. Audit a queries from analytics history. Identify which would benefit from late materialisation; redraw the plan with encoded predicates as the gating step.

When It’s the Right Tool