Re: Trigger more frequent autovacuums of heavy insert tables

On Thu, 06 Feb 2025 at 10:42, Melanie Plageman <melanieplageman(at)gmail(dot)com> wrote:
> Attached v6 is rebased over 306dc520b9dfd60
>
> On Wed, Feb 5, 2025 at 8:54 PM wenhui qiu <qiuwenhuifx(at)gmail(dot)com> wrote:
>>
>> Hi Melanie Plageman
>> Thank you for working on this ,Actually, there were two patches
>> aimed at optimizing vacuum-triggered processes, and one of them
>> reached a consensus and has been
>> committed:https://commitfest.postgresql.org/52/5046/ ,
>> https://commitfest.postgresql.org/51/5395/, Maybe referring to the
>> already committed patch and setting a maximum value for
>> vacuum_max_ins_threshold would be more acceptable.
>
> We could add autovacuum_vacuum_insert_max_threshold, but with an
> insert-only workload, we can expect that the cold data is being
> frozen. By calculating the threshold based on unfrozen data, we are
> effectively capping the threshold for inserted data without adding
> another guc. If any of that data is being unfrozen via updates or
> deletes, then the autovacuum_vacuum_max_threshold would apply.
>
> Perhaps I'm missing a case where calculating the insert threshold on
> unfrozen data would not act as a cap, in which case I could get on
> board with a guc.
>

Make sense.

It appears that there is an incorrect indentation in the config.sgml file.
The <literal> is accidentally removed.

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 38244409e3c..571c73668f9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8652,10 +8652,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</term>
<listitem>
<para>
- Specifies a fraction of the table size to add to
+ Specifies a fraction of the active (unfrozen) table size to add to
<varname>autovacuum_vacuum_insert_threshold</varname>
when deciding whether to trigger a <command>VACUUM</command>.
- The default is <literal>0.2</literal> (20% of table size).
+ The default is <literal>0.2</literal> (20% of active table size).
This parameter can only be set in the <filename>postgresql.conf</filename>
file or on the server command line;
but the setting can be overridden for individual tables by

--
Regrads,
Japin Li