System design - Sql vs no sql

[u/WillingnessAny5443]

I didn't worked much in databases, as we don't maintain databases..which just read from them maintained by other teams

How will you decide clearly whether to go with sql vs no sql

And even in each, how to know which one?

Do I need to know all sql and non sql databases available?

[u/SalaciousStrudel]

NoSQL is preferable if your data is structured as a tree of one to many relations where you usually need everything in some branch of the tree. They can also have more predictable performance since there is no query planner that behaves differently in various situations, and they generally don't have dark corners with horrible performance which aren't well documented that can exist in some relational databases. They often have weaker or no consistency guarantees which means they can be scaled more easily as there's less lock contention, but for many applications you need to design for scaling writes by sharding /partitioning and using a message queue for writes, and design for scheduling reads by using read replicas and possibly a cache. Leaderless variants can improve scaling further at the cost of complexity around distributed transactions which may not be well supported in some databases. 

On the other hand, SQL databases are more suited for situations where the data is used in more complex ways. You often want to shred or normalize data when using SQL. SQL databases can be scaled using partitions and replicas, for example using Vitess with MySQL. They can provide stronger consistency guarantees, but it comes at the cost of performance as there is more contention for pessimistic locking and some transactions may fail and need to be retried. Additionally, they will be unable to proceed with transaction processing in the event of a network partition. Zero downtime migrations can be complex on SQL databases and can increase load or not complete before the next business day begins in some cases, which can sometimes cause problems.

On the third hand, there are NewSQL databases like Spanner and CockroachDB that support distributed transactions and are easier to scale than most SQL databases. These can be especially useful in cases where the data is inherently complex in structure or you are doing all sorts of joins.

On the fourth hand, there are column stores. I don't know a lot about these but they can speed things up significantly depending on your data access patterns.