Efficiently RePartitioning Large Tables in PostgreSQL

Why Repartition?

• Performance Improvements: Our partitioning journey makes querying and indexing more efficient by limiting the amount of data scanned during operations. Smaller, more focused partitions – which we will mostly cover in this blogpost – improve read performance and reduce the burden on system resources (I/O, Vacuum, etc..)

• Maintenance Made Easy: With partitioned tables, operations like archiving, vacuuming, and purging old data become more manageable.

Why Not Use Existing Tools?

• There are some tools like pg_partman and pg_repack that are popular for managing PostgreSQL tables and partitions. However, these tools are not designed for the specific challenge of splitting an already massive, single partition into smaller, more manageable partitions. As such, we opted for a custom approach that provided the necessary control and efficiency.

Our Working Strategy for Partitioning Existing Tables

You can read through our previous blog post – Table Partitioning at Adyen – for more in-depth details. To partition existing tables effectively, we followed a straightforward strategy:

• Rename the Existing Table: Rename the existing table to <table>_mammoth.

• Create a New Table: Create a table with the original table name.

• Add Original as First Child: Add the renamed original table (<table>_mammoth) as the first child.

• Add Additional Partitions: Add at least one more partition to accommodate the incoming data.

Overview of the RePartitioning Process

Once we set up the initial structure, we use the following process to split the mammoth partition:

1. Create Empty Partitions: Define and create new partitions to replace the mammoth.

2. Load Data: Load data from the mammoth partition into the newly created partitions in batches.

3. Switch Partitions: Detach the mammoth partition and attach the new ones to complete the partitioning.

This approach avoids overwhelming resources by batching the data load, ensuring ownership and integrity constraints remain intact. In the following sections, we’ll detail each of these steps. While developing these steps for one of our largest tables, I want to give credit to Shailendra Chauhan for his significant contributions to this work.

In this initial step, we must lay the groundwork by defining and creating new partitions. The goal is to replace the mammoth partition with smaller, manageable segments, each representing a specific range of invoiceId. This step ensures that subsequent data loading and switching can proceed in an organized manner, limiting resource usage and improving system performance. First, we need to create partitions with boundaries that allow data to be split into manageable parts. In our case, each new partition covers a specific range of transaction IDs (Invoiceids) within the invoices table. To demonstrate this in a relatable way, let's use an invoices table that represents child records of orders stored in an orders parent table.

The load_data_in_invoices_partition() procedure migrates data from the mammoth partition to newly created partitions. This migration occurs in batches to ensure the process is manageable and does not overwhelm system resources. Each batch is inserted into the appropriate partition based on the specified invoiceId boundaries.

• Batch Size: The batch size is defined as 100,000 rows, which means we load the data in manageable portions rather than all at once. This minimizes the load on the system and helps avoid potential locks or performance issues.

• Renaming Partitions: The new partitions are renamed to be part of the invoices table.

• Loop for Loading Data: The data loading process is executed in a loop, where each iteration inserts a batch of rows and then commits the transaction to make the changes permanent. Committing after each batch helps minimize lock time and ensures the database remains responsive.

In this final step, we transition from the old mammoth partition to the newly created partitions. We need to ensure that the existing mammoth partition is detached smoothly while the new partitions are correctly attached to maintain the structure and integrity of the invoices table.

We utilize a method that avoids running an expensive validation process for checking data consistency across the table and its foreign key references. This approach requires locking the invoices table in exclusive mode to guarantee data consistency. Although this method involves bending some conventional rules, the lock is crucial to ensuring no data anomalies occur during the transition.

To streamline the entire process, you can wrap all three steps into a single consolidated procedure to execute the creation, data loading, and partition switching steps sequentially. This approach makes running the complete partitioning process in a single command easier, ensuring consistency across the entire operation. Even better here, we can create a test scenario like the following:

This single procedure (repartition_invoices) ensures that all the steps are executed in the correct sequence, minimizing manual intervention and making the entire process repeatable. It also helps maintain database consistency and limits downtime, especially for tables with large datasets. As you can see in the final step, there is no longer a mammoth partition, and we have created the desired ranges accordingly.

By applying this procedure, we have saved some storage space and created smaller partitions in place of one big one. It has various advantages: faster query execution , efficient index usage , and faster maintenance operations. This strategy covers the cases of declarative partitioning, but it can also be applicable to inheritance-based partitioning, as the only differences would be in the DDL commands used.

For this reason, it is a valuable input for a data archival strategy for append-only tables. Since the data in the historical partitions remains static, this approach ensures that we can perform partitioning without the risk of concurrent updates leading to inconsistencies.

Through these steps, it’s possible to partition an existing table with minimal impact on performance. By creating empty partitions, loading data in batches, and finally detaching the mammoth partition, we efficiently repartition a large table without too much impact. However, one could use the same method to partition existing tables efficiently. This approach not only improves performance but also maintains data integrity.

What’s Next?

The next challenges to consider are scenarios where historical data may change during the data load phase. In such cases, we could either use triggers to track changes or, more effectively, introduce Change Data Capture (CDC) mechanisms to ensure data consistency throughout the partitioning process.