-
|
My parent table has an bigint ID column and a created_date timestamp column. I am partitioning the table over the ID column (this, id-based partition). However, I want the retention based on the created_date column such that partitions older than 3years can be dropped. Is there any alternative to achieve this? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 1 reply
-
|
Directly built into pg_partman, there currently is no way to do this since retention is solely based on the partition column and its data type. I do have plans to see about allowing multi-column partitioning in the future, which may allow this easier. But that is going to wait until I see the results of this discussion around trigger-based partitioning ( #360 ). It will be much easier to refactor the code if I don't have to worry about supporting the old methods anymore. You can however manage this yourself by setting up a process that scans for the min-max values in each table based on your created_date column and drops the tables accordingly based on the result. |
Beta Was this translation helpful? Give feedback.
-
|
Thank you for the prompt response @keithf4 ! That helps. |
Beta Was this translation helpful? Give feedback.
Directly built into pg_partman, there currently is no way to do this since retention is solely based on the partition column and its data type.
I do have plans to see about allowing multi-column partitioning in the future, which may allow this easier. But that is going to wait until I see the results of this discussion around trigger-based partitioning ( #360 ). It will be much easier to refactor the code if I don't have to worry about supporting the old methods anymore.
You can however manage this yourself by setting up a process that scans for the min-max values in each table based on your created_date column and drops the tables accordingly based on the result.