Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 35 ยท Getting Started

Row vs Column Storage

Compare row-oriented and column-oriented storage, see why column stores read less and compress better, and learn the trade-offs for inserts and lookups.

Every database has to lay a table out on disk. The two big choices are row-oriented (all the fields of one row sit together) and column-oriented (all the values of one column sit together). Transactional databases such as PostgreSQL and MySQL are row stores. ClickHouse is a column store. Understanding this one decision explains most of what makes ClickHouse unusual: why queries are fast, why storage is small, and why single-row updates are awkward.

This lesson builds the idea from a small table, shows how to measure it, and lists the trade-offs. It follows What is ClickHouse?.

ClickHouse cannot run on this site. The runnable blocks use SQLite (a row store) and plain SQL to illustrate the ideas, and say so.

The same table, two layouts

Take a small table of page views:

id user page ms
1 ana /home 120
2 bo /docs 95
3 ana /home 210

A row store writes the bytes like this: 1 ana /home 120 | 2 bo /docs 95 | 3 ana /home 210. A column store writes one run per column: 1 2 3 | ana bo ana | /home /docs /home | 120 95 210.

Row store

One row together id, user, page, ms
Great for one row Fetch an order by id
Easy to update Rewrite one record

Best for transactions (OLTP).

1 / 2

Reading less

Suppose an events table has 50 columns and 5 billion rows, and a query is:

SQL
SELECT avg(duration_ms) FROM events WHERE country = 'PT';

A row store has to read pages that contain all 50 fields of each row, even though the query needs two. A column store reads the country file and the duration_ms file, and nothing else, so it reads roughly 2/50 of the data. Disk reading is usually the bottleneck of big queries, so reading 25 times less is a 25 times head start before any other trick.

flowchart TB
    Q["SELECT avg(ms) ... WHERE country='PT'"] --> R["Row store: read all 50 fields per row"]
    Q --> C["Column store: read country.bin + ms.bin only"]

The query only needs two columns. The layout decides whether the other 48 are read.

Compressing better

Values in one column share a type and often a pattern: status has a handful of distinct numbers, country repeats, timestamps increase. Compressors love that. ClickHouse compresses each column in blocks (LZ4 by default; ZSTD for stronger compression) and offers special codecs such as Delta and DoubleDelta for timestamps and Gorilla for floats. Ratios of 5x to 20x are typical, and sometimes much higher. Smaller data also means less I/O, so compression speeds queries up as well as saving disk. See TTL, codecs and mutations.

Processing in blocks

Because a column is a contiguous array of one type, the CPU can process it in chunks. ClickHouse moves blocks of thousands of values through the query plan and uses SIMD instructions that add or compare many values per instruction. A row store typically processes one row at a time through virtual function calls. This is called vectorized execution.

The costs

Column stores pay for these benefits:

  • Reading a whole row is slower. To rebuild row 42 you touch every column file.
  • Single-row writes are inefficient. One insert writes to many column files. That is why ClickHouse wants batches and writes each batch as a new immutable "part".
  • Updates and deletes are heavy, because data is compressed in blocks and files are never edited in place.

So the rule is: row stores for transactions, column stores for analysis. Many companies run both.

Seeing the idea with SQL

You cannot see storage layout from SQL, but you can see which columns a query touches. SQLite will happily answer both queries below, but only the first one is the kind of query that benefits from a column layout.

SQL Stand-in: aggregate vs row lookup (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

A rough size estimate

Here is a back-of-the-envelope comparison you can run. It models a billion-row table and what a query reads. The numbers are an illustration, not a benchmark.

SQL Stand-in: how much data does a query read?

Press Run (or Ctrl + Enter) to see the result here.

The result is about 141 GB for a row store scanning every field and about 6 GB when only country and duration_ms are read, before compression shrinks the second figure further.

When to choose which

Question Row store Column store
Look up an order by id Excellent Acceptable at best
Sum revenue by day for 3 years Slow Excellent
Update one customer's address Excellent Heavy
Insert 10 million events Fine Excellent in batches
Keep every field of a record together Yes No

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a column store, what is stored next to each other on disk?

    A column store writes each column as its own contiguous run of values.
  2. 2. Why can a column store compress better than a row store?

    Neighbouring values in a column resemble each other, so codecs like Delta and LZ4 work well.
  3. 3. Which query is the best fit for a column store?

    Aggregating one or two columns across many rows is the sweet spot.
  4. 4. Why does ClickHouse prefer large batch inserts?

    Every insert creates a part; many tiny parts slow queries and background merges.

Summary

  • Row stores keep one row together; column stores keep one column together.
  • Column stores read only the needed columns, compress better and process data in vectorized blocks.
  • They pay with slower whole-row reads, expensive single-row writes and heavy updates.
  • ClickHouse inserts data in batches, each becoming an immutable part.

Next: Your first table and queries