loading...
Cover image for When to choose NoSQL over SQL?

When to choose NoSQL over SQL?

ombharatiya profile image OM Updated on ・2 min read

The agenda of this blog is simple. We’ll discuss various parameters that we keep in mind while deciding a perfect database for our Application Service.

In terms of data engineering, data pressure is the ability of the system to process the amount of data at a reasonable cost or a reasonable time. When one of those dimensions is broken, that’s a technology trigger and new inventions happen that result in new data technologies. In simple terms …

SQL is not obsolete, it’s just that now we have a different choice.

Before we jump up to comparing SQL and NoSQL databases, let’s take some time to appreciate the fact that SQL has been long into the industry (over 30+ years) and still has a good place in the modern application development environment.

Comparison Time … 🤞

  1. SQL: Optimized for Storage
    NoSQL: Optimized for Compute/Querying

  2. SQL: Normalized/relational
    NoSQL: Denormalised(Unnormalized)/Hierarchical

  3. SQL: Table based data structure
    NoSQL: Depending on DBs, the data structures are …
    ★ Key-Values(DynamoDB, Redis, Voldemort)
    ★ Wide-column i.e. containers for rows(Cassandra, HBase)
    ★ Collection of Documents(MongoDB, CouchDB, DynamoDB)
    ★ Graph Structures(Neo4J, InfiniteGraph)

  4. SQL: Ad hoc queries
    NoSQL: Instantiated views

  5. SQL: Scale Vertically & Expensive. Can Scale Horizontally but challenging & time-consuming
    NoSQL: Scale Horizontally & Cheap

  6. SQL: Fixed schema, altering requires modifying the whole database
    NoSQL: Schemas are dynamic

  7. SQL: Good for OLAP
    NoSQL: Good for OLTP at scale

  8. SQL: ACID(Atomicity, Consistency, Isolation, Durability) properties
    NoSQL: BASE(Basically Available, Soft state, Eventual consistency) properties

When to choose NoSQL? 👍

For our application service, when it comes down to ...

✔ Well-known and well-understood types of access patterns
✔ Want simple queries
✔ Not much data calculation involved
✔ Have a common business process
✔ OLTP apps

If this is true, then NoSQL is a perfect Database and would be most efficient. We have to structure the data model specifically to support the given access pattern.

When NOT to choose NoSQL? 👎

If our application service has the requirements to support ...

✔ Ad-hoc queries. e.g. bi analytics use case or OLAP application
✔ May require “reshaping” the data
✔ Complex queries, inner joins, outer joins, etc.
✔ Complex value calculations

So in brief, for our application service, if we understand the access patterns very well, they’re repeatable, they’re consistent, and scalability is a big factor, then NoSQL is a perfect choice.

PS: Mature developers don’t use the word “flexible” for NoSQL databases. There is a difference in being dynamic and flexible. Data model design is an intelligent engineering exercise in NoSQL databases.

What your opinion over my discussion up here? Why do you choose NoSQL over SQL?

...

Thanks for reading this blog!

Add a ❤ if you liked the comparison. Leave a comment below if you have any questions/feedback. I'm gonna write a Series on DynamoDB here, follow me for updates.

More About Author: Om Bharatiya

Happy Coding <3

Posted on by:

ombharatiya profile

OM

@ombharatiya

❤ to Build - Software Engineer & Enthusiast!

Discussion

markdown guide
 

It's not a matter of being negative but recognizing that known, stable software is important for continuity in the enterprise and that "new and shiny" tends to be extremely unstable and break regularly at unexpected times.

Notably, even when NoSQL came out, devs could already accomplish nearly everything NoSQL did in a traditional RDBMS way by just serializing data plus get JOINs, ACID and transaction support, and still work within a known, stable software environment. NoSQL didn't offer any of the latter critical database features - it was all beta quality at best for quite a while and NoSQL was for "at Google scale" deployments (aka only Google, Amazon, Twitter, and Facebook really needed that kind of solution). And major RDBMS vendors have already added much of what drove people to NoSQL in the first place into their products with native field types and advanced search indexes. Even Google, which started the NoSQL movement, has begun moving away from NoSQL back to SQL-driven architecture, so that says something right there:

