SQL vs NoSQL vs NewSQL

INTRODUCTION:

SQL Databases also known as RDBMS (Relational Database Management Systems) is the most common and traditional approach to database solutions. The data is stored in a structured way in form of tables or Relations. With advent of Big Data however, the structured approach falls short to serve the needs of Big Data systems which are primarily unstructured in nature. Increasing capacity of SQL although allows huge amount of data to be managed, it does not really count as a solution to Big Data needs, which expects fast response and quick scalability.

To solve this problem a new kind of Database system called NoSQL was introduced to provide the scalability and unstructured platform for Big Data applications. NoSQL stands for Not Only SQL. NoSQL databases consist of key value pair, Documents, graph databases or wide – column stores which do not have a standard schema which it needs to follow. It is also horizontally Scalable as opposed to vertical scaling in RDBMS.

NoSQL provided great promises to be a perfect database system for Big Data applications; it however falls short because of some major drawbacks like NoSQL does not guarantee ACID properties (Atomicity, Consistency, Isolation and Durability) of SQL systems. It is also not compatible with earlier versions of database. This is where NewSQL comes into picture. NewSQL is a latest development in the world of database systems. NewSQL is a Relational Database with the scalability properties of NoSQL. This paper discusses each of these database systems and tries to find the ideal solution for Big Data requirements.

What is SQL Relational Database?

The term SQL is both a language and the type of database. SQL stood for structured query language and was the pioneer of database design philosophy. Since the mid-’80s, SQL has been a standard for managing and querying relational datasets; however, the early beginnings of the relational model can be dated back to the ’60s and ’70s where the urgent need to distinguish between the application data and application code emerged, allowing the developers to focus on other aspects of the program development such as access to and manipulation of data at hand. IBM’s IMS was the first fully functional relational database, albeit designed for a different purpose, to organize data for the Apollo space exploration program. The relational database is a collection of time-varying, normalized relations of assorted degrees.

What are the Concepts of SQL?

ACID

Atomicity, Consistency, Isolation, Durability to maintain the reliability of transactions.

1.    Atomicity – completion of the transaction as a whole or none at all

2.    Consistency – assures the stable state of the database with or without changes

3.    Isolation – multiple transactions do not interfere with each other

4.    Durability – permanent effect on the database by the changes.

Normalization

A process of designing efficient databases

1NF – Split the table by separating repeating and nonrepeating attributes. All domains are simple, and all elements are atomic

2NF – Remove partial dependency between attributes. No attribute should be functionally dependent on one part of an aggregated primary key.

3NF – Remove transitive dependency between table attributes. No prime attribute is functionally dependent on the non-prime attribute.

Scalability

The capability of the database to handle growing amounts of data. Vertical scaling helps to enhance the existing capacity of the database server. Most SQL databases support vertical scaling. They can, however, scale-up, not scale out.

Domains

A domain is a named set of scalar values, all of the same type. They help to impose semantic constraints. Rely on traditional features and utilizes a defined data schema. Support JOIN functionality, engineered for data integrity

Why SQL was not enough?

As data volume has grown over the past few decades, SQL databases have struggled with particular use cases driven by big data requirements. Some of the significant reasons were listed below.

• Explosion of social media sites with large data needs.

• Rise of cloud-based solutions.

• Shift to dynamically-typed data with frequent schema changes.

• Expansion of Open-source community.

• Handle big data.

 What is NoSQL?

These problems became the origin of the impetus for the NoSQL movement in the mid to late 2000s. The key working strategy is to forgo strong transactional guarantees and relational models of DBMS in favor of eventual consistency and alternative data models like key-value pairs and graphs. This was done following the belief that these aspects of existing DBMS inhibit their ability to scale out and achieve the high availability needed to support web apps on the go. The two most well-known systems that followed the creed were Google’s Big Table and Amazon’s Dynamo and were restricted to use inside their own organizations only, leading to organizations creating their own open-source clones like Facebook’s Cassandra and powerset’s HBase. By the end of the 2000s, there was a diverse set of scalable and affordable DBMS.

NoSQL database simply stands for “Not Only SQL” or “Not SQL. The original NoSQL systems were built for scale, unstructured data, and did not use relational (table-based) schema.

• Key-value databases store pairs of unique keys and associated values. Ex:- Redis

• Document databases store data in documents-like structure encoded in formats such as JSON and XML. Ex:- MongoDB

• Wide-column databases store data in tables that contain large numbers of columns (in a flexible schema). Ex:- Cassandra

• Graph databases store data in graph form to highlight the connections between different data elements. Ex:- Neo4j.

 

What are the Concepts of NoSQL?

Lack of schema

Support for structured, semi-structured, and unstructured data. No specific schema requires to define before entering data into NoSQL databases. New fields can be added and also supports nested data implementation and retrieval. Developers can use data type and query options requisite for the specific application resulting in faster development. Faster Development time is considerably reduced due to no complex SQL queries or join statements.

Auto Balancing

Division of data among multiple servers automatically, with no assistance required from applications.

Integrated Caching

NoSQL database cache data in system memory to increase data throughput and increase the performance in advance. High scalability, reliability with a simple data model and simple query language.

