Premium Video

This video is available to premium subscribers only

Unlock Full Access

Suppose an application stores its orders in a single table. At first, the table holds a few thousand rows. Queries are fast, indexes are small, and maintenance is simple.

Then the application grows. A few years later, the same table holds hundreds of millions or even billions of rows. At that scale, several problems start to appear:

Even if the database server has enough CPU, memory, and disk to hold all of this data, managing one enormous table becomes harder every month.

One way to make this easier is to divide the table into smaller pieces. That is the basic idea behind database partitioning.

This chapter covers what partitioning is, the common ways to split a table, how partitioning speeds up queries and simplifies data management, and the mistakes that can make it less effective.

Database partitioning means splitting one logical table into multiple smaller physical pieces. Each piece is called a partition.

From the application's point of view, it can still look like one table. The application keeps querying the orders table the same way it did before. Internally, though, the database stores different rows in different partitions. One partition might hold orders from January, another orders from February, and another orders from March.

The application talks to orders and never needs to know that three physical pieces sit underneath it. Together, those partitions still represent the same logical table. The database simply organizes the rows into smaller groups behind the scenes, and it decides which partition a new row belongs to when that row is inserted.

The most common type of partitioning is horizontal partitioning, which divides a table by rows.

Suppose the orders table contains 100 million rows. We might divide those rows based on order date. Orders from 2024 go into one partition, orders from 2025 into another, and orders from 2026 into a third.

Every partition has the same columns. The only difference between them is which rows they contain.

This is different from vertical partitioning, where we divide the columns of a table instead. For example, a users table might keep frequently read columns like name and email in one table and move a large, rarely read column like a profile bio into another.

When engineers talk about database partitioning in large-scale systems, they usually mean horizontal partitioning, and that is what the rest of this chapter focuses on.

Once we decide to split rows, the next question is how the database should decide which partition a row belongs to. There are three common strategies.

With range partitioning, rows are assigned to partitions based on ranges of a value.

Dates are the most common example. Suppose we partition an events table by month. Events from January go into the January partition, events from February go into the February partition, and so on.

Here is what that looks like in PostgreSQL. The parent table declares the partition key, and each partition declares the range of values it accepts.

The lower bound of each range is inclusive and the upper bound is exclusive, so an event at midnight on February 1 lands in the February partition, not January. Inserts go to events as usual, and the database routes each row to the right partition.

Ranges do not have to be dates. We could also partition by a numeric value. For example, customer IDs from 1 to 1,000,000 could go into one partition, and the next million into another.

Range partitioning is useful when queries frequently ask for a specific range of values, such as "orders from last week" or "events in March". It works especially well for time-series data, where recent data is read and written constantly while older data can be managed separately.

Sometimes the partition key is not a continuous value like a date. It is a category. List partitioning handles this by explicitly assigning specific values to each partition.

Suppose we have users from several regions. We might create one partition for the United States, one for Europe, one for India, and one for the rest of the world. Each row goes into a partition based on the value of its region column.

A single partition can accept several values, which is how the Europe partition collects rows from many countries. The last partition acts as a catch-all for any value that is not listed elsewhere, so a user from a new country still has somewhere to go.

List partitioning is useful when the partition key has a small number of known categories. But the distribution of those categories matters. If 80% of the users belong to one region, that partition will be much larger than the others, and most of the benefit of splitting the table disappears.

Range and list partitioning both group rows by their values. That keeps related data together, but it gives us no control over how evenly the rows spread out. Hash partitioning takes the opposite approach.

The database applies a hash function to the partition key, and the result decides which partition receives the row. For example, we might hash customer_id and spread the results across eight partitions.

The partition numbers in the diagram are only an illustration, since the actual result depends on the hash function. The point is that a good hash function scatters keys unpredictably, so rows tend to spread evenly across all eight partitions.

That also means nearby values do not end up together. Customer 1000 and customer 1001 might be stored in completely different partitions. A query for "customers 1000 to 2000" has to look at every partition, because the database cannot tell from the range which partitions hold those rows.

So hash partitioning is useful when even distribution matters more than keeping related ranges together.

The table below puts the three strategies side by side.

Choosing a strategy is only half the decision. The real payoff from partitioning comes when a query can skip partitions entirely.

One of the biggest benefits of partitioning is partition pruning.

Suppose we have five years of order data divided into monthly partitions. That is 60 partitions. Now we run a query for orders from September 2026:

The database knows the range of each partition. It can see that only the September 2026 partition could contain matching rows. So instead of scanning all 60 partitions, it reads only that one and ignores the rest.

That is partition pruning. The fewer partitions the database needs to inspect, the less data it has to read. In this example, the query touches roughly one sixtieth of the table.

Pruning only happens when the query filters on the partition key. If the same query filtered on customer_id instead of created_at, the database would have no way to rule out any partition, and it would check all 60. This makes the choice of partition key one of the most important decisions in the whole design.

A good partition key matches the way the data is commonly accessed.

Suppose an events table is almost always queried by timestamp, for example "events from the last 24 hours". Partitioning by time works well here, because each query usually touches only one or two partitions.

Now imagine we partition the same table by user_id instead, while most queries still ask for events from the last 24 hours across all users. Those recent events are scattered across every partition, since every user can generate events at any time. The database has to inspect all of them. The table is still partitioned, but pruning gives us almost nothing.

