Followup Timestamp to timestamp with TZ conversion

From: Peter Volk <peterb(dot)volk(at)gmx(dot)net>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Followup Timestamp to timestamp with TZ conversion
Date: 2021-07-21 05:48:28
Message-ID: CAFWHPeZwd2pr-wKf4W_8VCwEe8RGvc_x7H6a9BD7Kv1ZjQre6A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

this is a followup to a performance optimization during the conversion of a
column from a timestamp column to a "timestamp with tz" column. The initial
patch I am referring to is this one:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3c59263#patch4

and the previous discussion on this list is this one:

https://www.postgresql.org/message-id/CANsFX06xN-vPYxM+YXyfLezK9twjtK3dFJcOHoubTXng40muoQ@mail.gmail.com

The problem is that I have a 60TB+ PG installation for which we need to
modify all of the timestamp columns to timestamp with tz. The data in the
columns are already in UTC so we can benefit from the patch listed above.
Yet there are 2 cases in which we are having an issue.

1) Index rebuilds: The patch is only avoiding a rewrite of the table data
but is not avoiding a rebuild of the indexes. Following the logic in the
patch above this should also be avoidable under the same condition

2) Partitioned tables with the timestamp as partition column: In this case
the current version does not allow a modification of the column data type
at all. Yet also following the logic in the patch this can also be allowed
under the side condition if no table rewrite is required.

Question: What chances to we have to get the optimisations from the patch
above also "promoted" to the other 2 cases I listed?

Cheers,
Peter

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2021-07-21 06:03:29 Re: wrong relkind error messages
Previous Message Thomas Munro 2021-07-21 05:39:38 Re: A micro-optimisation for ProcSendSignal()