Re: Question related to partitioning with pg_partman

On Sat, Mar 9, 2024 at 3:41 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 3/8/24 00:23, sud wrote:
> >
> > Starting a new thread...
> >
> > Something interesting and not sure if its expected behaviour as below.
> > We are also confused a bit here.
> >
> > In the below example we created two partitioned tables on timestamptz
> > type columns with different time zones and the child partitions are
> > created appropriately with boundaries as one mid night to next mid night
> > of a day and so on. But when we change the time zone and query the data
> > dictionary views again, it shows the start and end of the partition
> > boundary as not midnights but different times of the day's values.
> >
> > So I was wondering if this can cause us any unforeseen issues in the
> > long run while creating the partitions though partman and persisting the
> > data into the tables from the end users then querying those and having
> > queries properly partitioned pruned?
> > or
> > should we always set the local timezone as UTC always before running or
> > calling the pg_partman/pg_cron process which creates the partitions?
> > Mainly in a database which serves global users sitting across multiple
> > timezones. And same thing while inserting data into the table, we should
> > use UTC timezone conversion function. Can you please confirm.
>
> '2024-03-07 00:00:00+00' and '2024-03-06 19:00:00-05' are the same time
> as is '2024-03-07 00:00:00-05' and '2024-03-07 05:00:00+00'.
>
> Still I would think for sanity sake you would want to stick with UTC.
>
>

Thank you so much Adrian.

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.

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?

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".

Regards
Sud