Partitioning and Sharding in DBMS

Modern databases store massive volumes of data that grow rapidly with time. Managing such large databases can become inefficient, affecting query performance, scalability, and availability. To overcome these challenges, database engineers use partitioning and sharding, both of which divide large datasets into smaller, more manageable units.

The principle is simple:

“A big problem can be solved efficiently when broken into smaller parts.”

---

What is Partitioning?

Partitioning is a database optimization technique used to divide a large database table into smaller, manageable segments known as partitions. Each partition contains a subset of the table’s data and can be stored on separate disks or servers.

Despite partitioning, the table remains logically one single entity — SQL queries can access it as usual.

Key Idea

Instead of processing a giant table, queries now operate on smaller, partitioned tables, improving:

• Performance
• Manageability
• Scalability

---

Purpose of Partitioning

Purpose	Description
Performance Optimization	Queries scan fewer rows within a partition rather than the entire table.
Improved Manageability	Easier to maintain and backup smaller data slices.
Parallelism	Multiple partitions can be queried in parallel.
High Availability	Even if one partition/server fails, others continue to function.
Cost Efficiency	Reduces the need for expensive hardware upgrades (scale-out instead of scale-up).

---

Types of Partitioning

Partitioning can be implemented in two main ways:

A. Vertical Partitioning

• Data is divided column-wise.
• Each partition stores different columns of a table.

Example:

ID	Name	Email	Salary
1	Akash	akash@example.com	60000

Vertical partitioning could split this into:

• Partition 1: (ID, Name, Email)
• Partition 2: (ID, Salary)

Pros:

• Useful when applications frequently access only specific columns.
• Reduces I/O load for partial data access.

Cons:

• To reconstruct a full record, data from multiple partitions must be joined (higher latency).

---

B. Horizontal Partitioning

• Data is divided row-wise (tuple-wise).
• Each partition contains different rows of the same table.

Example:

ID	Name	Age	City
1	Akash	22	Delhi
2	Priya	24	Mumbai
3	Raj	25	Chennai

Horizontal partitioning:

• Partition 1 → Rows 1–1000
• Partition 2 → Rows 1001–2000
• Partition 3 → Rows 2001–3000

Pros:

• Queries can be routed only to relevant partitions.
• Ideal for distributed systems and scalability.

Cons:

• Slight complexity in determining partition logic.

---

When Partitioning is Applied

Partitioning becomes essential when:

1. Dataset size grows enormously, making query performance slow.
2. Server load increases, causing high response times.
3. Backup, restore, or maintenance operations become cumbersome.
4. Distributed or Cloud databases are used (requiring data segmentation).

---

Advantages of Partitioning

Advantage	Description
Parallelism	Queries can run on multiple partitions simultaneously.
High Availability	Failure of one partition/server does not affect the entire system.
Performance	Queries and indexes operate on smaller data subsets.
Manageability	Easier to back up, migrate, or maintain data.
Scalability	Supports distributed data storage without affecting structure.
Cost Reduction	Enables horizontal scaling, avoiding costly hardware upgrades.

---

Distributed Database

A distributed database is a single logical database stored across multiple physical locations (servers). Each site manages its data locally but is connected by a network and appears as a single unified system to the user.

Partitioning, Clustering, and Sharding are optimization techniques used to implement distributed databases.

---

Sharding

Sharding is a specific implementation of horizontal partitioning across multiple servers or database instances. It involves splitting a large dataset into smaller subsets called shards, each hosted on a separate database instance.

A routing layer determines which shard contains the requested data.

---

Example of Sharding

Scenario:

A global e-commerce platform stores user data by region.

Shard	Region	Database Server
Shard 1	Asia	DB_Server_1
Shard 2	Europe	DB_Server_2
Shard 3	America	DB_Server_3

When a user from India logs in, the routing layer directs the query to Shard 1 (Asia), instead of searching all data globally.

---

How Sharding Works

1. Sharding Key: A field (like user ID or region) determines which shard stores the data.
2. Routing Layer: Routes queries to the appropriate shard using the key.
3. Independent Shards: Each shard behaves as a separate database instance.

---

Advantages of Sharding

Benefit	Description
Scalability	Allows horizontal scaling across multiple machines.
Availability	Failure in one shard does not affect others.
Performance	Queries operate on smaller datasets.
Load Distribution	Distributes data and workload evenly.

---

Disadvantages of Sharding

Limitation	Description
Complexity	Requires routing layer and consistent partition mapping.
Re-Sharding Issues	If data grows unevenly, rebalancing shards is challenging.
Non-uniform Data Distribution	May cause load imbalance.
Scatter-Gather Problem	Analytical queries need to fetch data from multiple shards, increasing latency.

---

9. Difference Between Partitioning and Sharding

Aspect	Partitioning	Sharding
Definition	Logical division of a large table into smaller pieces within a single database.	Physical division of data across multiple database instances.
Implementation Scope	Within one DB server.	Across multiple servers or databases.
Data Distribution	Logical (can exist on same hardware).	Physical (distributed across nodes).
Used For	Performance and management optimization.	Scalability and distributed storage.
Routing Layer	Not required.	Required to direct queries to shards.
Failure Impact	Failure affects the same DB instance.	Isolated; failure of one shard doesn’t affect others.
Complexity	Easier to manage.	More complex (requires mapping, balancing).
Best Suited For	Large single-server databases.	Distributed, cloud-scale applications.

---

Practical Examples

Partitioning Example (Horizontal)

-- Table Partition Example in MySQL
CREATE TABLE orders (
  order_id INT,
  order_date DATE,
  customer_id INT
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024)
);

Divides data by year into multiple partitions within the same database.

---

Sharding Example (Conceptual)

// Pseudocode for sharding using userID
if (userID % 3 === 0)
    connect(DB_Server_1);
else if (userID % 3 === 1)
    connect(DB_Server_2);
else
    connect(DB_Server_3);

Distributes user data across multiple databases (shards) based on user ID.

---

Summary Table

Concept	Definition	Example	Purpose
Partitioning	Divides a table into smaller segments within the same database.	Year-wise orders partition	Performance and manageability
Sharding	Distributes database segments across multiple servers.	Region-wise user data	Scalability and load balancing
Vertical Partitioning	Split columns of a table.	User info vs Salary data	Optimize column access
Horizontal Partitioning	Split rows of a table.	Orders by region	Optimize data access by key

---

Conclusion

• Partitioning enhances query performance and manageability within a single database.
• Sharding ensures scalability and high availability across distributed systems.
• Both are fundamental database optimization techniques in modern large-scale systems like Google, Amazon, and Netflix, where billions of records must be managed efficiently.

---