Row vs. Columnar Storage: Organizing Data for Speed
Row stores group data by record, like a phone book entry. Column stores group by attribute, like separate lists for all names. Use row stores for transactions (OLTP), but for analytics (OLAP), they force you to read unneeded data from disk.
WHY IT EXISTS: Databases need to arrange tabular data into a linear sequence for storage on disk or in memory. The choice of how to arrange this data has massive performance implications depending on how you plan to read it. Row-oriented and column-oriented are the two primary solutions to this problem, each optimized for different access patterns.
THE MENTAL MODEL: Imagine a table of user data: UserID, Name, Email, LastLogin. A row-oriented database stores this like a series of contact cards in a file box. To get User 123's info, you pull one card: [123, 'Alice', 'a@b.com', '2023-10-27']. A column-oriented database stores it as four separate lists. One list has all UserIDs, another has all Names, and so on. To find all user names, you just read the 'Name' list.
HOW IT WORKS: In a row store, data for a single row is stored contiguously on disk. To fetch a whole row, the disk head reads one continuous block. In a column store, all values for a single column are stored contiguously. When you query for the average of a single column, the database only reads that one block of data, ignoring all other columns. This also leads to better data compression, as similar data types are stored together.
WHEN TO USE IT: Use row-oriented storage for Online Transaction Processing (OLTP) systems. These are applications like e-commerce sites or banking apps where you frequently read or write entire records at once (e.g., "get all info for this customer"). Use column-oriented storage for Online Analytical Processing (OLAP) systems, like data warehouses. These are used for business intelligence and reporting, where queries often aggregate a small number of columns over a huge number of rows (e.g., "calculate total sales by region").
WHEN NOT TO USE IT: Avoid using row-oriented databases for large-scale analytics. A query that only needs one column will be forced to read all columns for every row, leading to massive I/O waste. Conversely, avoid using column-oriented databases for write-heavy, transactional workloads. Inserting or updating a single complete record requires writing to multiple separate files (one for each column), which is much less efficient than writing to one contiguous block in a row store.
ONE CANONICAL EXAMPLE: A classic row-oriented database is PostgreSQL or MySQL, perfect for powering a web application's backend. A classic column-oriented database is Amazon Redshift or Google BigQuery, designed to run complex analytical queries over petabytes of data for business intelligence dashboards.
Read the original → en.wikipedia.org
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.