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 via email to