The right key depends on the workload, not on the table. If the most common query were "all events for user 42", partitioning by user_id would be the better choice. So before picking a key, look at which columns appear in the WHERE clause of the queries that run most often.

Partitioning does not replace indexing. The two work at different levels and are often used together.

Suppose we partition an orders table by month and frequently search for orders by customer_id. Each monthly partition can have its own index on customer_id.

Now we query the orders for customer 42 during September. The database first prunes away every month except September. Then it uses the customer_id index inside the September partition to find the matching rows directly.

Two optimizations are combined here. Partition pruning reduces the amount of data the database needs to consider. The index then locates the matching rows inside the selected partition without scanning it. Each partition's index is also smaller than one index across the whole table, so it is more likely to fit in memory.

There is one constraint worth knowing. Because each partition has its own indexes, some databases limit what a unique constraint can enforce. In PostgreSQL, for example, a primary key or unique constraint on a partitioned table must include the partition key columns, since the database can only check uniqueness within each partition.

Partitioning also helps with data lifecycle management, which means deciding what happens to data as it ages.

Suppose we store application logs and keep only the last twelve months. Every month, the oldest month of logs needs to go. Without partitioning, that means a large DELETE:

If that removes 100 million rows, the database has to find and delete each one. That generates a large volume of transaction log, consumes I/O and CPU, and can run for a long time. In some databases, the deleted rows also leave dead space behind that needs cleanup afterward.

If each month is stored in its own partition, we can remove the whole month at once:

Instead of deleting rows one by one, the database removes the partition's underlying files. This is usually much faster, because the cost no longer depends on the number of rows.

The same idea helps with archiving. A detached partition does not have to be dropped. It can be moved to cheaper storage, exported to a data warehouse, or kept read-only while recent partitions continue to receive writes.

Smaller partitions also make routine maintenance easier.

Instead of rebuilding an index across a table with billions of rows, the database can often work on one partition at a time. Each operation is smaller, finishes sooner, and affects less of the table while it runs.

A few other tasks benefit in the same way:

This matters most for append-heavy workloads such as logs, events, transactions, and time-series data. In those tables, new rows arrive constantly, old rows rarely change, and the split between "active" and "historical" partitions is clear.

So far, partitioning has looked like a clean improvement. But it only works well when the partitions themselves are well designed.

Partitioning can still create problems if the data is not distributed well.

Suppose we partition users by region. One partition contains 10 million rows. Another contains 20 million. But one large region contains 600 million rows.

That one partition holds over 95% of the data. Queries for users in Region C scan a partition almost as large as the original table, so they get little benefit from partitioning. Maintenance on that partition, such as rebuilding its indexes, is also far more expensive than on the others.

This is why partition boundaries need to be chosen carefully, based on how the data is actually distributed. Sometimes a large partition needs to be split further as the dataset grows. Region C could be divided into its own sub-partitions, for example by country or by a hash of the user ID, while the smaller regions stay as they are.

The opposite mistake is also possible: creating far too many partitions.

Suppose we partition a table by minute. That is 525,600 partitions per year. After two years, the table has more than a million partitions.

At that point, the costs start to outweigh the benefits:

So partition granularity matters. The right size depends on how much data arrives and how the application queries it.

A useful check is to look at the most common query. If it asks for "the last 7 days", daily partitions let the database read 7 partitions. Hourly partitions would mean 168 of them for the same query, with little extra pruning benefit.

Partitioning and sharding are closely related ideas, but they are not always the same thing.

Partitioning keeps everything on one server, so it cannot add more CPU, memory, or disk. It makes a large table easier to work with. Sharding adds machines, so it can grow capacity, but it also brings cross-server queries and distributed coordination.

In practice, a system may use both. The data is sharded across several servers, and the tables inside each shard are internally partitioned.

Here, each shard is a separate database server holding half of the users. Inside each shard, the events table is partitioned by month, so both sharding and partitioning benefits apply at the same time.

Partitioning becomes useful when a table grows large enough that its size starts causing query or operational problems.

It works especially well when the data has a natural grouping. Time is the most common example. Logs, metrics, events, orders, and transactions are usually queried by time range, and old data in these tables is rarely updated.

Partitioning also helps when large amounts of old data need to be deleted or archived on a regular schedule.

Small tables usually do not need partitioning. A table with a few million rows and good indexes is often fast enough, and adding partitions brings extra design work, more objects to manage, and new rules around keys and constraints.

So before partitioning a table, ask:

If the answers are yes, partitioning is likely to help. If most of them are no, better indexes or query tuning may solve the problem with less complexity.

Database partitioning splits one logical table into smaller physical pieces called partitions. The application still queries a single table, while the database decides which partition each row belongs to.

Horizontal partitioning divides rows and is what most people mean by partitioning. The three common strategies are range partitioning for dates and numeric ranges, list partitioning for known categories, and hash partitioning for even distribution.

The main performance benefit is partition pruning. When a query filters on the partition key, the database reads only the partitions that can contain matching rows. This makes the partition key the most important design decision, and it should match the way the data is queried most often. Partitioning works alongside indexes rather than replacing them.

Partitioning also simplifies data management. Old data can be dropped or archived one partition at a time instead of through large DELETE operations, and maintenance can run on one partition at a time.

It has its own pitfalls. Uneven partitions leave one piece nearly as large as the original table, and too many partitions add metadata and planning overhead with little benefit.

Partitioning is different from sharding. Partitioning usually stays within one database server, while sharding spreads data across many servers. Large systems often use both.