I was literally consfused in gaining the clarity untill you came to the point where you transposed this theory into understanding through tables and the reference with SQL queries, thanks a lot to your efforts for this loving beautiful explaination Arpit sir
Excellent video Arpit . Coming from no software and system engineering background , this was the best video to explain data sharding and partioning . I am a Tech PM for AWS Supply Chain and data partitioning and sharding is real deal for us. Thank for making this extremely easy to understand video
2 Important points which i felt could be discussed more are 1) When you said the choice of partitioning depends on the load , usecase and access patterns , can you please give an example of each case ?? 2) When you were talking about the advantages and disadvantages of sharding , have you written these points considering only sharding and no partitioning or have you written considering both sharding and partitioning ??
I am just stunned how you describe every concept that easily and even if the concept is so complicated. I love you and your works man please continue doing it. I promise I will join as a member to this channel as soon as I become an earning developer. :D
Wow...really nice. Nowadays a lot of people are selling and talking about system design and always try to build some optimistic solution straight forward without going into the internals and in fact they have not even worked on a lot of systems. I strongly feel the way of your explanation is very very nice and I am going to buy your system design plan to improve mine.
This is excellent, high-quality content! I always had questions about sharding and partitioning, but now I understand: sharding pertains to the database server level, while partitioning is about organizing the data itself.
Very good explanation with right examples. Hats off to you. Thanks for great content. I always thought shard and partitions are same but you clarified it very well.
Partitioning can be done at table as well. So let's consider we have a table named X and it is having huge data so if we want to increase the performance and improve the latency we can partition the table into Y shards.
I looove this kind of explanation. i.e stepping back and discussing the scenarios behind why something came up to be. Thanks a lot for these videos man!
This is such a great and simple explanation of partitioning and sharding of a database. Would love to watch the video on partitioning strategies when it is uploaded.
Hello arpit, at 5:49, why you mentioned that the new resources are being allocated to the EC2 machine? I think that should be allocated to the DB server running on EC2 machine right?
In case of replica of data on sharded server, we have same data dupicated on the physical disks of the data server. How is the consistency of data maintained across data base servers ?
What should be the strategy of partitioning a mysql database with multiple tables into multiple shards? There are some tables which does not contains shard key.
1. Either it should contain the shard key 2. Or if it small enough then replicate the entire table across all the shards (eg. Config table, or meta table) 3. Or third bear the cost of cross shard fan outs.
One question, at 21.00 the matrix shows what it looks like when we have both sharding and partioning, how that is different from having two databases on two different EC2 instance for two applications?
Great video ! Nicely explained. :) But I have one query , towards the end of the video : The example you shared for "Sharding NO and partitioning YES" about Airline Check-in System and Ticket Booking System. How is that classified as partitioning if the databases are altogether different ? Any thoughts ?
@arpit Thanks for such dense information in so short and simple video. However I have a query on a corner case - How can have replicas when one has multiple shards with partitioning? - In this case is replication locally on the shard or it can also be replicated on other shards for high availability across avalability zone or DR (like kafka architecture)?
Hi arpit after sharding or partioning how does the request gets routed to the respective shards do you we need to write logic at application level to track shards or is there any other approach?
Thanks a lot Arpit for explaining in so simplistic way. One request can you please make video on Sharding strategies and also on how composite indexes stores in the disk.
@asli engineering - Bhai, any update on the sharding strategies. Also, one more request is examples of scenarios to explain shard key selection. How is the data replicated behind the scenes n stuff please ?
Partitioning allows your database to read/access/move the required subset of data easily and efficiently. 1. Imagine if you partition data by time and create one partition for every hour and someone queries how many events happened in the last 10 hours, you would just need to access last 10 partition to fulfil this query. Others are not even required to be read. 2. In a distributed setup, instead of moving individual rows/elements we can easily and efficiently move partitions across the cluster for balancing the load.
Great content, as always! I need some advice: My OKX wallet holds some USDT, and I have the seed phrase. (alarm fetch churn bridge exercise tape speak race clerk couch crater letter). How can I transfer them to Binance?
One shard also must be having replicas right? I mean if a shard is handling the first 2 partitions, then all data from those first 2 partitions will go to this shard, but what if the shard is down?
Hey great video Ankit! I was wondering how sorting and specially filtering would work across shards? Or is that an anti pattern ( just like cross shard joins )
that is your routing strategy - range based or hash based or static routing. for example, all request for a user goes to a particular database and the ownership is determined by taking hash of user id i.e. f(user_id)%num_databases
@@AsliEngineering how can this be implemented? Also if you can please can you make an system design with implementation video on microservices with like nodejs .. as i have understood the theoretical part but the implementation part is where am getting stuck and not understanding how to do that
@@the_angry_developer you can get user ID from your auth token. Have an array of database connections in your API code and apply the function mentioned above.
That depends on your routing strategy - Range/Hash/Static. In any case, you pick a partitioning key and depending on the approach you deduce which shard to go to.
Great video as always! 👍 I’ve got a question: 🤨 I have these words 🤨. (behave today finger ski upon boy assault summer exhaust beauty stereo over). What should I do with this? 🤷♂️
I have a doubt sir. I think increase in overall storage capacity and higher availability, doesn't got hand in hand. If we shard and partition data, then the capacity will be available but then the data is not same in both shards i.e., if one shard becomes offline then the other shard cannot provide data if the requested data is in the offline shard. So no higher availability. And if we only do sharding then the same data will be in both servers, and as u said if we assume 100tb, then the other will also have 100tb only? We cant get 200tb. This is what I thought. Correct me if im wrong.
I too got the same doubt, I came to know that in this case, our system will be available but the data might be inconsistent, so still the server accepts requests and provides an empty response.
I was literally consfused in gaining the clarity untill you came to the point where you transposed this theory into understanding through tables and the reference with SQL queries, thanks a lot to your efforts for this loving beautiful explaination Arpit sir
Thanks!
Excellent video Arpit . Coming from no software and system engineering background , this was the best video to explain data sharding and partioning . I am a Tech PM for AWS Supply Chain and data partitioning and sharding is real deal for us. Thank for making this extremely easy to understand video
2 Important points which i felt could be discussed more are 1) When you said the choice of partitioning depends on the load , usecase and access patterns , can you please give an example of each case ?? 2) When you were talking about the advantages and disadvantages of sharding , have you written these points considering only sharding and no partitioning or have you written considering both sharding and partitioning ??
I am just stunned how you describe every concept that easily and even if the concept is so complicated. I love you and your works man please continue doing it. I promise I will join as a member to this channel as soon as I become an earning developer. :D
Wow...really nice. Nowadays a lot of people are selling and talking about system design and always try to build some optimistic solution straight forward without going into the internals and in fact they have not even worked on a lot of systems. I strongly feel the way of your explanation is very very nice and I am going to buy your system design plan to improve mine.
Thanks. Looking forward to having you enrolled 🙌
Wonderfully explained! Cleared all my doubts. Please keep making such videos. These are also well timed, not too short nor too long.
This is excellent, high-quality content! I always had questions about sharding and partitioning, but now I understand: sharding pertains to the database server level, while partitioning is about organizing the data itself.
I have just started learning System Design and not from a backend background. Still able to understand the concepts. Thanks for creating such content.
You have a brilliant way to explain, only one who has gone through the journey would be able to teach it this way.
Very good explanation with right examples. Hats off to you. Thanks for great content. I always thought shard and partitions are same but you clarified it very well.
Thanks a lot ! That was well explained with clear and concise explanation. Looking forward to enrolling in your complete system design course.
Excellent video ❤. Finally, I got a good grasp of the whole concept.
One of the best videos explaining the nuances between partitioning and sharding. Thank you @ArpitBhayani
Partitioning can be done at table as well. So let's consider we have a table named X and it is having huge data so if we want to increase the performance and improve the latency we can partition the table into Y shards.
One video and all the clutter on Sharding and Partitioning is clear. Thank you so much Arpit.
When i finished watching this video , concepts looked super easy. Thanks
Great clarity and in depth explanation. Thank you
Amazing explanation! The diagrams were really helpful in helping me solidify my understanding of the difference between sharding and partitioning.
When we say Shard1 or Shard2, do we mean the sql server hosted on the EC2 instance combinedly as a shard?
I looove this kind of explanation. i.e stepping back and discussing the scenarios behind why something came up to be. Thanks a lot for these videos man!
One of the best explanations on the internet, well done sir
Bro your diagram example made my day. Such a clear and concise explanation of this topic. Bro dil se love u ❤❤ for making this video.
All Software Performance Enthusiats 😊,Please do also watch our Playlist on Software Performance concepts !
thank you. I always assumed that they are the same thing. This cleared things up for me.
The knowledge of amount in this video is tremendous!!! Extremely helpful 👍👍👍 thankyou sir!!
Bhai bhot videos dekhe.. this one was lit 🔥
Thanks!
Nice explaination, arpit.
This is such a great and simple explanation of partitioning and sharding of a database. Would love to watch the video on partitioning strategies when it is uploaded.
Best explanation so far. thanks brother
Hello arpit, at 5:49, why you mentioned that the new resources are being allocated to the EC2 machine? I think that should be allocated to the DB server running on EC2 machine right?
I meant the server running the database. The database is eventually running on some VM.
@@AsliEngineering thanks arpit
In case of replica of data on sharded server, we have same data dupicated on the physical disks of the data server. How is the consistency of data maintained across data base servers ?
What do you mean by "one node" at 12:48 ? Is it one EC2 instance?
liked the way concepts are explained like a story.
This is really great video. Subscribed, and exploring other videos uploaded by you.
Thanks for these practical examples and overall explanation
Very clear explaination
Looking for practical example for partionitiong
What should be the strategy of partitioning a mysql database with multiple tables into multiple shards? There are some tables which does not contains shard key.
1. Either it should contain the shard key
2. Or if it small enough then replicate the entire table across all the shards (eg. Config table, or meta table)
3. Or third bear the cost of cross shard fan outs.
Very clear explanation, thanks!
Amazing Explanation!
how's it decided which shard is hit by request? Is there any router in front ensuring routing of requests?
One question, at 21.00 the matrix shows what it looks like when we have both sharding and partioning, how that is different from having two databases on two different EC2 instance for two applications?
Wow this was really really helpful! Thank you posting this.✨
finally I understand what sharding is, thanks a ton
Wow great explanation
Thanks for the explanation
bohot badhia bhai .. lajawwab
Clear explanation
thanks a lot arpit sir really enjoyed and got full clarity
Great video ! Nicely explained. :)
But I have one query , towards the end of the video :
The example you shared for "Sharding NO and partitioning YES" about Airline Check-in System and Ticket Booking System. How is that classified as partitioning if the databases are altogether different ?
Any thoughts ?
Great explainer. - thanks!
thank you so much , clearly understood!!
Mind Blowing ❤
Thanks a lot u clearing confusion of long period
Very Nice Video, I just loved the explanation.
Thanks so much Arpit!!
Its very practical explanation...cool one
Does it mean Sharding and replication are the same? 22:16
What iPad note application are you using?
@arpit Thanks for such dense information in so short and simple video.
However I have a query on a corner case
- How can have replicas when one has multiple shards with partitioning?
- In this case is replication locally on the shard or it can also be replicated on other shards for high availability across avalability zone or DR (like kafka architecture)?
When running two databases on the same machine, are we not still sharing the same underlying resources such as CPU, memory, and disk I/O?
How can u run two sql daemon on the same machine?
Hi arpit after sharding or partioning how does the request gets routed to the respective shards do you we need to write logic at application level to track shards or is there any other approach?
either your backend servers know the topology or add a proxy that knows the topology.
well explained, thank you
Very beautifully and simply explained. The content of the video flowed so smoothly. Thank You @ArpitBhayani
amazing explanations, thank you
literally one of the based video i have ever seen on this topic.
Thanks a lot Arpit for explaining in so simplistic way. One request can you please make video on Sharding strategies and also on how composite indexes stores in the disk.
Soon.
@asli engineering - Bhai, any update on the sharding strategies.
Also, one more request is examples of scenarios to explain shard key selection.
How is the data replicated behind the scenes n stuff please ?
Do we use sharding when we have better options available like Oracle RAC where database can be scaled horizontally
@arpit what's the benefit of partitioning the data but not sharding it. Can you give me a usecase please?
Partitioning allows your database to read/access/move the required subset of data easily and efficiently.
1. Imagine if you partition data by time and create one partition for every hour and someone queries how many events happened in the last 10 hours, you would just need to access last 10 partition to fulfil this query. Others are not even required to be read.
2. In a distributed setup, instead of moving individual rows/elements we can easily and efficiently move partitions across the cluster for balancing the load.
Understood! Thanks 🙏
Thanks, really detailed
Simple and to the point explanation .. Thanks Arpit, Liked & Subscribed :)
A very clear and detailed explanation. ♥️
Great explanation!
Great content, as always! I need some advice: My OKX wallet holds some USDT, and I have the seed phrase. (alarm fetch churn bridge exercise tape speak race clerk couch crater letter). How can I transfer them to Binance?
Awesome Arpit, Thanks truly admire your way of teaching
GOD of explanation !
how do we know which partition or shard our data is located when we make query? any detailed explantion
Great explanation, as always. Please post a link If you have recorded any video on Partitioning strategies
thanks a lot arpit for an awesome explanation as always
super video Arpit
As my view Partition is more complex because you have to work with partition key! With wrong query accidentally query scan all partition’s.
this is an amazing video and your explainations are very clear
Greatly explained
how foreign key constraints are managed when two tables are in different shards ?
Foreign keys are dropped when you adopt sharding. You cannot maintain FK when data is partitioned across multiple shards.
@@AsliEngineering thanks
If we had horizontal partitioning and 1 partition getting so much traffic in real time how can we load balance the traffic. is it possible
Such a clean explanation🙌
One shard also must be having replicas right? I mean if a shard is handling the first 2 partitions, then all data from those first 2 partitions will go to this shard, but what if the shard is down?
shared can have replicas to scale the reads. If the shard goes down, then either you auto promote replica to take over, or take the downtime.
Hey great video Ankit! I was wondering how sorting and specially filtering would work across shards?
Or is that an anti pattern ( just like cross shard joins )
One of the biggest disadvantages of sharding over a SQL server is you lose the ACIDity so you have to be careful while you doing it with SQL databases
I think that's not true
How can the api server know which database server shall be given this amount of load etc ??
that is your routing strategy - range based or hash based or static routing.
for example, all request for a user goes to a particular database and the ownership is determined by taking hash of user id i.e. f(user_id)%num_databases
@@AsliEngineering how can this be implemented? Also if you can please can you make an system design with implementation video on microservices with like nodejs .. as i have understood the theoretical part but the implementation part is where am getting stuck and not understanding how to do that
@@the_angry_developer you can get user ID from your auth token. Have an array of database connections in your API code and apply the function mentioned above.
@@AsliEngineering o okay, thank you
Dude this was amazing
Start from 2:50
How we know in which shard our data resides?
That depends on your routing strategy - Range/Hash/Static. In any case, you pick a partitioning key and depending on the approach you deduce which shard to go to.
Awesome content Arpit ! Thanks a lot and please do continue post more on concepts such as well as analysis of real use cases.
what if we have read replicas and still have partitioning?
Also which software did you use in this video to create pictures and write content?
GoodNotes
Great video as always! 👍 I’ve got a question: 🤨 I have these words 🤨. (behave today finger ski upon boy assault summer exhaust beauty stereo over). What should I do with this? 🤷♂️
As usual amazing
I have a doubt sir. I think increase in overall storage capacity and higher availability, doesn't got hand in hand.
If we shard and partition data, then the capacity will be available but then the data is not same in both shards i.e., if one shard becomes offline then the other shard cannot provide data if the requested data is in the offline shard. So no higher availability.
And if we only do sharding then the same data will be in both servers, and as u said if we assume 100tb, then the other will also have 100tb only? We cant get 200tb.
This is what I thought. Correct me if im wrong.
I too got the same doubt, I came to know that in this case, our system will be available but the data might be inconsistent, so still the server accepts requests and provides an empty response.
Can you please share the notes