Introduction to Partitions

As your database tables grow to tens of millions or even billions of rows, even well-indexed queries can start to slow down. At a certain scale, managing a single massive table becomes a real challenge — not just for query performance, but also for maintenance operations like backups, index rebuilds, and data archival.

This is where table partitioning comes in. Partitioning allows you to split a large table into smaller, more manageable pieces while still treating it as a single logical table from a query perspective.

In this tutorial, you will learn:

• What partitioning is and why it exists
• The core concept behind horizontal partitioning
• How partitioning differs from simply creating multiple tables
• Real-world scenarios where partitioning makes a significant difference
• The key terminology you need before diving deeper

Table partitioning is the process of dividing a large table into smaller physical segments called partitions. Each partition holds a subset of the table's rows based on a specific rule — typically the value of a chosen column.

The crucial point is this: while the data is physically split across partitions, it still appears as one single table to the user. You query it, join it, and filter it exactly as before. The database engine handles the complexity of knowing where each row lives.

Think of it like organizing a massive filing cabinet. Instead of dumping every document into one giant drawer, you create labeled drawers — one for each year, each month, or each region. When someone asks for a document from 2023, you go straight to the 2023 drawer instead of searching through everything.

There are two ways to partition data, and it is important to know the difference:

Horizontal Partitioning (Row-Based)

This is what people usually mean when they say "partitioning." The table is split by rows — each partition contains a subset of the rows based on some column value.

Vertical Partitioning (Column-Based)

The table is split by columns — less commonly used and more of a database normalization or design concern than a built-in feature.

For the rest of this module, when we say "partitioning" we mean horizontal partitioning — dividing a table by rows.

You might wonder: why not just create separate tables like orders_2022, orders_2023, orders_2024? That achieves the same physical separation, right?

While separate tables do split data physically, partitioning provides significant advantages over that manual approach:

The Key Benefit: Transparency

With partitioning, your application code does not change at all. A query like this works before and after partitioning:

SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

The database engine automatically knows to look only in the 2023 partition. This behavior is called partition elimination (or partition pruning), and it is one of the biggest performance gains of partitioning.

Partitioning is not for every table. It adds complexity to your database design and should be used when the benefits clearly outweigh the overhead.

Good Candidates for Partitioning

Poor Candidates for Partitioning

Before going deeper into partitioning, here are the essential terms you need to know:

How These Pieces Fit Together

Here is what you should remember about table partitioning:

Key Differences from Other Optimization Techniques

Partitioning and indexing work together — you can (and should) create indexes on partitioned tables. In the next tutorials, we will explore the step-by-step process of setting up partitioning and learn how to create partitioned tables.