From: Adrian Klaver <adrian.klaver@aklaver.com>
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Question related to partitioning with pg_partman
Date: Sun, 10 Mar 2024 11:01:46 -0700
Message-ID: <0a663c42-32fe-486e-8fca-63c0faa7b7c9@aklaver.com> (raw)
In-Reply-To: <CAD=mzVXtks_qLKJiDO11oQnkRYs+Yr2=Hh_Ctj8yQVP10jqsmw@mail.gmail.com>
References: <CAD=mzVWc=HOHrccJ20BJB5sGj_pLkaCa-7qHjV9jGAbbE3X-TQ@mail.gmail.com>
<7ca9af73-6cb2-4ac0-8dfb-b83a50c64363@aklaver.com>
<CAD=mzVXP_WEXT_Zk4R8bb7ztar1N7XsG+7BgmqJJ-LgQrNAKHw@mail.gmail.com>
<46b8c853-a98c-41ee-b3f3-e1fcf11f26db@aklaver.com>
<CAD=mzVXtks_qLKJiDO11oQnkRYs+Yr2=Hh_Ctj8yQVP10jqsmw@mail.gmail.com>
On 3/10/24 10:51, sud wrote:
>
> On Sun, Mar 10, 2024 at 10:32 PM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 3/10/24 05:12, sud wrote:
> >
> > In my example in the first post, I see, if someone connected to a
> RDS
> > Postgres database and run the create partition command using
> pg_partman
> > by setting the timezone as "UTC", the 7th march partition looks
> to be
> > spanned from "7th march midnight" to "8th march midnight", when
> queried
> > the partition_experession from the data dictionary view. Which is
> correct.
> >
> > And same information if someone querying by setting the timezone
> as EST
> > is showing spanning from "6th march 7PM" to "7th March 7PM". And
> this
> > can cause sometimes the partition may shift to other days all
> together.
> > Similar differences happen if creating the partitions using EST
> timezone
> > initially and then querying the data dictionary from UTC timezone.
>
> The above is at odds with your example below which has the correct
> values:
>
> 2024-03-07 00:00:00+00 = 2024-03-06 19:00:00-05
>
> >
> > So my question was, if in these types of scenarios, we should
> follow a
> > standard approach of setting the timezone as UTC in such a type of
> > global user use case, while the system can persist data from
> multiple
> > users sitting across different time zones? So that the
> boundary(start
> > and end time) of each of the range partitions will be set as
> consistent
> > in one timezone across all the partitioned tables?
>
> You need to first determine what your time frames are going to be?
>
> 1) Midnight to Midnight in UTC will be consistent when viewed in
> UTC. It
> will not be when viewed in other time zone +/- the offset from UTC.
>
> 2) Or Midnight to Midnight in the users time zone, in which case the
> UTC
> values will differ.
>
> You have to decide which of the above is your goal. The bottom line is
> by definition the local wall clock time will not equal UTC, GMT
> excepted. This comes down to what the purpose of the partitions are? In
> other words how do you want to organize the data?
>
> >
> > And even while inserting the data , should we set the timezone to
> first
> > UTC and do the data load ?
>
>
> >
> > ******* Partition created by pg_partman by setting timezone as UTC
> > ***************
> >
> > *UTC*
> > *Partition_name Partition_expression*
> > test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-07
> 00:00:00+00') TO
> > ('2024-03-08 00:00:00+00')
> >
> > when queried the partition_expression using EST ..
> >
> > *EST*
> > *Partition_name Partition_expression*
> > test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-06
> 19:00:00-05') TO
> > ('2024-03-07 19:00:00-05')
> >
> >
> > ******* Partition created by pg_partman by setting timezone as EST
> > ***************
> >
> > *EST*
> > *Partition_name Partition_expression*
> > test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07
> 00:00:00-05')
> > TO ('2024-03-08 00:00:00-05')
> >
> > when queried the partition_expression using UTC ..
> >
> > *UTC*
> > *Partition_name Partition_expression*
> > test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07
> 05:00:00+00')
> > TO ('2024-03-08 05:00:00+00')
> >
> > *******
> >
> > Also i see both the "setting" and "reset_val" is showing as local
> > timezone only. If we set the timezone to a different value than the
> > local timezone then it gets updated on the "setting".
>
>
> Our requirement is to have the transaction table partitioned by
> range daily on the transaction_date column(i.e one midnight to next
> midnight transaction data in one partition). Transaction date column
> will be of timestamptz data type. And this application/database might be
> consuming data from users across multiple time zones in future. These
> tables will be queried based on the date range (minimum being ~1
> transaction day) and also will be purged one day partition.
>
> So for above I understand , it might not be possible to keep the users
> data restricted to one day partition in the table considering the users
> will perform transactions across multiple timezones, but we are thinking
> of restricting the database with UTC timezone irrespective of the users.
> And thus during creating the table partitions , we need to ensure the
> UTC timezone is set , such that the upper and lower boundary for the
> daily range partitions remains consistent for all. Correct me if my
> understanding is wrong.
1) The partition will be across one day(24 hours) it is just the times
may confuse people. Per you example 2024-03-07 00:00:00+00 is the same
time as 2024-03-06 19:00:00-05 for EST. The issue is that the +00 and
-05 maybe ignored. Also it depends on the clients being consistent in
using timestamptz.
2) You still have not answered what the datetime range(not date range)
is that will be queried. If you have the partitions Midnight to Midnight
UTC and the clients are querying Midnight to Midnight local time the
query will not match the partitions.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, suds1434@gmail.com
Subject: Re: Question related to partitioning with pg_partman
In-Reply-To: <0a663c42-32fe-486e-8fca-63c0faa7b7c9@aklaver.com>
References: <CAD=mzVWc=HOHrccJ20BJB5sGj_pLkaCa-7qHjV9jGAbbE3X-TQ@mail.gmail.com> <7ca9af73-6cb2-4ac0-8dfb-b83a50c64363@aklaver.com> <CAD=mzVXP_WEXT_Zk4R8bb7ztar1N7XsG+7BgmqJJ-LgQrNAKHw@mail.gmail.com> <46b8c853-a98c-41ee-b3f3-e1fcf11f26db@aklaver.com> <CAD=mzVXtks_qLKJiDO11oQnkRYs+Yr2=Hh_Ctj8yQVP10jqsmw@mail.gmail.com> <0a663c42-32fe-486e-8fca-63c0faa7b7c9@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox