Yes, I tried creating a table manually with column timestamptz(6) type and partitioned on that and then executed select query with the filter on that column and I do see partition pruning happening. Not able to visualize any other issues though, however some teammates say it may have a negative impact on aggregation type queries , not sure how but will try to test it. Thanks again for the response.
1)If there is any downside of having the partition key with "timestamp with timezone" type? Will it impact the partition pruning of the queries anyway by appending any run time "time zone" conversion function during the query planning/execution phase?
2) As it will take the default server times , so during daylight saving the server time will change, so in that case, can it cause any unforeseen issue?
3)Will this cause the data to be spread unevenly across partitions and make the partitions unevenly sized? If will go for UTC/GMT as db time, the user's one day transaction might span across two daily partitions.
My 2 cents.
We have cases which use the "timestamp with timezone" column as partition key and the partition pruning happens for the read queries without any issue, so we don't see any conversion functions applied to the predicate as such which is partition key. I think if the users go global it's better to have the database time in UTC time zone. and it's obvious that, In case of global users the data ought to be span across multiple days as the days won't be as per the users time zone rather UTC.