Rows vs Columnar Databases
Understand the key differences between row and columnar database formats. Learn how traditional row storage suits certain queries while columnar storage improves efficiency for analytical tasks by minimizing unnecessary data reads and optimizing storage space through alignment and padding.
We'll cover the following...
Rows vs Columnar Databases
Databases are frequently used to store data. They do so in a manner different from how we visually perceive data arranged in rows. Data is stored in two possible formats:
- Row Format
- Columnar Format
We are already familiar with databases that use the row format. These are the traditional relational databases such as MySQL, PostgreSQL, Oracle, and others Row format databases store records as rows in a table. A typical representation of our example Car table, consisting of four columns when stored in a relational database, is like this:
Storing data as rows is great for some use cases. For instance, if we want to retrieve the record for the car BMW from the above table, the row format is suitable because the columns for the BMW row are stored next to each other in memory. The CPU’s read operation benefits from locality of reference. Related data is stored nearby and can be retrieved in a minimal number of memory reads. However, there are other use cases where traditional databases in row format perform abysmally. Imagine that we want to compute the average horsepower of all ...