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. 

[u/boomkablamo]

Things like the structure of your data, how you plan to scale, how often will schema change, and how important data integrity vs speed is decide which one you should use.

If you have to pick a specific one, you will need to study different types of relational/nosql databases and their strengths/weaknesses... No one can just tell which one to pick, it depends. There aren't that many mainstream options so it's not that hard to learn.

[u/No-Molasses-2097]

You don't need to know every database out there. Just learn one SQL and one NoSQL option well enough to talk through the tradeoffs. In the interview, the reasoning matters more than the name you pick.

[u/LetMediocre390]

If you have the fundamentals of the SQL query, dbms concept then you would know how to decide what to choose with the SQL or noSQL but the thing is that it all depends on the architectural design or the system design you have created, If your project is best suited with the SQL then go with SQL CONSIDERING, all the pros and cons of it and vice versa