Column type modification in big tables

From: Lok P <loknath(dot)73(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Column type modification in big tables
Date: 2024-08-07 11:09:05
Message-ID: CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message sud 2024-08-07 11:21:26 Re: Column type modification in big tables
Previous Message David Rowley 2024-08-07 08:09:39 Re: ANALYZE on partitioned tables vs on individual partitions