Databases: Difference between Relational Database, NoSQL database, and Columnar database

You might have heard about these terms, but you might be wondering what is the difference between these 3 types of databases. I’ll try to explain my understanding of each and when to use them.

Relational Database

Relational database is such database that stores related data. For example, you own a show and keep a record about your clients and items they bought. We can draw two tables one that stores Users and second one stores Items. So, to store transactions we logically draw relationship between these 2 unrelated data. That’s why RDBMS is called relational database, because there is certain relationship between separate pieces of information. Example of relational databases are: MySQL, PostgreSQL, Oracle Database and so on. When you put data into this kind of database, you will need to plan relationship between the data before using database.

Ex of Relational data:

Relational Database Design

NoSQL database

NoSQL database is complete opposite of Relational Database. This kind of database allows you to simply put the data that you want to store. This is quite useful in this cases:

  1. You are rapidly developing your app. The beauty of NoSQL is that you don’t need to spend time about all the relationships between data that you want to store. You just simply store your data.
  2. When you primarily want to store data. For example, you have some sensor data and you want to store it.

Above two are the ones comes to my mind. There should be other uses too. Examples of NoSQL database is MongoDB and Cassandra. The difference between the two is that MongoDB is document based, meaning it stored objects as documents. Document here is some sort of logical representation of data. MongoDB uses JSON to store data. Casandra however, just stores key and value pair.

Ex of NoSQL data:

Columnar Database

Columnar database stores all the data as a sequence of data points. This is quite useful to perform aggregation operations such as summing. Since the data is stored sequentially, it’s easier for db to perform operations, because it doesn’t have to scan the rows of data like in a relational database, it just needs to read data by knowing it’s offset. Some of the example of columnar database is AWS Redshift

Ex of columnar data:

So here we go, the only difference between these 3 database types is the way how they store data. Of course, each of them have their own usages, so using one over another gives some benefits.