What is a Columnar Database?

Columnar databases are those where the data is stored in columns instead of rows as is done in the traditional row-based databases as they offer impressive benefits in certain types of queries and data manipulation operations. Therefore, databases that are organized in columns provide better performances for operations that require applications to read data such as data analytics and data warehousing. This approach enhances the rate of query processing and reduces disk I/O making it suitable for cases that involve a large number of queries that are processed on large amounts of data.

Columnar Database VS Row Database

Both Columnar and Row databases are a few methods used for processing big data analytics and data warehousing. But their approach is different from each other. 

For example:

• Row Database: "Customer 1: Name, Address, Location."(The fields for each new record are stored in a long row).
• Columnar Database: "Customer 1: Name, Address, Location."(Each field has its own set of columns).

Example of Columnar Database

Here is an example of a simple database table with four columns and three rows.

In a Columnar DBMS, the data stored is in this format:

534782, 585523, 479148; Miller, Parker, Stacy; Ginny, Peter, Gwen; 6000, 8000, 2000.

In a Row-oriented DBMS, the data stored is in this format:

534782, Miller, Ginny, 6000; 585523, Parker, Peter, 8000; 479148, Stacy, Gwen, 2000.

When to use the Columnar Database

1. Queries that involve only a few columns.
   
2. Compression but column-wise only.
   
3. Clustering queries against a huge amount of data.

Advantages of Columnar Database

1. Columnar databases can be used for different tasks such as when the applications that are related to big data comes into play then the column-oriented databases have greater attention in such case.
   
2. The data in the columnar database has a highly compressible nature and has different operations like (AVG), (MIN, MAX), which are permitted by the compression.
   
3. Efficiency and Speed: The speed of Analytical queries that are performed is faster in columnar databases.
   
4. Self-indexing: Another benefit of a column-based DBMS is self-indexing, which uses less disk space than a relational database management system containing the same data.

Limitation of Columnar Database

1. For loading incremental data, traditional databases are more relevant as compared to column-oriented databases.
   
2. For Online transaction processing (OLTP) applications, Row oriented databases are more appropriate than columnar databases.

Conclusion

Columnar databases are very beneficial for the use cases that have many queries per data record and insert only operations. Due to their characteristic of being able to read data and compress it, they are well suited to analytics and data warehouse. However; they are not suitable for transactional systems or any application where constant update is required.