In this lesson, you will learn how columnar storage shifts data organization to drastically accelerate analytical queries through projection and block-based compression.
The Shift: Rows vs. Columns
In a traditional row-store (like a B-Tree), a database saves data one row at a time. If you run a query to calculate the AVG(Price) for a table with 100 columns, the database must load every single row into memory, even though it only cares about the Price column. This is a massive waste of I/O.
Columnar Store Indexing flips this strategy. It stores all values for a single column physically together in a sequence. If you need the Price column, the engine reads only that column, ignoring the rest of the table entirely. This is known as Projection.
Block-Based Compression
Because data in a column is usually the same type (e.g., all integers or all strings) and often shares similar values, columnar stores group these values into "blocks" or "segments" that are highly compressible.
Imagine a column storing the status of millions of orders: ['Shipped', 'Shipped', 'Shipped', 'Pending', ...]. Instead of storing the text "Shipped" millions of times, the engine uses dictionary encoding or bit-packing to represent those millions of values in just a few bytes.
When you run an analytical query, the database:
- Projects only the required columns.
- Reads the compressed blocks.
- Performs calculations directly on the compressed data (or decompresses it on the fly in CPU cache), which is significantly faster than reading raw rows from disk.
A Conceptual Look
If you were looking at a standard table like this:
| ID | Name | Price |
|---|---|---|
| 1 | Bolt | 10 |
| 2 | Nut | 5 |
A columnar store effectively indexes it by splitting it into physical files:
File_ID:[1, 2]File_Name:['Bolt', 'Nut']File_Price:[10, 5]
When you run SELECT AVG(Price) FROM Table, the engine only opens File_Price. It never touches the Name data, reducing disk I/O to a tiny fraction of what a row-store would require.
Key Takeaway: Columnar indexing boosts performance by using projection to read only necessary data and block-based compression to minimize the physical footprint of that data.