Partitioning is a crucial element to keep the performance for databases with very large tables. For instance, when the table size grows over time, each operation cost on the table will increase as well. Also, another limitation is that we can't increase the size of the table over 32 gigabytes in normal conditions. However, way before reaching that size, performance issues may arise. In both cases, partitioning may be a good solution as it can help to divide a large table into smaller tables. Here, the idea is to allow the query processor to scan much smaller tables and indexes to find the needed data. Also, partitioning helps to scale polygrace scale by splitting large logical tables into smaller physical tables that could be placed on different storage devices. Partitioning should be the first option to improve performance. We should wait whether it makes sense to add it considering that it adds another level of complexity. Also, unlike other performance enhancing methods such as indexing, partitions are part of the table definition and are significantly harder to change. So, next, we'll consider when partitioning is actually beneficial and which are the signs that reveal if a table needs partitioning. The first sign is of course the table size. There is no rule or hardline for how big the table must be before partitioning becomes an option. Still, if you encounter long response times and the table is larger than 200 gigabytes, you could start to consider partitioning. Another important aspect is the table block. Update it and delete it rows result in dead tuples that need to be cleaned up. The vacuum process, whether manually or automatically, goes over every row in the table and determines if it is to be reclaimed or left alone. If the table is larger, then this process will be more aggressive and it will take longer. This also means more system resources will be used especially CPU. Even if 90% of the table is unchanged data, the vacuum process still has to scan the whole table data. So, partitioning the table can help to reduce the table that needs vacuuming. When there are smaller partitions, some with more changes, other with less, the vacuum process will clean only those with many changes at first. This will result in less time vacuuming overall and more system resources freed up for the user access rather than system maintenance. Partitioning can drastically improve performance on a table when done right. But when not needed or if done wrong, it can make the performance worse or it can make the database unstable. When splitting the tables, the first thing to look for is the access patterns. This is an essential step in defining partitions. The access pattern can be found either by knowing the applications that access the database or by monitoring logs and generating reports. Then, depending on how the tables are accessed, we can have options for a good partitioning strategy. And what exactly are we looking for? Basically, we look for the columns that are either in the workflows or in join conditions. These columns will be our partition keys. Every time a query specifies columns in the where or in the join closes, it tells the database this is the data we want. Similar to designing indexes, partitioning strategies rely on targeting these columns to separate data. With good design, we can have the query planner scan a small subset of data rather than the whole thing. In conclusion, we want to query as few partitions as possible. The partition key is not the only aspect to consider when we divide the table. We also have the partitioning methods. There are four methods to partition the tables, according to different use cases, which you'll see in the hands-on section. As a quick note, we'll cover shortly the two types of partitioning. In this tutorial, we'll make use of declarative partitioning, which is available starting with PostgreSQL 10. The other type called inheritance partitioning is an older type. This is much difficult to set up because it requires writing code to route the data and to maintain it. However, it could be more flexible in some cases. On the other hand, declarative partitioning is much easier to set up and requires almost no maintenance and it's suitable for most of the use cases. Next, let's see how partitioning works in practice. We'll use the range partitioning method since it's one of the most common. But we'll see later which are the other types and when to use them. Before starting, it's important to mention that if you already have a monolithic table, you'll have to migrate the data to a new partition table. You can do that with little or no downtime depending on your requirements and what strategy you use. In this example, we'll start from scratch. We'll do a simple example where we split the customer table by ranges of age. Before that, I'll create a temporary database and switch to it. Now, the first step is to create a new table named customers. Then we specify the columns as we normally do. And finally, we'll also add the close partition by and then the method we use for splitting range and the columns for splitting, which in this case is the age of the customer. Then we need to declare the partitions. The first one we'll call it customer underscore young, which is a partition of customers. And then we specify the range. In this case, we'll use the special value, mean value, to specify the lower bound. And for the upper bound, we'll specify the value 25. Then we create another partition called customer underscore medium. With values from 25 to 75. Finally, we create a partition called customer underscore old. With values from 75 to the special value, max value. Since all possible ranges are covered, there is no need to define a default partition. But here, we are considering that the age is a not null column. We can also check the customer table definition to see how the partitions are defined. Now that the table is well defined, we can add some values and we expect the values to be distributed. Insert into customers values. Bob 20. Alice 20. Though 38. And Richard 80. We do a simple select. Now, the data may be split to various partitions, but we can't tell from which partition each row comes from. For that, we'll have to use a hidden system column called table of ID that we also need to select. However, this column alone returns just an ID. It returns just the object identifier of the table that the row belongs to, which is not really obvious. We'll also need to cast this ID to a table name. We'll do that using the reg class type. Now, we can also see the partition name for each row. Deciding on the partitioning method is essential to performance. And, once said correctly, it can have a significant positive effect. The method is normally selected based on the use case. Partitioning a table by range values is commonly used with date fields. For example, a table containing cell data can be divided into monthly partitions according to the cell dates. Then partitioning a table by a list of known values. This is typically used when the partition key is a category value, such as departments or regions. Although, departments or regions alone might not be the most optimal because it could lead to uneven partition sizes. In this case, we could add additional columns so that the values are more evenly distributed. We could also partition a table using a hash function on the partition key. Basically, this will partition the data based on our modulus and a remainder. This method is especially useful when there is no obvious way of dividing the data into logical groups. For example, if the cell stable is often accessed by product, then the table might benefit from a hash partition on the product key. Last, a composite partition is sometimes known as a sub partition. For example, a table separated by range partitions can be further divided by a list partition. This kind of multi-level partitioning can be created up to N levels.