Database Partitioning — Database Scaling

Database Partitioning means splitting a very large table into smaller logical pieces called partitions, while applications continue to access the table as a single table.

The main goal is to improve query performance, maintenance, and manageability when tables become very large.

Why Do We Need Partitioning?

Imagine an Orders table containing 2 billion rows.

A query such as:

SELECT *
FROM Orders
WHERE CreatedAt >= '2026-01-01';

Without proper partitioning, SQL Server may need to examine a very large amount of data.

With date-based partitioning, data can be divided by year or month:

2024 Data → Partition 1
2025 Data → Partition 2
2026 Data → Partition 3
2027 Data → Partition 4

SQL Server can eliminate partitions that cannot contain the requested data. This is called partition elimination.

Basic Concept

flowchart LR
    A[Orders Table] --> B[Partitioning Column: CreatedAt]

    B --> C[Partition 1]
    B --> D[Partition 2]
    B --> E[Partition 3]
    B --> F[Partition 4]

    C --> C1[2024 Data]
    D --> D1[2025 Data]
    E --> E1[2026 Data]
    F --> F1[2027 Data]

The application still queries:

SELECT *
FROM Orders
WHERE CreatedAt >= '2026-01-01';

It does not need to know which physical partition contains the data.

Types of Partitioning

For large-scale database discussions, horizontal partitioning is the most commonly discussed form.

Horizontal Partitioning

flowchart TD
    A[Orders] --> B[Horizontal Partitioning]

    B --> C[Partition 1
2024]
    B --> D[Partition 2
2025]
    B --> E[Partition 3
2026]
    B --> F[Partition 4
2027]

    C --> C1[Rows]
    D --> D1[Rows]
    E --> E1[Rows]
    F --> F1[Rows]

Each partition contains a different set of rows.

SQL Server Complete Example

SQL Server provides two important objects:

• Partition Function — defines how rows are divided.
• Partition Scheme — maps partitions to filegroups.

Step 1 — Create Database

CREATE DATABASE PartitionDemo;
GO

USE PartitionDemo;
GO

Step 2 — Create Partition Function

Suppose we want to partition orders by year.

CREATE PARTITION FUNCTION OrderDatePartitionFunction
(
    DATE
)
AS RANGE RIGHT FOR VALUES
(
    '2025-01-01',
    '2026-01-01',
    '2027-01-01'
);
GO

This creates four logical partitions:

Partition 1 → dates before 2025-01-01
Partition 2 → 2025-01-01 to 2025-12-31
Partition 3 → 2026-01-01 to 2026-12-31
Partition 4 → 2027-01-01 and later

Step 3 — Create Partition Scheme

CREATE PARTITION SCHEME OrderDatePartitionScheme
AS PARTITION OrderDatePartitionFunction
ALL TO ([PRIMARY]);
GO

For a production system, different filegroups can be used. For this interview example, everything is stored in the PRIMARY filegroup.

Step 4 — Create Partitioned Table

CREATE TABLE Orders
(
    OrderId INT NOT NULL,
    CustomerId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    CreatedAt DATE NOT NULL,
    Status VARCHAR(30) NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED (OrderId, CreatedAt)
)
ON OrderDatePartitionScheme(CreatedAt);
GO

Step 5 — Insert Sample Data

INSERT INTO Orders
    (OrderId, CustomerId, Amount, CreatedAt, Status)
VALUES
    (1, 101, 1000.00, '2024-06-10', 'Completed'),
    (2, 102, 1500.00, '2025-03-15', 'Completed'),
    (3, 103, 2500.00, '2025-08-20', 'Pending'),
    (4, 104, 3200.00, '2026-02-10', 'Completed'),
    (5, 105, 4500.00, '2026-07-25', 'Pending'),
    (6, 106, 5500.00, '2027-01-10', 'Completed');
GO

Step 6 — Check Data

SELECT *
FROM Orders
ORDER BY CreatedAt;
GO

Expected output:

OrderId  CustomerId  Amount   CreatedAt    Status
-------  ----------  -------  ----------   ----------
1        101         1000.00  2024-06-10   Completed
2        102         1500.00  2025-03-15   Completed
3        103         2500.00  2025-08-20   Pending
4        104         3200.00  2026-02-10   Completed
5        105         4500.00  2026-07-25   Pending
6        106         5500.00  2027-01-10   Completed

Check Which Partition Contains Each Row

SQL Server provides the $PARTITION function.

SELECT
    OrderId,
    CreatedAt,
    $PARTITION.OrderDatePartitionFunction(CreatedAt) AS PartitionNumber
FROM Orders
ORDER BY CreatedAt;
GO

Expected output:

OrderId  CreatedAt    PartitionNumber
-------  ----------   ---------------
1        2024-06-10   1
2        2025-03-15   2
3        2025-08-20   2
4        2026-02-10   3
5        2026-07-25   3
6        2027-01-10   4

Partition Elimination

Now suppose the application only needs 2026 orders.

SELECT *
FROM Orders
WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt < '2027-01-01';
GO

SQL Server can identify that the required rows belong to Partition 3 and avoid scanning unrelated partitions.

flowchart LR
    A[Query: 2026 Orders] --> B[Partition Function]

    B --> C[Partition 1
Skip]
    B --> D[Partition 2
Skip]
    B --> E[Partition 3
Scan]
    B --> F[Partition 4
Skip]

This is called partition elimination.

Why Partitioning Helps

Partitioning vs Sharding

Real System Design Example

Consider a payment system storing billions of transactions.

flowchart LR
    A[Application] --> B[Database]

    B --> C[Transactions Table]

    C --> D[2024 Partition]
    C --> E[2025 Partition]
    C --> F[2026 Partition]
    C --> G[2027 Partition]

    H[Reporting Query] --> C
    I[Archive Process] --> D

Recent transactions can remain active while older partitions can be archived according to the business retention policy.

Important Interview Point

Partitioning does not automatically make every query faster.

Partition elimination generally requires the query to use the partitioning column in a way that allows SQL Server to identify the relevant partitions.

For example:

-- Can enable partition elimination
SELECT *
FROM Orders
WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt < '2027-01-01';

A poorly designed query may still scan unnecessary data.

Interview Scenario

Interviewer: Your Orders table has 5 billion rows. How would you manage it?

Answer:

First I would analyze the workload. If the queries are primarily date-based, I could use horizontal partitioning based on CreatedAt. SQL Server can then perform partition elimination for date-range queries. I would combine partitioning with appropriate indexes, archiving, and possibly read replicas. If a single database server still cannot handle the overall storage or traffic requirements, I would consider sharding.

Common Interview Questions

• What is database partitioning?
• Why do we need partitioning?
• What is horizontal partitioning?
• What is vertical partitioning?
• What is partition elimination?
• What is a partition function in SQL Server?
• What is a partition scheme?
• Does partitioning automatically improve performance?
• Partitioning vs sharding?
• How would you partition a 5-billion-row Orders table?

Key Takeaways

• Partitioning divides a large table into smaller logical partitions.
• Horizontal partitioning divides rows.
• Date-based partitioning is common for transaction tables.
• SQL Server uses partition functions and partition schemes.
• Partition elimination can reduce the amount of data scanned.
• Partitioning improves manageability as well as performance in suitable workloads.
• Partitioning is different from sharding.
• Partitioning alone does not solve unlimited database scaling.

Conclusion

Database Partitioning is an important technique for managing very large tables. In system design interviews, remember the key flow:

Large Table
    ↓
Choose Partition Key
    ↓
Split Data into Partitions
    ↓
Partition Elimination
    ↓
Better Query and Maintenance Management