MIME-Version: 1.0
References: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
In-Reply-To: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Wed, 7 Aug 2024 16:51:26 +0530
Message-ID: <CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000026fd95061f161da7"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVUmXmkdvvMG30G1%3DD4Kq3WqnzGo%3D0ov9JnRCs1p%3DKJiTQ%40mail.gmail.com>
Precedence: bulk

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

On Wed, Aug 7, 2024 at 4:39=E2=80=AFPM Lok P <loknath.73@gmail.com> wrote:

> Hello all,
> We have a postgres table which is a range partitions on a timestamp colum=
n
> having total size ~3TB holding a total ~5billion rows spanning across ~15=
0
> 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 an=
d
> 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)
>
>
>

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 =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;

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Wed, Aug 7, 2024 at 4:39=E2=80=AFPM Lo=
k P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&gt=
; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div di=
r=3D"ltr">Hello all,<div>We have a postgres table which is a range partitio=
ns on a timestamp column having total size ~3TB holding a total ~5billion r=
ows 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.<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 an=
d rewrite the whole table which may take hours of run time. <br><br>So tryi=
ng 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 considerin=
g 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>on=
e of the columns from varchar(20) to varchar(2)<br>one of the columns from =
Number(10,2) to Numeric(8,2)<br></div><div><br></div><div><br></div></div><=
/blockquote><div><br></div><div><br></div>Others may correct but i think, I=
f 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 i=
t will just add it to the data dictionary.<br><br>ALTER TABLE tab1 ADD COLU=
MN 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_column1 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=
 NUMERIC(3)), new_column2 =3D CAST(old_column2 AS varchar2(3)) ;<br>commit;=
<br>UPDATE tab1_part3 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)), =
new_column2 =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 y=
our_table DROP COLUMN old_column1, DROP COLUMN old_column2;=C2=A0</div></di=
v>

--00000000000026fd95061f161da7--