Обсуждение: Configure autovacuum
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 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
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
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 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.
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 adrian.klaver@aklaver.com