pg_partman online partitioning rollback strategies

[rohichau]

Hi,

I have a table with 800 million rows. I want to partition it as per timestamp, and hence planning to use the online partitioning strategy.

I tested with 7 million rows data and the call to partman.partition_data_proc('public.scheduled_jobs', p_batch := 200) took around 25 minutes (the sample data was generated with a random function such that it was uniformly distributed around time range). This call basically transfers data from the original un-partitioned table to the respective partitions of the partitioned table. I reset the sequence of the partitioned table to the max id of the original table before calling the proc such that the writes were going through while the proc was running.

My question is, is there any expected downtime of running partition_data_proc and run_maintenance ?? If yes, then do I need to have a separate rollback strategy in place or the procedure would automatically roll-back in case of failure ?
Are there any implications on the read/write operations ?

[keithf4]

partition_data_proc is a PROCEDURE, not a FUNCTION, so the batches that have been committed cannot be rolled back. If you need the entire process to be able to be rolled back, then just use the partition_data_time() FUNCTION. However, that means the entire migration process will run in a single transaction and, as it runs, more and more data will be locked until it finishes.

I tried to cover and explain both offline and online partitioning methods in this document here

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md#partitioning-an-existing-table

To do this online, you have to make the original table the default of the new partitioned table. The blocks of data being moved may be unavailable while they are being moved. It won't cause an error, the query will just be blocked until that section of data has been completed. Also when doing it online, the size of the batches cannot be any smaller than the partition interval size. So when this starts getting towards your current working set of data, you may see some blocked sessions around that time period in your application. There is no avoiding that at this time since the data is being moved between tables.

If you need to do smaller batch sizes than the partition interval, or if you didn't make the original table the default, then you will have to do this offline.

The performance of how this runs is entirely dependent on the resource the database has.