The BASE Principle for Transaction

The base is to NoSQL as what ACID is to SQL. It ensures that NoSQL databases ensure their reliability despite the loss of consistency. The base stands for Basically Available Soft-state, eventually consistent. Eventually consistent – System can become eventually consistent; information is updated wherever necessary.

 

Why NoSQL also was not enough?

• NoSQL databases have limited query capabilities as compared to RDBMS.

• NoSQL databases don’t offer any RDBMS capabilities like consistency and ACID transactions.

• Most of the NoSQL databases use key-value pairs to store the data. Hence it isn’t easy to maintain as the volume increases.

• NoSQL databases are new to the markets and can be challenging for RDBMS programmers to switch to these technologies.

• Most of the NoSQL databases are open source and are a restricted choice for enterprises.

What is NewSQL?

NewSQL, is a class of modern relational DBMS that provides the same scalable performance of NoSQL systems for online workloads but also provides ACID so as to not sacrifice data consistency. The term was first used by 451 Research in a 2011 research paper discussing the rise of new database systems as challengers to established vendors.

Basically, a NewSQL DBMS is engineered as a relational, SQL database system with a distributed, fault-tolerant architecture. Other typical features of NewSQL offerings include in-memory capability and clustered database services with the ability to be deployed in the cloud.

The general use case for NewSQL is to support enterprise systems that must process large amounts of data (big data) while maintaining transactional consistency and are, therefore, not practical for the eventual consistency of NoSQL database systems.

 

Concepts of NewSQL?

• Main Memory storage of OLTP databases enables in-memory computations of databases.

• Scaling out by splitting a database into disjoint subsets called either partitions or shards, leading to the execution of a query into multiple partitions and then combining them into a single result.

• NewSQL systems preserve the ACID properties of databases.

• Enhanced concurrency control system benefits traditional ones.

• Presence of a secondary index allowing NewSQL to support faster query processing times.

• High availability and Strong data durability only possible with the use of replication mechanisms.

• Configure NewSQL systems to provide synchronous updates of data over the WAN.

• Minimizes Downtime, provides fault tolerance with its crash recovery mechanism.

 

Difference Between SQL, NoSQL, and NewSQL?

 

Feature	SQL	NoSQL	NewSQL
Relational Property	Yes, it follows relational modelling to a large extent.	No, it doesn't follow a relational model. It was designed to be entirely different from that.	Yes, since the relational model is equally essential for real-time analytics.
ACID	Yes, ACID properties are fundamental to their application	No, rather provides for CAP support	Yes, Acid properties are taken care of.
SQL	Support for SQL	No support for old SQL	Yes, proper support and even enhanced functionalities for Old SQL
OLTP	Inefficient for OLTP databases.	It supports such databases, but it is not the best suited.	Fully functionally supports OLTP databases and is highly efficient
Scaling	Vertical scaling	Only Vertical scaling	Vertical + Horizontal scaling
Query Handling	Can handle simple queries with ease and fails when they get complex in nature	Better than SQL for processing complex queries	Highly efficient in processing complex queries and smaller queries.
Distributed Databases	No	Yes	Yes

Which is the Best Solution: SQL or NoSQL or NewSQL?

After reviewing the basics of each type of database system, Now I know that you expect me to answer this question… But the choice is totally up to the requirement that you have.

It makes sense to use SQL database systems as the baseline for most general-purpose requirements. There is a reason the SQL DBMS is as popular and entrenched as it is: It works very well for many of the most common use cases.

For applications with extreme availability and scalability needs or with unknown or changing data requirements where a flexible schema would be useful, consider a NoSQL database system. But take heed of the potential for data consistency issues and realize that NoSQL is a term that describes four different types of database systems, each with different use cases and architectures.

Finally, consider a NewSQL database system for applications that have expanded past the scalability capability of traditional SQL. Using NewSQL enables improved scalability with the strong transaction consistency of ACID, while still supporting data access using the familiar SQL.

 

Conclusion:

SQL provides vertical scaling with ACID properties while NoSQL is suitable for horizontal scaling providing BASE. However, NoSQL does not provide ACID properties which are necessary for a reliable database. The need of modern enterprises where data is growing day by day and all they work with is Big Data especially even while working in OLTP system, NewSQL is the best choice. NewSQL is enhancement of SQL providing horizontal scaling while maintaining ACID properties. This not only allows working with Big Data by providing the ability to work concurrently, it also maintains ACID properties. NewSQL has found the sweet spot between consistency, scalability, speed and availability. While still being in its infant stage, NewSQL ticks all the right boxes to make it an ideal database for Big Data OLTP applications.

 

References:

1.    “SQL vs. NoSQL vs. NewSQL- A Comparative Study” by Sneha Binani, Ajinkya Gutti ,Shivam Upadhyay, Volume 6 – No.1, October 2016.

2.  “Study on SQL vs. NoSQL vs. NewSQL” by Dr. Alshafie Gafaar Mhmoud Mohmmed and Prof. Saife Eldin Fatoh Osman, Vol. 3 Issue 6, June – 2017.