Nosql vs sql

Cisco
BlindBss

Go to company page Cisco

BlindBss
Apr 10 13 Comments

Looking for some advice here.

I am trying it really hard to understand what it take to use nosql vs SQL one. I went through few youTube videos and sys design book some are using nosql and some SQL..but no one seems to really provide (was not able to understand myself mostly) clarity around it other than some basic things like if relation then use SQL else use nosql.

I am particularly confused around when we have MULTIPLE table with key and fkey then how sharding. Consistent hashing works if SQL is choice.


Do we ensure both table stays on same node on the consistent hash ring?

For example taking Facebook newsfeed design,
SQL way:
Post table
- postid key
- userid fkey to user table uuid
- post text
-postmediaid
-...

MediaTable
-id
-location // some place where it's stored

UserTable:
-uuid
- name
-....

FriendsGraph: only keys with pair of me and my friend as key
Have no fkey in the postTable (user id and posiid values are duplicated for reference when rendering/preparing response.
Now with that both postTable has no relation and can scale without worrying about having mediaTable on separate node on consistent hash ring.

In case of SQL shard
Do we ensure both tables(relation) stays on same node on the consistent hash ring?

Please provide reference which can help understand how we pick here SQL/nksql with scaling and sharding in mind.

Blind tax: 390k

comments

Want to comment? LOG IN or SIGN UP
TOP 13 Comments
  • Google
    UWOM24

    Go to company page Google

    UWOM24
    Spanner interleaves the related tables, so related fkey records are colocated in the same shard. Google for Spanner interleaved table maybe.
    Apr 10 0
  • Robinhood / Eng
    STOCKD0WN

    Go to company page Robinhood Eng

    PRE
    Microsoft
    STOCKD0WN
    If you need ACID. (Financial or medical data mostly)

    Then you really need SQL + ACID turned on.

    If you don’t need ACID then it really comes down to scale. Only at large scales will noSQL be beneficial to you.

    Everything in between:

    You can argue to use either one. Both solutions have relations(this is a stupid argument… all data has relations and can be modeled in sql or no sql)

    NoSQL is actually not more flexible than SQL. When you design NoSQL databases you design for access patterns and if you add new access patterns you might has to rethink everything. (Sure there is no strict schema) but the fact that there are no joins makes it so that you have to do a lot of thinking beforehand where as in SQL you can fix or hide some mistakes by adding ugly ass queries.

    Additionally, the point of NOSQL is to use LESS compute and MORE storage to solve your problem. In 2022, compute is MUCH more expensive than storage. This is also another consideration.
    Apr 10 6
  • Overly simplistic answer:-
    Is your schema fixed?
    Use SQL
    Else nosql
    Apr 10 2
    • Robinhood / Eng
      STOCKD0WN

      Go to company page Robinhood Eng

      PRE
      Microsoft
      STOCKD0WN
      This is such a bad answer.

      There is this notion that noSQL is more flexible than sql which is actually not true.

      If you’ve ever actually used noSQL you have plan your primary and search keys very carefully to satisfy your access patterns.

      In reality, sql is actually more flexible if you need to change the schema of your data or your access patterns.

      You should watch amazon dynamo db tech talks.
      Apr 10
    • Yeah yeah, SQL database also allow to store json(variant) type columns,
      I did say it was overly simplified.
      Over all depends on usecase, queries which are going to be used, etx
      Apr 10
  • Cisco
    BlindBss

    Go to company page Cisco

    BlindBss
    OP
    Anything specific about keeping all tables on the same node?
    Apr 10 0
  • Amazon
    911s

    Go to company page Amazon

    911s
    Use sql if advanced queries are needed and the data is very structured and unlikely to change. Otherwise if you have a data team use nosql and pipe data to them that theyll aggregate across all data in the org
    Apr 10 0