Services

Resources

Company

Columnar database

A columnar database, or column-oriented database, stores the values of each column together on disk instead of storing each row together. Analytic queries that scan millions of rows but touch only a few columns read far less data. ClickHouse, Apache Druid, BigQuery and Snowflake work this way, and Apache Parquet applies the same layout to files.

How it works

Take an events table with 100 columns and 1 billion rows. SELECT country, sum(amount) FROM events GROUP BY country needs 2 of the 100 columns, so a column store reads about 2% of the table before compression. Values in one column share a type and often repeat, so encodings such as dictionary and run-length compress them well. Engines then process columns in vectorised batches.

Why it matters in production

Columnar storage makes dashboards and ad hoc analytics over large data affordable. The trade-off is writes. Updating one row touches many column files, so these systems prefer batched inserts and append-mostly data, while row stores such as PostgreSQL stay better for transactions. Teams often feed a column store from the transactional database through change data capture.

See also

On the One2N blog

Further reading

Comic about a columnar database: a row store reads all 100 columns of 1 billion rows to get sales by country; a column store keeps each column together, so the query reads only country and amount; SELECT country, sum(amount) GROUP BY country touches 2 of 100 columns, about 2%, and repeated values compress well; scans and dashboards are fast, single-row updates slow, so transactions stay in a row store.