Horton

Re: Should vacuum process config file reload more often

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Masahiko Sawada <sawada.mshk@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Amit Kapila <amit.kapila16@gmail.com>
Date: 2023-04-05T20:59:03Z
Lists: pgsql-hackers 
On Wed, Apr 5, 2023 at 1:38 PM Daniel Gustafsson <daniel@yesql.se> wrote:
> Not to derail this thread, and pre-empt a thread where this can be discussed in
> its own context, but isn't that kind of the main problem?  Tuning autovacuum is
> really complicated and one of the parameters that I think universally seem to
> make sense to users is just autovacuum_max_workers.  I agree that it doesn't do
> what most think it should, but a quick skim of the name and docs can probably
> lead to a lot of folks trying to use it as hammer.

I think that I agree. I think that the difficulty of tuning autovacuum
is the actual real problem. (Or maybe it's just very closely related
to the real problem -- the precise definition doesn't seem important.)

There seems to be a kind of physics envy to some of these things.
False precision. The way that the mechanisms actually work (the
autovacuum scheduling, freeze_min_age, and quite a few other things)
*are* simple. But so are the rules of Conway's game of life, yet
people seem to have a great deal of difficulty predicting how it will
behave in any given situation. Any design that focuses on the
immediate consequences of any particular policy while ignoring second
order effects isn't going to work particularly well. Users ought to be
able to constrain the behavior of autovacuum using settings that
express what they want in high level terms. And VACUUM ought to have
much more freedom around finding the best way to meet those high level
goals over time (e.g., very loose rules about how much we need to
advance relfrozenxid by during any individual VACUUM).

-- 
Peter Geoghegan

Commits

  1. Fix assertion failure in heap_vacuum_rel

  2. Fix vacuum_cost_delay check for balance calculation.

  3. Fix autovacuum cost debug logging

  4. Refresh cost-based delay params more frequently in autovacuum

  5. Separate vacuum cost variables from GUCs

  6. Make vacuum failsafe_active globally visible

  7. Don't balance vacuum cost delay when per-table settings are in effect