My neighbour asked me today:
SQL was built on integrity and consistency, and it scales up. NoSQL is designed for geographical distribution and speed, and it scales out. What I don’t get is why don’t we always use NoSQL for everything? When the traditional SQL db is more beneficial ?
Typical SQL database is designed to store relational data. The design, for each transaction, follows ACID principle which means:
- A – Atomic
- C – Consistent
- I – Integral
- D – Durable
So each operation, once saved to storage, remains “forever” and if you have multiple instances, the data on each node will be consistent and integral.
On the opposite side, we have NoSQL which stores non-relational data like key-value pairs, trees, documents etc (there is some various kinds). The design is a bit different – it follows CAP theorem so you need to choose 2 of 3:
- C – Consistency
- A – Availability
- P – Partition tolerance
Moreover thanks to its nature, NoSQL DBs are low latency even tough they have thousands of TBs of data. However efficient usage of NoSQL requires good setup of partition keys. Otherwise some of read operations might be resource consuming and will cause high delays.
If we look into market, most of software developers is familiar with SQL concept and SQL databases, just few understand NoSQL really good. However it changes as more and more projects uses NoSQL (the most often for cache and log storage). Due to the nature of NoSQL (partitioning), it requires more resources (nodes) to run the system than typical SQL database (which typicaly consumes high memory).
In modern solutions, cross-regional data distribution is not a problem (since replication become asynchronous comparing to historical solutions which required synchronous replication).
Conclusion
When you work on really high data volumes, which has to be available with low latency and also the data are not in relational form, NoSQL might be good choice. With relational data, typically stored in most projects, you would rather pick SQL, however most of modern solutions use combination of them.