Partitioning: A Salvation, Or Another Problem?
Partitioning is one of the methods used to improve database performance. Especially when working with large datasets, it seems appealing for shortening query times and simplifying administrative tasks. However, this magic wand may not always deliver the expected results. In some cases, the complexity and cost introduced by partitioning can outweigh the benefits gained. In this article, drawing from my real-world field experiences, I will delve into when database partitioning makes sense and when it should be avoided.
While working on a production ERP system, the order history table had reached hundreds of millions of rows within a few years. This situation significantly slowed down reporting queries, as well as new order insertion and update operations. Even simple queries like SELECT COUNT(*) could take minutes. It was at that point that we had to seriously consider partitioning. However, determining the right strategy took us weeks, not just immediately.
Why Partition? What Are Its Benefits?
The primary motivation for partitioning is often performance improvement. Instead of scanning an entire large table, a query scanning only the relevant partition can dramatically reduce I/O. This is particularly evident in queries that retrieve data belonging to a specific time period or category. For example, when you query sales for the last month, the database only scans the partition corresponding to that last month.
Furthermore, data management becomes easier. When you want to archive or delete old data, managing only the relevant partition is much faster and more efficient than processing the entire table. For instance, if you keep each month’s data in a separate partition, you can clear January’s data by simply using the DROP command on the January partition. This operation is many times faster than deleting millions of rows one by one.
Another significant benefit is that maintenance operations are faster and less impactful. Operations like VACUUM and REINDEX can be run only on specific partitions instead of the entire table. This helps shorten maintenance windows, especially in busy systems. In fact, some database systems even allow different maintenance strategies to be applied on a per-partition basis.
Real-World Scenarios: Successful Partitioning Implementations
In my experience, the areas where partitioning is most successful are typically time-series data or large datasets that can be clearly separated geographically or categorically. For example, sensor data in an IoT platform sees incredible query performance improvements when partitioned hourly or daily. Similarly, order data on an e-commerce site, when partitioned monthly, speeds up both reporting and operational queries.
In a telecommunications project I worked on last year, the Call Detail Records (CDR) table contained terabytes of data. When this data was partitioned by month, the runtime for reports analyzing calls within a specific month decreased from 4 hours to 15 minutes. Moreover, the process of archiving old call records could be handled by simply DETACHing a few partitions.
RANGE is the method I reach for most often in the field, but it’s not the only option. PostgreSQL offers three basic partition types, and choosing the right one depends on how the data is distributed and what the queries filter on. Use RANGE for date or numeric ranges; LIST for a finite, distinct set of values (country code, region); and HASH when you want to distribute data evenly.
In LIST partitioning, data is divided based on the exact values the partition key takes. It works for data that splits cleanly along geographic or categorical lines:
-- Partition customers by country code (LIST)
CREATE TABLE customers (
customer_id SERIAL,
name TEXT NOT NULL,
country_code TEXT NOT NULL
) PARTITION BY LIST (country_code);
CREATE TABLE customers_us PARTITION OF customers
FOR VALUES IN ('US');
CREATE TABLE customers_eu PARTITION OF customers
FOR VALUES IN ('DE', 'FR', 'UK');With this structure, a query with WHERE country_code = 'US' scans only the customers_us partition. I used this pattern on a global service provider’s customer data; region-based reports returned without ever touching data from other continents.
HASH partitioning, on the other hand, distributes data evenly across a fixed number of partitions based on the hash value of a column. It makes sense when there’s no natural range or category, but you want to balance the load:
-- Distribute sessions across 4 partitions by user_id hash (HASH)
CREATE TABLE sessions (
session_id SERIAL,
user_id INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY HASH (user_id);
CREATE TABLE sessions_p0 PARTITION OF sessions
FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
FOR VALUES WITH (modulus 4, remainder 1);
-- ... and likewise for remainder 2 and 3HASH’s appeal is its balanced distribution; that’s also its trap. Because it scatters data randomly, queries that filter on a specific key can’t benefit from pruning — I’ll come back to this point shortly with a concrete story.
Another successful use case is user-based data separation. Especially in multi-user systems, keeping each user’s data in a separate partition can improve both performance and security. However, this approach requires careful planning as it can significantly increase the number of partitions.
Costs and Disadvantages of Partitioning
Like any technology, partitioning comes with a cost. The most prominent disadvantage is the increased management complexity. Creating, managing, and maintaining partitions is more cumbersome than dealing with a single large table. Setting up automated partition creation and cleanup mechanisms becomes almost mandatory.
For example, in one of our client’s systems, we were creating daily partitions. Initially, everything was fine. But one day, our cron job failed to trigger the VACUUM operation, and the number of partitions in our system suddenly jumped from 500 to 5000. This quickly consumed the database server’s memory, leading to system instability. It took us several hours to resolve the issue and required us to make our automated cleanup scripts more robust.
There’s an often-overlooked dimension of this cost that plays out at the operating-system layer. In PostgreSQL, each partition is a separate file on disk; once you add its indexes and TOAST tables, multiple files are opened per partition. On a project where I managed session data for millions of users with date-based partitions, each new day meant a new partition, and within a few months the table count reached the hundreds. One day, queries started failing in a strange way; the cause wasn’t the database but the server’s open files limit — we had hit the ceiling on open file descriptors.
When I looked at the PostgreSQL process with ls -l /proc/<pid>/fd/, I saw thousands of file descriptors held by these partitions and their indexes. Each partition also consumes an inode on the file system; a large number of small partitions can even lead to inode exhaustion on low-I/O storage. The fix was two-pronged: raise the open-files limit with ulimit -n (and LimitNOFILE in the systemd unit file), and more importantly, revisit partition granularity — moving from daily to weekly to cut the file count. Looking for the cost of partitioning solely inside the database is misleading; part of the bill is paid in kernel resources.
Another significant cost is the increased complexity of query optimization. Partition pruning (selecting the relevant partition) may not always work perfectly. If your query is not written appropriately for the partition key, the database might unnecessarily scan multiple partitions. This can lead to a performance decrease instead of an increase. Especially JOIN operations involving partitioned tables can become more challenging from a planning perspective.
When to Avoid Partitioning?
Partitioning is not a panacea. If your table size is manageable (e.g., a few million rows) and your queries are generally fast, trying to implement partitioning might introduce unnecessary complexity. A simple INDEX strategy or well-written queries can often provide more benefits than partitioning.
Another scenario is when data cannot be logically divided by a distinct partition key. If your data is randomly distributed, or if your queries generally scan the entire table, partitioning loses its meaning. In fact, in such cases, your queries might run slower because the database may have to switch between partitions.
Furthermore, resorting to partitioning in database systems where it is not supported or poorly implemented is risky. While systems like PostgreSQL, MySQL, and Oracle support partitioning well, in simpler databases or those with different architectures, this feature might be absent or negatively impact performance.
I know this mistake from the field, not from theory. Two concrete cases illustrate well how choosing the wrong partition type backfires.
The first was a table holding user settings. To distribute the load evenly across servers, we used HASH partitioning on user_id — flawless on paper. But the application’s actual query pattern didn’t make use of it: most queries, instead of fetching all settings for a single user, retrieved settings for various users together. Because HASH scatters data randomly, every query spread across multiple partitions, and the planner couldn’t prune. The result: performance didn’t improve, and in some queries it flatly dropped. The lesson is clear — HASH’s balanced distribution becomes a hindrance, not an advantage, in targeted queries that filter on a single key. We later moved this table to RANGE.
The second was a mobile application’s user activity logs. We had partitioned the logs by day; querying individual days was fast. But the application’s most frequent query was “last 7 days,” which meant scanning 7 separate partitions every time — eating up most of the pruning gain. We considered switching to weekly or monthly partitions, but that would have complicated the archiving process. In the end, without touching the partition scheme at all, we solved it by precomputing the “last 7 days” view as a materialized view; the hot query now hits a single object, and the cross-partition scan disappears. Partitioning isn’t the first and only answer to every problem; sometimes it needs a complementary layer.
Finally, if your system has a very short data retention period (e.g., a few days or weeks), the administrative overhead of partitions might outweigh the performance gains you achieve. In such cases, a simple table or time-based indexing might be a more suitable solution.
Partitioning and Query Optimization: Complementary Elements
When partitioning, you must ensure that your queries are written to benefit from partition pruning. This means using WHERE or JOIN conditions in your queries that include the partition key. For example, if you have partitioned by the sale_date column in PostgreSQL, your queries should use conditions like WHERE sale_date >= '2023-01-01' AND sale_date < '2023-02-01'.
Analysis tools allow you to see which partitions your queries are scanning. In PostgreSQL, the EXPLAIN ANALYZE command shows the query plan and scanned partitions in detail. You can use this information to optimize your queries and prevent unnecessary scans.
Remember that partitioning is not a standalone solution. A good indexing strategy, proper database server tuning, and efficient queries maximize the effect of partitioning. These complementary elements provide significant advantages when working with large datasets.
Partitioning Cost: An Evaluation with Numbers
The cost of partitioning should not be considered solely in terms of disk space or CPU time. Administrative effort, the likelihood of errors, and the learning curve are also part of this cost.
For example, for one of my clients, I developed a special Python script to manage the number of partitions. This script ran every night, deleting old partitions and creating new ones. The development and testing of the script took approximately 2 days. Additionally, I regularly need to allocate time for maintaining the script and troubleshooting potential errors. While this isn’t directly billed as a cost item, it adds to the overall project cost.
The most concretely I ever measured this burden was on a manufacturing company’s inventory-movements table. After moving to monthly partitions, queries sped up by 30-40% — that was the expected gain. But the script that created a new partition and archived the old one at each month’s end pushed our weekly maintenance window from 2 hours to 5 hours. That’s not just engineering time; it also lengthened the period the system was inaccessible during maintenance. This line item usually doesn’t show up on partitioning’s invoice, yet it’s paid regularly, every month.
It’s worth clarifying a distinction that often gets confused here: partitioning splits a table into physical pieces on a single database server. Sharding, by contrast, distributes the database horizontally across multiple servers. Partitioning relieves the I/O and management load of one machine; sharding genuinely scales both reads and writes but loads distributed-system complexity (cross-shard queries, rebalancing, consistency) onto your back. For most “my table got too big” problems, the right first step is partitioning; you move to sharding only when a single server truly isn’t enough.
From a disk space perspective, partitions themselves can create overhead. In PostgreSQL, each partition might have its own TOAST table and indexes. This means additional disk space beyond the data itself. While this overhead is negligible for small partitions, it can make a difference with a very large number of partitions.
Another cost item is query planning time. More partitions mean the database query planner has more options to evaluate. While this difference is generally negligible for a simple query, in complex queries or high-transaction-volume systems, it can lead to increased query planning time. In one of my studies, I observed that when the number of partitions exceeded 1000, the planning times for complex queries increased by 10-15%.
Maximizing the Benefits of Partitioning
There are several points to consider to get the most out of partitioning. First, choosing the right partition key is vital. This key should be usable as a filter in most of your queries. Generally, date-based partitioning (daily, weekly, monthly) is one of the most common and effective methods.
Second, it’s necessary to optimize partition size. Neither too small nor too large partitions are ideal. Very small partitions increase management overhead, while very large partitions reduce the performance advantage. Typically, a partition size is aimed to be kept between a few gigabytes and a few hundred megabytes. However, this can vary depending on the database system and hardware.
Third, automation is essential. Using scripts or tools that automate partition creation, cleanup, and maintenance operations significantly reduces administrative overhead. The reliability of this automation is also critically important.
Finally, partitioning should be viewed as an architectural decision, not just a performance tweak. Considering partitioning when designing your data model is more effective than interventions made later. If your data volume is expected to grow significantly in the future, planning a partitioning strategy from the outset can prevent major problems down the line.
Conclusion: Partitioning is Valuable When Used Wisely
Database partitioning is a powerful technique that, when implemented correctly, significantly improves performance and manageability when working with large datasets. It is particularly well-suited for time-series data, large log tables, or data sets that can be categorically separated. However, this power also comes with a cost: increased administrative complexity, potential query optimization challenges, and development/maintenance effort.
In my own experiences, I’ve seen that situations where the benefits of partitioning outweigh its costs are typically scenarios where data can be logically divided into distinct pieces, and there is a frequent need to access these pieces. If your table is not yet “large” or most of your queries scan the entire table, it might be wiser to focus on simpler solutions instead of partitioning.
It’s important to remember that, like any technological solution, partitioning creates value only when applied in the right context and in the right way. Otherwise, instead of solving a simple problem, you might create new, more complex, and costly problems. Therefore, before implementing partitioning, a detailed analysis of your current situation, data growth expectations, and query patterns will enable you to make the most informed decision.