blog.timescale.com/blog/why-sql-be...

Most of the core problems in computing were solved in the 1960s and 1970s. And that goes for SQL too. Whatever is new and shiny today was probably already solved back then but there's always someone who wants to reinvent it now so their name is on it and they can be famous (or something). SQL is broadly understood, is Standardized (e.g. ISO/IEC 9075:2016), has decades of computing behind it, and scales well enough for most needs. Those "devs who refuse to use NoSQL" probably understand all that and are simply willing to wait until NoSQL goes away.

 

Memcached protocol in MySQL makes a nice way of updating things using a straightforward key/value pair system too (if only it worked for their JSON data type it would be ideal for everything). We use a combination of standard SQL and JSON data in SQL with indexed columns projected out - this is the benefit of the application of NoSQL techniques to SQL in a mature product for sure - I can do a multi-table join and a report on it, I'd have a much harder time doing that in a pure NoSQL enviornment.

 

Well said. I also found that people were driven to the NoSQLs of the world for the same reason as the JavaScripts and Pythons... No types to be concerned about.

A lot of people don't understand types, and why they matter.

But then, as more people started using it, they realized that types really do make for a better coding experience, and thus all of the above started building (among others) types into their feature set.

Similarly, transactions also weren't a thing until much later after the release of these NoSQL / DocumentDB platforms.

It's fine to learn about new platforms if they solve a problem, but don't throw existing platforms away because they're not new / flashy. If all you're getting from a new platform is "bragging rights", then you're going in the wrong direction :-D

 

Great summary! I've always been a huge fan of The CRUD aspects of SQL and the power of the data access language itself. Some of the added required constraints of a relational database add complexities, at times unnecessarily. NoSQL is great for fast, agile development, but, like you mention, it can lead to issues when data needs to be accessed in indirect methods. I've always pushed for database with SQL support in projects I've worked on due to the power of the query language itself. Because of that, it's now one of my top priorities at HarperDB to ensure that we offer full SQL support on top of our NoSQL database to blend together what we see as the best of both worlds.

 

Thanks for adding such insightful comment, Jacob! This sums up as a smart job now ... a proper balance of both the databases adds to lot of performance and other unsung benefits. Awesome!! 🙌

 

Very good comparison, some really useful information. One thing I'd like to point out - these techs are usually treated as an "either or" situation, as in, you must only use one of these approaches in an application. I find that they work pretty well I'm conjunction. I use NoSql for the real-time current data layer of an app (something like firebase for example). Then for any retained not realtime data, such as logs/errors/transaction history ( anything that isn't going to be real-time data layer ) I use more of a traditional relational SQL style storage.

 

I think that using their strengths for compatible situations is how you can provide the best value when creating a solution.

It is not by accident that all major (cloud) platforms provide both SQL and non-SQL database technology.

Mix-n-Mash to optimize your value!

 

I very much go along with Kyle & Edwin's points. Both DBs have their own pros and cons, and we are mostly interested in utilizing their best in our apps. I've also worked on apps that uses a hybrid of both databases, i.e. as u too specified. And it works perfectly. It's all engineering practices and this is how industries work.

 

been with SQL for almost a decade and started using mongodb about a year ago. now SQL server is dead to me 😋

 

Working on NoSQL have it's own grace. It's awesome!! 🙌

 

Thanks for writing this out. I love the simple comparison and you inspired me to dive into this some more 🙌🏻

 

Thanks! I'm glad you loved it.

It encourages me to write more! 🙏💕

 

This is a very good analysis. Having gone through a conversion from SQL to MongoDB, these types of analytical decisions are key.

 

Thanks! I just tried to honestly define the decisive boundaries.

Will write more about NoSQL 🤗😎

 

Glad to see this informative post. Since, I am working on database so I think this definitely will help me.
Thanks.

 

Thanks for the kind words, I'm glad it was useful 🤗

Firestore is great for basic cases but not for advanced filters. It's better used as an addition to SQL for real-time data.

 

I thought that SQL is good for OLTP and NoSQL for OLAP.
And other explanation is a bit confusing like mixing properties of SQL and NoSQL

 

SQL is a good fit for OLTP and NoSQL for OLAP.