SQL vs NoSQL Databases

SQL Database is also known as a relational database. It has the following properties.

  • It uses a table or relation to store the data. Table stores data in row and columns.

  • Relational databases are used to store structured data. Data are stored in the form of relationships.

  • Uses Structured Query Language ( SQL) for defining and manipulating data.

  • Define the schema before you add any data to the system. All your future data must follow the same structure.

  • Relational databases are mostly vertically scaled. You need to add more memory, CPU or storage to the same server to process more data.

  • SQL Databases follows ACID consistency model (Strong consistency or write consistency).

  • Most suitable for a system where data is structured and data format is known e.g Transactional System, Auditing System etc.

  • SQL databases are mostly closed source and require licensing.

  • Most popular SQL databases are, MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

NoSQL (Not only SQL) databases have the following properties.

  • Data is stored in different formats like key-value pair, document, graph, column etc.

  • NoSQL databases are used to store unstructured data.

  • No standard language for defining and manipulating data. Different NoSQL databases use different syntax.

  • No need to define the schema. Add fields as you go.

  • NoSQL databases are horizontally scalable. To scale the database, need to add more cheap commodity servers.

  • Most NoSQL databases provides BASE consistency model (Eventual consistency). Chooses availability over consistency.

  • Most suitable for the use cases where data is unstructured and the format is unknown e.g Social media, Real-time streaming systems etc.

  • NoSQL databases are mostly open source and do not require licensing.

  • Most popular NoSQL databases are Cassandra, Riak, Dynamo, MonoDB etc.

ACID and BASE are the two most common consistency models which are followed by the databases. Let’s discuss them in detail.

ACID Consistency Model Link to heading

ACID stands for Atomic, Consistent, Isolation and Durable.

Atomic ( A ) :- A transaction consists of many operations. All operation in a transaction succeeds or every operation is rolled back.

Consistent ( C ): After a transaction completes database must be in a consistent state. A transaction can only bring the database from one consistent state to another.

Isolation ( I ): There can be many transactions in the system executing concurrently but each transaction execution is isolated from another and looks like they were executed in sequential order.

Durable ( D ): The result of applying transaction is permanent, even in the presence of failures.

ACID properties ensure that once a transaction is complete, It’s data is consistent and stable on the disk. It follows a strong consistency model.

BASE Consistency Model Link to heading

BASE stands for BA (Basic Availability), S (Soft-State) and E (Eventual Consistency). ACID is less fashionable in the NoSQL world. BASE provides eventual consistency over immediate consistency for scalability, availability, and resiliency.

Basic Availability (BA) : The database is available most of the time.

Soft-State ( S ): Stores don’t have to write consistent. No different replicas have to be mutually consistent all the time.

Eventual Consistency ( E ): Once a write completes and there is no write after that then all reads after the write will receive last updated value.

Databases with BASE consistency model ( NoSQL databases) prefers availability over the consistency of replicated data at write time. The BASE is less strict than ACID. Data will be consistent in the future either at the read time or it will be always consistent.

Types of NoSQL Database Link to heading

Typically NoSQL databases fall into four categories:

  1. Key-Value Store: Every item in the database is stored as an attribute name ( or “key”) together with its value. Redis, AWS DynamoDB, Riak are the most popular databases in this category.
  2. Wide-Column Store: These kinds of NoSQL databases stores data together as columns instead of rows and are optimized for queries over large datasets. The most popular are Cassandra and HBase.
  3. Document Store: Pairs each key with a complex data structure known as a document. A document can contain different key-value pairs or even nested documents. MongoDB is the most popular document store database.
  4. Graph Store: Are use to store info about network such as a social network. Neo4J and HypergraphDB are popular ones.