RDBMS vs NoSQL

Relational Database Management System (RDBMS)

RDBMS is a type of database management system that stores data in a structured format, using rows and columns.

Characteristics:

• Structured Data: Data is stored in tables with rows and columns.
• Schema: A predefined schema defines the structure of the data.
• ACID Properties: Transactions are processed following the ACID properties (Atomicity, Consistency, Isolation, Durability).

Examples: MySQL, PostgreSQL, SQLite, Oracle, MS SQL Server

NoSQL Database

NoSQL is a type of database that stores and retrieves data in a non-tabular format. It is designed to handle large volumes of unstructured data.

Data in NoSQL databases can be stored in various formats like key-value pairs, documents, or graphs.

Characteristics:

• Unstructured Data: Data is stored in a flexible format like JSON, key-value pairs, or documents.
• Schema-less: No predefined schema is required.
• Scalability: NoSQL databases are designed to scale horizontally.

Examples: MongoDB, Cassandra, Redis, DynamoDB, Couchbase

Differences

Not every NoSql database is not ACID compliant, but most of them are not. MongoDB introduced multi-document transactions in version 4.0.

What is ACID?

ACID is a set of properties that guarantee that database transactions are processed reliably. It stands for:

• Atomicity: All operations in a transaction are completed successfully, or none of them are.
• Consistency: The database remains in a consistent state before and after the transaction.
• Isolation: Transactions are processed independently without interference, handling concurrent transactions.
• Durability: Once a transaction is committed, the changes are permanent and survive system failures (e.g. outage, crash).

CAP Theorem

CAP Theorem states that in a distributed system, it is impossible to guarantee all three of the following:

• Consistency: All nodes see the same data at the same time.
• Availability: Every request receives a response, without guarantee of the data being the latest.
• Partition Tolerance: The system continues to operate despite network partitions (failures, delays).

NoSQL databases are designed to be AP (Availability and Partition Tolerance) systems following Eventual Consistency (BASE), while RDBMS focuses on CP (Consistency and Partition Tolerance) following ACID.

No distributed system is safe from network failures, thus network partitioning generally has to be tolerated.

ACID vs BASE

• ACID: Guarantees consistency and reliability of transactions.
• BASE: Focuses on availability, soft state, and eventual consistency.
  • Basically Available: The system remains operational even in case of failures.
  • Soft State: The system can be in an inconsistent state during updates.
  • Eventual Consistency: The system will become consistent over time.

When to use RDBMS?

• When the data is structured and requires a predefined schema.
• When the application requires complex queries and transactions.
• When the data is relational and requires ACID properties.

When to use NoSQL?

• When the data is unstructured and schema-less.
• When the application requires horizontal scalability.
• When the data is document-oriented or key-value pairs.