Re: Configure autovacuum

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