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
