MIME-Version: 1.0
References: <mail.669c3e0f.59c4.4be7fe7e6c0b49b1@storage.wm.amazon.com> <01010190d2526ef9-5d0a93fe-30ad-4d6b-9bc8-269c188bd42e-000000@us-west-2.amazonses.com>
In-Reply-To: <01010190d2526ef9-5d0a93fe-30ad-4d6b-9bc8-269c188bd42e-000000@us-west-2.amazonses.com>
From: khan Affan <bawag773@gmail.com>
Date: Mon, 22 Jul 2024 16:21:22 +0500
Message-ID: <CAF4emOmFCx33fvTqROw0u++vDoRH5w0D0YHVHSgR6VRsjq7d2w@mail.gmail.com>
Subject: Re: Bloated pg_catalog.pg_largeobjects
To: postgresql@thewickedtribe.net
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005dfbf4061dd43f7f"
Archived-At: <https://www.postgresql.org/message-id/CAF4emOmFCx33fvTqROw0u%2B%2BvDoRH5w0D0YHVHSgR6VRsjq7d2w%40mail.gmail.com>
Precedence: bulk

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

Hi

I would suggest to backup your DB before doing such a thing.

Run Vaccum Full, (VACUUM FULL pg_catalog.pg_largeobject) Running this on
the system table might be risky Make sure you backup the database.

& if you are using PG version above 9.1 use Pg_repack to reclaim the space.

Note:  It can be disruptive, so planning and preparing for potential
downtime is essential.

Thanks & regards


*Muhammad Affan (*=EC=95=84=ED=8C=90*)*

*PostgreSQL Technical Support Engineer** / Pakistan R&D*

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Sun, Jul 21, 2024 at 3:46=E2=80=AFAM <postgresql@thewickedtribe.net> wro=
te:

> Hello All,
>
> I've got a cluster that's having issues with pg_catalog.pg_largeobject
> getting massively bloated. Vacuum is running OK and there's 700GB of free
> space in the table and only 100GB of data, but subsequent inserts seem to
> be not using space from the FSM and instead always allocating new pages.
> The table just keeps growing.
>
> Is this a known thing, maybe something special about LOs?
>
> Also, is the only way to recover space here a vacuum full on the table
> since it's a catalog table?
>
> Thanks,
> --
> Jon Erdman (aka StuckMojo on IRC)
>     PostgreSQL Zealot
>

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

<div dir=3D"ltr">Hi=C2=A0<br><br>I would suggest to backup your DB before d=
oing such a thing.<br><br>Run Vaccum=C2=A0Full, (<code class=3D"gmail-">VAC=
UUM FULL pg_catalog.pg_largeobject</code>) Running this on the system table=
 might be risky Make sure you backup the database.<br><br>&amp; if you are =
using PG version above 9.1 use Pg_repack to reclaim the space.<br><br>Note:=
=C2=A0

 It can be disruptive, so planning and preparing for potential downtime is =
essential.

<br><br>Thanks &amp;=C2=A0regards<br><br><table width=3D"180px" style=3D"te=
xt-align:center;border-width:1px 2px 1px 1px;border-style:solid;border-colo=
r:rgb(255,255,255) rgb(216,216,216) rgb(255,255,255) rgb(255,255,255)"><tbo=
dy><tr align=3D"center"><td style=3D"border:1px solid rgb(255,255,255)"><im=
g width=3D"96" height=3D"71" src=3D"https://ci3.googleusercontent.com/mail-=
sig/AIorK4xPDLoHVVvHOBlBBe0cMeIZnfE5Gg8-8_LKsSpVq4Elp7C8VQOCzn4kW2pPgxDqKka=
xcjv4W7k"><br></td></tr></tbody></table><p style=3D"line-height:1.2;margin-=
top:6pt;margin-bottom:6pt"><b><span style=3D"font-family:Arial;color:rgb(34=
,85,255);background-color:transparent;vertical-align:baseline">Muhammad Aff=
an (</span></b><span style=3D"background-color:transparent;vertical-align:b=
aseline"><font color=3D"#2a363f" face=3D"ui-sans-serif, system-ui, -apple-s=
ystem, BlinkMacSystemFont, Segoe UI, Roboto, Helvetica Neue, Arial, Noto Sa=
ns, sans-serif, Apple Color Emoji, Segoe UI Emoji, Segoe UI Symbol, Noto Co=
lor Emoji"><span style=3D"font-size:16px">=EC=95=84=ED=8C=90</span></font><=
font color=3D"#2255ff" face=3D"Arial"><b>)</b></font></span></p><p dir=3D"l=
tr" style=3D"line-height:1.2;margin-top:6pt;margin-bottom:6pt"><b><span sty=
le=3D"font-family:Arial;background-color:transparent;vertical-align:baselin=
e"><font color=3D"#2255ff" size=3D"2">PostgreSQL Technical Support Engineer=
</font></span></b><b><span style=3D"font-family:Arial;background-color:tran=
sparent;vertical-align:baseline"><font color=3D"#022ea9" style=3D"font-size=
:13.3333px">=C2=A0/ Pakistan R&amp;D</font></span></b></p><p dir=3D"ltr" st=
yle=3D"line-height:1.2;margin-top:6pt;margin-bottom:6pt"></p><table width=
=3D"355px" align=3D"left" style=3D"border:1px solid rgb(255,255,255);margin=
-left:10px"><tbody><tr><td style=3D"font-family:Roboto,sans-serif;border:1p=
x solid rgb(255,255,255)">Interlace Plaza 4th floor Twinhub office 32 I8 Ma=
rkaz, Islamabad, Pakistan</td></tr></tbody></table><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Jul 21, 2024=
 at 3:46=E2=80=AFAM &lt;<a href=3D"mailto:postgresql@thewickedtribe.net">po=
stgresql@thewickedtribe.net</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><u></u>


<div>
<div><p style=3D"margin:0px;font-family:Arial,Tahoma,Helvetica,sans-serif;f=
ont-size:small">Hello All,<br><br>I&#39;ve got a cluster that&#39;s having =
issues with pg_catalog.pg_largeobject getting massively bloated. Vacuum is =
running OK and there&#39;s 700GB of free space in the table and only 100GB =
of data, but subsequent inserts seem to be not using space from the FSM and=
 instead always allocating new pages. The table just keeps growing.<br><br>=
Is this a known thing, maybe something special about LOs?<br><br>Also, is t=
he only way to recover space here a vacuum full on the table since it&#39;s=
 a catalog table?<br><br>Thanks,</p><div><span>--=C2=A0</span><br>Jon Erdma=
n (aka StuckMojo on IRC)<br>=C2=A0=C2=A0=C2=A0 PostgreSQL Zealot</div></div=
>
</div>
</blockquote></div>

--0000000000005dfbf4061dd43f7f--