Subpartitioning for time -> id

[carina2211]

Hi,

we want to make subpartitioning with time and id. The target is, that we need to use the retention for dropping the partionen depending on the id (usecase_id) and the time.
For example we have usecase_id 10 that have a retention of 2 days and usecase_id 25 with a retention of 4 days. The first idea was to make the partition like usecase_id (parent_table) and then the time-partions.
But we wand to have this in the order time and then id.

Do you have any idea how to do that with partman sub-partioninig? Is this possible?

Thank you and BR
Carina

[keithf4]

There's no current way built in to pg_partman to have the top level of the partition set have different retention periods based on a value in the child tables.
You could have pg_partman do the subpartition management for you as described (time -> id) which would hopefully help a lot with the complexity of managing that. But you'd have to have some separate process to do the retention management for you. pg_partman does not care much about the past partitions, so you are free to have some other process dropping them over time.

[keithf4]

And actually thinking about this more, I'm not sure you'd really be able to effectively do this with it being time -> id. The actual time-based child table wouldn't be able to be dropped until ALL the ids contained in it reached their retention period. To effectively do what you'd want, you'd probably have to do id -> time. Then each top level partition set would only contain the subpartitioned children within that desired time period.

[carina2211]

Hi Keith,

thank you for the fast response. You helped me a lot with confirming my opinion.

Have a nice day and BR
Carina