Database Sharding and Partitioning
ฝัง
- เผยแพร่เมื่อ 15 พ.ค. 2024
- System Design for Beginners: arpitbhayani.me/sys-design
System Design for Experienced Engineers: arpitbhayani.me/masterclass
Redis Internals: arpitbhayani.me/redis
Build Your Own Redis / DNS / BitTorrent / SQLite - with CodeCrafters.
Sign up and get 40% off - app.codecrafters.io/join?via=...
In the video, I explained the concepts of sharding and partitioning for scaling systems at a database level. Initially, I discussed scaling databases through different stages, the difference between sharding and partitioning, and when to introduce these concepts. I highlighted the advantages and disadvantages of adopting them. I also promoted my system design course, emphasizing collaborative learning and practical problem-solving. The video covered vertical scaling, read replicas, horizontal scaling, and the importance of sharding and partitioning for high throughput in database systems.
Recommended videos and playlists
If you liked this video, you will find the following videos and playlists helpful
System Design: • PostgreSQL connection ...
Designing Microservices: • Advantages of adopting...
Database Engineering: • How nested loop, hash,...
Concurrency In-depth: • How to write efficient...
Research paper dissections: • The Google File System...
Outage Dissections: • Dissecting GitHub Outa...
Hash Table Internals: • Internal Structure of ...
Bittorrent Internals: • Introduction to BitTor...
Things you will find amusing
Knowledge Base: arpitbhayani.me/knowledge-base
Bookshelf: arpitbhayani.me/bookshelf
Papershelf: arpitbhayani.me/papershelf
Other socials
I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
LinkedIn: / arpitbhayani
Twitter: / arpit_bhayani
Weekly Newsletter: arpit.substack.com
Thank you for watching and supporting! it means a ton. - วิทยาศาสตร์และเทคโนโลยี
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 a lot ! That was well explained with clear and concise explanation. Looking forward to enrolling in your complete system design course.
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
One video and all the clutter on Sharding and Partitioning is clear. Thank you so much Arpit.
Wonderfully explained! Cleared all my doubts. Please keep making such videos. These are also well timed, not too short nor too long.
this is an amazing video and your explainations are very clear
The knowledge of amount in this video is tremendous!!! Extremely helpful 👍👍👍 thankyou sir!!
Wow this was really really helpful! Thank you posting this.✨
One of the best videos explaining the nuances between partitioning and sharding. Thank you @ArpitBhayani
Very beautifully and simply explained. The content of the video flowed so smoothly. Thank You @ArpitBhayani
Awesome content Arpit ! Thanks a lot and please do continue post more on concepts such as well as analysis of real use cases.
amazing explanations, thank you
Awesome Arpit, Thanks truly admire your way of teaching
A very clear and detailed explanation. ♥️
Excellent video ❤. Finally, I got a good grasp of the whole concept.
One of the best explanations on the internet, well done sir
well explained, thank you
Best explanation so far. thanks brother
Mind Blowing ❤
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.
Such a clean explanation🙌
thank you. I always assumed that they are the same thing. This cleared things up for me.
Simple and efficient explanation 👍🏻
Dude this was amazing
super video Arpit
thanks a lot arpit sir really enjoyed and got full clarity
Amazing !!
Thanks so much Arpit!!
Its very practical explanation...cool one
Very clear explanation, thanks!
bohot badhia bhai .. lajawwab
Wow great explanation
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.
Kudos to you❤
thanks a lot arpit for an awesome explanation as always
Great explanation!
Very clear explaination
Clear explanation
Greatly explained
Very Nice Video, I just loved the explanation.
Nice explaination, arpit.
Great explanation, as always. Please post a link If you have recorded any video on Partitioning strategies
Arpit sir I need your help clearifying few doubts
In ecommerce platform like shopify each mechant is given their own collection for order cart account differentiated by some merchant identifier (projectId-order ) vs Same order table index by merchant ideidentifier ie projectId.So we can't apply sharding in first case.
Also is it wise idea to deploy each merchant application separately as we would have to maintain each merchant app separately.So what do you suggest in those case?
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 🙌
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?
When we say Shard1 or Shard2, do we mean the sql server hosted on the EC2 instance combinedly as a shard?
@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)?
Simple and to the point explanation .. Thanks Arpit, Liked & Subscribed :)
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 ??
Nicely expalined. :)
Thanks!
Awesome!!!!
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
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?
literally one of the based video i have ever seen on this topic.
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
how's it decided which shard is hit by request? Is there any router in front ensuring routing of requests?
how do we know which partition or shard our data is located when we make query? any detailed explantion
I think I'm in love with this guy. 😢
Beautiful
It makes you understand the value of a unified data platform team when scale increases.
Wow
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
Cross sharding quiries very very expensive. Its best to use tools to find out how the database is being used before making these decisions. I use PG Analizer tool for PostgreSQL
Also which software did you use in this video to create pictures and write content?
GoodNotes
what if we have read replicas and still have partitioning?
Can you please share the notes
@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 🙏
Awesome, can you give some practical examples.
These are practical as they can get keeping it generic and not touching upon SRE side of things :) Every database comes it its own partitioning and sharding strategy and we need to go through their documentation to apply it.
I talked about using a database proxy to bifurcate the request in one of the earlier videos, in case you are looking for that.
Would recommend you picking a database and seeing how you can actually create shards and manage them. ElasticSearch can be a great start.
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.
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
When you say we can have these partitions on the same server? That confuses me. On my linux server i installed MySQL which runs on port 3306. I have one MySQL process in this situation, so how can i spread the partition on this server.
multiple databases within same MySQL server.
finally I understand what sharding is, thanks a ton
💯
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
Hi Arpit... You have nice videos. I like interviewes with people involved in growing high scale systems.
However in this video, concept explained is wrong. Partition & Shards are same (term is used interchangeably). What you are referring as Shard is Nodes (or host container). You may want to correct the same. Hope this helps.
I agree the terms are used interchangeably; but overall what i explained is correct also I cleared the same in the video as well.
When to use master master, master candidate master replications?
master master - scaling writes beyond one machine
master replica - scaling reads
Does it mean Sharding and replication are the same? 22:16
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.
How do we know that in which partition does the data lie?
That's that partitioning strategy
22:34 cross share queries are expensive
As my view Partition is more complex because you have to work with partition key! With wrong query accidentally query scan all partition’s.
Sharding strategies could have been taken up like range based and hash based sharing with their user case
Sir. Video would have been too long. No one would have watched it. But definitely planning it for the next one.
Definitely sirji that could have added another 30 mins of content. Awesome content as always and looking forward to further stuff 👍
12:10
20:12 👍👍
I think we should not consider only read replicas as sharding concept.
dude, the way you have explained higher availability as an advantage of sharding is not right. When you have a sharded DB and various shards live on different servers, if one of the shards go down, availability is not an advantage since you can't perform any operations on that specific shard which is not available. For example, if you have two shards named A and B, if shard is down or not available, you can't read anything from that shard so all of the queries that are expected to read from shard A would fail unless you have a read replica of that shard. I feel that there is a better way to explain it. However, thanks for all your efforts and your content is helpful to a large extent.
Yes we cannot perform operation on that shard but we can still serve requests that can be served from the other shards. Hence the system still remains partially available.
Why course price exceeded by 20k in 1 year?
In 2 years, not one.
The course has changed completely and I go much more in-depth and the sessions go for 4 hours each. Earlier it used to be 2.5
Hi
I think sharding has specific use cases not every solution requires sharding. The way he arrives at sharding solution is totally absurd.
If one really wants to scale the writes he can also upscale the master db servers. Why to shard then ?
When did I not consider vertical scaling?
@@AsliEngineering it's not about vertical scaling it's about we can scale database with horizontal scaling and that too without using sharding
Like multiple master servers for writes and multiple slave servers to handle reads
Small suggestion ad beech me mat daala karo bro, concentration toot jaata hai
TH-cam daalta hai. I just enable them. It is upto their algorithm to decide where to place.
And I totally understand your frustration with ads but the world runs on them. Can't do much without it.
We both worked for Amazon and you know nobody there would have taken this course... So you're either lying or... nah, you're lying.
15 SDE-2s, 3 SDE-3, 1 PE and 1 HoE took my course. If you do not to believe it is upto you.
Fun fact, after I replied to your comment I went on a 1:1 call and it was with an SDE-2 at Amazon working in CCF org :D
Iio
Very bad. Hindu never shave off moustache and keep beard. Mussalman banne ka irada hain keya ? Please understand that you are Sanatani
Either shave both beard and moustache or keep both moustache and beard. Don't just shave moustache only and keep beard.
He's put so much effor into the content of this video and this is all what you've got to say?
Hey arpit acan drop link for the notes that you presented in this video, thanks!
found them on your github, i wont spam anymore. thanks!!
thank you so much , clearly understood!!