A Deep Dive into Table Partitioning part 1 🐰: Introduction to Table Partitioning

Payment processing technology enables everyone make payments everywhere; whether they are online or in a store, and nowadays it is part of our daily life. Payments are meant to be as easy as possible, both for clients and merchants: we all want hassle-free payments by a single tap of a card or a phone, or by seamless checkout on our favorite website. However, things are not as easy behind the scenes.

Many engineering challenges come with processing payments globally, and Adyen is not short on these. Besides high performance and high availability across the globe, we also have to tackle database storage challenges due to the high volume of data.

At the scale we operate, our engineers have to apply future proof database techniques that will ensure a healthy and steady growth of our database clusters, and the solutions are far from easy.

Table partitioning is one of such techniques we are applying to our tables to help us with stable storage growth, high performance, and easy maintenance. Given the volume of data we have been processing over the years, we went through many challenges and various solutions regarding table partitioning and we ended up designing our own in-house framework that truly meets our requirements.

In this blog series, we share some of the challenges and learnings we encountered over the years with table partitioning. But first, let’s get to the basics (the WWW):

What?

Table partitioning is a well-known technique in relational databases that helps with fast and ever growing tables. It is a logical grouping strategy that "places the data in its right buckets”. As an example from real life, you could think of partioning as keeping your LEGO pieces grouped by shape or by color; or a step further by both shape and color. Partitioning is a way of keeping your data grouped by a meaningful trait that would not only help you find the piece you are looking for faster, but it would also help with easier cleaning and maintenance.

In database language, you could think of partitioning a table based on some meaningful column. For example: by the date of a payment, grouping them in weekly or monthly partitions; or by payment id with well defined ranges (depicted in the image below) .

The column you choose to partition your tables highly depends on your use case, and a good understanding of your current and future business requirements is crucial to picking the right partitioning column. This will have a significant impact on your partitioning efficiency.

Why

Query performance: since the table is divided into smaller partitions, PostgreSQL does not have to scan the whole table or index, but it can directly go through the partitions it’s interested in. For example, if you are interested in failed payments from January 2022 and your table is partitioned by date with monthly partitions, PostgreSQL can look directly through payments from January 2022 and only filter the failed ones.

Stable growth: partitioning gives you control over your partitions’ size. Instead of having one big table growing indefinitely, now you split the problem into multiple, smaller partitions. This significantly improves the reliability of the platform since you can always expect queries to perform similarly from one partition to another (if they have the same size). At Adyen we have a peak season, a period of time when we process considerably more payments than usual. But, since our tables are partitioned by range, we don’t have to worry about their size. As soon as a single partition becomes big enough (the exact threshold is decided case by case), we just create another one and we get the benefits of a fresh, empty partition, ready to handle our heavy queries.

Easier database maintenance: this is one of the biggest advantages of table partitioning. Database maintenance is extremely important and it is usually not easy to achieve. Indexes become bloated eventually, table statistics get outdated, deleting data requires VACUUM to kick in and claim back the space… you can quickly see how much work maintenance involves. However, partitioning significantly helps! Since you divide your big table into smaller partitions, each partition can be maintained individually and they won’t compromise the performance of other partitions. You can (re)create indexes only on specific partitions, ANALYZE one single partition to retrieve up-to-date statistics, trigger the VACUUM to claim back that unused space… all these with minimal overhead.

Deleting old data: there are multiple approaches to use when deleting old data, but we can look at them as DML (Data Manipulation Language) or DDL (Data Definition Language) approaches. Deleting data using DML commands (e.g. `DELETE FROM … WHERE …`) is a very expensive operation and it comes with a lot of overhead on your table. On top of this, you need the VACUUM to kick in and reclaim that space back. If we want to go even further, the VACUUM only cleans up 180 million rows at a time. So, if you are deleting more than 180 million rows, then you need multiple VACUUMs to claim the whole space back.

We can quickly see where this is going, deleting data using DML commands is not the most efficient approach and it comes with a lot of downsides.

