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,
