Hello, Has anybody got experience of using a range partitioning table using timestamptz or "timestamp with no timezone" Column and saw any of such known issues in pruning?
> On Tue, 5 Mar, 2024, 1:09 am sud, <suds1...@gmail.com> wrote: > >> Hi, >> We are designing one application which is currently restricted to one >> time zone users but has the possibility to go global in future. Some of the >> transaction tables are going to be daily range partitioned on the >> transaction_create_date column. But the "date" data type will have no time >> component in it, so we are thinking to make it as timestamp data >> type(timestamptz(6)), so that it will help us in us two ways, >> >> firstly , though current use cases in which the majority of the queries >> are going to happen on a day or multiple days of transactions. But if we >> have any use case which needs further lower granularity like in hourly >> duration , then having "timestamp" data type with an index created on it >> will help. And in future , if we plan to partition it based on further >> lower granularity like hourly , that can be accommodated easily with a >> "timestamp" data type. >> >> 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. * >> >> >> Thanks and Regards >> Sud >> >
