SQL vs NoSQL Databases - tarunchhabra/parakalo GitHub Wiki
**# No SQL- **
NoSQL db has limited form of transactions and less general querying capability. Advantage- More flexible data model, better performance, and scalability. Good Choice for CQRS views
DisAdvantage - the NoSQL model, with the lack of a schema, transactions and weaker consistency guarantees proved to be harder to program against that the SQL model.
Note: 1) MongoDB now offers distributed transactions. https://www.mongodb.com/blog/post/announcing-the-mongodb-global-transactions-beta-program https://docs.mongodb.com/manual/core/transactions/
MongoDB is a popular NoSQL database management system that stores data as BSON (binary JSON) documents. MongoDB is a CP data store—it resolves network partitions by maintaining consistency, while compromising on availability. For situations that require atomicity of reads and writes to multiple documents (in a single or multiple collections), MongoDB supports multi-document transactions. With distributed transactions, transactions can be used across multiple operations, collections, databases, documents, and shards.
- MySQL has excellent support for json.
https://www.mongodb.com/compare/mongodb-mysql
-
Performance - NoSQL database can handle more requests by routing them to different shards.
-
the rise of “scalable” distributed SQL databases like Cloud Spanner and AWS Aurora, in the public cloud, and open source alternatives like CockroachDB, which address most of the underlying technical reasons why traditional SQL databases “didn’t scale”.
https://aws.amazon.com/blogs/database/scaling-your-amazon-rds-instance-vertically-and-horizontally/ https://aws.amazon.com/about-aws/whats-new/2019/06/rds-storage-auto-scaling/ https://aws.amazon.com/blogs/database/sharding-with-amazon-relational-database-service/ https://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/
-
No SQL database offers very flexible data model.
Data is stored as documents or items. For example, in AWS DynamoDB, you can define a table with a partition key and sort key. The rest of attributes are inserted/updated during runtime in a relaxed manner.
- Most NoSQL only offer eventual consistency, or some support strong consistency but offer eventual consistency as default.
3)### Vertical Scalability vs Horizontal Scalability Cost of scaling SQL database horizontally is very high due to the complexity of managing ACID compilance in massive number of nodes. Hence, most SQL databases scale vertically. That means it requires to upgrade hardware for a single node. That kind of scalabity is much higher comparing to horizontal scalability. Horizontal scalability relies on many low-cost commodity computers to perform intense workload. Most NoSQL can expand horizontally to get better performance without/with very little nodes management cost. NoSQL database can handle more requests by routing them to different shards.
4)### Query SQL database has standard query language. In contrast, the NoSQL databases have its own query language. So migrating a SQL database from one vendor to another vendor may not need to change anything at the application level. While changing NoSQL database from one to another may requires some code change
First, the NoSQL model, with the lack of a schema, transactions and weaker consistency guarantees proved to be harder to program against that the SQL model.
In a blog post titled “Why you should pick strong consistency, whenever possible”, Google states:
One of the things we’ve learned here at Google is that application code is simpler and development schedules are shorter when developers can rely on underlying data stores to handle complex transaction processing and keeping data ordered. To quote the original Spanner paper, “we believe it is better to have application programmers deal with performance problems due to overuse of transactions as bottlenecks arise, rather than always coding around the lack of transactions.”
The second reason is due to the rise of “scalable” distributed SQL databases like Cloud Spanner and AWS Aurora, in the public cloud, and open source alternatives like CockroachDB, which address most of the underlying technical reasons why traditional SQL databases “didn’t scale”. Even MongoDB, which was the poster child of the “NoSQL” movement, now offers distributed transactions.
For situations that require atomicity of reads and writes to multiple documents (in a single or multiple collections), MongoDB supports multi-document transactions. With distributed transactions, transactions can be used across multiple operations, collections, databases, documents, and shards.
https://cloud.google.com/blog/products/gcp/why-you-should-pick-strong-consistency-whenever-possible
https://kylelix7.github.io/SQL-(Relational-Database)-or-NoSQL-Ace-the-FAANG-System-Design-Interview/
https://medium.com/@copyconstruct/a-decade-in-review-in-tech-1cde76c9b43c