We strongly recommend deleting data using DDL commands when possible (e.g. `DROP TABLE table_partition_yearly_2015`). This operation is much easier for PostgreSQL to execute, and it does not require any VACUUM to get the space back. With partitioned tables, this is very easy to achieve. You can quickly DROP old partitions (depending on your data retention policy) with no overhead and without impacting the parent table. An even better approach is to DETACH your partition before dropping it. Deleting old data has never been easier!

When?

There is no hard threshold for when you need to partition your table. However, as a rule of thumb in Adyen, we recommend partitioning if a table is expected to grow over 100GB.

There are multiple ways and methods to partition your table. In the sections below, we go into the difference between declarative partitioning (introduced in PostgreSQL 10) and partitioning using table inheritance. We explain different ways of partitioning by a partition key (range, list, and hash), and share a bit about the (in)famous DEFAULT partition.

Declarative vs Inheritance partitioning

Declarative partitioning was introduced in PostgreSQL 10 and we strongly recommend this approach as the first option to partition your table. While most of our tables are partitioned using this approach, a few specific use cases were only possible using inheritance.

Declarative (Native) partitioning

Declarative - also called native - partitioning  was introduced in PostgreSQL 10 and it is the built-in solution from Postgres. This approach brings a strong set of advantages. However, it is not as flexible as partitioning using inheritance.

One drawback is that you can only partition a table natively on a column that is part of the primary key. Since Postgres has only local indexes, the only way to enforce uniqueness over all partitions is to include all partitioning columns in the unique index.

This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are no duplicates in different partitions.

Advantages

- Transparent to the application: no code changes needed because the so-called "parent-table" is capable of handling all the queries executed against it.

- Enhanced partition pruning: partition pruning (the step in which PostgreSQL decides whether or not to look inside a particular partition) is done both at planning and execution time; this significantly improves the performance of some queries.

Limitations

- Partition key has to be part of the primary key. - Only works with range, list, or hash partitioning.

Inheritance partitioning

Partitioning using table inheritance is the legacy way. This was how tables were partitioned prior to PostgreSQL 10. Although declarative partitioning comes with advantages and it is suitable for most of the use cases, sometimes we need the flexibility of partitioning using inheritance.

The idea is simple: you have an empty root table from which all your child tables (partitions) inherit. Then, for each partition, you define a set of non-overlapping constraints which should uniquely define the data going to sit in that particular partition. Lastly, your application code has to be aware that the table is partitioned using inheritance and, therefore, any data manipulation statement must target the specific child table.

Advantages

Flexibility - The partition key does not need to be part of the primary key. - A child table can inherit multiple tables.

Weaker locking in general (we’ll tackle “locking” more in depth in our next blog posts).

Limitations

- Poor partition pruning: since tables partitioned using inheritance use constraint exclusion to prune child tables, partition pruning can only happen at planning time; this can lead to poor query performance.

- Not transparent to the application: the code needs to be aware, therefore leading to more difficult maintenance and increased coupling.: the code needs to be aware, therefore leading to more difficult maintenance and increased coupling.

PostgreSQL offers multiple options of table partitioning. Depending on your use case, you may choose one over the other. However, in our case, partitioning by range has proven to be the suitable approach, and we use this strategy for most of our tables.

Range based partitioning

Range based partitioning is the approach we prefer the most in Adyen due to its wide range of applications. Based on a partitioning key, you can choose to divide your table in (usually equal) ranges.

This approach is really powerful since it gives you full control over your partitions’ size. Not only can you limit the amount of rows inserted in a partition (stable growth), but you can also make sure all partitions are equally balanced, ensuring a query will perform similarly regardless of the partition(s) it needs.

We’ve seen above an example of range partitioning based on the payment id, but the table can be partitioned on any continuous value, for example by date. As we show in the image below we chose to create monthly payment partitions.

This partitioning approach offers the complete list of benefits specified above and it’s very flexible. You may even choose a different range size for your newer partitions. For example, you could choose to go for weekly partitions instead of monthly.

List based partitioning

Partitioning by list is another approach that uses a predefined list of values to divide the data into partitions. This approach is suitable when you already have the list of values on which you want to divide your data and, ideally, these values do not change often. Thus, compared to partitioning based on a continuous value, such as range based, this approach offers the option of partitioning based on discrete values.

