Horizontal scaling is cheaper (horizontal scaling is hard for SQL)
Both vertical and horizontal scaling is possible for NoSQL
Vertical scaling is limited by technology- It is limited by technology and hardware that’s available
Joins and Relationships
SQL provide better support for joins, many-to-one and many-to-many relationships
NoSQL has Schema flexibility, Better performance due to locality (Joins not needed to fetch data)
For highly interconnected data, Document DB is not good, Relational DB is good and Graph DB is best
Performance
NoSQL is generally faster:
NoSQL is not ACID - It was designed to be faster by forgoing one of the ACID properties
NoSQL stores commonly accessed data together - They have better performance on reads/writes on a single data entity.
SQL schemas validate data changes - hence NoSQl is faster
SQL databases are normalized - Data is broken down and stored in tables. As such, it is faster than NoSQL for complex queries like joins. “Joins” in NoSQL are done by retrieving multiple data entities and joining them “manually”.
Indexes are more efficient for SQL - Indexes work by duplicating existing data and efficiently storing them in the RAM. They work better for structured data and thus, SQL benefits more from it.
Security
SQL is better:
SQL guarantees ACID transactions
NoSQL has minimal in-built security features
Horizontal scaling has higher availability - There is no single point of failure unlike vertical scaling
When to use SQL
Working with complex queries - NoSQL is less robust and has limited query functionality
ACID transactions are important e.g. financial or accounting transactions
Data is highly structured and does not change frequently - Predefined schemas allow for easy navigation
Example Use cases
Banking account’s transaction history
Text Messaging System
Ecommerce transactions
When to use NoSQL
Unstructured data - different data entities can have different structure
Faster development speed - No need to design database schema
Priority is easy scalability and availability - Vertical scaling is expected to be too expensive
Priority is not data consistency and integrity - NoSQL is not ACID compliant
Example Use cases
Social media posts don’t have to appear in the same order for every user
Storing product views of millions of shoppers
Storing items in users’ shopping cart
Fraud detection - Detection algorithm rules require a huge amount of data from various sources
Personalization and recommendation engine - Huge amount of unstructured data
Market Basket Analysis e.g. study of purchases done by a customer in a supermarket