MIME-Version: 1.0
References: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
 <CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
In-Reply-To: <CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
From: Lok P <loknath.73@gmail.com>
Date: Wed, 7 Aug 2024 17:00:02 +0530
Message-ID: <CAKna9VZRc4+Vzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d912ba061f163b21"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VZRc4%2BVzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ%40mail.gmail.com>
Precedence: bulk

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

On Wed, Aug 7, 2024 at 4:51=E2=80=AFPM sud <suds1434@gmail.com> 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 wil=
l
> 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 =3D CAST(old_column1 AS NUMERIC(3)),
> new_column2 =3D CAST(old_column2 AS varchar2(3)) ;
> commit;
> UPDATE tab1_part2 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)),
> new_column2 =3D CAST(old_column2 AS varchar2(3)) ;
> commit;
> UPDATE tab1_part3 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)),
> new_column2 =3D 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?

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Wed, Aug 7, 2024 at 4:51=E2=80=AFP=
M sud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div dir=3D"=
ltr" class=3D"gmail_attr"><br></div>Others may correct but i think, If you =
don&#39;t have the FK defined on these columns you can do below.<br><br><br=
>--Alter table add column which will be very fast within seconds as it will=
 just add it to the data dictionary.<br><br>ALTER TABLE tab1 ADD COLUMN new=
_column1 NUMERIC(3),=C2=A0 =C2=A0new_column2 varchar2(3);<br>							<br>			=
				<br><b>-- Back populate the data partition wise and commit, if it&#39;s=
 really needed</b><br><br>UPDATE tab1_part1 SET new_column1 =3D CAST(old_co=
lumn1 AS NUMERIC(3)), new_column2 =3D CAST(old_column2 AS varchar2(3)) ;<br=
>commit;<br>UPDATE tab1_part2 SET new_column1 =3D CAST(old_column1 AS NUMER=
IC(3)), new_column2 =3D CAST(old_column2 AS varchar2(3)) ;<br>commit;<br>UP=
DATE tab1_part3 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)), new_co=
lumn2 =3D CAST(old_column2 AS varchar2(3)) ;<br>commit;<br>.....<br><br><b>=
--Alter table drop old columns which will be very fast within seconds as it=
 will just drop it from the data dictionary.<br></b><br>ALTER TABLE your_ta=
ble DROP COLUMN old_column1, DROP COLUMN old_column2;=C2=A0</div></div></bl=
ockquote><div><br></div><div><br></div><br>Thank you so much. <br><br>I und=
erstand this will be the fastest possible way to achieve the column modific=
ation. <br><br><div>But talking about the dropped column which will be sitt=
ing in the table and consuming=C2=A0storage space, Is it fine to leave as i=
s or auto vacuum will remove the column values behind the scene and also an=
yway , once those partitions will be purged they will be by default purged.=
 Is this understanding correct?</div><div><br></div><div>=C2=A0And also wil=
l this have any impact on the partition=C2=A0maintenance which is currently=
 done by pg_partman as because the template table is now different internal=
ly(not from outside though). Will it cause conflict because=C2=A0of those d=
ropped columns from the main table?</div></div></div>

--000000000000d912ba061f163b21--