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> <CAD=mzVW9d0cPfccGVHLvCkcq4Mc-Wg5La3+XAHHEY1AMeOQ_Bw@mail.gmail.com>
 <20240716003731.m75sye7qq74wadhe@hjp.at>
In-Reply-To: <20240716003731.m75sye7qq74wadhe@hjp.at>
From: sud <suds1434@gmail.com>
Date: Tue, 16 Jul 2024 09:56:16 +0530
Message-ID: <CAD=mzVVTE1Jx0fpsQ=OVW0CL5LiGg_RHA4kq+cbPqTShA5EwVw@mail.gmail.com>
Subject: Re: Dropping column from big table
To: pgsql-general@lists.postgresql.org, "Peter J. Holzer" <hjp-pgsql@hjp.at>
Content-Type: multipart/alternative; boundary="000000000000e68d8d061d55bf51"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVVTE1Jx0fpsQ%3DOVW0CL5LiGg_RHA4kq%2BcbPqTShA5EwVw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jul 16, 2024 at 6:07=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> =
wrote:

>
> > 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 ?
>
> This is unlikely to recover the space.
>
> The UPDATE will duplicate all rows. Since - presumably - there isn't
> much free space within each partition the new rows will go at the end of
> each partition, effectively doubling its size.
>
> A regular VACUUM (whether autovacuum or invoked manually) will then
> remove the old rows. and make the space available for new data. But
> since that newly free space is at the beginning of each partition it
> can't be returned to the OS. It would be available for new data written
> to those partitions I guess not much is written to old partitons.
>
> You could, however, do this in small steps and vacuum after each.
> Something like this (in Python)
>
>     conn =3D ...
>     csr =3D conn.cursor()
>     for i in range(100):
>         csr.execute("UPDATE tab set id =3D id WHERE id % 100 =3D %s", (i,=
))
>         conn.commit()
>         csr.execute("VACUUM tab")
>         conn.commit()
>
> That might just be able to squeeze the new rows in between the existing
> rows and not grow the table.
>
>
> >  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?
>
> Yes. But a nullable column with a null value takes only a single bit of
> storage, so that's negligible.
>
>
>

Thank you so much.

Normal vacuum marks the space occupied by the dead tuples as free or
reusable but vacuum full removes those completely. However even with
"vacuum full", the old rows will be removed completely from the storage ,
but the new rows will always be there with the 'dropped' column still
existing under the hood along with the table storage,  with just carrying
"null" values in it. However,  as it's a single bit of storage so will be
having negligible overhead. If we want to fully remove that column from the
table , we may have to create a new table and dump the data into that from
the existing table and then rename it back to old. Is this understanding
correct?

--000000000000e68d8d061d55bf51
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 Tue, Jul 16, 2024 at 6:07=E2=80=AFAM 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; But the only issue would be &quot;VACUUM FULL&quot; will take a table =
lock and also it<br>
&gt; may take longer to run this vacuum on the full table considering the s=
ize of<br>
&gt; the table in TB&#39;s. Thus, is it fine to just leave it post executio=
n of the<br>
&gt; &quot;update&quot; statement , so that the normal vacuum operation (wh=
ich will be online<br>
&gt; operation) and that will take care of the removal of old rows ?<br>
<br>
This is unlikely to recover the space.<br>
<br>
The UPDATE will duplicate all rows. Since - presumably - there isn&#39;t<br=
>
much free space within each partition the new rows will go at the end of<br=
>
each partition, effectively doubling its size. <br>
<br>
A regular VACUUM (whether autovacuum or invoked manually) will then<br>
remove the old rows. and make the space available for new data. But<br>
since that newly free space is at the beginning of each partition it<br>
can&#39;t be returned to the OS. It would be available for new data written=
<br>
to those partitions I guess not much is written to old partitons.<br>
<br>
You could, however, do this in small steps and vacuum after each.<br>
Something like this (in Python)<br>
<br>
=C2=A0 =C2=A0 conn =3D ...<br>
=C2=A0 =C2=A0 csr =3D conn.cursor()<br>
=C2=A0 =C2=A0 for i in range(100):<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 csr.execute(&quot;UPDATE tab set id =3D id WHER=
E id % 100 =3D %s&quot;, (i,))<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.commit()<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 csr.execute(&quot;VACUUM tab&quot;)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.commit()<br>
<br>
That might just be able to squeeze the new rows in between the existing<br>
rows and not grow the table.<br>
<br>
<br>
&gt; =C2=A0And also, As you mentioned we may also leave it as is and wait f=
or the<br>
&gt; partition to be dropped, so that the dropped column with &quot;not nul=
l&quot; values<br>
&gt; which are still lying under the hood and are occupying space will be r=
emoved<br>
&gt; automatically. But even then, is that dropped column still lying in th=
e rows<br>
&gt; with null values in it throughout its lifecycle, till the table exists=
 in the<br>
&gt; database?<br>
<br>
Yes. But a nullable column with a null value takes only a single bit of<br>
storage, so that&#39;s negligible.<br>
<br>=C2=A0 =C2=A0=C2=A0<br></blockquote><div><br></div><div>Thank you so mu=
ch.</div><div><br></div><div>Normal vacuum marks the space occupied by the =
dead tuples as free or reusable but vacuum full removes those completely. H=
owever even with &quot;vacuum full&quot;, the old rows will be removed comp=
letely from the storage , but the new rows will always be there with the &#=
39;dropped&#39; column still existing under the hood along with the table s=
torage,=C2=A0 with just carrying &quot;null&quot; values in it. However,=C2=
=A0 as it&#39;s a single bit of storage so will be having negligible overhe=
ad. If we want to fully remove that column from the table , we may have to =
create a new table and dump the data into that from the existing table and =
then rename it back to old. Is this understanding correct?</div></div></div=
>

--000000000000e68d8d061d55bf51--