Search for a command to run...
Important points to consider, Relational vs Non-relational Database, CAP theorem, and more!
Senior Software Engineer. I write about practical tips and epic stories about my experiences in the field of computer science and engineering!
Speed and scalability are critical.
Just out of curiosity. Could you explain about NoSQL's speed 🤔 ? I know NoSQL Database is very scalable. But SQL Databases (at least in my mind) is faster because of their maturity and indexing features.
Thank you for the great article 😀!
Hi Alvin Endratno. Thanks.
And for your question, as per what I have discovered, In SQL databases, there is a process that validates that the inserted data corresponds to the defined schema of the table.
This process sometimes takes time as we validate each data item against the corresponding column. Once we go schemaless with NoSQL, we can save this time. Thus, NoSQL databases may provide better write operations per second as compared to SQL DB.
In the case of read operations, both SQL as well as NoSQL support indexes. Yes, SQL joins are better optimized than MongoDB's aggregate functions but MongoDB can store more complex documents, with all the dependent data together in nested structures. That way you don't have to do joins which optimizes for a certain kind of data retrieval.
Thank you so much Andrew Baisden. Glad you like it :)
Bits and Pieces I learned on how to build and scale a distributed system. All the posts in the series belong to the real problems I faced during my 9-5 and the solutions we went through with.
Low Altitude Flight Over the Messaging Queue Landscape
Stop guessing your maximum-pool-size. Learn how to calculate limits backwards from your database
Learn what Jib is, how it works, and when to use Jib vs Dockerfile. A guide with real-world scenarios
A prod debugging story you’ll probably relate to
The $0 solution to a Distributed Cache Invalidation
Introduction At our current organization, earlier this year as we were looking at the errors at one of our signup API, we observed that nearly 5% of our requests were getting failed, all due to 400 BAD REQUEST errors, and the root cause was traced ba...
Choosing which database to use is one of the most important decisions you can make when starting working on a new app or website.
If you realize down the line that you’ve made the wrong choice, migrating to another database is very costly and sometimes more complex to do with zero downtime.
Taking time to make an informed choice of database technology upfront can be a valuable early decision for your application.
(A bonus reason for why this is important is because understanding the different DBs and their properties & which one to choose over the other is quite commonly asked in job interviews)
Each database technology has advantages and disadvantages. Cloud providers like Amazon offer various database and storage options making it harder to figure out which one is the right one.
Relational databases have been a primary data storage mechanism for a long time. Non-relational databases have existed since the 1960s but recently gained traction with popular options such as MongoDB.
In this article, we will go through the factors you should consider while choosing any database.
First things first. The language or the technology you are going to use never determines the database ❌
We’ve grown accustomed to technology stacks, such as MERN (MongoDB, Express, React, Node.js) & LAMP (Linux, Apache, MySQL, PHP).
There are certain reasons why these stacks evolved. But don’t assume these are the rules. You can use a MongoDB database in your Java project. You can use MySQL in your Node.js. You might not have found as many tutorials, but it's your requirements that should determine the database type & not the language.
Let's now understand the basic tradeoffs we need to deal with while making the DB decision.
The reason that we have many database options available today is due to the CAP Theorem. CAP stands for Consistency, Availability, and Partition tolerance.
Consistency means that any read request will return the most recent write.
Availability means all (non-failing) nodes are available for queries & must respond in a reasonable amount of time.
Partition Tolerance means that the system will continue to work despite node failures.
Only two of these 3 requirements can be fulfilled at any given time. If you're building a distributed app then partition tolerance is a must. So the choice remains whether we want our database to be highly available or highly consistent.
If your data needs to be structured or there is a relation between different types of data that you're going to keep and at the same time you want strict data integrity then a relational database should be a better choice for you.
For example, let's say we are making a student management application where we need to store the information of certain courses and every course needs to be taken by one or more students. In this case, we can create 2 tables (Students and Courses) where the value in the Student ID column inside the Courses table points to rows in the Students table by the value of their ID column.
Apart from this if you want your DB to be ACID compliant for example in cases when you're handling payments and transactions in your application then in that case too you should prefer the SQL based databases because the counterpart i.e the NoSQL database offers weak consistency.
ACID compliance protects the integrity of your data by defining exactly what a transaction is and how it interacts with your database. It avoids database tables from becoming out-of-sync, which is super important for financial transactions. ACID compliance guarantees the validity of transactions even in failures, disastrous events, and more.
On the other hand, if your data requirements aren’t clear or if your data is unstructured, NoSQL may be your best choice.
The data you store in a NoSQL database does not need a predefined schema. This provides much more flexibility and less upfront planning when managing your database.
A NoSQL database is a much better fit to store data like article content, social media posts, sensor data, and other types of unstructured data that won’t fit neatly into a table. NoSQL databases were built with flexibility and scalability in mind and follow the BASE consistency model, which means:
This means that while the database guarantees the availability of the data, the database may fail to obtain the requested data, or the data may be in a changing or inconsistent state.
The state of the database can be changing over time.
The database will eventually become consistent, and data will propagate everywhere at some point in the future.
The structure of your data is the most important factor in deciding whether to use a SQL or NoSQL database, so put a lot of thought into this before making a decision.
The next factor to consider is how you’ll query your data. This is one of the main ways to find the best database for your use case
Do you need retrieval by a single key, or by various other parameters? Do you also need a fuzzy search on the data?
Use Non-Relational databases if you are going to fetch data by key, then all you need is a key-value store (e.g. DynamoDB).
On the other hand, if you will require to query many different fields you can choose both Relational DB (e.g.MySQL) or Document DB (e.g.MongoDB).
In case you are looking for fuzzy search query capabilities (free text search), then search engines like Elasticsearch(Which also comes under NoSQL DBs) are the best fit.
But in case your data is nicely structured and organized, it is very efficient to query your data with a SQL database.
SQL is a popular query language that has been around for over 50 years now, so it’s highly mature and well-known. It efficiently executes queries and retrieves using JOINs and edits data quickly. It’s very lightweight and declarative.
Is strong consistency required (read after write) or eventual consistency is OK?
In case you need to read your data right after your write operation (i.e. strong consistency) then a Relational database (e.g. MySQL) is usually more suited than a Non-Relational Database (e.g.MongoDB).
All databases' performance degrades as the amount of read/write traffic increases. This is the time when optimizations such as indexing your data and scaling your DB come into the picture.
Performance depends on various factors but the overall performance depends to a very large degree on choosing the right implementation for your use case.
SQL and NoSQL databases scale differently, so you’ll have to think about how your data set will grow in the future.
SQL databases scale vertically, meaning you’ll need to increase the capacity of a single server (increasing CPU, RAM, or SSD) to scale your database. SQL databases were designed to maintain the integrity of the data so they are best to run on a single server, hence they’re not easy to scale.
NoSQL databases are easy to scale horizontally, which means you can add more servers as your data grows. This is an advantage that NoSQL has over SQL.
This uncomplicated horizontal scaling nature of non-relational databases makes them superior to relational databases as far as availability is concerned.
The ability of NoSQL databases to scale horizontally has to do with the lack of structure of the data. Because NoSQL requires much less structure than SQL, each stored object is pretty much self-contained. Thus objects can be easily stored on multiple servers without having to be linked. This is not the case for SQL, where each table row and column needs to be related.
This is also the issue regarding performance and where SQL falls short is scaling. As a database grows in size and numbers, RDBMS as we have seen requires solutions in vertical scaling. This however comes at a significant cost. Although many commercial RDBMS products offer horizontal scaling, these can also be very expensive and even complex to implement.
If you predict you will face such an issue, then NoSQL is to be considered, as many of them were designed specifically to tackle these scale and performance issues.
In this article, we went through the multiple factors you should consider while selecting the type of database. In short, SQL databases provide great benefits for transactional data whose structure doesn’t change frequently (or at all) and where data integrity is paramount. It’s also best for fast analytical queries. NoSQL databases provide more flexibility and scalability, which makes them useful for rapid or iteration development.
Here's a small summary of what all we have discussed above:
I hope this helps you understand what you need to think about when selecting your database. What additional questions do you think about while selecting a database? Let me know your thoughts in the comments.
