Columnar Storage

Physically store table data grouped by column rather than by row, so an analytical query that touches a fraction of a table's columns reads only those columns from disk, at the cost of making single-row reads and writes far more expensive than in a row-store.

A row-store forces an analytical query to read every column of every matching row even when the query only needs a handful of them, wasting I/O bandwidth and cache space on unread columns and capping scan throughput on wide tables. Analytical workloads that aggregate over billions of rows need a physical layout where the cost of a scan scales with the columns actually read, not the width of the table.

A row-store (PostgreSQL heap, InnoDB clustered index) physically lays out all of a row's columns contiguously, because the common OLTP access pattern is "fetch or modify one row." A columnar engine (ClickHouse, Snowflake) inverts that layout: all values for one column across every row are stored contiguously, in their own file or block, because the common OLAP access pattern is "aggregate one or a few columns across many rows." A query that touches 3 of a table's 50 columns reads only those 3 columns' data from disk; a row-store reading the same query would read every column of every matching row and discard the other 47, wasting the I/O and cache space on data the query never needed.

Storing values column-wise also unlocks compression that row-wise storage cannot reach. Values within one column tend to be far more similar or repetitive than values across a row: a status column might have three distinct values across a billion rows, a timestamp column is monotonic or near-monotonic. Run-length encoding (store a value once plus a repeat count) and dictionary encoding (store each distinct value once and reference it by a small integer) both work far better against that column-wise regularity than they do against a row's mix of unrelated types and cardinalities. ClickHouse and Snowflake apply this compression per column, then execute scans in a vectorized fashion: operating on a batch of column values at once rather than one row at a time, which is both cache-friendly and amenable to SIMD instructions.

The same physical layout that makes wide scans cheap makes single-row operations expensive. Reading "one row" means touching a separate file or block for every column in that row and reassembling them, the inverse of the row-store's single contiguous read. Writing or updating "one row" is worse: it means touching every column's physical structure for a single logical change. ClickHouse's MergeTree engine avoids this entirely for the write path by batching inserts into immutable parts that are merged together in the background; a workload that sends many single-row inserts causes excessive part fragmentation and degraded read performance, because merge throughput cannot keep up with part creation. In-place row updates are not a native operation in ClickHouse at all: ALTER TABLE ... UPDATE and DELETE are asynchronous mutations that rewrite whole parts rather than modifying rows in place. Snowflake takes a related approach at the storage layer: data is organized into immutable micro-partitions with per-column metadata (min/max zone maps) that let a query prune micro-partitions that cannot match a predicate without reading them, but changing a single row still means writing a new micro-partition. Both designs are why columnar engines are built for OLAP and are a poor fit for OLTP point-query or point-update workloads.