Re: autovacuum: automatically propagate updated parameters - Mailing list pgsql-bugs

On Thu, Sep 24, 2026 at 2:04 AM Daniel Gustafsson <daniel@yesql.se> wrote:
>
> > On 24 Sep 2026, at 08:34, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
> > On Wed, Sep 23, 2026 at 5:39 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> > I also don't like the wait-100ms-wakeup approach that a separate wait function would run all the time, since it
wastespower and CPU cycles. Imagine a worker vacuuming an index that is hundreds of GBs or even TBs, while the leader
hasonly a small index and finishes first. The leader then sits in the wait loop for longer until the large index is
done,waking up every 100ms the whole time. So I would prefer not to go that route. 
>
> Agree, we should avoid polling loops like that as much as possible.

Agreed.

>
> > How about doing the check inside the existing wait loop, only when the process is an autovacuum worker, along the
linesof the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also
believethe wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still
fixesthe reported issues. 
>
> It's not great to sprinkle in worker specific code in the generic parallel
> handling.  My original thinking was to reject the idea, but the vacuum costing
> is already used for non-vacuum purposes (and there have been discussions to
> rename and generalize it) so with that in mind I am less concerned for this
> particular case.

Same here. After thinking on the Bharath's patch, I now think it might
make sense. I guess it's likely that when autovacuum wants to wait for
other workers to finish in other cases like parallel heap vacuum, we
would want to use WaitForParallelWorkersToFinish() and want it to
update the cost-based delay params during the wait.

> > What I am less sure about is the fix for the second issue in the attached WIP patch, which needs any leader waiting
forits workers to be woken up after the cost limit is rebalanced. I haven't found a better one yet. 
>
> Not sure I see a better solution either, and we are running short of time
> before 19 RC1.

While it's a good idea to wake the leader up instead of polling, I'm a
bit concerned that we call SetLatch() on all autovacuum workers
participating in cost balancing, whereas we need it only in a narrow
situation: when the worker is participating in cost balancing, using
parallel vacuum, and waiting for its parallel workers to finish.
Calling SetLatch() on a process that is not waiting doesn't send a
signal, butit still leaves the latch set, causing a spurious wakeup
the next time the process waits for something else.

I think a condition variable fits better here. The leader can prepare
to sleep on a condition variable in AutoVacuumShmemStruct while
waiting on its latch, like WalSndWait() does, and a process
recalculating the balance can wake it up by broadcasting on it. This
way, we wake up only the leaders that actually need it. That said, I
don't think it's a good idea to make WaitForParallelWorkersToFinish()
prepare to sleep on autovacuum's condition variable. So if we want to
use the condition variable, it seems better to me to have a dedicated
wait function in vacuumparallel.c that waits until all indexes are
completed, and then call WaitForParallelWorkersToFinish().

Regards,

--
Masahiko Sawada


Amazon Web Services: https://aws.amazon.com