Database performance is fundamentally constrained by how data is physically arranged on the storage medium. While SQL abstracts the retrieval process, the database engine must ultimately read blocks of data from a disk or memory to fulfill a request. The arrangement of these blocks determines whether a system excels at processing individual transactions or aggregating millions of records.
To understand why analytical systems prefer columnar storage, we must look at the unit of IO transfer. Databases do not read individual rows or cells; they read blocks (or pages). A typical block size ranges from 4KB to 32KB depending on the system configuration.
When a query requests a single data point, the engine loads the entire block containing that point into memory. The efficiency of a database architecture is largely defined by the ratio of useful data to total data loaded during this operation.
In a row-oriented database (like PostgreSQL, MySQL, or SQL Server), data is stored sequentially by row. If you have a Sales table with columns for Date, Product_ID, Store_ID, and Revenue, the storage engine writes the data for the first transaction followed immediately by the data for the second transaction.
Consider the memory layout of three transactions:
Memory layout showing how row-oriented systems interleave attributes. All attributes for Row 1 are stored adjacent to each other.
This layout is ideal for Operational (OLTP) workloads. When a user logs in or checks their specific order history, the query typically looks like this:
SELECT * FROM Sales WHERE Transaction_ID = 105;
The database seeks the specific block containing transaction 105. Because the data is row-oriented, the engine retrieves the Date, Product, Store, and Revenue for that transaction in a single IO operation. This is highly efficient for "point lookups" where the intent is to retrieve all context regarding a specific entity.
The performance profile changes drastically when the objective shifts to analytics. Analytical queries rarely care about a single transaction. Instead, they scan areas of data to compute aggregates.
Consider a query calculating total revenue:
SELECT SUM(Revenue) FROM Sales;
In the row-oriented layout shown above, the database must read every block containing sales data. However, for every block loaded, only the Revenue integer is useful. The Date, Product_ID, and Store_ID are loaded into memory, consuming bandwidth and cache space, only to be discarded.
If the Revenue column represents 5% of the total row width, then 95% of the IO throughput is wasted. This inefficiency is the primary reason operational databases struggle with data warehousing workloads.
Columnar storage addresses this inefficiency by pivoting the physical layout. Instead of storing data row-by-row, it stores data column-by-column. All values for the Date column are stored sequentially, followed by a separate storage area for all Product_ID values, and so on.
This structural change means that a query summing revenue only needs to access the specific blocks containing Revenue data. The engine can completely ignore the blocks containing dates, products, or customer notes.
Columnar layout separates attributes into distinct storage blocks. A query requesting only Revenue accesses only the purple blocks.
We can define the performance difference mathematically. Let be the number of rows in a table, and be the average width of a row in bytes.
In a row store, a full table scan requires reading a total volume :
In a column store, if we only require a subset of columns with a combined width , the volume scanned is:
Since analytical queries typically select a small subset of columns (often 2 to 5 columns out of a table of 50+), is significantly smaller than .
For example, if a table has 50 columns and a total width of 500 bytes, and we only need 1 column of width 4 bytes:
The following chart illustrates the impact on scan time as the number of projected columns increases.
Comparison of scan times. Row-oriented stores (red) incur a fixed high cost regardless of selected columns because they read the full row. Columnar stores (blue) scale linearly with the number of columns needed.
Beyond simple IO reduction, columnar storage enables superior data compression. In a row-oriented block, data types vary wildly, a string follows an integer, which follows a timestamp. This entropy makes compression algorithms less effective.
In a columnar block, every value has the same data type and often a similar domain. A column storing "Country" might repeat the value 'USA' thousands of times consecutively.
Columnar databases utilize encoding schemes like Run-Length Encoding (RLE) to exploit this repetition. If a column contains the value 'USA' 500 times in a row, the database stores it as a simplified tuple ('USA', 500) rather than writing 'USA' 500 times. This can reduce storage footprint by 10x to 50x, further accelerating scans because the CPU processes compressed data directly in the L1/L2 cache.
There is no free lunch in database architecture. The optimization for read speed imposes a heavy penalty on write operations.
In a row store, inserting a record is a single append operation to the end of the active file. In a columnar store, inserting a single record requires the database to:
This process, known as "tuple splitting," is expensive. Consequently, analytical systems (OLAP) generally avoid singleton INSERT statements. Instead, they rely on batch loading processes where thousands or millions of rows are buffered and written simultaneously, amortizing the reconstruction cost. This fundamental architectural difference dictates why we separate our analytical data warehouses from our live transactional databases.
Was this section helpful?
© 2026 ApX Machine LearningAI Ethics & Transparency•