MIME-Version: 1.0
References: 
 <CAApHDvp1=FOs6GneTzLSCHnCmC7z1_80=U3M=CKd82-pwS3YHg@mail.gmail.com>
 <aPuWev3D9M4iGCUt@nathan>
 <CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
 <aP-YgrcPi0EhgR9x@nathan>
 <CAA5RZ0u2Mbks+O2DKBYen94AH3OMUcg+A7wvxrXYkmjTddBx4g@mail.gmail.com>
 <aP_g61kSkGAQOu3F@nathan>
 <CAA5RZ0sybfRyKp+DY+r=2U+-r7HfSF4GL1oVOOcVtEWmk2ewUw@mail.gmail.com>
 <CAApHDvpVE5F-_8rpPC+-L98mA0yK0S_jtQGqLn69fkRevf726g@mail.gmail.com>
 <aQEwRD5XW4wfJE6G@nathan>
 <CAA5RZ0uGbpaom3v+2K=bNuitnSGX_Lw4yPj3SbJdABYKgGur_A@mail.gmail.com>
 <aQI7tGEs8IOPxG64@nathan>
 <CAGjGUAJ-XqLN6VU+HeXn0iooiJrrnN4Pqp+xVU8VKKNsZcfR2w@mail.gmail.com>
In-Reply-To: 
 <CAGjGUAJ-XqLN6VU+HeXn0iooiJrrnN4Pqp+xVU8VKKNsZcfR2w@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 30 Oct 2025 16:41:56 +1300
Message-ID: 
 <CAApHDvrhEH8okuL+WVHxrC_W7JBSuvk6A5i3mzmrGTgp4RAvqg@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: wenhui qiu <qiuwenhuifx@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 Sami Imseih <samimseih@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>,
 Jeremy Schneider <schneider@ardentperf.com>,
	pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvrhEH8okuL%2BWVHxrC_W7JBSuvk6A5i3mzmrGTgp4RAvqg%40mail.gmail.com>
Precedence: bulk

On Thu, 30 Oct 2025 at 15:58, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
> In fact, with the introduction of the vacuum_max_eager_freeze_failure_rat=
e feature, if a table=E2=80=99s age still exceeds more than 1.x times the a=
utovacuum_freeze_max_age, it suggests that the vacuum freeze process is not=
 functioning properly. Once the age surpasses vacuum_failsafe_age, wraparou=
nd issues are likely to occur soon.Taking the average of vacuum_failsafe_ag=
e and autovacuum_freeze_max_age is not a complex approach. Under the defaul=
t configuration, this average already exceeds four times the autovacuum_fre=
eze_max_age. At that stage, a DBA should have already intervened to investi=
gate and resolve why the table age is not decreasing.

I don't think anyone would like to modify PostgreSQL in any way that
increases the chances that a table gets as old as vacuum_failsafe_age.
Regardless of the order in which tables are vacuumed, if a table gets
as old as that then vacuum is configured to run too slowly, or there
are not enough workers configured to cope with the given amount of
work. I think we need to tackle prioritisation and rate limiting as
two separate items. Nathan is proposing to improve the prioritisation
in this thread and it seems to me that your concerns are with rate
limiting. I've suggested an idea that might help with reducing the
cost_delay based on the score of the table in this thread. I'd rather
not introduce that as a topic for further discussion here (I imagine
Nathan agrees). It's not as if the server is going to consume 1
billion xids in 5 mins. It's at least going to take a day to days or
longer for that to happen and if autovacuum has not managed to get on
top of the workload in that time, then it's configured to run too
slowly and the cost_limit or delay needs to be adjusted.

My concern is that there are countless problems with autovacuum and if
you try and lump them all into a single thread to fix them all at
once, we'll get nowhere. Autovacuum was added to core in 8.1, 20 years
ago and I don't believe we've done anything to change the ratelimiting
aside from reducing the default cost_delay since then. It'd be good to
fix that at some point, just not here, please.

FWIW, I agree with Nathan about keeping the score calculation
non-magical. The score should be simple and easy to document. We can
introduce complexity to it as and when it's needed and when the
supporting evidence arrives, rather than from people waving their
hands.

David