Table size is constantly growing and causing performance problems

From: srinivasan s <srinioracledba7(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Table size is constantly growing and causing performance problems
Date: 2025-02-12 04:44:53
Message-ID: CAOEeMcVpRyRkXwFiyks9a_N1_2E2gYdx=0d=b3iq3ccNPza_aA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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*

2. the last autovacuum

* table_name | last_autovacuum | last_autoanalyze
| last_vacuum | last_analyze*
*------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------*
* xxxxxx | 2025-02-09 03:09:46.938845+00 | 2025-02-07
23:45:11.367977+00 | 2025-02-08 03:12:33.468125+00 | 2025-02-10
21:09:57.461369+00*
*(1 row)*

3. Also noticed that there is a auto vacuum job running on the table with
(to prevent wraparound)

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. Will this block
the regular auto vacuum?

My assumption is the bloated rows on the table are not reused and the table
is growing. I am trying to find out the cause and fix it.

Looking for some help around this topic, any suggestions or ideas to
troubleshoot and fix would be highly appreciated.

Thanks,

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Thiemo Kellner 2025-02-12 06:39:08 Re: Table size is constantly growing and causing performance problems
Previous Message Sbob 2025-02-11 22:57:14 Re: Security definer function to alter a sequence