public inbox for [email protected]  

help / color / mirror / Atom feed

From: Shenavai, Manuel <[email protected]>
To: Adrian Klaver <[email protected]>
To: pgsql-general <[email protected]>
Subject: RE: Configure autovacuum
Date: Thu, 4 Jul 2024 15:16:15 +0000
Message-ID: <AM9PR02MB7410BBF4916E9720CBB6BA06E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <AM9PR02MB7410C756DC2DFADC7B4E2DD6E8C22@AM9PR02MB7410.eurprd02.prod.outlook.com>
	<[email protected]>

We see that our DB keeps increasing under high load (many updates). We see that our DB has a size of 200GB and we got 199GB bloat, 0 dead tuple. And when the DB is put on high load (many updates), we still see that the DB size grows. We try to find parameters to avoid DB growth.

I think we need to tweak the autovacuum settings and maybe limit the volume of data that can be written to the DB.

Is there any setting in postgres that would allow to write only certain volume? For example, limit the amount of data that can be written to a table to 100MB/minute.

Best regards,
Manuel

-----Original Message-----
From: Adrian Klaver <[email protected]> 
Sent: 14 June 2024 16:32
To: Shenavai, Manuel <[email protected]>; pgsql-general <[email protected]>
Subject: Re: Configure autovacuum

On 6/13/24 23:20, Shenavai, Manuel wrote:
> Hi everyone,
> 
> I would like to configure the autovacuum in a way that it runs very 
> frequently (i.e. after each update-statement). I tried the following 

Why?

What is the problem you are trying to solve?

> settings on my table:
> 
> alter table mytable set (autovacuum_vacuum_scale_factor  = 0.0);
> 
> alter table mytable set (autovacuum_vacuum_cost_delay  = 0.0);
> 
> alter table mytable set (autovacuum_vacuum_cost_limit  = 10000);
> 
> alter table mytable set (autovacuum_vacuum_threshold  = 1);
> 
> I do a lot of updates on a single tuple and I would expect that the 
> autovacuum would start basically after each update (due to 
> autovacuum_vacuum_threshold=1). But the autovacuum is not running.
> 
> Is it possible to configure postgres to autovacuum very aggressively 
> (i.e. after each update-statement)?
> 
> Thanks in advance &
> 
> Best regards,
> 
> Manuel
> 

-- 
Adrian Klaver
[email protected]

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: RE: Configure autovacuum
  In-Reply-To: <AM9PR02MB7410BBF4916E9720CBB6BA06E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox