Date: Fri, 28 Feb 2025 00:51:41 +0100 (CET)
From: Jean-Paul POZZI <jp.pozzi@izzop.net>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Message-ID: <1030640143.483.1740700301871.JavaMail.zextras@jpp.fr>
In-Reply-To: 
 <CANzqJaAmM-d7nZEAhA7NdcYD5aiXivzNFA4wP044=dh9seJ+HA@mail.gmail.com>
References: 
 <CA+wokJ8utE8=5OAx8e_9Zo3S9QUoKy0pTK27PZqYgRujpdvVYA@mail.gmail.com>
 <CANzqJaAmM-d7nZEAhA7NdcYD5aiXivzNFA4wP044=dh9seJ+HA@mail.gmail.com>
Subject: RE: delete and pg_toast = shrink database?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=_c22ee23f-d246-4e4c-a786-ec688ac09575"
Thread-Topic: delete and pg_toast = shrink database?
Thread-Index: zSofcoXrtLYXLrJ/cis2AJWT7nzVEA==
Archived-At: 
 <https://www.postgresql.org/message-id/1030640143.483.1740700301871.JavaMail.zextras%40jpp.fr>
Precedence: bulk

--=_c22ee23f-d246-4e4c-a786-ec688ac09575
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hello,


As a precaution, save the data to be purged so that you can possibly use it=
 in the future.


Regards


JP P


De: "Ron Johnson" <ronljohnsonjr@gmail.com>
=C3=80: "Pgsql-admin" <pgsql-admin@lists.postgresql.org>
Envoy=C3=A9: vendredi 28 f=C3=A9vrier 2025 00:39
Objet: Re: delete and pg_toast =3D shrink database?


On Thu, Feb 27, 2025 at 4:19=E2=80=AFPM Edwin UY <edwin.uy@gmail.com> wrote=
:
Hi,


DB is currently 500G. About 75% of this is pg_toast.
Had advised the application team to check for data purging as there are som=
e very old data.


I've been in that same situation.=C2=A0 Had to write the purge job myself..=
.

Is it correct to assume that doing so should shrink the database size?


No. DELETE=C2=A0+ VACUUM=C2=A0frees space in the data files.=C2=A0 (This is=
 good once you get a regular purge process running, since the post-purge va=
cuum will free up space for the next records.=C2=A0 The tables' free space =
percentages will then hover between relatively high and low ranges, unless =
insert volume is growing.)


I believe I have to run vacuum full/table at some stage to really shrink it=
?


Also, no.=C2=A0 But then yes.


VACUUM FULL (use pg_repack instead!)
1.=C2=A0copies=C2=A0the remaining data to a new (temporary) table and rebui=
lds the indices,
2. deletes the old files,
3. renames the temporary table to the original name.


Thus, you'll temporarily need=C2=A0more=C2=A0disk space.


--

Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.

<Redacted> lobster!
--=_c22ee23f-d246-4e4c-a786-ec688ac09575
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><style>p {margin:0};</style><body><div style=3D"font-family: arial, h=
elvetica, sans-serif; font-size: 12pt; color: #000000"><p>Hello,</p>
<p>&nbsp;</p>
<p><span class=3D"HwtZe" lang=3D"en"><span class=3D"jCAhz ChMk0b"><span cla=
ss=3D"ryNqvb">As a precaution, save the data to be purged so that you can p=
ossibly use it in the future.</span></span></span></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><span class=3D"HwtZe" lang=3D"en">Regards</span></p>
<div class=3D"signature-div">&nbsp;</div>
<div class=3D"signature-div">JP P</div>
<p><br><br></p>
<hr id=3D"zwchr">
<div style=3D"color: black; font-size: 12pt; font-family: tahoma, arial, he=
lvetica, sans-serif;"><strong>De:</strong> "Ron Johnson" &lt;ronljohnsonjr@=
gmail.com&gt; <br><strong>=C3=80:</strong> "Pgsql-admin" &lt;pgsql-admin@li=
sts.postgresql.org&gt; <br><strong>Envoy=C3=A9:</strong> vendredi 28 f=C3=
=A9vrier 2025 00:39 <br><strong>Objet:</strong> Re: delete and pg_toast =3D=
 shrink database? <br><br>
<div dir=3D"ltr">
<div dir=3D"ltr">On Thu, Feb 27, 2025 at 4:19=E2=80=AFPM Edwin UY &lt;<a hr=
ef=3D"mailto:edwin.uy@gmail.com" target=3D"_blank" rel=3D"nofollow noopener=
 noreferrer">edwin.uy@gmail.com</a>&gt; wrote:</div>
<div class=3D"gmail_quote gmail_quote_container">
<blockquote class=3D"gmail_quote" style=3D"margin: 0px 0px 0px 0.8ex; borde=
r-left: 1px solid rgb( 204 , 204 , 204 ); padding-left: 1ex;">
<div dir=3D"ltr">
<div><span style=3D"font-family: monospace;">Hi,</span></div>
<div><span style=3D"font-family: monospace;">&nbsp;</span></div>
<div><span style=3D"font-family: monospace;">DB is currently 500G. About 75=
% of this is pg_toast.</span></div>
<div><span style=3D"font-family: monospace;">Had advised the application te=
am to check for data purging as there are some very old data.</span></div>
</div>
</blockquote>
<div>&nbsp;</div>
<div>I've been in that same situation.&nbsp; Had to write the purge job mys=
elf...</div>
<div>&nbsp;</div>
<blockquote class=3D"gmail_quote" style=3D"margin: 0px 0px 0px 0.8ex; borde=
r-left: 1px solid rgb( 204 , 204 , 204 ); padding-left: 1ex;">
<div dir=3D"ltr">
<div><span style=3D"font-family: monospace;">Is it correct to assume that d=
oing so should shrink the database size?</span></div>
</div>
</blockquote>
<div>&nbsp;</div>
<div>No. DELETE&nbsp;+ VACUUM&nbsp;frees space in the data files.&nbsp; (Th=
is is good once you get a regular purge process running, since the post-pur=
ge vacuum will free up space for the next records.&nbsp; The tables' free s=
pace percentages will then hover between relatively high and low ranges, un=
less insert volume is growing.)</div>
<div>&nbsp;</div>
<blockquote class=3D"gmail_quote" style=3D"margin: 0px 0px 0px 0.8ex; borde=
r-left: 1px solid rgb( 204 , 204 , 204 ); padding-left: 1ex;">
<div dir=3D"ltr">
<div><span style=3D"font-family: monospace;">I believe I have to run vacuum=
 full/table at some stage to really shrink it?</span></div>
</div>
</blockquote>
</div>
<div>&nbsp;</div>
<div>Also, no.&nbsp; But then yes.</div>
<div>&nbsp;</div>
<div>VACUUM FULL (use pg_repack instead!)</div>
<div>1.&nbsp;<strong>copies</strong>&nbsp;the remaining data to a new (temp=
orary) table and rebuilds the indices,</div>
<div>2. deletes the old files,</div>
<div>3. renames the temporary table to the original name.</div>
<div>&nbsp;</div>
<div>Thus, you'll temporarily need&nbsp;<strong>more</strong>&nbsp;disk spa=
ce.</div>
<div>&nbsp;</div>
<span class=3D"gmail_signature_prefix">-- </span><br>
<div class=3D"gmail_signature" dir=3D"ltr">
<div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.
<div>Don't boil me, I'm still alive.<br>
<div>
<div>&lt;Redacted&gt; lobster!</div>
</div>
</div>
</div>
</div>
</div>
</div></div></body></html>
--=_c22ee23f-d246-4e4c-a786-ec688ac09575--