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,
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 |