MIME-Version: 1.0
References: <CAMxxg=a0PCvrSenxkTSjTsBygh5KJ-bqOo2ZqJvGcsjdiECOWg@mail.gmail.com>
In-Reply-To: <CAMxxg=a0PCvrSenxkTSjTsBygh5KJ-bqOo2ZqJvGcsjdiECOWg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 29 Dec 2025 11:06:00 -0500
Message-ID: <CANzqJaBiH+q2qiZiThvLa_LdvGRCY+ydBz0VELQbmmh2No6AJQ@mail.gmail.com>
Subject: Re: How did VACUUM ANALYZE reclaim large TOAST bloat at disk level in
 PostgreSQL 16?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fc474e0647196bb2"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBiH%2Bq2qiZiThvLa_LdvGRCY%2BydBz0VELQbmmh2No6AJQ%40mail.gmail.com>
Precedence: bulk

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

On Mon, Dec 29, 2025 at 10:53=E2=80=AFAM pramod gupta <mail2sony2010@gmail.=
com>
wrote:

> Hello Everyone,
>
> We have a table with a total size of ~628 GB, out of which ~601 GB was
> TOAST data.
> After running VACUUM ANALYZE on a weekly basis, the table size reduced
> significantly to ~109 GB, indicating a large amount of bloat removal.
>
> I would like to understand:
>
> How was VACUUM ANALYZE able to reclaim such a large amount of space,
> especially for TOAST data?
>
> Under what conditions does PostgreSQL reclaim disk space without requirin=
g
> VACUUM FULL or CLUSTER?
>
> Is this behavior expected in PostgreSQL 16, particularly for heavily
> updated or deleted TOASTed columns?
>
> Any insights or documentation references would be greatly appreciated.
>
> PostgreSQL version: 16
>

See the TRUNCATE option:
https://www.postgresql.org/docs/12/sql-vacuum.html

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Dec 29, 2025 at 10:53=E2=80=AFAM =
pramod gupta &lt;<a href=3D"mailto:mail2sony2010@gmail.com">mail2sony2010@g=
mail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_containe=
r"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello=
 Everyone,<br><br>We have a table with a total size of ~628 GB, out of whic=
h ~601 GB was TOAST data.<br>After running VACUUM ANALYZE on a weekly basis=
, the table size reduced significantly to ~109 GB, indicating a large amoun=
t of bloat removal.<br><br>I would like to understand:<br><br>How was VACUU=
M ANALYZE able to reclaim such a large amount of space, especially for TOAS=
T data?<br><br>Under what conditions does PostgreSQL reclaim disk space wit=
hout requiring VACUUM FULL or CLUSTER?<br><br>Is this behavior expected in =
PostgreSQL 16, particularly for heavily updated or deleted TOASTed columns?=
<br><br>Any insights or documentation references would be greatly appreciat=
ed.<br><br>PostgreSQL version: 16<br></div></blockquote><div><br class=3D"g=
mail-Apple-interchange-newline">See the TRUNCATE option:</div><div><a href=
=3D"https://www.postgresql.org/docs/12/sql-vacuum.html">https://www.postgre=
sql.org/docs/12/sql-vacuum.html</a></div><div><br></div></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--000000000000fc474e0647196bb2--