MIME-Version: 1.0
From: Lok P <loknath.73@gmail.com>
Date: Wed, 7 Aug 2024 16:39:05 +0530
Message-ID: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
Subject: Column type modification in big tables
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f7e0ba061f15f00f"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ%3DLFXx-hPdw7E6RoqmeA%40mail.gmail.com>
Precedence: bulk

--000000000000f7e0ba061f15f00f
Content-Type: text/plain; charset="UTF-8"

Hello all,
We have a postgres table which is a range partitions on a timestamp column
having total size ~3TB holding a total ~5billion rows spanning across ~150
daily partitions and there are ~140+columns in the table. Also this table
is a child to another partition table. And we have partition creation
handled through pg_partman extension on this table.

We have a requirement of modifying the existing column lengths as below .
So doing it directly through a single alter command will probably scan and
rewrite the whole table which may take hours of run time.

So trying to understand from experts what is the best possible way to
tackle such changes in postgres database? And if any side effects we may
see considering this table being child to another one and also dependency
with pg_partman extension.

two of the columns from varchar(20) to numeric(3)
one of the columns from varchar(20) to varchar(2)
one of the columns from Number(10,2) to Numeric(8,2)

Regards
Lok

--000000000000f7e0ba061f15f00f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello all,<div>We have a postgres table which is a range p=
artitions on a timestamp column having total size ~3TB holding a total ~5bi=
llion rows spanning across ~150 daily partitions and there are ~140+columns=
 in the table. Also this table is a child to another partition table. And w=
e have partition creation handled through pg_partman extension on this tabl=
e.<br><br>We have a requirement of modifying the existing column lengths as=
 below . So doing it directly through a single alter command will probably =
scan and rewrite the whole table which may take hours of run time. <br><br>=
So trying to understand from experts what is the best possible way to tackl=
e such changes in postgres database? And if any side effects we may see con=
sidering this table being child to another one and also dependency with pg_=
partman extension. <br><br>two of the columns from varchar(20) to numeric(3=
)<br>one of the columns from varchar(20) to varchar(2)<br>one of the column=
s from Number(10,2) to Numeric(8,2)<br></div><div><br></div><div>Regards<br=
></div><div>Lok</div></div>

--000000000000f7e0ba061f15f00f--