columnar database

A columnar database is a database management system (DBMS) that stores data in columns instead of rows. 

The goal of a columnar database is to efficiently write and read data to and from hard disk storage in order to speed up the time it takes to return a query.  

Download this free guide

Machine Learning Meets Data Quality

Read an exclusive interview with Andrew Burt, chief privacy offer and legal engineer at Immuta Inc., on data governance and machine learning integration. Plus, uncover steps IT managers are taking to improve data quality in their big data environments in order to ensure analytics accuracy.

Start Download

• Corporate E-mail Address:

  You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

  Please provide a Corporate E-mail Address.

• • I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

  Please check the box if you want to proceed.

• • I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

  Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

In a columnar database, all the column 1 values are physically together, followed by all the column 2 values, etc. The data is stored in record order, so the 100^th entry for column 1 and the 100th entry for column 2 belong to the same input record. This allows individual data elements, such as customer name for instance, to be accessed in columns as a group, rather than individually row-by-row. 

Here is an example of a simple database table with 4 columns and 3 rows.

ID         Last      First      Bonus

1          Doe      John      8000

2          Smith    Jane      4000

3          Beck     Sam      1000

In a row-oriented database management system, the data would be stored like this:  1,Doe,John,8000;2,Smith,Jane,4000;3,Beck,Sam,1000;   

In a column-oriented database management system, the data would be stored like this:  1,2,3;Doe,Smith,Beck;John,Jane,Sam;8000,4000,1000; 

One of the main benefits of a columnar database is that data can be highly compressed. The compression permits columnar operations — like MIN, MAX, SUM, COUNT and AVG— to be performed very rapidly.  Another benefit is that because a column-based DBMSs is self-indexing, it uses less disk space than a relational database management system (RDBMS) containing the same data. 

As the use of in-memory analytics increases, however, the relative benefits of row-oriented vs. column oriented databases may become less important. In-memory analytics is not concerned with efficiently reading and writing data to a hard disk.  Instead, it allows data to be queried in random access memory (RAM).