Determining the Optimal Postgres Partition Size

Written by Carlota Soto and James Blackwood-Sewell

We’ve been exploring the topic of Postgres partitioning and how it can help you scale your large PostgreSQL databases, both in terms of performance and operational management.

Today, we wanted to address the main question that always arises when designing a database partitioning strategy: "How large should each partition be?"

The true answer is a big “It depends.” Frustrating, I know. It is extremely hard to give advice on the topic because almost anything we say could be contradicted by the personal experience of a personal use case—that’s how circumstance-dependent this is. The best option will always be to test, test, test.

But we feel you: where should you even begin? To provide you with some guidance, we’ve put together some very general advice on the topic that (we hope) will point you in the right direction on what to look for.

This said, we know for sure some of you will have personal anecdotes that directly contradict our advice—please, share them with us on Twitter/X! We’d love to hear your stories and help expand our knowledge as a community. It’d be awesome to update this post in the future with your advice. 🔥

Until then, let's dive into the nuanced world of Postgres partitioning to help you determine your optimal partition size when using range (or time-based) partitions!

This advice applies to PostgreSQL native partitioning, pg_partman, and Timescale (where partitions are called chunks).

Why Create a Postgres Partition?

Before you embark on your data partitioning journey, it's essential to comprehend the 'why' behind partitioning. This is worth revisiting. Essentially, partitioning in PostgreSQL divides tables into smaller, more manageable chunks. Typically, a single column, known as the partition key, determines how data is allocated among the partitions.

This partition key is crucial for ensuring data is correctly routed to the appropriate partition and must be part of any unique indexes or primary keys that span all partitions. While the primary table serves as a conceptual umbrella, the actual data resides within the individual partitions, each of which can have its own indexes.

In theory, by breaking down a large table into smaller partitions, you can benefit from improved query performance, optimized index sizes, and more efficient data maintenance operations, keeping your large-scale databases more agile and responsive overall.

But partitioning isn’t always the best choice: its effectiveness will greatly depend on the characteristics of your particular use case. Before moving on with implementing partitioning, make sure to check out our article on when to consider PostgreSQL partitioning.

If your ingestion rate is too light, your data access patterns are uniform, you do frequent full scans, and you don’t delete data regularly, there’s a great chance that partitioning is not the best solution for you.

Why Your Postgres Partition Size Matters

Getting the right partition size (for example, one day or eight hours) is incredibly important. When tuning partition sizes, our best general advice is to strive for a good balance between too small and too large. If you end up with too many partitions, your queries will suffer from a long planning time. If you have too few, this could completely negate the benefits of partitioning.

If you set your partition size too large, you will not have enough partitions to see any benefits.

The main goal of partitioning, especially if you’re trying to improve query performance, is to reduce the amount of table or index data you have to read for each query. If your partitions are too large, you would still be scanning a large amount of data you don’t need to, and any potential performance improvements would be negligible.

If you set your partition size too small, you will soon have too many partitions.

“Let’s then set up smaller partitions,” you may be thinking. But not so fast. Smaller partitions might seem efficient, especially when thinking of data retrieval, but it’s key to remember something: partitions are essentially treated as individual tables in PostgreSQL. When you query data spanning multiple partitions, PostgreSQL must plan how to access each relevant partition.

If your partition sizes are tiny, and you frequently run queries that span many partitions, Postgres will take much longer to plan that query (sometimes longer than it takes for the query to run). Ideally, PostgreSQL should prune (ignore) irrelevant partitions and only target relevant ones, but if your partitions are too small, the pruning effect disappears.

On paper, this inefficiency reaches a tipping point: once a query touches beyond a certain number of partitions (12 by default), PostgreSQL switches to using a genetic algorithm to optimize planning time. But as the number of partitions continues to rise, even this adaptive algorithm faces challenges. Smaller partitions aren’t the answer to all your problems!

If your partition size doesn't align with the typical query range, Postgres might need to scan many tables for each query.

For example, imagine that you've partitioned your data such that each partition contains data for a one-hour window, but you’re typically pulling data on a monthly basis. This scenario would force PostgreSQL to scan approximately 730 individual partitions just to satisfy a single month-long query.

This extra planning and scanning time is computationally expensive, leading to slower response times and inefficiencies. Imagine the overhead when you make multiple such queries a day. Not good.

If the working set of data from partitions (and their indexes) don’t fit in memory, you’ll be reading from disk more often.

Most production workloads have a pattern where the most recent data is accessed more frequently—think of graphs showing the latest metrics or analytics for the past day or week. If the partitions holding this recent data (and their indexes) are not readily available in memory, querying will be slower. Ingest speed can also be degraded since if the relevant indexes aren't in memory, the system must fetch them from the disk, update them, and then potentially write them back to the disk.

If you don’t align your partition size with your retention settings, you will not be able to remove old data by truncating tables.

Imagine you've set up your partitions by week, but your data retention policy dictates that once data crosses a one-year threshold, it should be removed daily. If your partitions are segmented weekly, there's no straightforward way to just trim a single day's worth of old data. If you attempt to remove data day by day, you'd essentially be breaking into each week-long partition repeatedly.

Choosing Your Postgres Partition Size: (Very) General Guidelines

As you can see, there is no magic setting for partition sizing: you have to look at your own use case and find the right balance. This is our most general advice, which should work for most cases, but follow it with a grain of salt.

