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, <suds1434(at)gmail(dot)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
>>
>