A Deep Dive into Table partitioning 🐰 Part 5: Partitioning an environment not individual tables

By Derk van Veen, Database Engineer, Adyen

If we had known better, this would have been the first blog post about partitioning to publish. The reality is, the first article was published two years and two weeks ago, as I write these opening sentences. So this article will not only give insight into the most important decisions you will have to make when considering your partitioning planning, but as a bonus, it will give some insights into how much it takes to write an article like this.

One of my values is sharing knowledge and giving back to the community. I share what I learn and take people along on my journey and investigations into the problems we’ve faced. For this reason, the previous articles have documented these learnings, and this one is about a lesson we learned a little too late.

The Journey so far

Up to this point:

• We’ve shared the what, why and when to partition in the first blog post.
• In the second article, we covered how to maintain partitioned tables, as they provide some additional challenges when it comes to adding/removing partitions and managing indexes and foreign keys.
• Only in the third article did we disclose why we decided to work on our own partitioning framework: the existing partitioning solutions didn’t provide us with the tools to minimize locks on tables.

Our databases are always up and pressured, and the locks around partitioning were simply impossible to deal with. The key lesson from this article was that the default partition is the root of (almost) all evil regarding table partitioning in PostgreSQL.

• In the last article, we shared a cool solution to a problem we faced, where the default partition actually became the hero of the story. Yes, default partitions are usually the root of partitioning evil, but they also provide a welcome exception to the rule. Just be very careful with its usage.

The Journey Continues…

What ingredients can be missing after reading these four articles? You already know all about partitioning tables by now: You know why, how, and what it takes to partition and maintain your partitioned tables.

The problem is precisely this: you know all about partitioning individual tables. Many tables are not standalone tables. They live in a relational database and are tightly coupled to information in other tables. That is why this article should have been the first one: it explains how to partition your tables with respect to other tables in your environment. At the end of the day, it’s not about partitioning individual tables; it’s about partitioning all/many tables in conjunction with each other to reflect the key design of your database. It’s only when you partition your tables as part of a larger plan that performance will not be impacted too badly.

Two years ago, I wrote that partitioning is good for performance. Today, I disagree with the younger version of me. Partitioning a single table in your database might improve performance; that’s correct. But when you start joining partitioned tables, performance is likely to get worse — possibly even terrible — and will probably deteriorate with every partition you add.

Starting all over again: why, what and how

I still have nightmares about incident number 2016. We were preparing for one of the most critical seasons for our merchants — December shopping– when the CPU unexpectedly spiked. We realized that automatically adding one more partition to an already partitioned table would bring one of our most critical databases to its knees, as CPU usage would grow over 100%. The root cause? We partitioned individual tables, not the environment.

There are three reasons to do partitioning

1. Physical limitations: A table can’t grow beyond 32 TB
2. Maintenance
3. Data life cycle management

I will state here: Only range-based, native partitioning fulfills all these needs. Range-based, native partitioning checks all the boxes. You can prevent a single table from reaching 32TB in size, but you should stop around 100GB and start partitioning there.

You can create a very large number of range-based partitions, keeping maintenance tasks like vacuuming under control. Range-based partitions can always be added when they are part of some practically infinite range. List and hash-based partitions don’t fulfill this requirement.

Data life cycle management is probably the easiest one to glance over, but I would argue it’s the most important one with respect to business continuity. Partitioning opens the door to easy data life cycle management, saving both you and the engineers lots of time. With range-based partitioning, there is an easy way to drop all your data when it reaches a certain age– you just detach the partition from its parent: no more cleanup jobs, no massive WAL generation, just a simple catalog operation.

After reading the first article, partitioning your tables on an integer range should not be difficult. In this article, and for simplicity, we’ll assume that you are using an integer-based primary key.

If you are about to start with partitioning, I recommend you investigate partitioning on a uuidv7 column type, including in core PostgreSQL from version 18. This partitioning type gives you all the benefits of integer based partitioning and date range partitioning. In this article, we won’t elaborate on uuids, but if you are interested, you can watch my talk from POSETTE 2025 where I explain how to use uuids and partitioning. There is also an excellent talk from Chris Ellis about uuid’s in general from the same conference.

