public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Shenavai, Manuel <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Configure autovacuum
Date: Fri, 14 Jun 2024 09:41:02 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM9PR02MB7410C756DC2DFADC7B4E2DD6E8C22@AM9PR02MB7410.eurprd02.prod.outlook.com>
References: <AM9PR02MB7410C756DC2DFADC7B4E2DD6E8C22@AM9PR02MB7410.eurprd02.prod.outlook.com>

On Fri, 2024-06-14 at 06:20 +0000, Shenavai, Manuel wrote:
> 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 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)?

The formula in the source code is

        /* Determine if this table needs vacuum or analyze. */
        *dovacuum = force_vacuum || (vactuples > vacthresh) ||
            (vac_ins_base_thresh >= 0 && instuples > vacinsthresh);

So you need to update at least two rows to exceed the threshold.

If you want a single update to trigger autovacuum, you have to set
"autovacuum_vacuum_threshold" to 0.

I cannot imagine a situation where such a setting would be beneficial.
Particularly if you have lots of updates, this will just burn server resources
and may starve out other tables that need VACUUM.

Yours,
Laurenz Albe

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: <[email protected]>

* 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