Database Sharding and Partitioning

System Design for SDE-2 and above: arpitbhayani.me/masterclass
System Design for Beginners: arpitbhayani.me/sys-design
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 discussed the importance of sharding and partitioning in scaling systems. Sharding distributes data across multiple machines for improved throughput and availability. We explored how databases evolve through stages, the differences between sharding and partitioning, and when to introduce these concepts. I also highlighted the benefits of a collaborative system design course I offer. Scaling databases vertically involves increasing resources, while horizontal scaling adds more servers for higher throughput. Sharding splits data across shards, while partitioning divides data within a shard. Strategic partitioning is crucial for efficient data management.
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 am on a mission to bring out the best engineering stories from around the world and make you all fall in
love with engineering. If you resonate with this then follow along, I always keep it no-fluff.

[@visheshjindal1073]

I have just started learning System Design and not from a backend background. Still able to understand the concepts. Thanks for creating such content.

[@vipulsharma5140]

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.

[@shishirchaurasiya7374]

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

[@ranjithpals]

Thanks a lot ! That was well explained with clear and concise explanation. Looking forward to enrolling in your complete system design course.

[@nuclearniraj]

One video and all the clutter on Sharding and Partitioning is clear. Thank you so much Arpit.

[@jaskiratwalia]

Wonderfully explained! Cleared all my doubts. Please keep making such videos. These are also well timed, not too short nor too long.

[@AlokMehta24]

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

[@neerajdixit7102]

Awesome Arpit, Thanks truly admire your way of teaching

[@nimitkanani1691]

Very beautifully and simply explained. The content of the video flowed so smoothly. Thank You @ArpitBhayani

[@___vandanagupta___]

The knowledge of amount in this video is tremendous!!! Extremely helpful 👍👍👍 thankyou sir!!

[@chaitanyawaikar382]

One of the best videos explaining the nuances between partitioning and sharding. Thank you @ArpitBhayani

[@AqibJavaid-zl7vc]

Excellent video ❤. Finally, I got a good grasp of the whole concept.

[@jithinb7047]

Awesome content Arpit ! Thanks a lot and please do continue post more on concepts such as well as analysis of real use cases.

[@Jamsessions0]

One of the best explanations on the internet, well done sir

[@kritibindra4232]

Wow this was really really helpful! Thank you posting this.✨

[@timamet]

amazing explanations, thank you

[@codecspy3479]

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 ??

[@aditijalaj5036]

this is an amazing video and your explainations are very clear

[@mohitkumartoshniwal]

A very clear and detailed explanation. ♥️

[@sameer1571]

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.

[@iMakeYoutubeConfused]

Very clear explanation, thanks!

[@anandahs6078]

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.

[@nikhilrajput8696]

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.

[@AsliEngineering]

Thanks. Looking forward to having you enrolled 🙌

[@vamsidharvemuluri3817]

Best explanation so far. thanks brother

[@varshard0]

thank you. I always assumed that they are the same thing. This cleared things up for me.

[@pixiedustdreams]

I think I'm in love with this guy. 😢

[@KishoreThatavarthi]

thanks a lot arpit sir really enjoyed and got full clarity

[@zeyuli53]

well explained, thank you

[@Sharmasurajlive]

Simple and efficient explanation 👍🏻

[@jasper5016]

Thanks so much Arpit!!

[@DEEPAKKUMAR-wk5pk]

Wow great explanation

[@KriszSch]

Great explanation!

[@akshayrahangdale8511]

Very Nice Video, I just loved the explanation.

[@kaal_bhairav_23]

thanks a lot arpit for an awesome explanation as always

[@TechSpot56]

Nice explaination, arpit.

[@kalinduabeysinghe8917]

Such a clean explanation🙌

[@pramodpatil-ue8sm]

Great explanation, as always. Please post a link If you have recorded any video on Partitioning strategies

[@dhaanaanjaay]

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?

[@hanzalasiddique6313]

Mind Blowing ❤

[@vijaymunavalli335]

Its very practical explanation...cool one

[@anshujaiswal5622]

Simple and to the point explanation .. Thanks Arpit, Liked & Subscribed :)

[@letsexplorewithanika2642]

Very clear explaination

[@prashantkamble898]

Greatly explained

[@lazry1773]

Dude this was amazing

[@aneksingh4496]

super video Arpit

[@shintojoseph9166]

Clear explanation

[@ryan-bo2xi]

bohot badhia bhai .. lajawwab

[@PoojaDurgi]

Amazing !!

[@heykalyan]

Kudos to you❤

[@shreyanshsinha37]

When we say Shard1 or Shard2, do we mean the sql server hosted on the EC2 instance combinedly as a shard?

[@amananurag07]

@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)?

[@ankitmaheshwari2341]

Do we use sharding when we have better options available like Oracle RAC where database can be scaled horizontally

[@jivanmainali1742]

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?

[@sarthaknarayan2159]

Awesome!!!!

[@sumeetsingh1729]

how's it decided which shard is hit by request? Is there any router in front ensuring routing of requests?

[@ranjithpals]

Thanks!

[@pranjalchoudhury1670]

Nicely expalined. :)

[@hemsagarpatel8992]

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

[@vikasbhutra9400]

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.

[@AsliEngineering]

Soon.

[@hc90919]

@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 ?

[@tawseefbhat977]

how do we know which partition or shard our data is located when we make query? any detailed explantion

[@user-dq8sg4ik5k]

literally one of the based video i have ever seen on this topic.

[@rahulpanjwani1887]

Beautiful

[@rahulpanjwani1887]

It makes you understand the value of a unified data platform team when scale increases.

[@ohmygosh6176]

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

[@aditigupta6870]

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?

[@AsliEngineering]

I meant the server running the database. The database is eventually running on some VM.

[@aditigupta6870]

@@AsliEngineering thanks arpit

[@GaneshSrivatsavaGottipati]

what if we have read replicas and still have partitioning?

[@aditiagarwal7081]

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?

[@likith1337]

How can u run two sql daemon on the same machine?

[@kritibindra4232]

Also which software did you use in this video to create pictures and write content?

[@AsliEngineering]

GoodNotes

[@imperfecto7734]

@arpit what's the benefit of partitioning the data but not sharding it. Can you give me a usecase please?

[@AsliEngineering]

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.

[@imperfecto7734]

Understood! Thanks 🙏

[@aditigupta6870]

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?

[@AsliEngineering]

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.

[@sachinjindal4921]

Awesome, can you give some practical examples.

[@AsliEngineering]

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.

[@abhigujjar7439]

Can you please share the notes

[@Bluesky-rn1mc]

how foreign key constraints are managed when two tables are in different shards ?

[@AsliEngineering]

Foreign keys are dropped when you adopt sharding. You cannot maintain FK when data is partitioned across multiple shards.

[@Bluesky-rn1mc]

@@AsliEngineering thanks

[@GaganJain2508]

Does it mean Sharding and replication are the same? 22:16

[@arbazadam3407]

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.

[@AsliEngineering]

multiple databases within same MySQL server.

[@shrad6611]

finally I understand what sharding is, thanks a ton

[@geekmuralin]

Wow

[@gigachad400]

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

[@ankitmaheshwari2341]

I think that's not true

[@iHariPatel]

As my view Partition is more complex because you have to work with partition key! With wrong query accidentally query scan all partition’s.

[@sachthecool]

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.

[@AsliEngineering]

I agree the terms are used interchangeably; but overall what i explained is correct also I cleared the same in the video as well.

[@bikramjeetsarmah9995]

How can the api server know which database server shall be given this amount of load etc ??

[@AsliEngineering]

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

[@bikramjeetsarmah9995]

@@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

[@AsliEngineering]

@@bikramjeetsarmah9995 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.

[@bikramjeetsarmah9995]

@@AsliEngineering o okay, thank you

[@user-nu5nn7by6t]

How we know in which shard our data resides?

[@AsliEngineering]

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.

[@ManojYadav-ls6wo]

12:10 20:12 👍👍

[@dbads]

💯

[@anupkut]

I think we should not consider only read replicas as sharding concept.

[@jineshbagrecha6278]

When to use master master, master candidate master replications?

[@AsliEngineering]

master master - scaling writes beyond one machine master replica - scaling reads

[@aadimanchekar1032]

How do we know that in which partition does the data lie?

[@AsliEngineering]

That's that partitioning strategy

[@mudassarh4268]

Sharding strategies could have been taken up like range based and hash based sharing with their user case

[@AsliEngineering]

Sir. Video would have been too long. No one would have watched it. But definitely planning it for the next one.

[@mudassarh4268]

Definitely sirji that could have added another 30 mins of content. Awesome content as always and looking forward to further stuff 👍

[@abhishekdhillon7110]

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.

[@AsliEngineering]

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.

[@pranavnadimpalli4929]

22:34 cross share queries are expensive

[@kumarshubham4640]

Why course price exceeded by 20k in 1 year?

[@AsliEngineering]

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

[@akshatreddy9870]

Hi

[@arun10071990]

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 ?

[@AsliEngineering]

When did I not consider vertical scaling?

[@arun10071990]

@@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

[@sharoonaustin551]

Small suggestion ad beech me mat daala karo bro, concentration toot jaata hai

[@AsliEngineering]

KZbin daalta hai. I just enable them. It is upto their algorithm to decide where to place.

[@AsliEngineering]

And I totally understand your frustration with ads but the world runs on them. Can't do much without it.

[@jose000]

Iio

[@luisdanielmesa]

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.

[@AsliEngineering]

15 SDE-2s, 3 SDE-3, 1 PE and 1 HoE took my course. If you do not to believe it is upto you.

[@AsliEngineering]

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

[@akshatreddy9870]

Very bad. Hindu never shave off moustache and keep beard. Mussalman banne ka irada hain keya ? Please understand that you are Sanatani

[@akshatreddy9870]

Either shave both beard and moustache or keep both moustache and beard. Don't just shave moustache only and keep beard.

[@iMakeYoutubeConfused]

He's put so much effor into the content of this video and this is all what you've got to say?

[@eatajerkpal99]

Hey arpit acan drop link for the notes that you presented in this video, thanks!

[@eatajerkpal99]

found them on your github, i wont spam anymore. thanks!!

[@amogu_07]

thank you so much , clearly understood!!