MIME-Version: 1.0
References: <AM9PR02MB7410F60E0B73567795B4D034E8C12@AM9PR02MB7410.eurprd02.prod.outlook.com>
In-Reply-To: <AM9PR02MB7410F60E0B73567795B4D034E8C12@AM9PR02MB7410.eurprd02.prod.outlook.com>
From: Kashif Zeeshan <kashi.zeeshan@gmail.com>
Date: Thu, 13 Jun 2024 13:08:49 +0500
Message-ID: <CAAPsdhfgtid0=b4j0xC=MNG1tpQrMxL6OJb9GHm+PC7jVQG+WA@mail.gmail.com>
Subject: Re: TOAST Table / Dead Tuples / Free Pages
To: "Shenavai, Manuel" <manuel.shenavai@sap.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f03105061ac102f3"
Archived-At: <https://www.postgresql.org/message-id/CAAPsdhfgtid0%3Db4j0xC%3DMNG1tpQrMxL6OJb9GHm%2BPC7jVQG%2BWA%40mail.gmail.com>
Precedence: bulk

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

Hi

You can use the CLUSTER command, which will physically reorder the table
based on index, effectively reducing the size of the table without using
VACUUM.

CLUSTER your_table USING your_index;

Or you can use the pg_repack extension as well.

pg_repack -d your_database -t your_table

Regards
Kashif Zeeshan


On Thu, Jun 13, 2024 at 12:55=E2=80=AFPM Shenavai, Manuel <manuel.shenavai@=
sap.com>
wrote:

> Hi everyone,
>
>
>
> I created a simple scenario to understand the handling of TOASTs
> <https://www.postgresql.org/docs/current/storage-toast.html>: There is an
> empty database with a single table and record. The single record gets
> updated multiple times with 10MB (bytea column). I can see that the
> table/toasttable size is growing (500MB).
>
>
>
> Now I tried to find a way to get the DB size down again (it should be
> around 10MB instead of 500MB). I don=E2=80=99t want to use VACUUM FULL du=
e to the
> exclusive lock.
>
>
>
> Is there any way to remove the dead tuples and free the pages?
>
>
>
> Thanks in advance &
>
> Best regards,
>
> Manuel
>
>
>

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

<div dir=3D"ltr"><div>Hi</div><div><br></div><div>You can use the CLUSTER c=
ommand, which will physically reorder the table based on index, effectively=
 reducing the size of the table without using VACUUM.</div><div><br></div><=
div>CLUSTER your_table USING your_index;</div><div><br></div><div>Or you ca=
n use the pg_repack extension as well.</div><div><br></div><div>pg_repack -=
d your_database -t your_table<br></div><div><br></div><div>Regards</div><di=
v>Kashif Zeeshan<br></div><div><br></div><div><br></div></div><br><div clas=
s=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Jun 13, 202=
4 at 12:55=E2=80=AFPM Shenavai, Manuel &lt;<a href=3D"mailto:manuel.shenava=
i@sap.com">manuel.shenavai@sap.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex"><div class=3D"msg7608991686369011826">


<div style=3D"overflow-wrap: break-word;" lang=3D"en-DE">
<div class=3D"m_7608991686369011826WordSection1">
<p class=3D"MsoNormal"><span lang=3D"EN-US">Hi everyone,<u></u><u></u></spa=
n></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">I created a simple scenario to =
understand the handling of
<a href=3D"https://www.postgresql.org/docs/current/storage-toast.html" targ=
et=3D"_blank">TOASTs</a>: There is an empty database with a single table an=
d record. The single record gets updated multiple times with 10MB (bytea co=
lumn). I can see that the table/toasttable size is growing
 (500MB).<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Now I tried to find a way to ge=
t the DB size down again (it should be around 10MB instead of 500MB). I don=
=E2=80=99t want to use VACUUM FULL due to the exclusive lock.<u></u><u></u>=
</span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Is there any way to remove the =
dead tuples and free the pages?<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Thanks in advance &amp;<u></u><=
u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Best regards,<u></u><u></u></sp=
an></p>
<p class=3D"MsoNormal"><span lang=3D"EN-US">Manuel<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span lang=3D"EN-GB"><u></u>=C2=A0<u></u></span></p>
</div>
</div>

</div></blockquote></div>

--000000000000f03105061ac102f3--