> Part of the underlying problem here is that, AFAIK, neither PostgreSQL > as a piece of software nor we as human beings who operate PostgreSQL > databases have much understanding of how autovacuum_max_workers should > be set. It's relatively easy to hose yourself by raising > autovacuum_max_workers to try to make things go faster, but produce > the exact opposite effect due to how the cost balancing stuff works.
Yeah, this patch will not fix this problem. Anyone who raises av_max_workers should think about adjusting the av_cost_delay. This was discussed up the thread [4] and even without this patch, I think it's necessary to add more documentation on the relationship between workers and cost. > So I feel like what this proposal reveals is that we know that our > algorithm for ramping up the number of running workers doesn't really > work. And maybe that's just a consequence of the general problem that > we have no global information about how much vacuuming work there is > to be done at any given time, and therefore we cannot take any kind of > sensible guess about whether 1 more worker will help or hurt. Or, > maybe there's some way to do better than what we do today without a > big rewrite. I'm not sure. I don't think this patch should be burdened > with solving the general problem here. But I do think the general > problem is worth some discussion. This patch is only solving the operational problem of adjusting autovacuum_max_workers, and it does so without introducing complexity. A proposal that will alleviate the users from the burden of having to think about autovacuum_max_workers, cost_delay and cost_limit settings will be great. This patch may be the basis for such dynamic "auto-tuning" of autovacuum workers. Regards, Sami [4] https://www.postgresql.org/message-id/20240419154322.GA3988554%40nathanxps13
