public inbox for [email protected]  

help / color / mirror / Atom feed

From: sud <[email protected]>
To: Lok P <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Column type modification in big tables
Date: Thu, 8 Aug 2024 01:05:56 +0530
Message-ID: <CAD=mzVUX13ZM16kP4QhY+F5XiLr=ezCXftKOTKA4eUvhphgOJw@mail.gmail.com> (raw)
In-Reply-To: <CAKna9VZRc4+Vzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ@mail.gmail.com>
References: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
	<CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
	<CAKna9VZRc4+Vzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ@mail.gmail.com>

On Wed, Aug 7, 2024 at 5:00 PM Lok P <[email protected]> wrote:

>
>
> On Wed, Aug 7, 2024 at 4:51 PM sud <[email protected]> wrote:
>
>>
>>
>> Others may correct but i think, If you don't have the FK defined on these
>> columns you can do below.
>>
>>
>> --Alter table add column which will be very fast within seconds as it
>> will just add it to the data dictionary.
>>
>> ALTER TABLE tab1 ADD COLUMN new_column1 NUMERIC(3),   new_column2
>> varchar2(3);
>>
>>
>> *-- Back populate the data partition wise and commit, if it's really
>> needed*
>>
>> UPDATE tab1_part1 SET new_column1 = CAST(old_column1 AS NUMERIC(3)),
>> new_column2 = CAST(old_column2 AS varchar2(3)) ;
>> commit;
>> UPDATE tab1_part2 SET new_column1 = CAST(old_column1 AS NUMERIC(3)),
>> new_column2 = CAST(old_column2 AS varchar2(3)) ;
>> commit;
>> UPDATE tab1_part3 SET new_column1 = CAST(old_column1 AS NUMERIC(3)),
>> new_column2 = CAST(old_column2 AS varchar2(3)) ;
>> commit;
>> .....
>>
>>
>> *--Alter table drop old columns which will be very fast within seconds as
>> it will just drop it from the data dictionary.*
>> ALTER TABLE your_table DROP COLUMN old_column1, DROP COLUMN old_column2;
>>
>
>
>
> Thank you so much.
>
> I understand this will be the fastest possible way to achieve the column
> modification.
>
> But talking about the dropped column which will be sitting in the table
> and consuming storage space, Is it fine to leave as is or auto vacuum will
> remove the column values behind the scene and also anyway , once those
> partitions will be purged they will be by default purged. Is this
> understanding correct?
>
>  And also will this have any impact on the partition maintenance which is
> currently done by pg_partman as because the template table is now different
> internally(not from outside though). Will it cause conflict because of
> those dropped columns from the main table?
>

I think leaving the table as is after the dropping column will be fine for
you because your regular partition maintenance/drop will slowly purge the
historical partitions and eventually they will be removed. But if you
update those new columns with the old column values, then autovacuum should
also take care of removing the rows with older column values (which are
dead actually) .

Not sure if pg_partman will cause any issue ,as because the table now has
the column data type/length changed. Others may confirm.

view thread (22+ messages)  latest in thread

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], [email protected]
  Subject: Re: Column type modification in big tables
  In-Reply-To: <CAD=mzVUX13ZM16kP4QhY+F5XiLr=ezCXftKOTKA4eUvhphgOJw@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