Re: Trigger more frequent autovacuums of heavy insert tables

On Mon, Mar 03, 2025 at 12:18:37PM -0500, Melanie Plageman wrote:
> I noticed the docs wording is kind of different than that in
> postgresql.conf.sample. The docs wording mentions that the scale
> factor gets added to the threshold and postgresql.conf.sample does not
> (in master as well). I just wanted to make sure my updates to
> postgresql.conf.sample sound accurate.

> -#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table
> - # size before insert vacuum
> +#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of unfrozen pages
> + # inserted to before insert vacuum

Hm. So for autovacuum_vacuum_scale_factor and
autovacuum_analyze_scale_factor, we have

"fraction of table size before vacuum"
"fraction of table size before analyze"

And currently, for autovacuum_vacuum_insert_scale_factor, we have

"fraction of inserts over table size before insert vacuum"

I think the key change here is that we are replacing "table size" with
"unfrozen pages," which would give us

"fraction of inserts over unfrozen pages before insert vacuum"

However, I find that unclear, if for no other reason than I'm not sure what
"over" means in this context. A reader might think that refers to the
fraction (i.e., inserts / unfrozen pages), or they might think it means
"on" or "above." IMHO your proposed wording is much clearer. The only
part that feels a bit awkward to me is "to before". My mind wants the "to"
to be followed with a verb, so I stumbled the first time I read it and had
to read it again. Perhaps we could just say

"fraction of unfrozen pages before insert vacuum"

That more closely matches the other scale factor parameters. It's
admittedly quite terse, but so are the vast majority of other descriptions
in the sample file. I don't think those are intended to serve as
substitutes for the "real" documentation.

I would make the same argument for leaving out the base threshold. While
it is important information, these extremely concise descriptions don't
really have enough room for the nitty-gritty details.

--
nathan