Is partition pruning impacted by data type

From: sud <suds1434(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Is partition pruning impacted by data type
Date: 2024-03-04 19:39:16
Message-ID: CAD=mzVX3VMjD-qY_Zm=+i3womvLb5u+LMzUn1GnK6fM2fc=R2g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message veem v 2024-03-04 20:01:46 Re: When manual analyze is needed
Previous Message Greg Sabino Mullane 2024-03-04 16:15:47 Re: When manual analyze is needed

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Sabino Mullane 2024-03-05 01:50:00 Re: Optimizing count(), but Explain estimates wildly off
Previous Message Chema 2024-03-04 19:13:56 Re: Optimizing count(), but Explain estimates wildly off