Native online process downtime

[jdesouza]

I'm trying to follow the online process described here:
https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md#online-partitioning

So far so good, until I got to the step where I need to call partition_data_proc().

I can't have a big downtime during this process because this can take more than a day to get finished.
I noticed partition_data_proc() is maybe locking the table for read and write. Does that make sense?
Also wondering if it's locking because I have new FKs refererencing the partitioned table or just because it's the standard to lock everything.
I was expecting it to lock at least just the partition it's moving.

[keithf4]

The locking is more around the rows and because you have foreign keys, that may cause some cascading locks around those rows in other tables as well. The rows have to be exclusively locked because they are moving between two different tables. The length of the lock is really dependent on the interval of partitioning because it has to move as many rows as fit in a single child table at one time. So the longest downtime is going to be however long it takes to move the data for a single child. It doesn't all have to be done at one time, so you could take smaller outages over a longer period of time.

If the data that's being accessed more frequently is more recent data, you could wait until that data isn't accessed quite so much and then work on migrating that data then. Then the locks won't be quite as intrusive. Newer data should hopefully be going into the partitioned tables.

[jdesouza]

Thanks @keithf4 for quick reply. When you say the locking is around the rows, you mean all rows in the default table or just the rows that are being moved to one specific partition? Also is there a way to specifiy the partition ID I want to move?

[keithf4]

I believe it is just the rows that are being moved. You can confirm this by looking at the pg_locks system catalog and the mode column. I do explicitly take a FOR UPDATE lock on the rows to ensure that nothing else can modify them during the move operation. But even without that, the rows would be locked during the move anyway (not sure the exact lock that is taken in that case).

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/partition_data_time.sql#L249

https://www.postgresql.org/docs/15/view-pg-locks.html

An example

keith=*# select * from audit_log_p2023 where id = '22' for update;
 id |          change_time          
----+-------------------------------
 22 | 2023-06-28 18:01:15.788868-04
(1 row)

keith=*# select oid from pg_class where relname = 'audit_log_p2023';
  oid   
--------
 161079
(1 row)

In another session

keith=# select * from pg_locks where relation = 161079;
-[ RECORD 1 ]------+-------------
locktype           | relation
database           | 16384
relation           | 161079
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 3/9243
pid                | 865652
mode               | RowShareLock
granted            | t
fastpath           | t
waitstart          | 

The partition_data_proc() (and underlying partition_data_*() functions) have a p_order parameter that you can use to tell it which end of the data range to start with. ASC (the default) will start with the oldest data. DESC can start with the newest. That is the most control these functions give you for which child tables they operate on.

If you only want to do one child table at a time, I'd recommend using the partition_data_*() functions directly. By default these only partition a single child table per call unless p_batch_count is set higher. The partition_data_proc() will move all data by default unless p_batch is specifically set.

[jdesouza]

Thanks a lot. That's very helpful.