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

Columnar 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

mysql, PostgreSQL