ClickHouse can scan a billion rows in under a second on a single machine. PostgreSQL on the same hardware would take minutes. Same data. Same queries. Different layout.
This isn't a tuning problem. You can't VACUUM your way to ClickHouse performance in Postgres. The gap is structural, baked in at the moment the data was written to disk. Understanding why requires thinking about what "reading data" actually means at the hardware level.
Row storage: the mental model
PostgreSQL stores data in heap files. Each row is written contiguously: all
the columns for a row packed together before moving to the next row. For a table with columns (id, name, email, age, created_at,
country, plan), the on-disk layout looks roughly like this:
[id=1][name="Alice"][email="alice@…"][age=31][created_at=…][country="US"][plan="pro"]
[id=2][name="Bob"][email="bob@…"][age=27][created_at=…][country="DE"][plan="free"]
...
Now consider this query: SELECT AVG(age) FROM users. You want
one number. The database has to scan every row in the table to get there,
including every name, every email, every
created_at. If the table is 1 million rows and each row is 200
bytes, you're reading 200 MB to extract maybe 4 MB worth of
age values. You're paying for 196 MB of data you immediately
discard.
Disk I/O is the bottleneck for large scans. It doesn't matter how fast your CPU is if it's waiting for bytes that aren't on the page it's reading. Row storage is excellent for queries that need all columns of a small number of rows ("give me everything about user 42"), because everything you need is co-located. For queries that need one column from millions of rows, it's structurally bad.
Columnar storage
A columnar store flips the layout. Instead of storing all columns for row 1, then all columns for row 2, it stores all values for column 1, then all values for column 2:
age file: [31][27][44][31][22][…]
name file: ["Alice"]["Bob"]["Carol"][…]
email file: ["alice@…"]["bob@…"][…]
created_at file: […]
To compute AVG(age) over 1 million rows, you open one file, read
4 bytes per row, sum them. You never touch the name file, the email file, or
any other column. I/O is proportional to the columns you actually query, not
the width of your schema. A table with 50 columns where your query touches 3
of them reads roughly 3/50th of the data a row store would.
This is the core idea. Everything else: compression, vectorized execution, late materialization, all follows from having homogeneous data in one place.
Why it compounds: compression
Compression ratios on columnar data are dramatically better than on row data, and this matters more than people expect.
Consider the age column. The values range from roughly 0 to 120.
They're all integers. Many repeat. A run-length encoding scheme (which stores
"value 31 appears 8,432 times" instead of 8,432 copies of 31) can compress
this to a fraction of its original size. Dictionary encoding stores a lookup
table of unique values and replaces each value with a small index.
Frame-of-reference encoding stores the minimum value once and then stores
deltas. These are all simple techniques that work well because the data is
homogeneous: all the same type, all following the same distribution.
Compare that to a row in a typical OLAP table: an integer next to a UUID next to a variable-length string next to a timestamp next to a boolean. General compression (like gzip) can find some redundancy, but you're working against the mixed types. Row stores compress at maybe 2-3x. Columnar stores regularly achieve 10x or more on analytical workloads.
Why does this matter? Because "10x compression" means "1/10th the I/O." If
reading the age column would take 400 MB uncompressed, you're
reading 40 MB after compression. At 500 MB/s of disk throughput, that's the
difference between 800ms and 80ms. The compression is free throughput: the
CPU overhead of decompression is cheap compared to the I/O you saved.
Vectorized execution
Columnar storage also changes what the CPU can do with the data. Modern CPUs have SIMD instructions (Single Instruction, Multiple Data) which apply one operation to many values simultaneously using wide registers. An AVX2 instruction can hold 8 32-bit integers and add them all in one clock cycle.
In a row store, the execution model looks like this: fetch row, extract age, add to accumulator, advance to next row. Each iteration involves a pointer chase, a struct-field offset calculation, and a single arithmetic operation. The CPU's wide SIMD units are idle.
In a columnar store, the execution model looks like this: load 8 ages into a SIMD register, add all 8 to the accumulator register in one instruction, advance by 8 values. The loop is tight, the data is contiguous in memory, and the CPU's pipeline stays full. This is vectorized execution: processing data in chunks sized to the CPU's SIMD width rather than one value at a time.
ClickHouse, DuckDB, and modern analytical engines build their entire query execution model around this. Instead of row-at-a-time, they operate on vectors of ~1,000 values per step. Branches are minimized. Memory access patterns are predictable. Cache hit rates are high. The result is that the CPU actually approaches its theoretical throughput instead of spending most of its time waiting.
The tradeoff: writes
Nothing comes free. The write path in a columnar store is more expensive than in a row store.
In PostgreSQL, inserting a row means appending it to one location in one heap file. One write, one location. In a columnar store, inserting one row means writing to N files, one per column. If your table has 50 columns, you have 50 file operations instead of one. For large batch inserts this amortizes fine. For row-level transactional writes, say an e-commerce app recording individual orders as they arrive, it's painful.
Updates are worse. Columnar files are typically immutable (for good reasons tied to their compression schemes). Updating a single value means rewriting the relevant column segment, or marking the old row as deleted and writing a new one, and then running a compaction process to merge things. Some systems (like ClickHouse's MergeTree engine) handle this well in bulk. None of them handle it gracefully one row at a time.
Deletes have the same problem. In columnar stores, "deletes" are often just markers that tell the query engine to skip certain rows during reads. Actual data removal happens during background compaction.
When to use what
The divide has a name. OLTP (Online Transaction Processing) describes workloads with many small reads and writes by many concurrent users. Think: a bank recording a transfer, a web app loading a user's profile, an inventory system updating stock. Each operation touches one or a few rows, needs a quick response, and runs in a transaction. Row stores are good at this.
OLAP (Online Analytical Processing) describes workloads that scan large portions of the dataset to compute aggregates. Think: a data analyst computing monthly revenue by country, a recommendation system training on 6 months of user behavior, a dashboard showing DAU trends. These queries touch few columns but many rows, tolerate higher latency, and don't need strict transactional guarantees. Columnar stores are good at this.
The line is genuinely blurring. DuckDB is a fully columnar, SIMD-accelerated analytical database that runs in-process: you can embed it in a Python script and query Parquet files directly. It's great for analytics but not for concurrent transactional workloads. Postgres has features that help for time-range scans (BRIN indexes, partitioning by date), but it's still a row store at heart. ClickHouse handles high-throughput inserts via batching and its MergeTree engine, but you wouldn't run your web app's transactional writes through it.
Hybrid approaches exist: Snowflake, BigQuery, and CockroachDB's analytical mode keep a columnar representation for reads while handling transactional writes through a separate path. The operational complexity of "two storage layers" is real, but so is the performance gain.
The choice you make before the data exists
You can add indexes, rewrite queries, and throw hardware at a row store running analytical workloads, and you will hit a ceiling that a columnar store clears without effort. That ceiling isn't a tuning parameter. It's the architecture.
Picking the right storage model isn't glamorous. It doesn't show up in a demo. But it's what makes a billion-row query take one second instead of three minutes, and you have to make the call before the data exists.
Back to writing