Recovering From a Partition Disaster with pg_partman
Introduction
Partitions are an excellent mechanism to reduce your queryable data size. PG_partman is a tool that enables DB admins to create partitions in the Postgres database. It is packed with features and offers a lot of functionality to create and manage partitioned tables.
Ideally, when partitions are created by using pg_partman, we need a monitoring mechanism to ensure that as time goes by we have enough partitions to accommodate our incoming data. This post explains how to monitor and auto-create partitions in pg_partman on an AWS infrastructure.
However, if we have no monitoring mechanism in place and we run out of partitions, then we are in a difficult situation. We will have to write a custom script to create new partitions and copy data from a default partition. This post guides how to get out of a problematic partition situation.
The Problem
Let’s say that we work for a marketing company that sends communications to users on behalf of its customers. We have a table customer_comms, which logs all the communications sent to the customers partitioned by month. The table is queried at runtime to check if customers have credit in their account to send the next message to the user.
The customer_comms table looks like as shown below:
Observe carefully that our partitions are exhausted as of the month Feb 2023. If our application tries to insert data in the table in the month of Feb 2023, it would go to the default table customer_comms_default. While the writes are preserved because of the default table, it will certainly slow down our reads from the customer_comms table.
The reads are slow because all the data that is going into the tables are now in a single partition instead of being divided over multiple partitions. If we do not create partitions for the coming months, our reads would become very slow, depending on the amount of data that we insert in our table.
So an obvious solution that comes to our mind, is to create new partitions to accommodate the future data. Now, we know that partitions can be created on an existing table using the run_maintainence() command of pg_partman. But, when we try to run this command we come across an error like the one below:
The error in the above gist says that the new partitions could not be created because data already exists in the default partition. This is the difficult situation we want to get out of. Imagine that your application is writing data to the partitioned table at high volumes but all of it goes into the default partition, making your reads slower. This is one problem you definitely want to get out of.
The Solution
As we have seen the problem is not a simple one, but the solution is pretty simple and straightforward. The steps outlined below will help you to get out of such a situation:
Step 1: Stop the Writes
Stop incoming wites to the application for a brief period of time. Stopping the writes will ensure that no more data is written into the default partition. We can divert your writes to a queue and later read from the queue and insert our data. Alternatively, we can take the application down, but this is the worst-case scenario.
Step 2: Move Data from the Default Partition
Now that the writes are stopped to our partitioned table, we can now safely move the data out of the default partition. We can create a temp table and move all our default partition data to it. Once data is moved out of the default partition, we can truncate it.
Here is an example of how we would do it in the case of our customer_comms table:
Step 3: Run Maintenance
We can now, run maintenance on our table and it should run successfully. As there is no more data in the default partition, the error that we got earlier will not come up. Here is how you can run maintenance on the table in our example:
Step 4: Check your partitions
As the maintenance command above is successful, we can check our partitions and ensure that they are created properly. Here is what our customer_comms table looks like now:
Step 5: Copy the Data Back
Now that we have recovered from the error, we can successfully copy the data back into our partitions. Remember the temp table we created in step 2 above. We need to add an index to it on the partition column so that we can query it and write select queries that can insert data into the partitions.
For our example, we create an index on the sent_at column as it is our partition column. We craete an index on the sent_at column in the temp table. Here is how do it:
We can now query the temp table and insert data back into our partitions. Here is how we do it four our customer_comms table:
Step 6: Restart the Writes
Our application has successfully recovered from the partitioning disaster. We can now restart our writes to the table by reading from the queue or starting our application. Do ensure to set up monitoring for partitions as described in this post.
Conclusion
Partitioning with pg_partman is an excellent solution for managing our data in the long run. When we are very sure that data is going to be very huge it makes sense to partition it for the sake of faster reads. However, if we do not pay attention to its maintenance part, it can become a business-stopping problem. So it is necessary to set up a proper maintenance process for Postgres partitions.
If we fail to do proper maintenance then we may have to stop our business application and take care of partitions as mentioned in the solution above.