Tuesday, September 11, 2018

SQL vs NoSQL

SQL and NoSQL are two important choices for application data storage needs. There is a lot of confusion about which one to choose and what is a good fit. There are buzzwords on both sides which make the choices more confusing. How to choose one over there?

SQL vs NoSQL databases can be compared on the following aspects:

  • Schema: SQL has strict schema but NoSQL is schemaless
  • Relational: SQL DB has relations among tables but in NoSQL no such enforced relationship exists.
  • Transactional: SQL DB support ACID integrity. NoSQL does not have any such notion
  • Scaling: It's difficult to scale SQL DB horizontally thought there are techniques like sharding. NoSQL DB can be scaled horizontally.
One of the fundamental thing that will be driving your choice would be the kind of application you want to build. The SQL system is primarily a data model driven model and NoSQL is primarily a query-driven model. However, with NoSQL one has to be very careful as it's quite possible that with changed requirements the NoSQL model can quickly become unwieldy.

Some of the use cases that can help in doing choices:
  • For transactional scenarios with ACID integrity, SQL systems are best. They are stable, well tested and had been around for a while. They are battle-hardened system.
  • When the use case is primarily write-once and read multiple times and very less or non-existent updates, NoSQL is good. Make sure you don't see any changes happening in the use cases in future. There are techniques for handling those scenarios by doing indexing and all but it will be painful.
  • If the data is non-structured, then NoSQL is a good data storage. Hierarchical data or document based data can be handled much easier by NoSQL
  • SQL databases scale by going vertical. NoSQL scale horizontally. An easy analogy to understand is that NoSQL can be thought of a map with a key-value pair. There is no concept of constraint across data items. However, with SQL databases foreign keys and constraints are important notions to take care of. They provide integrity but make it harder to scale. Techniques like sharding need to be used to do that.
  • It's common in NoSQL databases to replicate the information across tables so that multiple queries can be avoided. SQL databases support joins and complex queries to handle that.
If you have to choose, my suggestion would be to first evaluate SQL databases and find a good reason to not to use that. Now there are SQL databases which support NoSQL notions also. NoSQL is very good in data like time series. With NoSQL make sure the use cases are clearly laid out and are not going to change. SQL databases can evolve with time in terms of structure.

Examples of SQL databases: Oracle, PostgresQL, MySQL, MariaDB, SQL Server, DB2
NoSQL databases: Cassandra, MongoDB, Cassanadra, neo4j(Graph database).

More article on Databases

No comments:

Post a Comment