Question on creating different partition intervals for the same parent_table

[TonyJulin]

Hello all! I am converting a table that we previously had partitioned manually (by range) over to using pg_partman, but I'm not sure how to convert this over given the requirements we have. Currently, the way it's manually configured, and required, is that there should be monthly partitions going back 2 years from the current date, yearly partitions up to 3 years before that, and then everything else is archived. For example:

• monthly partition tables => partition_table_2022_january, partition_table_2024_june
• yearly partition tables => partition_table_2019, partition_table_2021
• Everything else is in an archive partition => partition_table_archive

Looking at the docs and some of our much simpler examples, it looks like only one parent_table configuration can be created where an interval and start_partition are specified. Is something like going to have to be a custom maintenance procedure via pg_cron? If so, any sort of guidance is appreciated. Thanks!

[keithf4]

pg_partman currently only supports managing a single partition interval.

However, since the time intervals involved are all 1 day or longer, you may still be able to use partman to at least manage new table creation and possibly retention as well. You would just have to maintain the process to re-partition the data older than 2 years separate from partman. Partman only cares about older tables for retention purposes and in version 5.0+, the child table naming pattern is the same for yearly and monthly, so think it would work. That's why I said possibly, You would need to test that.

I'd like to have multi-interval support added at some point. Just need the time to investigate it. Or I'd be happy to review any contributions to try and add the feature.

[TonyJulin]

Thank you for the quick response! I'll check out child table naming patterns in the docs and see what I can scrape together. If I find something I'll try and be sure to post it here.

[TonyJulin]

Actually. Another question with regards to your statement: "...in version 5.0+, the child table naming pattern is the same for yearly and monthly". By this, do you mean when setting the p_interval to "1 year" vs "1 month" that I can expect the partition tables to have the same suffix? e.g. _p2022_02? Tested this out briefly and the format for the yearly interval is _pYYYY and monthly is _pYYYY_MM.

[keithf4]

You must be running version 4.x. Those were the naming patterns then. Version 5.0 simplified the naming pattern down to two possibilities

For 1 day and longer:

_pYYYYMMDD

For shorter than one day

_pYYYYMMDD_HH24MISS

Version 5 was a big change
https://github.com/pgpartman/pg_partman/blob/master/CHANGELOG.md#upgrade-notes

[TonyJulin]

I'll have to double check the version somehow. For reference, the db is hosted in an Amazon RDS PostgreSQL database and for this test I upgraded to use engine version 15.5 which apparently uses version 5.0.0 of the pg_partman extension according to AWS. Normally we use 15.4 which has 4.7.3 installed.
See: https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html#postgresql-versions-version155