The picture below depicts the payment table partitioned based on region; using the list of country codes where payments are currently being processed. In case we start processing payments in other countries, the list can be updated.

One disadvantage we foresee with this approach is the lack of control over your partitions’ size, while also suffering from imbalanced partitions. Compared to partitioning using ranges, you can quickly lose control over each partition’s growing pace and end up with very big or very small partitions. This could become problematic.

While it initially comes with the benefits of increased query performance and easy maintenance, it does not offer control over partition sizes, potentially leading to the opposite effect: decreased performance and slow vacuum. However, if your business requirements are well defined and you do not expect them to change a lot in the future (often not the case), partitioning by a list of values could be a suitable approach for your table.

Hash based partitioning

Lastly, you can also partition your table using a hash function, the partitions being defined by the remainder of a modulo operation. This approach is useful when the partition key is of numerical type and you want to distribute your data equally between partitions.

So, instead of range partitioning based on the payment id, one could also choose hash partitioning.

At first this approach seems fine, enabling an equal distribution between multiple partitions. Each partition will effectively be smaller than what the whole table would have been, bringing query performance benefits and faster vacuums. It also keeps the partitions balanced, which means queries will behave similarly, regardless of the partition.

However, this approach still comes with a lack of control over partitions’ size. In theory, your partitions could grow indefinitely; since they are not capped to some number of rows such as when using range partitioning. Furthermore, if your application scales up and you start to have more traffic, the partitions will also grow faster. With this, you can quickly lose control and you might end up needing to repartition the table (which is often an extremely challenging problem; we will tackle it in this blog series).

Flexibility is also an issue. Once you choose the divisor for the modulo operation, changing it in the future becomes problematic. You might already have a lot of data in the existing partitions, and since you can only increase the value of the divisor , the new partitions will effectively be much smaller… as you can see, not a very flexible approach.

Last but not least, you would never benefit from the ability to DROP old data since, well, there are no insights into what old data is. Any data, old or new, is split across all partitions. Thus, you can never drop partitions, which is a major drawback.

Subpartitions

We’ve only shown partitioning using a single approach at a time, but PostgreSQL also lets you use more granular partitioning. After all, each partition is a table itself, so why not partition it further if you need to? Below, we showcase a user table that is first partitioned by list of active and inactive users, and further on you can use range partitioning on the user id.

Before we get to the conclusion of this article, we’d like to address a common issue/solution regarding table partitioning:

What if, suddenly, you give some data to the database and it does not know where to place it? Maybe you forgot to create a new range partition for this month, or perhaps you started processing payments in Asia and you have not updated your table list partition definition.

How does PostgreSQL handle cases that are unknown to it? Obviously you should not forget to do such things, but what if it did happen? You would not want your whole application to crash, right?

This is where the DEFAULT partition comes at hand. This special partition helps your database place data that does not fall into any partitioning rule you defined.

So, the DEFAULT partition is your safe fallback, right? You should always have a DEFAULT partition in place so it protects your application from crashing. No!

At Adyen we have mixed feelings about the DEFAULT partition and, as much as we used it in the past, we’ve made some serious efforts to drop all the DEFAULT partitions we had and to ensure proper partition maintenance.

Why? Because, as much as it can help, it can also make things very problematic. We are not big fans of DEFAULT partitions and in the next few blog posts we will go into the details of why this is the case. We will be covering some incidents we’ve had and also some very specific use cases in which we decided, after all, to use the DEFAULT partition.

In this first post, we laid down the foundation needed for understanding the future articles. Scaling an application globally comes with a lot of engineering challenges and partitioning helps us handle our day to day work and, in effect, helps us process payments.

In the next post, we will give a real taste of what partitioning at Adyen truly means and showcase how we really do it. Have you ever wondered how to partition a 10+ TB table in production? Struggled with locking while attaching or detaching partitions? What about partition maintenance and automatically creating partitions in the future? These are a few of the questions we’ll aim to answer, followed later by deep-dive into query optimizations and the real pains of the DEFAULT partition. 🐰