Rows vs Columns: The Cache-Line Math That Decides Your Database Layout
A 48-byte record holds a 4-byte column you actually need. Reading that one column through a row-oriented layout pulls twelve times more bytes from memory than the query uses, because the CPU cannot fetch four bytes without fetching the 44 bytes sitting next to them. Multiply that by a hundred million rows and a layout choice you never thought about becomes the difference between scanning 4…
Rows and columns represent two distinct approaches to storing data within a database. The physical arrangement of data on disk - whether records are stored as rows or columns - has a significant impact on a database's performance.
A row-oriented layout stores each record's data adjacent to one another, similar to a line in a CSV file or an array structure in programming. In contrast, a column-oriented layout stores each column's data together, akin to a struct in array-of-structs configuration. This structural difference has profound implications for how databases handle memory access and execution.
When reading data, the CPU operates on fixed-size blocks known as cache lines - typically 64 bytes on most systems, though larger on Apple's M-series chips. If the data the CPU needs resides within a cache line, it retrieves the entire line, even if the exact information sought is just a small portion of that data. In a row-oriented format, querying a specific column necessitates fetching the entire record, which often amounts to fetching 12 times more data than required. This inefficiency multiplies across vast datasets, leading to substantial performance disparities.
To illustrate, consider a hypothetical database containing 100 million records, each approximately 48 bytes in size. Utilizing a row-oriented layout, a query requiring data from a single column would compel the CPU to fetch an additional 44 bytes for each record, resulting in the need to scan 4 gigabytes of data. In contrast, a column-oriented layout would allow the CPU to directly access the necessary four bytes without any extra fetching, dramatically reducing the required data retrieval.
This fundamental difference in data storage and retrieval strategies forms the core of the performance gap observed between row and column databases. PostgreSQL and MySQL, for example, traditionally employ row-oriented storage, while DuckDB and ClickHouse utilize column-oriented storage. Despite both systems ultimately yielding identical results when queried, the performance gap between them can be staggering - often reaching an order of magnitude difference, driven by physical rather than algorithmic factors.
The article delves deeper into the intricacies of these storage mechanisms, examining the memory layout, execution models, and compression techniques employed by each approach. It further explores the column store's notable advantages, particularly in scenarios involving large datasets and frequent querying of specific columns. Through this analysis, the article seeks to clarify the profound impact of physical data organization on database performance, highlighting the critical role that cache-line math plays in determining the efficiency of database layouts.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.