Partition your environment, not your tables

Your partitioning journey should start with one important question: What is your leading figure? The leading figure is the unit that ties all your data together, the center of the spider’s web, the sun of the solar system.

As we at Adyen are processing financial transactions, a natural leading figure would be the transaction. All the data in this database is centered around transactions.

When it comes to partitioning, you must partition all your tables on the same leading figure. Let me emphasize the crucial words here: When it comes to partitioning, you must partition all your tables on the same leading figure.

Yes, all tables, no exceptions.

But if the transaction has to be part of all tables, doesn’t this violate the normalisation rules? Yes, it does. You can’t make an omelet without breaking some eggs; normalisation is the first one that should break.

In the above example, TxnLedger would be an associative table between a transaction and a ledger. From a partitioning perspective, that is a problem, as we can’t partition the ledger table on transactions.

This observation brings us to the heart of the matter. There is only one efficient way of joining partitioned tables: you have to access only the partitions required to get your data, and not a single partition more. For an individual table, this is easy for the database, but it’s way more complicated for joining.

From the image example above, assume you must join transactions with ledgers. When you need transaction 1500, you immediately know it lives in the second partition based on the partition boundaries. To find the related ledgers, you would go to the associative TransactionLedger table and collect the required primary keys for the ledgers. But as there is no way at either planning or run time to guarantee which partitions your ledgers live in, the database would have to scan all ledger partitions. When your ledger table only consists of two partitions, you would hardly feel the difference. But by the time the ledger table contains 40 partitions, and you join this with the ledgerLine table, which also contains 40 partitions, you are suddenly checking 80 individual tables. And that will drastically hurt performance. This is not only because you are doing 80 individual index scans, but also because you are holding 160 locks at both planning and execution time.

LockManager

In PostgreSQL, the first 16 locks of your transaction are very cheap. The other locks go through the (slower) LockManager. When you join the unpartitioned tables Transaction, TransactionLedger, and Ledger, you need 6(six) locks: three for the table objects and three more for the indexes. That is well within the 16 fast locks. Joining partitioned tables allows you to exceed the 16 fast locks very easily. Gitlab has a very interesting publication about how exceeding the fast path locks can escalate: https://gitlab.com/gitlab-com/gl-infra/observability/team/-/issues/2301.

When you access all partitions of a table in a query, you will likely exceed the number of fast path locks, and LockManager overflow will likely occur.

This brings us to why it’s necessary to partition all tables on the same leading figure.

If we denormalise the ledger table, joining transactions with ledgers becomes easy. As soon as we know the transaction lives in the second partition, all the ledgers must live in the second partition of the ledger table, as the ledger table is now partitioned on the same column as the transaction table.

Pros and cons

Although joining becomes much easier, the implications are significant. First, this affects the application. The ledger table can now have multiple entries with the same transactionId, impacting all queries on this table. Not only the reads, but also the updates might hit multiple rows instead of a single one.

The new ledger table will also be bigger than the older one. Normalisation guarantees a single entry for the ledger and has the TransactionLedger table resolve the n-to-m relationship. Now, the n-to-m relationship results in multiple entries in the ledger table, all columns equal except the newly added transactionId.

If the impact is so big, why go through all this hassle? As already mentioned, the impact on the CPU usage was gigantic. It’s hard to put a concrete percentage on the difference because the workload changes over time. Still, it’s definitely the difference between drowning the CPU and having a healthy profile over time. Even more importantly, CPU consumption doesn’t increase every time we add a partition to a table. This is not a joke; we used the CPU profile to check on what days we created new partitions.

If the CPU story didn’t convince you, I might have an even more compelling argument. The query performance difference for queries joining the transactions with the ledgers is a factor of almost 70! The denormalised situation is almost 70 times faster than the setup with the two different partitioning schemes with the associative table in the middle.

More boundary conditions

