MIME-Version: 1.0
References: <CAD=mzVUGj2Zg0RY9YJwgDzAeghPjuT0FOnNs-aysTBGss87Yyw@mail.gmail.com>
 <f519a562-667c-4a96-9844-a2aaa42928b3@aklaver.com> <CAD=mzVUS8mFK42zmVUUC3JkiN=9QSUZ_Qxayd6=KJwbqdd0HzA@mail.gmail.com>
 <CANzqJaBAOzCjXaBFLa6cYweVNf5LbBvLYFkWx_hb3YezOP3quw@mail.gmail.com>
 <CAD=mzVXK+4tLMbENMecruPbk8cmeE4smD=ENUrwx5wh4N7hZhA@mail.gmail.com>
 <acc8b587497a65d38fafe4e0030e252eda78398f.camel@cybertec.at>
 <20240713220545.cgjghaggksov3xkt@hjp.at> <f70b1f5c575497a615c797588964d3c21823ef7e.camel@cybertec.at>
 <20240715142744.7e7a3wtdfxinochf@hjp.at>
In-Reply-To: <20240715142744.7e7a3wtdfxinochf@hjp.at>
From: sud <suds1434@gmail.com>
Date: Tue, 16 Jul 2024 02:00:27 +0530
Message-ID: <CAD=mzVW9d0cPfccGVHLvCkcq4Mc-Wg5La3+XAHHEY1AMeOQ_Bw@mail.gmail.com>
Subject: Re: Dropping column from big table
To: pgsql-general@lists.postgresql.org, 
	Laurenz Albe <laurenz.albe@cybertec.at>, "Peter J. Holzer" <hjp-pgsql@hjp.at>
Content-Type: multipart/alternative; boundary="000000000000239cd3061d4f1a81"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVW9d0cPfccGVHLvCkcq4Mc-Wg5La3%2BXAHHEY1AMeOQ_Bw%40mail.gmail.com>
Precedence: bulk

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

On Mon, Jul 15, 2024 at 7:58=E2=80=AFPM Peter J. Holzer <hjp-pgsql@hjp.at> =
wrote:

>
> > Hm, true.
> >
> > You can always do
> >
> >   UPDATE tab SET id =3D id;
> >
> > followed by
> >
> >   VACUUM (FULL) tab;
>
> Yes, that should work. It needs about twice the size of the table in
> temporary space, though.
>
> Since the OP wrote that the table is "daily ... and 90 partitions"
> (which understand that there is one partition per day and partitions are
> kept for 90 days) it might be better to just wait. After 90 days all the
> partitions with the obsolete column will be gone.
>
>
>
Thank You very much.

As I understand, after dropping a column, it will still internally hold the
"NOT NULL" values in that column for all the rows, even though it's not
visible outside.

So, after the DROP column,  it will force update any of the columns as
below, Then it will force create another copy of each of the rows even if
the column is updated to the same value. The new copy will have the dropped
column with values as NULL. And the post "VACUUM FULL '' will clean all the
rows with "NOT NULL '' values of that dropped column and thus reclaim the
space.

But the only issue would be "VACUUM FULL" will take a table lock and also
it may take longer to run this vacuum on the full table considering the
size of the table in TB's. Thus, is it fine to just leave it post execution
of the "update" statement , so that the normal vacuum operation (which will
be online operation) and that will take care of the removal of old rows ?

 UPDATE tab SET id =3D id;
 VACUUM (FULL) tab;

 And also, As you mentioned we may also leave it as is and wait for the
partition to be dropped, so that the dropped column with "not null" values
which are still lying under the hood and are occupying space will be
removed automatically. But even then, is that dropped column still lying in
the rows with null values in it throughout its lifecycle, till the table
exists in the database?

 Seems there is no other option exist to drop the column with space
reclaimed from the table in immediate effect, other than above discussed.

--000000000000239cd3061d4f1a81
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 Mon, Jul 15, 2024 at 7:58=E2=80=AFPM P=
eter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp.at</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"><br>
&gt; Hm, true.<br>
&gt; <br>
&gt; You can always do<br>
&gt; <br>
&gt;=C2=A0 =C2=A0UPDATE tab SET id =3D id;<br>
&gt; <br>
&gt; followed by<br>
&gt; <br>
&gt;=C2=A0 =C2=A0VACUUM (FULL) tab;<br>
<br>
Yes, that should work. It needs about twice the size of the table in<br>
temporary space, though.<br>
<br>
Since the OP wrote that the table is &quot;daily ... and 90 partitions&quot=
;<br>
(which understand that there is one partition per day and partitions are<br=
>
kept for 90 days) it might be better to just wait. After 90 days all the<br=
>
partitions with the obsolete column will be gone.<br>
<br>=C2=A0 =C2=A0=C2=A0<br></blockquote><div>Thank=C2=A0You very much.</div=
><div><br></div>As I understand, after dropping a column, it will still int=
ernally hold the &quot;NOT NULL&quot; values in that column for all the row=
s, even though it&#39;s not visible outside.<br><br>So, after the DROP colu=
mn, =C2=A0it will force update any of the columns as below, Then it will fo=
rce create another copy of each of the rows even if the column is updated t=
o the same value. The new copy will have the dropped column with values as =
NULL. And the post &quot;VACUUM FULL &#39;&#39; will clean all the rows wit=
h &quot;NOT NULL &#39;&#39; values of that dropped column and thus reclaim =
the space. <br><br>But the only issue would be &quot;VACUUM FULL&quot; will=
 take a table lock and also it may take longer to run this vacuum on the fu=
ll table considering the size of the table in TB&#39;s. Thus, is it fine to=
 just leave it post execution of the &quot;update&quot; statement , so that=
 the normal vacuum operation (which will be online operation) and that will=
 take care of the removal of old rows ?<br><br>=C2=A0UPDATE tab SET id =3D =
id;<br>=C2=A0VACUUM (FULL) tab;<br>=C2=A0<br>=C2=A0And also, As you mention=
ed we may also leave it as is and wait for the partition to be dropped, so =
that the dropped column with &quot;not null&quot; values which are still ly=
ing under the hood and are occupying space will be removed automatically. B=
ut even then, is that dropped column still lying in the rows with null valu=
es in it throughout its lifecycle, till the table exists in the database? <=
br>=C2=A0<br>=C2=A0Seems=C2=A0there is no other option exist to drop the co=
lumn with space reclaimed from the table in immediate effect, other than ab=
ove discussed.<br><div>=C2=A0</div></div></div>

--000000000000239cd3061d4f1a81--