When it comes to relational databases, there are two types – row-oriented databases and column oriented databases. The row-oriented databases store all values in a row together while the column-oriented databases (aka columnar databases) store all values from a column together. You have to choose between row-oriented and columnar databases carefully because your decision will have a big impact on performance. Row-oriented databases have been around for a long time. Columnar database are relatively new. They have only been around since 2005.
Columnar databases vs row-oriented databases
Faster queries for analytics
In a data warehouse, you are likely to query a few columns but a large number of rows. In this case, a columnar database will be faster. So columnar databases are great for analytical processing applications (OLAP). Popular data warehouse databases such as RedShift, Snowflake, and BigQuery are columnar databases.
Data is partitioned vertically
Columnar are databases are partitioned vertically. Since these databases store values by column, reads are more efficient.
Better data Compression
Values in a column are more likely to have the same data type. I addition, the number of distinct values are smaller in a column compared to a row. As a result, data compression is more efficient in columnar databases.
Popular columnar databases
BigQuery, Redshift, Snowflake
What’s not so good
While columnar databases are great for reading, when it comes to writing data, they are not as efficient.
Row oriented databases
Ideal for OLTP (online transaction processing)
Inserts, updates, and deletes involving small amounts of data are common in most transaction databases. In such cases, a row- oriented databases make sense.
Data is partitioned horizontally
Row oriented databases are partitioned horizontally. These databases store values by row , so writing data is more efficient. Performance of these databases can be improved by indexing
Common row-oriented databases