We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
This idea carries a full system design question on its own. Each walks through the full answer.
System Design Masterclass · Database Types Storage
Free preview: the first 2 parts of this 8-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Database Types Storage
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
A data analyst runs this query on a 500-million-row orders table:
SELECT country, AVG(order_total) FROM orders GROUP BY country;
The table has 30 columns: customer name, shipping address, payment method, timestamps, product details, and so on. But the query only needs two columns: country and order_total.
In a row-oriented database, the engine reads all 30 columns for every single row. That is 500 million rows times 30 columns of data, when you only needed 2 columns. You just did 15x more I/O than necessary.
The query takes 45 minutes. The analyst stares at their screen. The database server's disk I/O is maxed out.
This exact problem is why columnar storage was invented. Instead of storing all columns of a row together, what if you stored all values of each column together? Then the query above reads only the country and order_total columns, skipping the other 28 entirely.
That 45-minute query? It finishes in 90 seconds.
Take the same table from the previous lesson:
| id | name | age | |
|---|---|---|---|
| 1 | Alice | alice@email.com | 30 |
| 2 | Bob | bob@email.com | 25 |
| 3 | Carol | carol@email.com | 35 |
In columnar storage, the data on disk looks like this:
id column: [1, 2, 3]
name column: ["Alice", "Bob", "Carol"]
email column: ["alice@email.com", "bob@email.com", "carol@email.com"]
age column: [30, 25, 35]
Each column is stored as its own contiguous block. When you run SELECT AVG(age) FROM users, the database reads only the age column, [30, 25, 35], and ignores everything else. Zero wasted I/O.
But there is a second massive advantage: compression.
Look at the age column: [30, 25, 35]. These are all small integers. They compress beautifully. Run-length encoding, dictionary encoding, or delta encoding can shrink this data by 5-10x. In a row store, the age value sits next to a string (name) and another string (email), so the types are mixed and compression is poor.
In columnar storage, each column contains values of the same type, and often with limited cardinality (think: country codes, status enums, boolean flags). This uniformity enables compression ratios of 10:1 or better, which means less data to read from disk, which means faster queries.