Need to migrate large amount of data from default

[CharlesLiu02]

Hi, I have an issue where I have a table that has been partitioned, however, auto partitioning was not setup and the latest partition was from 2023w09. This has resulted in all the data since then going into the default partition. I am now trying to setup auto partitioning again. I want to keep the data in default and also want to prevent the database from going offline if possible. I know that we could try running partition_data_proc, but the default partition has around 2.5 TB of data and over 1 billion rows. I'm concerned about the length of time partition_data_proc will take to move the data from default, especially if there are reads and writes to the table at the same time. I have also considered moving the data out of default, fixing the partitions and then moving the data back into the correct partitions, or just creating a new database entirely with partitioning setup correctly. Is there a way to run partition_data_proc or create_patition_time asynchronously that won't lock the table? I'm wondering what is the best solution here. Thanks!

[keithf4]

I'm moving this to a Discussion since it's not really reporting a bug, but trying to plan out a method to move a lot of data in the default.

So partition_data_proc() will commit after each child table is created. With native partitioning, the shortest the transaction will be, and the smallest amount of data that can be moved, is a whole child table. So that will depend on what your partition interval is for how long each transaction will be.

The only locks that will happen will be on the rows that are being moved in that transaction. There is no aoviding this because the rows are being moved between tables. Old transactions before the rows are moved may not be blocked, but new transactions trying to see those rows may be blocked. The other lock that will occur is when a new child table is added. This is a lighter weight lock and generally doesn't block most transactions, however it may do that here a little more frequently since you're creating child partitions that may contain data that users are trying to access. These locks will only last the length of a single transactional commit of partition_data_proc().

If you're able to uniherit the default table, turn it into a new partitioned table on its own and then run partition_proc_data() on that, that may work better to have this done as quickly as possible. But that can only work if the users don't need that data. However, as each child table is finished, you could uniherit that child table from the "default" parent and re-inherit in to the original table relatively easily. You'll likely also want to add a normal default table back to your main table to avoid missing any data that may be going to it.

Hope that makes sense. If you have any other questions, let me know.

[CharlesLiu02]

Got it, makes sense. Just wondering, how does create_partition_time work? I remember reading that it needs to go through the data in default so I'm wondering if would acquire any locks/take a long time if there is a lot of data in default.

[keithf4]

create_partition_time never touches the default. What happens is that when a new child table is added to a partition set, PostgreSQL itself checks if any data exists at all in the default and, if it does, compares that against the boundaries of the new child table. If any data in the default would fit in that new child table, it throws an error. If it allowed that, then there would be two valid child tables that a single value could exist in. This is why you don't want a large amount of data existing in the default since it would have to do this check every time a new child table is made. This is part of core PostgreSQL and nothing to do with partman itself.

[CharlesLiu02]

I see, but I'm assuming this operation would take a long time if there is a lot of data in default right?

[keithf4]

Yes that is a possibility. A user posting about long maintenance run times in the past is how I learned about data being in the default causing long run times. They had approx 2 billion rows in the default and it was making maintenance take 2-4 minutes

#262

[CharlesLiu02]

Got it, thank you!