public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lok P <[email protected]>
To: pgsql-general <[email protected]>
Subject: Column type modification in big tables
Date: Wed, 7 Aug 2024 16:39:05 +0530
Message-ID: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com> (raw)

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

reply

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Column type modification in big tables
  In-Reply-To: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox