Thank you. 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.
On Wed, Mar 6, 2024 at 12:35 AM Lok P <loknath...@gmail.com> wrote: > > On Tue, Mar 5, 2024 at 1:09 AM sud <suds1...@gmail.com> wrote: > >> >> However the question we have is , >> 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. > > > >
