Date: Tue, 16 Jul 2024 02:37:31 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Dropping column from big table
Message-ID: <20240716003731.m75sye7qq74wadhe@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="mwr7vnspvfa73srn"
Content-Disposition: inline
In-Reply-To: <CAD=mzVW9d0cPfccGVHLvCkcq4Mc-Wg5La3+XAHHEY1AMeOQ_Bw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240716003731.m75sye7qq74wadhe%40hjp.at>
Precedence: bulk


--mwr7vnspvfa73srn
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-07-16 02:00:27 +0530, sud wrote:
>=20
> 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
>     >
>     >=C2=A0 =C2=A0UPDATE tab SET id =3D id;
>     >
>     > followed by
>     >
>     >=C2=A0 =C2=A0VACUUM (FULL) tab;
>=20
>     Yes, that should work. It needs about twice the size of the table in
>     temporary space, though.
>=20
>     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.
>=20
>     =C2=A0 =C2=A0=C2=A0
>=20
> Thank=C2=A0You very much.
>=20
> As I understand, after dropping a column, it will still internally hold t=
he
> "NOT NULL" values in that column for all the rows, even though it's not v=
isible
> outside.
>=20
> So, after the DROP column, =C2=A0it will force update any of the columns =
as below,
> Then it will force create another copy of each of the rows even if the co=
lumn
> is updated to the same value. The new copy will have the dropped column w=
ith
> 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.

Correct.


> 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 o=
nline
> 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.=20

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.


> =C2=A0And 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 remo=
ved
> automatically. But even then, is that dropped column still lying in the r=
ows
> 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.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--mwr7vnspvfa73srn
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaVwMYACgkQ8g5IURL+
KF2FWw/9H2UQ0ZkQ0gnHakpEEVvjAmLuWyUXlT2h5BCoApwWsUX+iWSGRkvvZVIm
BfOGHIhTvEFiHzaTF7Q/21FaOOaHIoRkE7IpTL9ehw0pCFdXyuhVXFJpjyb4fkkV
ikSwDk/GR0XopMR1Y21xZJnUAIWlyPypg7SkJRZlIc9GyXU3Vvj3z82gWOL40IGl
dKuT56bUsFrEWNxCXqVOFh/GLLWi+HkMIipTlOAi0Bb8Wp7Hcx9GN2DJmIn/AbQz
SbeEyXwjRpNxH4wVLmZLDNVdfq0Igln890lZ+gz1CsQjUJtcBZFRvBQqBwi9EU/m
696hsydkw/Tl/3OiMf3QxzXln1m5QR6bEmiaZnURB3DJq2fLfV8liXRCqy87l81n
O7g6DNALPZ8PWkZAFu5Cfxq2O84hfS5YSlZSfLG9xCF3BATwmgzar8ISSAEna1fA
Fb1zQgH/0a6TujB9a60DxNk6+f+0H+u/29qyabI9Sq2TMIU2/+LZeZex6FaH+VNf
srzJ8T/IT/Zzo/3STIj+ttMVb5QQ3Wrqiwzcvz5j2I41xOYgjLC0HPsj5JX4FHFD
EegwJlwOmBh5Ah/Yp77e+RpumeHIWVukvDCf6zumUhTMmfLUOQ7D3IxTIhkMvBzd
1wEUYozs7vwqxfhT9sHV3dcjKnuKmR2heSHPUGZLhjvKvDri06w=
=h/qz
-----END PGP SIGNATURE-----

--mwr7vnspvfa73srn--