NoSQL versus SQL Database

I have been working with SQL and MPP databases since very long time. After working so long, I learnt depth and breadth of those databases. When NoSQL databases were launched, they became very popular; specially to build scalable and high preforming applications. Cloud packaged services helped them to become more popular as we got opportunity to choose them very easily (as per our need). After hearing about NoSQL databases I also tried to build my knowledge around this by reading the online articles. But as you know, best way to learn the technologies are do hands on! Recently, I got opportunity to implement a solution using DynamoDB . This helped me to learn a lot around NoSQL databases. I am very impressed with its capabilities and features! I will not go into the details of architecture and how it works because for that you can find tons of materials over the internet. I will put some light on SQL versus NoSQL database based on my experience.

  1. NoSQL is not a replacement of SQL database: Yes, NoSQL is not an alternate for the SQL databases. Both have different features and purposes. Based on the use case, database selection should be made. In fact, I say that they are compliment to each other. If something is complex to implement using SQL database, It may be easy using NoSQL database and vice versa. If you don't know the schema(structure) of your table in advance or the schema is dynamic then NoSQL is perfect fit for that. But if you want normalization, multirow data update, fixed schema, joins and complex queries then SQL is the answer.
  2. Design Pattern and Modeling: By now you would have realized that NoSQL database is almost zero modeling database. You don't need to define the table structure in advance except the primary key((Primary key is the combination of partition key and sort key). If your schema is changing with the data, NoSQL can accommodate it easily. The table schema is created dynamically based on the data. This is a big advantage for designing some particular type of applications like if your customer data is changing based on individual customer or your order schema is changing based on your order type. If you have requirement that schema is fixed and it should be enforced, SQL database is better bet! For NoSQL database, it is suggested that you should create one table per application because it doesn't support joins. If you have a requirement to maintain different masters data , and enforce referential integrity, SQL database is better fit. NoSQL database allows update, insert and delete based on only primary key. If you would like to update and delete the data based on any random column of the table , SQL database is better fit. With NoSQL database from modeling perspective, you have to think only about the primary key. You must choose the right primary key to get good performance and avoid the data skew. The data retrieval and insertion all will be focused around the primary key, rest all fields are just the attributes. SQL database required more modeling efforts like primary key, indexes, foreign keys, DDL and data types etc. On the modeling parameter you will find NoSQL has many good things compared to SQL database.
  3. Performance: SQL database is vertical scalable that means you can add more power to the existing machines (like CPU/RAM) while NoSQL database is horizontal scalable that means you can add more machines to your resources pool. We can say that NoSQL database performance will perform better due to horizontal scaling. NoSQL is good if we want to build the applications with very low latency. But, this comes with the some cost. You may have to pay more compared to SQL database.
  4. Querying: You can't write complex quires and join tables in NoSQL database while SQL databases are good in this. Using regular querying tools (sql developer, winsql etc) you may not be able to query NoSQL database . You may need special third party tools such as Studio 3T, PartiQL(for Dyanmodb). Many time we use a programming language such as Python to query NoSQL databases.

For me, SQL versus NoSQL is similar to appetizer versus main course. Both have different features and purpose and can't replace each other. Actually, while dining out, combination of both is awesome!!!

Krishna Kumar Singh

Teradata Certified Consultant DBA | GCP Cloud Architect & Data Engineer | Snowflake Certified | Splunk & ITIL Certified

3 年

Nice overview of both with pros n cons.

Tamanna Gupta

Data Leader Driving Business Transformation through Advanced Analytics and AI | Database Architecture | Data Mesh |Data Lake |Snowflake -Data Cloud Certified Architect

3 年

Simple but effective write up

要查看或添加评论,请登录

Gopal Kumar Roy的更多文章

社区洞察

其他会员也浏览了