In this lesson, you will learn how to shift from row-based to columnar storage formats to drastically accelerate large-scale analytical queries.
The Problem with Row-Oriented Storage
Traditional databases store data in a row-oriented format (often called an N-tuple store). In this structure, every field for a single record is stored together on the disk. While this is perfect for transactional tasks—like inserting a new customer record—it is inefficient for analytics.
If you want to calculate the SUM(revenue) for the entire year, a row-oriented database must read every column in every row (e.g., customer name, address, phone number) just to get the revenue value. This results in massive I/O overhead because you are loading unnecessary data into memory.
The Columnar Advantage
Columnar storage flips this model by grouping data by column instead of by row. Each column is stored in its own separate file or block on the disk. When you run an aggregation, the system only reads the specific columns required for the calculation.
Imagine a Fact_Sales table with 100 columns and 1 billion rows.
Row-based approach:
- Reads: All 100 columns.
- Performance: Slow; high I/O latency.
Columnar approach:
-- You only scan the 'revenue' column
SELECT SUM(revenue) FROM Fact_Sales;
- Reads: Only the
revenuecolumn. - Performance: Extremely fast; the engine skips 99% of the irrelevant data.
Why This Matters for Analytics
Beyond reducing I/O, columnar storage excels because of how data is physically laid out. Because columns contain data of the same type, they are highly compressible (e.g., using run-length encoding or dictionary encoding). This reduces the storage footprint significantly and allows the database engine to perform "vectorized processing"—executing operations on entire blocks of column data at once rather than one row at a time. By minimizing the amount of data read from the disk and optimizing how that data fits into CPU caches, you create a high-performance environment for your semantic models and fact tables.
Key Takeaway: By storing data in columns rather than rows, you eliminate unnecessary I/O, enabling the database to scan only the specific attributes needed for aggregations and drastically reducing query latency.