Thread: Table size is constantly growing and causing performance problems
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,
Hi
12.02.2025 05:45:36 srinivasan s <srinioracledba7@gmail.com>:
> One of the tables in our database suddenly started growing very fast without any changes to the environment.
> we are using postgres version 12 on ubuntu
Is there a reason, you stay on v12 when v17 is available?
Are we to understand that the table takes in news rows but also those get deleted in about the same number so only little grow is expected if at all? Does count(*) reflect the size growth? If not did you check your processes for insert/delete? Do they apply/affect the same amount of rows before? Has there popped up an imbalance? I am assuming the processes as in my second question.
Cheers Thiemo
12.02.2025 05:45:36 srinivasan s <srinioracledba7@gmail.com>:
> One of the tables in our database suddenly started growing very fast without any changes to the environment.
> we are using postgres version 12 on ubuntu
Is there a reason, you stay on v12 when v17 is available?
Are we to understand that the table takes in news rows but also those get deleted in about the same number so only little grow is expected if at all? Does count(*) reflect the size growth? If not did you check your processes for insert/delete? Do they apply/affect the same amount of rows before? Has there popped up an imbalance? I am assuming the processes as in my second question.
Cheers Thiemo
On Wed, 2025-02-12 at 10:14 +0530, srinivasan s wrote: > 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 That's a mistake. Use a supported version. As it is, you could be suffering from some already fixed (data corruption?) bug. > We are running a vacuum analsye on a full database every weekend and an auto > 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. > > [...] > > 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. You should check "pg_stat_progress_vacuum" to see if the numbers are changing, 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 -- *E-Mail Disclaimer* Der Inhalt dieser E-Mail ist ausschliesslich fuer den bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen. *CONFIDENTIALITY NOTICE & DISCLAIMER *This message and any attachment are confidential and may be privileged or otherwise protected from disclosure and solely for the use of the person(s) or entity to whom it is intended. If you have received this message in error and are not the intended recipient, please notify the sender immediately and delete this message and any attachment from your system. If you are not the intended recipient, be advised that any use of this message is prohibited and may be unlawful, and you must not copy this message or attachment or disclose the contents to any other person.
Thanks @Laurenz Albe , let me take a look at the bug and view that you shared.
I didn't mention Vacuum full in my message, I mentioned vacuum analyze; it is scheduled to run every weekend.
On Wed, Feb 12, 2025 at 1:00 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2025-02-12 at 10:14 +0530, srinivasan s wrote:
> 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
That's a mistake. Use a supported version. As it is, you could be suffering
from some already fixed (data corruption?) bug.
> We are running a vacuum analsye on a full database every weekend and an auto
> 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.
>
> [...]
>
> 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.
You should check "pg_stat_progress_vacuum" to see if the numbers are changing,
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
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
srinivasan s schrieb am 12.02.2025 um 05:44: > 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 > > 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound) Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
Hi Shammat,
There were some very old idle sessions waiting for clientread, which we have killed. no idle in transaction for a long time
On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
srinivasan s schrieb am 12.02.2025 um 05:44:
> 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
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
Do you see "duplicate key violations" in the logs ?
Regards,
Mahesh Shetty
On Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com> wrote:
Hi Shammat,There were some very old idle sessions waiting for clientread, which we have killed. no idle in transaction for a long timeOn Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:srinivasan s schrieb am 12.02.2025 um 05:44:
> 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
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
Hi Mahesh, I see unique constraint violation errors on the logs, is this anyway related to the issue that I explained?
On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com> wrote:
Do you see "duplicate key violations" in the logs ?Regards,Mahesh ShettyOn Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com> wrote:Hi Shammat,There were some very old idle sessions waiting for clientread, which we have killed. no idle in transaction for a long timeOn Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:srinivasan s schrieb am 12.02.2025 um 05:44:
> 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
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
Searched yesterday's log, there were 2000+ unique constraints error. I don't know if it is the contributor
On Wed, 12 Feb, 2025, 1:42 pm srinivasan s, <srinioracledba7@gmail.com> wrote:
Hi Mahesh, I see unique constraint violation errors on the logs, is this anyway related to the issue that I explained?On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com> wrote:Do you see "duplicate key violations" in the logs ?Regards,Mahesh ShettyOn Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com> wrote:Hi Shammat,There were some very old idle sessions waiting for clientread, which we have killed. no idle in transaction for a long timeOn Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:srinivasan s schrieb am 12.02.2025 um 05:44:
> 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
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
Yes - The tuples are inserted into the heap and checked if they are valid as per the constraint. If it fails, it’s marked as deleted.
regards,
Mahesh Shetty
On Wed, Feb 12, 2025 at 1:53 PM srinivasan s <srinioracledba7@gmail.com> wrote:
Searched yesterday's log, there were 2000+ unique constraints error. I don't know if it is the contributorOn Wed, 12 Feb, 2025, 1:42 pm srinivasan s, <srinioracledba7@gmail.com> wrote:Hi Mahesh, I see unique constraint violation errors on the logs, is this anyway related to the issue that I explained?On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com> wrote:Do you see "duplicate key violations" in the logs ?Regards,Mahesh ShettyOn Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com> wrote:Hi Shammat,There were some very old idle sessions waiting for clientread, which we have killed. no idle in transaction for a long timeOn Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:srinivasan s schrieb am 12.02.2025 um 05:44:
> 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
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
What is the functional difference between regular autovaccum and autovaccum (to prevent wrap around)
On Wed, 12 Feb, 2025, 8:45 pm Mahesh Shetty, <maheshetty20@gmail.com> wrote:
Yes - The tuples are inserted into the heap and checked if they are valid as per the constraint. If it fails, it’s marked as deleted.regards,Mahesh ShettyOn Wed, Feb 12, 2025 at 1:53 PM srinivasan s <srinioracledba7@gmail.com> wrote:Searched yesterday's log, there were 2000+ unique constraints error. I don't know if it is the contributorOn Wed, 12 Feb, 2025, 1:42 pm srinivasan s, <srinioracledba7@gmail.com> wrote:Hi Mahesh, I see unique constraint violation errors on the logs, is this anyway related to the issue that I explained?On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com> wrote:Do you see "duplicate key violations" in the logs ?Regards,Mahesh ShettyOn Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com> wrote:Hi Shammat,There were some very old idle sessions waiting for clientread, which we have killed. no idle in transaction for a long timeOn Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:srinivasan s schrieb am 12.02.2025 um 05:44:
> 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
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
On Wed, 2025-02-12 at 22:12 +0530, srinivasan s wrote: > What is the functional difference between regular autovaccum and autovaccum (to prevent wrap around) The latter will want to visit all pages that are not "all frozen", and it won't silently die if its lock blocks a user process. Yours, Laurenz Albe -- *E-Mail Disclaimer* Der Inhalt dieser E-Mail ist ausschliesslich fuer den bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen. *CONFIDENTIALITY NOTICE & DISCLAIMER *This message and any attachment are confidential and may be privileged or otherwise protected from disclosure and solely for the use of the person(s) or entity to whom it is intended. If you have received this message in error and are not the intended recipient, please notify the sender immediately and delete this message and any attachment from your system. If you are not the intended recipient, be advised that any use of this message is prohibited and may be unlawful, and you must not copy this message or attachment or disclose the contents to any other person.
On Tue, Feb 11, 2025 at 11:45 PM srinivasan s <srinioracledba7@gmail.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 ubuntuWe 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 sizecurrent_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