If you hope to remove old partitions by implementing a retention policy, then the amount of data you’d like to drop at a time should be a multiple of your partition size.

Aligning your partition size with your data retention strategy will make things so much easier, both from the management and performance perspectives. For instance, if your policy deletes data older than a year on a monthly basis, consider having monthly partitions. This will allow you to efficiently drop data by truncating and removing entire partitions rather than using costly and performance-intensive DELETE operations.

If your application frequently reads the most recent data, you might want to size your partitions to accommodate the most commonly accessed data range.

For instance, if the bulk of your queries pull data within the last week, weekly partitions might be optimal. This can be a frequent query pattern in real-time applications, where the most important thing is to get fast performance for the most recent data.

However, it is often impossible to reduce your entire query pattern to one timeframe for other applications. Just be mindful of how many partitions your most frequent queries are touching, and make sure they’re not too many.

Try to fit your most recent data in memory.

Your performance will be optimal if your commonly accessed data fits in memory, removing the need for additional disk I/O. PostgreSQL uses memory from its shared_buffers pool for caching (which is usually recommended to be 25 % of your system's memory).

A good rule of thumb is to ensure that a single partition, along with its indexes, from each partitioned table, comfortably fits within the shared_buffers, especially if you’re dealing with an application for which the most recent data is the most frequently accessed/the one for which performance matters the most (e.g., real-time IoT).

If you’re adding data chronologically, your partition size should at least be able to accommodate the volume of data you typically ingest in a single transaction.

By doing so, you will ensure that data from one operation primarily lands in one or two partitions, streamlining writes and reducing fragmentation.

Rolling Up Your Sleeves: Practical Steps

Hopefully, now you’ll have a good picture of how your partition size interacts with your database. But as we’ve already mentioned, there’s truly only one way to determine which partition size is more optimal for your use case: testing. We can’t emphasize it enough.

Your database isn't a theoretical playground; it's a living entity with real-world demands. So, set up a testing environment. Simulate your workloads with various partition sizes and measure query performance, maintenance task durations, and disk and memory usage. Armed with this data, you can make an informed decision on the ideal partition size.

Here’s a quick guide on how to run the testing:

1. Establish a testing environment. Ideally, you want a staging environment that mimics production as closely as possible, so use a copy of your production database. Managed databases like Timescale make it easy to duplicate production databases for testing.

2. Identify key operations. List down the most frequent queries, insert operations, and maintenance tasks your database undertakes. This will help you simulate real-world scenarios.

3. Define different partitioning strategies you’d like to test. If you’re partitioning by time, for example, you may want to experiment with daily or weekly partitions. You can also experiment with range, list, and hash partitioning to see which aligns best with your data patterns.

4. Benchmark. Record query execution times, insertion times, and other performance metrics for each partitioning strategy. Execute tasks like VACUUM, ANALYZE (we recently built a BPFtrace program to monitor the former), and back up operations to see how different partitioning strategies influence their performance.

5. Analyze results. Look for partition strategies that provide the best performance consistency, especially during peak loads. Determine which partition strategy offers the smoothest maintenance operations.

6. Iterate. Based on the results, adjust partition sizes or types and rerun tests to refine your strategy.

If you’re a Timescale customer, we’ll be more than happy to give you personalized advice during this entire process. Just reach out to us.

Adjusting the Sails

One last important thing: what's optimal now might not remain so. As your application evolves, data growth patterns and query behaviors might (or will) change. It’s incredibly useful to get into the practice of regularly monitoring your partitioned tables—as soon as something starts going off the rails, you’ll be able to course-correct quickly instead of letting the problem grow and attributing its cause to something else.

How can you set this up? Fortunately, PostgreSQL offers tools and commands to help you adjust your partitioning strategy without significant downtime, so leverage them as needed:

• Use tools like pg_stat_statements or pgBadger. They provide insights into slow queries, dead rows, and other metrics that can signal inefficiencies.

• Monitor metrics like the number of partitions, partition size, partition creation and deletion, frequently accessed partitions, frequency of queries that don’t benefit from partition pruning, index, and table bloat, etc.

• Pay special attention to disk I/O operations to ensure that the partitioning strategy isn't causing excessive or inefficient I/O patterns.

• Keep an eye on CPU spikes or increased memory usage, which could indicate inefficient data access patterns caused by misconfigured partitioning.

• Track operations like VACUUM, ANALYZE, and REINDEX on your partitions to get insights into your partition health and performance.

Before We Part Ways

Partitioning is one of the most potent tools in the PostgreSQL toolkit. Its efficiency hinges on the correct implementation, with partition size playing a pivotal role. Unfortunately, there’s not a single answer that works for everyone in terms of the optimal partition size: understanding your data, testing rigorously, and staying adaptive is the best path to success.

To all developers venturing into PostgreSQL partitioning for the first time: happy partitioning! We hope this advice was helpful.

If you’re partitioning by time, make sure to check out Timescale—it will simplify your partitioning journey considerably. With hypertables, you’ll get fully automated partitioning by time.

Timescale also comes with features aimed to ease maintenance operations in your partitioned tables, like data retention policies, and it also makes it much simpler to achieve optimal query performance via an enhanced query planner and features like continuous aggregates.