Hopefully, I have convinced you by now to partition all your tables on the same column, even at the cost of denormalising your tables and changing application logic. If you have come this far and you have started testing your queries, you will see that very little has actually changed.

Partition alignment

In the above example, we used the same partition boundaries for Transaction and Ledger. This was no coincidence. To efficiently join partitioned tables, you need to meet two requirements:

1. Both tables should be partitioned on the same column
2. The partition boundaries have to match

The first requirement is not very strict. The column name can be different, but it needs to be the join column. I never tested it, but it’s safe to assume the data type must also be the same. There is no implicit casting.

The second requirement is very strict. The lower and upper boundaries have to be one-to-one matches. But don’t despair if your partition boundaries are currently not matching. You can align your partitions from now on, and the performance gains would be applicable from that moment onward, as long as your query doesn’t need unmatched partitions.

In the above example, you can get better query performance as soon as your queries no longer need data below the 2250 boundary.

PostgreSQL settings

When you align your partitions at this moment and start testing, you wouldn’t see the full extent of the improvement yet. The query plan would show you are accessing only the third partition for the Transaction table, but all the partitions for the Ledger table. That is because pruning is automatically applied to the first table, but not to the other tables in your query when the where clause differs from transaction.transactionid = ledger.transactionid.

If you want optimal partition pruning for queries with a more complicated where clause, like where transactionId in (1,2), you have to change a PostgreSQL setting: enable_partitionwise_join.

This setting changes the optimizer’s behaviour from looking at partitioned tables to assessing every partition as an individual table. This requires more CPU usage, and doesn’t provide any benefits unless you have aligned partitioned tables. That is why it’s turned off by default. But after we put in all the effort to denormalise tables, change application logic and align partitions around the leading figure, we are more than happy to pay this little extra amount to get optimal query performance!

With enable_partitionwise_join to off we would (index) scan all the ledger partitions.

While when we set enable_partitionwise_join to on we would only (index) scan the ledger partitions that might hold the data we are looking for.

I recommend you play around with this setting and really master how it influences the query behaviour in your environment.

Summary

As soon as one of your tables reaches 100GB or more, it’s time to think about partitioning. When you get to 100GB, you will also grow to TB-size tables, and then it’s definitely too late to start partitioning.

Partitioning helps you to:

• Prevent hitting the physical table size limit of 32 TB
• Keeps maintenance tasks under control
• Manage the natural data life cycle

Partitioning doesn’t evolve around partitioning individual tables; but it’s about implementing partitioning correctly over the entire database.

In an environment where the data volume forces you to use partitioning, there is only one good way: Range-based, native partitioning for all involved tables around your leading figure. The partition boundaries must be aligned to make efficient query planning and joining possible. Only after aligning all your tables around the leading figure with matching boundaries is it time to set enable_partitionwise_join to on and truly leverage the benefits partitioning has to offer you.

After working extensively with partitions over the last three years, I will no longer say partitioning is good for performance. If you were to ask me today, I would tell you this:

“The environment dictates the requirement of partitioning. Until then, don’t do it; it’s complicated. If you have to do it, do it in the best possible way or performance problems will definitely hit you on the way. If you partition your environment well, the performance problems might not be so bad”.

After partitioning your tables, you automatically open the door for some very cool data life cycle management options. I briefly mentioned them already in my POSETTE 2025 presentation, Leveraging table partitioning for archiving, but my skills are not yet at a level where I can write an article about it. As soon as I have figured it all out, I will definitely write and talk about it.

I wish you all the best with your partitioning journey, and reach out if you need help.

Special thanks

For anyone who thinks I’m a good writer, it’s worth noting that up to this point, I’ve had great help from my colleague, Amy Mbaegbu. She always suggests a ton of small corrections that make the articles so much better to read. Unfortunately, she is now moving on to a new position within the company and will no longer be able to help me out.

I want to finish today with a very big “Thank you, Amy”. It was a pleasure working with you on all the articles I’ve published so far, and I’m really going to miss your understanding of both language and technique..

Fortunately, you’ll be staying on as a colleague, and I hope to work with you again in the future.