Re: Table size is constantly growing and causing performance problems

On Tue, Feb 11, 2025 at 11:45 PM srinivasan s <srinioracledba7(at)gmail(dot)com>
wrote:

> Hi Team,
>
> One of the tables in our database suddenly started growing very fast
> without any changes to the environment. it has grown over 10GB in the last
> week 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 continuously. we are using postgres version 12 on ubuntu
>
> We are running a vacuum analsye on a full database every weekend and an
> auto vacuum is set up.
>
> My observation on the DB so far.
> 1. The table is bloated around 50GB is bloat out of 95GB table size
>
> * current_database | schemaname | tablename
> | tbloat | wastedbytes*
>
> *---------------------+------------+-----------------------------------------------------------------+--------+-------------*
> *xyxyxyxy_production | public | xxxxxx
> | 2.5 | 51894779904*
>

What is the query which produced that? That looks like a user-space query,
which exists in multiple different iterations.

You can use the pg_freespacemap extension to see if PostgreSQL knows how to
find the free space in order to reuse it.

> 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 can use pg_stat_progress_vacuum to monitor the progress.

> Will this block the regular auto vacuum?
>

Yes, two vacuums won't run in the same table at the same time. But if the
bloat has been going on for a week, it is hard to see how it could be due
to an only 15 hour vacuum to prevent wraparound. It could be that this
gets interrupted repeatedly without ever finishing, but that seems
inconsistent with the last_autovacuum timestamp.

My best guess is that you have a long-held snapshot which is preventing
vacuum from being effective at its job.

Cheers,

Jeff