Table partition dynamic expression / partition grouping
Hi,
I saw there is a new feature "partition grouping" where its possible to have dynamic expression that changes the partitions as time goes by.
For example:PARTITION BY ((store_orders.order_date)::date) GROUP BY ( CASE WHEN ("datediff"('year', (store_orders.order_date)::date, ((now())::timestamptz(6))::date) >= 2) THEN (date_trunc('year', (store_orders.order_date)::date))::date WHEN ("datediff"('month', (store_orders.order_date)::date, ((now())::timestamptz(6))::date) >= 2) THEN (date_trunc('month', (store_orders.order_date)::date))::date ELSE (store_orders.order_date)::date END);
I have a scenario where couple of processes write to same table, and for each process I want to have different partition grouping.
For example,
Process A should have monthly partition for data in current year and rest will be in yearly partition.
Process B generates small amount of data, so I want to keep all the data in one partition.
Partitions in my case of multiple processes writing to same table is very convenient because of the SWAP function, but I'm not sure how to implement partition grouping.
I'm currently using version 8.1 but supposed to upgrade in the near future.
Is it possible to use another field except the DATE column in the partition expression?
Another option could be to have "partition_id" column in the table, partition according to it, and implement the partition grouping by updating the column when time advances.
What is the best approach in this case?
Thanks