Message-ID: <1bd0d7ac8f0ee3a38a55f960403e856ad0c9f82a.camel@cybertec.at>
Subject: Re: vacuuming taking long time for pg_attribute
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Siraj G <tosiraj.g@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Fri, 17 Jan 2025 09:46:22 +0100
In-Reply-To: 
 <CAC5iy63HKROj55PSZ3Ls7WmXVWi-DvRaqqRpp_7A6g1QTPAb=Q@mail.gmail.com>
References: 
	<CAC5iy63HKROj55PSZ3Ls7WmXVWi-DvRaqqRpp_7A6g1QTPAb=Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/1bd0d7ac8f0ee3a38a55f960403e856ad0c9f82a.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-01-17 at 11:45 +0530, Siraj G wrote:
> The PgSQL version is 16 and it runs in Cloud SQL managed by GCP.
>=20
> Problem was that we were unable to conveniently=C2=A0get the object detai=
ls in the
> schema browser (within the Cloud SQL Studio) as it was getting timeout ag=
ain
> and again. Several application jobs start to fail. Eventually we found th=
at
> pg_catalog.pg_attribute was having tons of dead tuples. While vacuum on t=
his
> took several hours, the problem got resolved eventually.
> Command ran: vacuum pg_attribute;
>=20
> I would like to understand how this issue can be prevented. We do have
> autovacuum ON and I could see the last vacuum on this table was just abou=
t
> 30hrs back.=C2=A0
>=20
> Appreciate any suggestions.

I can only guess, but my guess is that you are creating temporary tables
very frequently.  Creating a temporary table insers the columns into
"pg_attribute", and they get deleted again when the session ends.

Perhaps autovacuum was just not fast enough, perhaps there were
long-running transactions or queries that prevented autovacuum from cleanin=
g
up the dead rows in "pg_attribute".

Make sure your transactions are short and that autovacuum is configured
sufficiently aggressive.

Yours,
Laurenz Albe