SQL v/s NoSQL Databases

SQL v/s NoSQL Databases | We will see the major differences between SQL and NoSQL databases. Also see:- Spring Boot with MongoDB Basic Example

SQL	NoSQL
For every operation, we should define one SQL query.	Use Commands/functions to do any operation (SQL query does not exist).
It is called a structured database because first execute the DDL queries than other queries like DML. This means create/alter tables then only we can insert data or modify.	It is called an unstructured database. There is no need to create/alter any schema. Directly perform insert/update/delete/fetch.
It supports different PL/SQL concepts.	No such concepts, store/retrieve/modify etc.
Stores data as tables # rows.	Stores data as a Collections # Json documents.
Supports vertical scaling only.	Supports both horizontal and vertical scaling.
Supports any programming API as a client for web apps. But no reactive operations.	Supports web with fully reactive API even.

Can we insert a row in the SQL database without creating a table? No. First, we should design schema (overall tables with relation) then we can perform CRUD Operations. We need to modify the schema for additional data insert.SQL DBs entirely depend on schema ie called Structured Database.

In NoSQL databases like MongoDB such creation is not required to directly insert, it auto-creates its schema (Unstructured Database).

SQL DB: emptab(table) example:-
+------+------+------+
| eid | ename| esal |
+------+------+------+
| 10 | A | 2.2 |
| 11 | B | 2.3 |
| 12 | C | 2.4 |
+------+------+------+

NoSQL Employee (Collection) example:-
[
   {"eid":10,"ename":"A","esal":2.2},
   {"eid":11,"ename":"B","esal":2.3},
   {"eid":12,"ename":"C","esal":2.4},
]

Scaling of SQL vs NoSQL database

Scaling is a process of increasing service capacity for a System/Application.

There are two types of scaling:-

• Horizontal scaling:- Create the same instance multiple times. Assume previously the application was running on 1 server, and later for horizontal scaling we ran the same application at multiple servers.
• Vertical scaling:- Increase system hardware configuration. Assume previously the server has 10 GB RAM, 500 TB HD, and 128 Core CPU. Now for vertical scaling, we upgraded the system to 25 GB RAM, 1000 TB HD, and 256 Core CPU.

In real-time, Horizontal and Vertical scaling is done parallelly.

In the case of SQL databases, only vertical scaling is possible. When database data is getting increased then we have to increase the system capacity. We can’t break the tables because there will joining of two tables. We can’t place one table in one server and another table in another server because of joins.

However, the NoSQL database supports both Horizontal and Vertical scaling. We can have multiple servers, and we can store a few collections in one server and others in different servers. Direct joints are not applied here only ID storage will be there.

For NoSQL, we can manage multiple database instances in Microservices. Example:- OrderDatabase, ProductsDatabase, UserDatabase.

In microservices, there will be independent projects and each project can have its own NoSQL database.

Reactive Operations

Let us first see the Server Thread Model.

• The server creates one object for a servlet (singleton class).
• The server creates one Servlet thread for the Servlet object that will process requests and give responses.
• If any I/O (Database call exists) then the Thread will be in a WAITING (Blocked) state, until data comes back to the server then it will be unblocked.
• Converting the Blocking-Unblocking process to better resource utilization using mediator ‘EventLoop’, in that case, DB and threads will become independent and process parallel requests, such process is supported by NoSQL databases only.

Whenever the browser requests the server, then the server will create a request thread i.e. servlet thread. Now, the server makes a database call then the thread will be blocked till data comes back to the server. During the blocking/waiting period that thread won’t perform any other request. Whatever web application or web services we develop comes under this category. It is the behavior of the default web/web services application. It is also called a non-reactive operation. We can see more about it in Spring Web flux (Reactive web).

The asynchronous concept comes under a reactive process between the browser and the server. Whereas reactive/non-reactive process comes under server and database.

In the reactive process, instead of waiting/blocking the thread, the thread continues processing the other request and when the data comes back to the server from the database then the same thread or different thread (which is free) can be allocated for that. So, there will be no waiting. Keeping all threads busy for better resource utilization is called a reactive process which uses a mediator Event Loop mechanism. To implement reactive both database and web application should support the reactive operation.

A reactive database means it supports the Event Loop mechanism and it never connects to a direct thread. Reactive database example:- MongoDB. SQL database does not support reactive operations.

We do not write instance variables inside the servlet class because it is a singleton-multithread design and gives problems with Data Synchronization.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search