Difference between Row oriented and column oriented database


There are two ways of organizing data in a DBMS − one is row-oriented database and another is column-oriented database. The basic difference between the two is that a row-oriented database stores the data tables by rows, whereas a column-oriented database stores the data tables by columns.

Read this article to learn more about row-oriented database and column-oriented database and how they are different from each other.

What is Row-Oriented Database?

A Roworiented database is a traditional database like Oracle, MySQL, etc. It stores data tables by rows and the common method of storing a table is to serialize each row of data. Row-based systems are designed to efficiently return data for an entire row, or record. In a row-oriented database, the rows are stored in sequence which means that the rows will immediately follow each other.

The disadvantage of a row-oriented database is that it is slower, hence takes longer time. This is because the row-oriented database requires multiple disk reads. Row-oriented databases are best suited for OLTP (Online Transaction Processing).

What is Column-Oriented Database

A column-based database is a type of "No SQL" database such as HBase and Cassandra. Columnoriented databases do not support "traditional" transactional secondary indices. It is the responsibility of the user to maintain the "inverted index".

In a column-based database, the data is stored and retrieved in columns. Therefore, they allow high compression rates because of the unique values in columns. Columnbased databases are more efficient in executing the operations. These types of databases are best suited for OLAP (Online Analytical Processing).

Difference between Row Oriented Database and Column Oriented Database

The following table highlights all the important differences between row-oriented and column-oriented database −

Key

Row Oriented Database

Column Oriented Database

Basic 

It stores data table by row.

It stores data table by column.

Data Accessing 

Data accessing happens row by row

Data accessing happens column by column

Storage

Storage size optimization limited due to reduced ability of data compression in row-based systems

Column based systems provide better storage size optimization capabilities.

Performance

It takes longer time than column-oriented database because it requires multiple disk reads

It is faster than row-oriented database

Use Case 

Best suited for OLTP

Best suited for OLAP

Conclusion

The most significant difference between the two types of databases is that in a row-oriented database, data is accessed row by row; while in a column-oriented database, data is accessed column by column.

You should select a row-oriented database or a column-oriented database based on the specific needs of your application and the types of queries that will be performed most frequently. Row-oriented databases are optimized for transactional processing, while columnoriented databases are best suited for analytics and reporting.

Updated on: 22-Feb-2023

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements