EP 6.2: A Simple Guide to Database Partitioning

Imagine you own a "small neighbourhood" bookstore.

At first, you have one bookshelf. Finding books -> EASY -> Customers Happy.

But as your business grows, you buy more books.

Eventually, that single bookshelf becomes a nightmare, it’s too crowded, it’s slowing down your ability to find anything, and if the shelf breaks, you lose everything. :(

In the world of System Design, your database is that bookshelf. As your app grows from 100 users to 100 million, a single database server will eventually struggle to keep up. So here we will now use database partitioning.

What is Database Partitioning?

At its core, partitioning is the process of splitting a large database into smaller, more manageable pieces. Instead of one massive table with a billion rows, you break it into smaller "partitions." The best part? To the user, it still looks like one single table, but behind the scenes, the system is much faster and more organized.

Why Do We Need It?

1. Speed (Performance): Searching through 10,000 rows > searching through 10,000,000.
2. Scalability: Spread partitions across different hard drives or even different servers.
3. Maintenance: If you need to delete old data (like logs from three years ago), you can simply "drop" that specific partition instead of deleting millions of individual rows.

Two Main Ways to Slice the Pie

There are two primary directions you can split your data:

1. Horizontal Partitioning (The most common way)

Think of this as splitting the rows. You keep the same columns (Name, Email, Date), but you put different rows into different buckets.

• Ex: You put users with names A–M in Partition 1 and N–Z in Partition 2.

2. Vertical Partitioning

Think of this as splitting the columns. You take some columns and move them to a different place.

• Ex: In a User table, keep Username and Password in one partition and "Profile Pictures" or "Bio" data to another.

What cld be out Partitioning Strategies?

How do you decide which data goes where? Here are the most common methods:

1. Range Partitioning: Data is split based on a range of values.
2. Real-world use: Storing sales data by month. (e.g. "JAN", "FEB").
3. List Partitioning: Data is split based on a predefined list of values.
4. Real-world use: Splitting customers by country. (e.g. "IND", "USA").
5. Hash Partitioning: You use a mathematical formula (a hash function) on a specific column (like User_ID) to randomly but evenly distribute data across partitions.
6. Real-world use: When you don't have a clear "range" or "list" and just want to make sure every server has roughly the same amount of work.

The "Catch": Challenges of Partitioning

Partitioning sounds great, but it adds complexity. Some DIS ADVs:

• Joins Get Harder: It can be slow and complicated for the system to "stitch" them back together.
• Complexity: Your application code needs to know which partition to access to.
• The "Hotspot" Problem: If you partition by "Country" and 90% of your users are from India, your "IND" will be overloaded while the others sit idle. This is called a Hot Partition.

ONE LINE

Database partitioning is like moving from a single filing cabinet to a whole library of organized sections. It requires more planning, but it’s the only way to ensure your system doesn't crash as it becomes more popular.

Now finally you can successfully order you bookstoree :)