MIME-Version: 1.0
References: <CAC5iy61zUBySCMFH3JSKd6W922nV3fHMpXi+qUuPvXQ3shuOBg@mail.gmail.com>
 <202503191713.6pezw2uqo2c3@alvherre.pgsql> <CAFj8pRDhfOs5mDQNL7D7Z+CxX6ceXppQFLCOYPQKmLqH3BHotg@mail.gmail.com>
In-Reply-To: <CAFj8pRDhfOs5mDQNL7D7Z+CxX6ceXppQFLCOYPQKmLqH3BHotg@mail.gmail.com>
From: Siraj G <tosiraj.g@gmail.com>
Date: Wed, 19 Mar 2025 23:02:04 +0530
Message-ID: <CAC5iy60eCTtUJPTrwt3f0hgvWyn9Au7kiYGJYYxqj=+7Khx27g@mail.gmail.com>
Subject: Re: size of attributes table is too big
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000009b9000630b567b8"
Archived-At: <https://www.postgresql.org/message-id/CAC5iy60eCTtUJPTrwt3f0hgvWyn9Au7kiYGJYYxqj%3D%2B7Khx27g%40mail.gmail.com>
Precedence: bulk

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

Hello Pavel

The SQL instance is a target of google DMS and it does have a physical
replica.

A couple of weeks back we did have a performance issue and vacuum was run
at that time to fix the problem. Very soon we may run into the same problem
I presume.

Regards
Siraj

On Wed, Mar 19, 2025 at 10:47=E2=80=AFPM Pavel Stehule <pavel.stehule@gmail=
.com>
wrote:

>
>
> st 19. 3. 2025 v 18:14 odes=C3=ADlatel =C3=81lvaro Herrera <alvherre@alvh=
.no-ip.org>
> napsal:
>
>> Hello
>>
>> On 2025-Mar-19, Siraj G wrote:
>>
>> > I have a PG (v16) instance which is occupying around 1TB of storage.
>> Out of
>> > this, around 350GB is occupied by the table pg_catalog.pg_attribute.
>> > Why is the catalog table's size so big?
>>
>> Heavy use of temp tables is a known cause of this.
>>
>> > I think this table must have tons of dead tuples. Please suggest to me
>> if
>> > we can purge any data/shrink the size of this table.
>>
>> Yeah, I'd also bet that there are tons of dead tuples, or just unused
>> free space.  To purge it you would use VACUUM FULL, though that would
>> need to lock all accesses to the table.
>>
>> Does your instance run with autovacuum disabled perchance?
>>
>
> or long unclosed transactions, maybe forgotten replication slots, ...
>
> It is very strange so with this size it is still usable. Today hardware i=
s
> unbelievable strong
>
> Regards
>
> Pavel
>
>
>>
>> --
>> =C3=81lvaro Herrera         PostgreSQL Developer  =E2=80=94
>> https://www.EnterpriseDB.com/
>> Bob [Floyd] used to say that he was planning to get a Ph.D. by the "gree=
n
>> stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd=
'.
>> After collecting 500 such letters, he mused, a university somewhere in
>> Arizona would probably grant him a degree.              (Don Knuth)
>>
>>
>>

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

<div dir=3D"ltr">Hello Pavel<div><br></div><div>The SQL instance is a targe=
t of google DMS and it does have a physical replica.</div><div><br></div><d=
iv>A couple of weeks back we did have a performance issue and vacuum was ru=
n at that time to fix the problem. Very soon we may run into the same probl=
em I presume.</div><div><br></div><div>Regards</div><div>Siraj</div></div><=
br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Wed, Mar 19, 2025 at 10:47=E2=80=AFPM Pavel Stehule &lt;=
<a href=3D"mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">st 19. 3. 2025 v=C2=A018:14 odes=C3=ADlatel =
=C3=81lvaro Herrera &lt;<a href=3D"mailto:alvherre@alvh.no-ip.org" target=
=3D"_blank">alvherre@alvh.no-ip.org</a>&gt; napsal:<br></div><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Hello<br>
<br>
On 2025-Mar-19, Siraj G wrote:<br>
<br>
&gt; I have a PG (v16) instance which is occupying around 1TB of storage. O=
ut of<br>
&gt; this, around 350GB is occupied by the table pg_catalog.pg_attribute.<b=
r>
&gt; Why is the catalog table&#39;s size so big?<br>
<br>
Heavy use of temp tables is a known cause of this.<br>
<br>
&gt; I think this table must have tons of dead tuples. Please suggest to me=
 if<br>
&gt; we can purge any data/shrink the size of this table.<br>
<br>
Yeah, I&#39;d also bet that there are tons of dead tuples, or just unused<b=
r>
free space.=C2=A0 To purge it you would use VACUUM FULL, though that would<=
br>
need to lock all accesses to the table.<br>
<br>
Does your instance run with autovacuum disabled perchance?<br></blockquote>=
<div><br></div><div>or long unclosed transactions, maybe forgotten replicat=
ion slots, ...</div><div><br></div><div>It is very strange so with this siz=
e it is still usable. Today hardware is unbelievable strong</div><div><br><=
/div><div>Regards</div><div><br></div><div>Pavel <br></div><div>=C2=A0<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
-- <br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PostgreSQL Developer=
=C2=A0 =E2=80=94=C2=A0 <a href=3D"https://www.EnterpriseDB.com/" rel=3D"nor=
eferrer" target=3D"_blank">https://www.EnterpriseDB.com/</a><br>
Bob [Floyd] used to say that he was planning to get a Ph.D. by the &quot;gr=
een<br>
stamp method,&quot; namely by saving envelopes addressed to him as &#39;Dr.=
 Floyd&#39;.<br>
After collecting 500 such letters, he mused, a university somewhere in<br>
Arizona would probably grant him a degree.=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 (Don Knuth)<br>
<br>
<br>
</blockquote></div></div>
</blockquote></div>

--00000000000009b9000630b567b8--