Database Management System (DBMS) / Clustering, Partitioning and Sharding

Partitioning for scaling out the DB

Partitioning is the process of dividing a large database into smaller, manageable pieces called partitions. Each partition holds a subset of the total data, and they can be stored on the same server or multiple servers.

👉 When we scale out (also called horizontal scaling), we spread these partitions across multiple machines to handle:

  • More data

  • More concurrent users

  • Higher throughput

🔸 Types of Partitioning

Type

Description

Example

🔹 Horizontal Partitioning (Sharding)

Rows of a table are divided across partitions. Each shard has a different subset of rows.

Users with ID 1–1000 in one partition, 1001–2000 in another

🔸 Vertical Partitioning

Columns are divided across tables (or servers).

Store frequently accessed columns separately from rarely accessed ones

🔹 Range Partitioning

Data is partitioned based on a range of values.

Orders from Jan–Mar in one partition, Apr–Jun in another

🔸 Hash Partitioning

A hash function determines which partition data goes to.

hash(user_id) % 4 to decide among 4 partitions

🔹 List Partitioning

Based on predefined values (categories, regions, etc.)

Country = ‘India’ in one, ‘USA’ in another

🔸 Why Use Partitioning for Scaling Out?

Benefit

Explanation

✅ Improved Performance

Each query deals with smaller datasets

✅ Parallel Processing

Different partitions can be queried in parallel

✅ High Availability

Failures in one partition don’t affect the others

✅ Easy Maintenance

You can back up or archive partitions independently

✅ Scalability

Easily add new servers and move partitions around

🔸 Real-World Example – MongoDB Sharding (Horizontal Partitioning)

MongoDB uses shards (horizontal partitions) to scale out the database:

# Each shard stores part of the data
Shard 1: Users 1–100000
Shard 2: Users 100001–200000
Shard 3: Users 200001–300000

MongoDB decides which shard to use based on a shard key (e.g., user ID).

🔸 SQL Example – Range Partitioning

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE,
    ...
)
PARTITION BY RANGE (YEAR(OrderDate)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

This example partitions orders by year, helping queries on a specific year to run faster.

🔸 Partitioning vs Sharding

Aspect

Partitioning

Sharding (Horizontal Partitioning)

Scope

General term

Specific type of partitioning

Goal

Manageability & performance

Scalability across distributed systems

Distribution

Can be on single or multiple servers

Always across multiple servers

🔚 Summary

  • Partitioning helps divide and conquer large datasets.

  • It's essential for scaling out a database system.

  • Horizontal partitioning (sharding) is the most common method for scaling out.

  • Popular in NoSQL (MongoDB, Cassandra) and now widely supported in SQL databases (PostgreSQL, MySQL, etc.).