Message-ID: <1c43ff66a3587ec99b64fff3caa5173ef972f5b1.camel@cybertec.at>
Subject: Re: Table size is constantly growing and causing performance
 problems
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: srinivasan s <srinioracledba7@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Wed, 12 Feb 2025 08:29:59 +0100
In-Reply-To: 
 <CAOEeMcVpRyRkXwFiyks9a_N1_2E2gYdx=0d=b3iq3ccNPza_aA@mail.gmail.com>
References: 
	<CAOEeMcVpRyRkXwFiyks9a_N1_2E2gYdx=0d=b3iq3ccNPza_aA@mail.gmail.com>
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1c43ff66a3587ec99b64fff3caa5173ef972f5b1.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-02-12 at 10:14 +0530, srinivasan s wrote:
> One of the tables in our database suddenly started=C2=A0 growing very fas=
t without
> any changes=C2=A0to the environment. it has grown over 10GB in the last w=
eek and
> this is causing performance issues. We ended up adding an index to solve =
the
> performance problem but the table growth didn't stop. It is growing conti=
nuously.
> we are using postgres version 12 on ubuntu

That's a mistake.  Use a supported version.  As it is, you could be sufferi=
ng
from some already fixed (data corruption?) bug.

> We are running a vacuum analsye on a full database every weekend and an a=
uto
> vacuum is set up.

VACUUM (FULL) is also a mistake.  It should be a regular VACUUM.

Funny, you say that you are experiencing bloat.  How can that be if you are
running VACUUM (FULL)?  Perhaps something is blocking VACUUM from removing
dead rows:
https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-=
rows/

> My observation on the DB so far.
>=20
> [...]
>
> 3. Also noticed that there is a auto vacuum=C2=A0job running on the table=
 with
>    (to prevent wraparound)
>=20
> I am not sure if this auto vacuum (to prevent wraparound) is progressing,
> it is running for more than 15 hours and status is active.

You should check "pg_stat_progress_vacuum" to see if the numbers are changi=
ng,
that is, if there is actually any progress.  That may also give you a clue =
as
to how long it will still take.

Yours,
Laurenz Albe


--=20

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den=20
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat=
=20
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,=20
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder=
=20
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich=
=20
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are=20
confidential and may be privileged or otherwise protected from disclosure=
=20
and solely for the use of the person(s) or entity to whom it is intended.=
=20
If you have received this message in error and are not the intended=20
recipient, please notify the sender immediately and delete this message and=
=20
any attachment from your system. If you are not the intended recipient, be=
=20
advised that any use of this message is prohibited and may be unlawful, and=
=20
you must not copy this message or attachment or disclose the contents to=20
any other person.