Re: autovacuum: automatically propagate updated parameters
Re: autovacuum: automatically propagate updated parameters
Hi, On Mon, Aug 24, 2026 at 5:31 AM Daniel Gustafsson wrote: > > > On 24 Jul 2026, at 10:33, Zsolt Parragi wrote: > > > > Parallel workers launched for Parallel Vacuum are using the same cost > > delay parameters as the leader worker. If any of these parameters are > > changed in the leader worker, it will propagate the new parameter > > values to all of its parallel workers. > > > > But in practice, parallel_vacuum_propagate_shared_delay_params was > > only called during config reload. Otherwise when the leader adjusted > > the parameters, it didn't share them with the other workers. > > > > See the attached patch which adds a test case about this and adds an > > additional parallel_vacuum_propagate_shared_delay_params call to the > > update logic. > > I reviewed this today and I agree with the proposed fix. The alternative would > be to update the documentation to match the reality of requiring a configuration > reload, but that brings on other baggage so I think fixing the code is the > better option here. Nice catch! Yes, this needs to be fixed and backpatched to PG19 (1ff3180ca01). It misses propagating the changes made after the delay update and requires one to reload the config. Mostly, the caller doesn't know from outside whether the params have changed at all, making the parallel workers for autovacuum not honor the cost params at all. How about propagating the params to parallel workers launched by autovacuum right after updating cost limits in AutoVacuumUpdateCostLimit()? With this change, the existing propagate-upon-config-reload path could be removed. This looks centralized and future-proof, unless I'm missing something. Thoughts? > The part that worry me is the below testcode. The relation 'filler' is sized > large enough to outlast the test: I understand that having a test for this in the first place could have helped catch this issue. However, I don't see a strong point in having one now. Can we test it manually and get the fix alone? -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
> On 27 Aug 2026, at 23:15, Bharath Rupireddy wrote: > > Hi, > > On Thu, Aug 27, 2026 at 4:57 AM Daniel Gustafsson wrote: >> >>>> we can stop the >>>> second worker at the injection point "vacuum-truncate-enabled" for >>>> example. While it works and we can reduce the filler table size, it >>>> would add an unclear dependency as vacuum-truncate-enabled is not >>>> related to parallel autovacuum. >>> >>> I added a new injection point and stopped it there in v3. If you think >>> that's unnecessary, we can replace it to vacuum-truncate-enabled in >>> the test and remove the injection point, and it works the same way. >>> This just seemed cleaner to me. >> >> I prefer this approach, injection points are cheap enough that we don't need to >> reuse and cause undefined dependencies. I'll try to get this applied later today. > > +1, a separate injection point makes sense here. The attached v3 patch > looks good to me. pgindent and tests are happy. This needs to be > backpatched to PG19. Thanks for review, I have it scheduled for commit and backpatch tomorrow morning. -- Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
On Thu, Aug 27, 2026 at 3:02 PM Daniel Gustafsson wrote: > > > On 27 Aug 2026, at 23:15, Bharath Rupireddy wrote: > > > > Hi, > > > > On Thu, Aug 27, 2026 at 4:57 AM Daniel Gustafsson wrote: > >> > >>>> we can stop the > >>>> second worker at the injection point "vacuum-truncate-enabled" for > >>>> example. While it works and we can reduce the filler table size, it > >>>> would add an unclear dependency as vacuum-truncate-enabled is not > >>>> related to parallel autovacuum. > >>> > >>> I added a new injection point and stopped it there in v3. If you think > >>> that's unnecessary, we can replace it to vacuum-truncate-enabled in > >>> the test and remove the injection point, and it works the same way. > >>> This just seemed cleaner to me. > >> > >> I prefer this approach, injection points are cheap enough that we don't need to > >> reuse and cause undefined dependencies. I'll try to get this applied later today. > > > > +1, a separate injection point makes sense here. The attached v3 patch > > looks good to me. pgindent and tests are happy. This needs to be > > backpatched to PG19. > > Thanks for review, I have it scheduled for commit and backpatch tomorrow morning. Thank you for taking care of it. The v3 patch looks good to me. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
> On 28 Aug 2026, at 00:52, Zsolt Parragi wrote: > > Unfortunately v3 failed on CI, and after some more local testing it > was slightly unstable even on my machine. > > v4 aims to fix that by changing autovacuum thresholds so that it > effectively only runs for the test tables Thanks for the additional testing and update, I hadn't seen it fail in CI (or locally) so it was good that you caught it. Will run v4 in CI and locally today before going ahead. -- Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
On Thu, Aug 27, 2026 at 3:43 PM Masahiko Sawada wrote:
> Thank you for taking care of it. The v3 patch looks good to me.
AI found one more gap while testing the committed fix on REL_19_STABLE at
b368bdd2. I haven't manually reviewed the code yet.
Once an autovacuum leader enters WaitForParallelWorkersToFinish(), SIGHUP
wakes its latch, but the loop only runs CHECK_FOR_INTERRUPTS(), leaving
ConfigReloadPending set. A cost-limit rebalance is not signalled at all. In
both cases, the leader does not publish changed cost parameters until the
parallel worker finishes.
The standalone reproducer uses 100 rows and two indexes, with no fixed
sleeps. Apply it to b368bdd2 and run:
make -C src/test/modules/test_autovacuum check \
PROVE_TESTS=t/002_cost_reload_while_waiting.pl
It fails with:
got: 'pending'
expected: 'processed'
The other attached patch adds an optional callback to the worker-finish
wait. Parallel autovacuum uses it to handle reloads and poll cost-limit
rebalancing every 100 ms; other callers keep the existing behavior. It also
adds tests for both cases to 001_parallel_autovacuum.pl.
With the fix, test_autovacuum passes, as do the core regression (239 tests)
and isolation (133 tests) suites.
The reproducer and fix patches are separate alternatives against b368bdd2,
not a series.
Nik
Re: autovacuum: automatically propagate updated parameters
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
Hi, Sorry for the late reply. I didn't realize your report until now. On Mon, Sep 21, 2026 at 11:28 AM Nikolay Samokhvalov wrote: > > On Thu, Aug 27, 2026 at 3:43 PM Masahiko Sawada wrote: > > Thank you for taking care of it. The v3 patch looks good to me. > > AI found one more gap while testing the committed fix on REL_19_STABLE at > b368bdd2. I haven't manually reviewed the code yet. > > Once an autovacuum leader enters WaitForParallelWorkersToFinish(), SIGHUP > wakes its latch, but the loop only runs CHECK_FOR_INTERRUPTS(), leaving > ConfigReloadPending set. A cost-limit rebalance is not signalled at all. In > both cases, the leader does not publish changed cost parameters until the > parallel worker finishes. Good catch, we should fix it. > The other attached patch adds an optional callback to the worker-finish > wait. Parallel autovacuum uses it to handle reloads and poll cost-limit > rebalancing every 100 ms; other callers keep the existing behavior. It also > adds tests for both cases to 001_parallel_autovacuum.pl. I've confirmed that the patch fixes the issue. While it works fine, I'm a bit concerned that adding WaitForParallelWorkersToFinishWithCallback() with a callback and a timeout might be overkill, as I don't see any usecase other than parallel autovacuum that needs to pass a callback. An alternative approach would be to have a function in vacuumparallel.c that waits for all index statuses to become PARALLEL_INDVAC_STATUS_COMPLETED while periodically checking for cost parameter updates. We still need the timeout there since nothing wakes up the leader on a cost limit rebalance. While it adds another wait loop before WaitForParallelWorkersToFinish(), that call should return almost immediately. We can consider adding a callback to WaitForParallelWorkersToFinish() when we find other use cases in the future. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
On Wed, Sep 23, 2026 at 5:39 PM Masahiko Sawada wrote: > I'm a bit concerned that adding > WaitForParallelWorkersToFinishWithCallback() with a callback and a > timeout might be overkill, as I don't see any usecase other than > parallel autovacuum that needs to pass a callback. > > An alternative approach would be to have a function in > vacuumparallel.c that waits for all index statuses to become > PARALLEL_INDVAC_STATUS_COMPLETED while periodically checking for cost > parameter updates. Thanks for testing it. Agreed; attached v2 keeps the timed wait in vacuumparallel.c. It uses an atomic completion count rather than polling the index statuses while workers update them, then calls the existing WaitForParallelWorkersToFinish() for final worker errors and WAL feedback. On REL_19_STABLE at b73d13c3, test_autovacuum, core regression, and isolation pass. Nik
Re: autovacuum: automatically propagate updated parameters
Hi,
On Wed, Sep 23, 2026 at 5:39 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Good catch, we should fix it.
>
> > The other attached patch adds an optional callback to the worker-finish
> > wait. Parallel autovacuum uses it to handle reloads and poll cost-limit
> > rebalancing every 100 ms; other callers keep the existing behavior. It also
> > adds tests for both cases to 001_parallel_autovacuum.pl.
>
> I've confirmed that the patch fixes the issue. While it works fine,
> I'm a bit concerned that adding
> WaitForParallelWorkersToFinishWithCallback() with a callback and a
> timeout might be overkill, as I don't see any usecase other than
> parallel autovacuum that needs to pass a callback.
>
> An alternative approach would be to have a function in
> vacuumparallel.c that waits for all index statuses to become
> PARALLEL_INDVAC_STATUS_COMPLETED while periodically checking for cost
> parameter updates. We still need the timeout there since nothing wakes
> up the leader on a cost limit rebalance. While it adds another wait
> loop before WaitForParallelWorkersToFinish(), that call should return
> almost immediately. We can consider adding a callback to
> WaitForParallelWorkersToFinish() when we find other use cases in the
> future.
Thanks for reporting the issue. I believe this issue can happen fairly often in practice, especially when the autovacuum worker (leader) gets smaller or fewer indexes than the parallel workers for index vacuuming (workers), and the workers take longer than the leader (for example, non-core indexes that can take a while). So I think we do need to fix it.
A separate wait function would lose all the cases that the existing wait loop for parallel workers, WaitForParallelWorkersToFinish(), already handles, like detecting error messages reported by the workers, their liveness checks, attach/detach, and so on. It would also likely end up duplicating that same logic along with the cost param update code at the end.
A separate wait function would lose all the cases that the existing wait loop for parallel workers, WaitForParallelWorkersToFinish(), already handles, like detecting error messages reported by the workers, their liveness checks, attach/detach, and so on. It would also likely end up duplicating that same logic along with the cost param update code at the end.
I also don't like the wait-100ms-wakeup approach that a separate wait function would run all the time, since it wastes power and CPU cycles. Imagine a worker vacuuming an index that is hundreds of GBs or even TBs, while the leader has only 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.
How about doing the check inside the existing wait loop, only when the process is an autovacuum worker, along the lines of the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also believe the wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still fixes the reported issues.
What I am less sure about is the fix for the second issue in the attached WIP patch, which needs any leader waiting for its workers to be woken up after the cost limit is rebalanced. I haven't found a better one yet.
PS: I noticed the attached v2 patch posted upthread only after finishing my response.
Thoughts?
--
Bharath Rupireddy
How about doing the check inside the existing wait loop, only when the process is an autovacuum worker, along the lines of the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also believe the wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still fixes the reported issues.
What I am less sure about is the fix for the second issue in the attached WIP patch, which needs any leader waiting for its workers to be woken up after the cost limit is rebalanced. I haven't found a better one yet.
PS: I noticed the attached v2 patch posted upthread only after finishing my response.
Thoughts?
--
Bharath Rupireddy
Re: autovacuum: automatically propagate updated parameters
От:
Daniel Gustafsson <daniel@yesql.se>
Дата:
> On 24 Sep 2026, at 08:34, Bharath Rupireddy wrote: > On Wed, Sep 23, 2026 at 5:39 PM Masahiko Sawada wrote: > > I've confirmed that the patch fixes the issue. While it works fine, > > I'm a bit concerned that adding > > WaitForParallelWorkersToFinishWithCallback() with a callback and a > > timeout might be overkill, as I don't see any usecase other than > > parallel autovacuum that needs to pass a callback. I was also looking at this thread over the past few days and I agree that this seems too invasive for the issue at hand given where we are in the cycle. > I also don't like the wait-100ms-wakeup approach that a separate wait function would run all the time, since it wastes power and CPU cycles. Imagine a worker vacuuming an index that is hundreds of GBs or even TBs, while the leader has only 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. > How about doing the check inside the existing wait loop, only when the process is an autovacuum worker, along the lines of the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also believe the wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still fixes the 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. I can verify that the posted reproducer is still fixed with this patch applied (and it survives a check-world). > What I am less sure about is the fix for the second issue in the attached WIP patch, which needs any leader waiting for its 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. -- Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
> I was also looking at this thread over the past few days and I agree that this > seems too invasive for the issue at hand given where we are in the cycle. I had the same issue, I tried to figure out something better but couldn't. I still don't have a better solution. I have a somewhat simpler version of v2, but the alternative WIP patch overall looks better to me. I attached an updated version of that with the tests integrated, and a few code changes related to that (new injection points and one simplification in parallel_vacuum_refresh_cost_params), so it's easier to test. > What I am less sure about is the fix for the second issue in the attached WIP patch, which needs any leader waiting for its workers to be woken up after the cost limit is rebalanced. I haven't found a better one yet. Any alternative solution seems more complex to me, with no clear advantage.
Re: autovacuum: automatically propagate updated parameters
Hi, I ran v3 on REL_19_STABLE (e60ee52841d) with --enable-cassert and --enable-injection-points, next to Nikolay's v2 and two cut-down copies of v3, to check the new tests and the polling question from upthread. 1. Each part of the fix has a test that fails without it v3 tests and injection points, no fix: tests 1-3 pass, 4 fails v3 without the SetLatch() in autovac_recalculate_workers_for_balance(): tests 1-4 pass, 5 fails v3: all pass 2. Stability test_autovacuum passed 30 of 30 runs on an idle machine, and 20 of 20 with every CPU busy (one busy loop per core), at about 10 s and 16 s a run. 3. A case the tests don't cover: a worker leaving the balance Test 5 checks a worker joining while the leader waits. A worker leaving goes through another path: FreeWorkerInfo() sets AutoVacRebalance, and the launcher recalculates the count and so does the SetLatch(). I added it as test 6 (attached, on top of v3): the second worker joins and is held as in test 5, the leader goes to 400, then the second worker is released and exits. With v3 the leader, still in ParallelFinish, logs cost_limit=800 by the first check after the worker is gone, and the parallel worker ends at 800. Without the SetLatch() the test stops earlier, at the join, like test 5, so it does not tell the two paths apart. It does show that the launcher path works, which the current tests don't exercise. 4. Polling vs. waking, measured With the parallel worker held before its index and the leader waiting for it for 20 s, the leader's voluntary context switches were: v3: 0 Nikolay's v2: 199 (9.9 per second) The time from pg_reload_conf() to the leader logging the new limit was 7 ms with v3 and 8 ms with v2. So the timed wait costs about ten wakeups a second for as long as the largest index takes, and makes no difference to how fast a reload is picked up. 5. Things I checked in the code, in case they come up - The extra SetLatch() does not cut short a busy worker's cost delay: vacuum_delay_point() sleeps with pg_usleep(), not on the latch, so "a worker that is busy vacuuming ignores the extra wakeup" holds. - No wakeup is lost in the wait. The refresh runs at the top of each pass, before WaitLatch(). A SetLatch() after it makes WaitLatch() return at once, and one between WaitLatch() and ResetLatch() is followed by another pass that reads the current count anyway, since the count is written before the SetLatch(). - The leader also wakes for its workers' messages, but parallel_vacuum_propagate_shared_delay_params() bumps the generation only when a value changed, so those wakeups make no worker re-read its parameters. The scripts and all outputs are in the second attachment. Regards, Manu
Re: autovacuum: automatically propagate updated parameters
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
On Thu, Sep 24, 2026 at 2:04 AM Daniel Gustafsson wrote: > > > On 24 Sep 2026, at 08:34, Bharath Rupireddy wrote: > > On Wed, Sep 23, 2026 at 5:39 PM Masahiko Sawada wrote: > > > I also don't like the wait-100ms-wakeup approach that a separate wait function would run all the time, since it wastes power and CPU cycles. Imagine a worker vacuuming an index that is hundreds of GBs or even TBs, while the leader has only 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 lines of the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also believe the wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still fixes the 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 for its 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
Re: autovacuum: automatically propagate updated parameters
Hi,
Masahiko Sawada wrote:
> 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.
I wanted to know how narrow, so I logged every SetLatch() that v3 adds,
with the target's wait event and whether its latch was already set
(the log line is in the attachment, on top of v3, REL_19_STABLE
e60ee52841d).
Load: autovacuum_max_workers = 3, three databases with the same 120
small tables each (every third table with its own
autovacuum_vacuum_cost_limit, so it is not in the balance) plus one
table with three indexes and autovacuum_parallel_workers = 2, updated
in rounds for 120 s. That gave 14506 table vacuums, 28 of them
parallel.
SetLatch() calls: 10066
on the calling worker itself: 4871
on another worker: 5195
waiting in ParallelFinish: 235
in VacuumDelay: 4635
anything else: 325
other-worker latch already set: 4724
ParallelFinish latch already set: 1
Workers made 10001 of the calls and the launcher 65. The count moves
every time a worker starts a table that is in the balance or not, often
2 -> 3 -> 2 within a few milliseconds, and v3 turns each move into a
SetLatch() on every balanced worker.
So you are right that almost none of them are needed: 235 out of 5195
reach a leader that is waiting. The cost is smaller than the count
suggests, though. A worker that is vacuuming sleeps with pg_usleep()
and does not reset its latch, so 4724 of those calls found it already
set and changed nothing. That leaves 237 new spurious sets in
120 s across all workers, each costing at most one early return from
the worker's next wait. Workers already get the same from a config
reload, since SignalHandlerForConfigReload() does SetLatch(MyLatch).
With a single database, only one worker ran at a time and every call
was on the caller itself. Those 4871 calls are never needed, because
the caller runs VacuumUpdateCosts() right after the recalculation. If
the SetLatch() stays, skipping wi_proc == MyProc removes about half of
them. With the condition variable you describe, none of this applies,
and only the 235 waiting leaders would be woken.
The script, the log line and the full results are in the attachment.
Regards,
Manu
Re: autovacuum: automatically propagate updated parameters
От:
Zsolt Parragi <zsolt.parragi@percona.com>
Дата:
On Thu, 24 Sep 2026, Masahiko Sawada wrote: > 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(). That'll bring back the more complicated approach of v2, and also make this part of the code more complex. While I didn't do proper measurements, my logic against it was similar to what Manu already posted, that it seems harmless here and it is significantly simpler than adding a cv. Also, I think both issues could be solved in a more proper way with more refactoring, but that seems excessive for 19. (And in that case, going with a simple solution for 19 also seems like a good answer to me)
Re: autovacuum: automatically propagate updated parameters
От:
Daniel Gustafsson <daniel@yesql.se>
Дата:
> On 25 Sep 2026, at 09:45, Zsolt Parragi wrote: > > On Thu, 24 Sep 2026, Masahiko Sawada wrote: >> 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(). > > That'll bring back the more complicated approach of v2, and also make > this part of the code more complex. I think that would be cleaner, but also more invasive at post-beta4. > While I didn't do proper measurements, my logic against it was similar > to what Manu already posted, that it seems harmless here and it is > significantly simpler than adding a cv. > > Also, I think both issues could be solved in a more proper way with > more refactoring, but that seems excessive for 19. (And in that case, > going with a simple solution for 19 also seems like a good answer to > me) Given where we are in the cycle I am also in favor of a simpler solution unless it's shown to have (severe) performance regressions. -- Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
Hi, Daniel Gustafsson wrote: > Given where we are in the cycle I am also in favor of a simpler solution unless > it's shown to have (severe) performance regressions. My earlier numbers counted the SetLatch() calls, not their cost, so I measured the server's CPU with and without v3 (REL_19_STABLE e60ee52841d, -O2, no cassert). The load is the same as before: small tables moving in and out of the balance plus one parallel table, with autovacuum saturated, 120 s per run, runs alternated. 3 workers, 8 runs each: median 25.75 s CPU both with and without v3. One v3 run used 28.4 s, and it did not repeat in 7 more. 10 workers, 4 runs each: 125.8 s without v3 (sd 0.8), 126.1 s with it (sd 0.3). So I see no CPU cost from v3 at either size. One difference did show up at 10 workers: v3 vacuumed 2.5% fewer tables in the same window, in all four pairs. I have not checked why. It would fit the parallel workers now following the rebalanced limit, but that is a guess. The scripts and all runs are attached. Regards, Manu
Re: autovacuum: automatically propagate updated parameters
Hi, On Thu, Sep 24, 2026 at 4:47 PM Masahiko Sawada wrote: > > > > How about doing the check inside the existing wait loop, only when the process is an autovacuum worker, along the lines of the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also believe the wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still fixes the 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 for its 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. Thanks all for the comments. I understand the cost of setting the latch and waking up every leader participating in rebalancing could be higher. But we only need to wake the leaders that are in the wait-for-parallel-workers-to-finish loop. Those are the ones that need the rebalanced cost limit, so they can propagate it down to their workers still doing index vacuuming. That way the workers can adjust to the system needs. The user may have changed the cost limits to slow things down or speed them up, and either way it is not good for the workers to miss them. Now, on the cost of setting the latch. It depends on three things. First, how many leaders can exist, which depends on autovacuum_max_workers and autovacuum_worker_slots. Second, how often the number of leaders sharing the cost limit changes, that is, when the recount comes out different from the previous count. This happens when a leader starts or exits, or when the user sets or removes a per-table cost limit, which moves a leader in or out of the sharing set the next time that table is vacuumed. Third, how often a leader is waiting in the loop at that moment. Having all three happen together looks fairly rare in practice IMHO. And even if it happens often, the cost is small, since set-latch sends a signal only to a process that is in a latch wait at that moment. For a leader not in the loop, it only marks the latch as set and returns, and that leader's next latch wait just returns once early and re-checks. If we still want to set the latch only for the leaders in the loop, there are a couple of ways, and I don't prefer either. We could check the leader's wait event info to see whether it is in the loop, or the leader could set a flag in its WorkerInfo when it enters the loop and clear it when it leaves. Both are prone to race conditions and add more code than they save. I would leave it as a follow-up if anyone still wants to pursue it. That said, please find the attached v3 patch set. 0001 has only the fixes, and 0002 has the tests posted upthread along with the injection points. I think 0001 is ready to go in, unless there are further comments. I'm fine leaving 0002 here for the record, since I haven't reviewed it in depth and I don't think it has had a close review yet, and it needs more dev cycles. Anyone with cycles can pick it up for HEAD. Given the time left, my suggestion would be to consider 0001 for PG19 and HEAD. Thoughts? -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi, Bharath Rupireddy wrote: > I'm fine leaving 0002 here for the record, since I haven't > reviewed it in depth and I don't think it has had a close review yet, I ran v3 0001+0002 on REL_19_STABLE (407687a0fb1), with assertions and injection points. 001_parallel_autovacuum.pl and 003_cost_reload_while_waiting.pl passed 10 runs out of 10. As a control I removed only the SetLatch() loop that 0001 adds to autovac_recalculate_workers_for_balance(). Then test 5 in 001 times out waiting for parallel-autovacuum-leader-cost-updated, while 003 still passes. So 003 covers the reload path and test 5 the rebalance path, each on its own. The opposite case, a worker leaving the balance while the leader waits, is not covered. I tried a test for it and could not make it deterministic: with autovacuum_naptime = 1s the launcher starts other workers, and one of them joining the balance wakes the leader too, so the test passed even with the launcher's wakeup removed. Regards, Manu
Re: autovacuum: automatically propagate updated parameters
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
On Fri, Sep 25, 2026 at 2:41 PM Bharath Rupireddy wrote: > > Hi, > > On Thu, Sep 24, 2026 at 4:47 PM Masahiko Sawada wrote: > > > > > > How about doing the check inside the existing wait loop, only when the process is an autovacuum worker, along the lines of the attached WIP? This is simple, the pattern already exists elsewhere in the code, and it looks safe. I also believe the wait loop is not in a performance-critical hot path, and this check is not costly. I checked that it still fixes the 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 for its 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. > > Thanks all for the comments. > > I understand the cost of setting the latch and waking up every leader > participating in rebalancing could be higher. But we only need to wake > the leaders that are in the wait-for-parallel-workers-to-finish loop. > Those are the ones that need the rebalanced cost limit, so they can > propagate it down to their workers still doing index vacuuming. That > way the workers can adjust to the system needs. The user may have > changed the cost limits to slow things down or speed them up, and > either way it is not good for the workers to miss them. > > Now, on the cost of setting the latch. It depends on three things. > First, how many leaders can exist, which depends on > autovacuum_max_workers and autovacuum_worker_slots. Second, how often > the number of leaders sharing the cost limit changes, that is, when > the recount comes out different from the previous count. This happens > when a leader starts or exits, or when the user sets or removes a > per-table cost limit, which moves a leader in or out of the sharing > set the next time that table is vacuumed. Third, how often a leader is > waiting in the loop at that moment. > > Having all three happen together looks fairly rare in practice IMHO. > And even if it happens often, the cost is small, since set-latch sends > a signal only to a process that is in a latch wait at that moment. For > a leader not in the loop, it only marks the latch as set and returns, > and that leader's next latch wait just returns once early and > re-checks. I think the cost argument should be the other way around. Setting the latch of a leader that is waiting for its parallel workers is exactly what we want, and using a cv wouldn't change how often that happens. What I was concerned about is the case where SetLatch() is wasted, i.e., when the worker is not waiting in that loop. Since the wakeups we actually need are not frequent, most SetLatch() calls would be wasted, but a wasted SetLatch() is cheap as you and Mau mentioned. It only marks the latch set if the worker is not waiting, and at worst causes one spurious wakeup if the worker is waiting on something else. And it happens only when the number of workers sharing the cost limit changes. So I agree that it's acceptable, and given we're close to RC1, I'm fine with the SetLatch() approach. > That said, please find the attached v3 patch set. 0001 has only the > fixes, and 0002 has the tests posted upthread along with the injection > points. I think 0001 is ready to go in, unless there are further > comments. I'm fine leaving 0002 here for the record, since I haven't > reviewed it in depth and I don't think it has had a close review yet, > and it needs more dev cycles. Anyone with cycles can pick it up for > HEAD. Given the time left, my suggestion would be to consider 0001 for > PG19 and HEAD. > > Thoughts? I'll review the 0002 patch in depth and see if it's reasonable to push some parts of it along with the fix. As for the 0001 patch, it looks good to me. I have one minor comment, though it's a matter of personal preference: +/* + * Refresh the cost-based vacuum delay parameters of an autovacuum worker + * running a parallel vacuum (leader) and propagate them to its parallel + * workers. + * + * The leader normally does this at its own cost delay points, which it no + * longer reaches once it is only waiting for the parallel workers to finish. + * It calls this from that wait instead, on every wakeup, to pick up a config + * reload and a change in the number of autovacuum workers sharing the cost + * limit. Both wake the leader, the second through the latch set by + * autovac_recalculate_workers_for_balance() when the count is recalculated. + */ +void +parallel_vacuum_refresh_cost_params(void) The second paragraph explains how this function is called. Since what the function does, as well as its name, is not specific to that wait, I guess it's better to explain it on the caller side, and the comment newly added in WaitForParallelWorkersToFinish() already explains a similar thing. Then the function comment can focus on what the function does and its side effects, such as reloading the configuration file. For example: /* * Reload the configuration file if requested, and refresh the cost-based * delay parameters of an autovacuum worker running a parallel vacuum * (leader), propagating any change to its parallel workers. */ Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
Hi, On Fri, Sep 25, 2026 at 5:32 PM Masahiko Sawada wrote: > > And it happens only when the number of workers sharing the cost limit > changes. So I agree that it's acceptable, and given we're close to > RC1, I'm fine with the SetLatch() approach. > > As for the 0001 patch, it looks good to me. I have one minor comment, > though it's a matter of personal preference: Thanks for reviewing it. > +void > +parallel_vacuum_refresh_cost_params(void) > > The second paragraph explains how this function is called. Since what > the function does, as well as its name, is not specific to that wait, > I guess it's better to explain it on the caller side, and the comment > newly added in WaitForParallelWorkersToFinish() already explains a > similar thing. Then the function comment can focus on what the > function does and its side effects, such as reloading the > configuration file. For example: > > /* > * Reload the configuration file if requested, and refresh the cost-based > * delay parameters of an autovacuum worker running a parallel vacuum > * (leader), propagating any change to its parallel workers. > */ Agreed. Please find the attached v4 patches. 0002 remains the same. -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
От:
Daniel Gustafsson <daniel@yesql.se>
Дата:
> On 26 Sep 2026, at 06:37, Bharath Rupireddy wrote:
> On Fri, Sep 25, 2026 at 5:32 PM Masahiko Sawada wrote:
>> As for the 0001 patch, it looks good to me.
Agreed, +1 pn 0001.
> Please find the attached v4 patches. 0002 remains the same.
Regarding 0002, ISTM that it will be stable but we could also just commit to
master and hold off on REL_19_STABLE for now. Once proven it can be back-
patched even post GA as it is test-only. That could give us more time while
reducing the risk of unstable tests this very late in the cycle. A few small
comments on 0002:
+ if (pvs->shared->is_autovacuum)
+ INJECTION_POINT("parallel-autovacuum-leader-before-index", NULL);
I think our common coding pattern is to wrap constructions like this (and many
more in the patch) in #ifdef USE_INJECTION_POINTS.
+$node->poll_query_until(
+ 'postgres', q{
+ SELECT count(*) = 0 FROM pg_stat_activity
+ WHERE backend_type = 'autovacuum worker' AND datname = 'regress_db2'
+}) or die "second autovacuum worker did not finish";
I'm not a fan of tests that die() when the condition fails rather than report a
test failure.
+$node->wait_for_log(
+ qr/parallel-autovacuum-leader-after-worker-wait \(reload (?:pending|processed)\)/,
+ $log_offset);
+
+my $log = slurp_file($node->logfile, $log_offset);
+my ($reload_state) =
+ $log =~ /parallel-autovacuum-leader-after-worker-wait \(reload (pending|processed)\)/;
+is($reload_state, 'processed',
+ 'autovacuum leader processes a configuration reload while waiting');
While not a problem with this patch, I wish wait_for_log could either return
the match, or at least the offset of the match, and not just the size of the
file. If we had that we could avoid reading excessive amounts of log data and
reduce the risk of buggy tests matching on the wrong part of the log.
--
Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
On Mon, Sep 28, 2026 at 9:13 AM Daniel Gustafsson wrote: > > > On 26 Sep 2026, at 06:37, Bharath Rupireddy wrote: > > On Fri, Sep 25, 2026 at 5:32 PM Masahiko Sawada wrote: > > >> As for the 0001 patch, it looks good to me. > > Agreed, +1 pn 0001. > > > Please find the attached v4 patches. 0002 remains the same. > > Regarding 0002, ISTM that it will be stable but we could also just commit to > master and hold off on REL_19_STABLE for now. Once proven it can be back- > patched even post GA as it is test-only. That could give us more time while > reducing the risk of unstable tests this very late in the cycle. Agreed. Daniel, are you planning to push the 0001 patch separately from the tests? I will be attending Postgres Summit US 2026 this week, so I can push 0001 early next week instead just in case the buildfarm turns red and requires monitoring. > comments on 0002: Aside from these comments, I think we could simplify the tests by introducing a boolean variable, say, leader_participates that controls whether the leader participates in parallel index vacuuming. It would default to true, but we could toggle it off via an injection point. When set to false, the leader would vacuum parallel-unsafe indexes but skip parallel-safe ones. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
I addressed these in v5. 0001 is unchanged. > I think we could simplify the tests by > introducing a boolean variable, say, leader_participates > ... I am not convinced that this change made things simpler, but it is included in 0002. > While not a problem with this patch, I wish wait_for_log could either return > the match, or at least the offset of the match, and not just the size of the > file. If we had that we could avoid reading excessive amounts of log data and > reduce the risk of buggy tests matching on the wrong part of the log. I ended up completely removing that test, it seemed redundant.
Re: autovacuum: automatically propagate updated parameters
От:
Daniel Gustafsson <daniel@yesql.se>
Дата:
> On 30 Sep 2026, at 04:19, Masahiko Sawada wrote: > Daniel, are you planning to push the 0001 patch separately from the > tests? I will be attending Postgres Summit US 2026 this week, so I can > push 0001 early next week instead just in case the buildfarm turns red > and requires monitoring. I hadn't really thought about that part yet, but if you are at a conference I can get 0001 in this week to get it building in the BF well ahead of the next release. When you are back we can see about the 0002 part. -- Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
On Wed, Sep 30, 2026 at 11:25 AM Daniel Gustafsson wrote: > > > On 30 Sep 2026, at 04:19, Masahiko Sawada wrote: > > > Daniel, are you planning to push the 0001 patch separately from the > > tests? I will be attending Postgres Summit US 2026 this week, so I can > > push 0001 early next week instead just in case the buildfarm turns red > > and requires monitoring. > > I hadn't really thought about that part yet, but if you are at a conference I > can get 0001 in this week to get it building in the BF well ahead of the next > release. When you are back we can see about the 0002 part. That's very helpful for me. I'll monitor buildfarm animals as much as I can. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
От:
Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Дата:
Hi, On Wed, Sep 30, 2026 at 8:36 PM Masahiko Sawada wrote: > > On Wed, Sep 30, 2026 at 11:25 AM Daniel Gustafsson wrote: > > > > > On 30 Sep 2026, at 04:19, Masahiko Sawada wrote: > > > > > Daniel, are you planning to push the 0001 patch separately from the > > > tests? I will be attending Postgres Summit US 2026 this week, so I can > > > push 0001 early next week instead just in case the buildfarm turns red > > > and requires monitoring. > > > > I hadn't really thought about that part yet, but if you are at a conference I > > can get 0001 in this week to get it building in the BF well ahead of the next > > release. When you are back we can see about the 0002 part. > > That's very helpful for me. I'll monitor buildfarm animals as much as I can. Thanks Daniel, and thanks Sawada-san. I will also keep an eye on the BF animals. -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
On Wed, Sep 30, 2026 at 9:26 PM Bharath Rupireddy wrote: > > Hi, > > On Wed, Sep 30, 2026 at 8:36 PM Masahiko Sawada wrote: > > > > On Wed, Sep 30, 2026 at 11:25 AM Daniel Gustafsson wrote: > > > > > > > On 30 Sep 2026, at 04:19, Masahiko Sawada wrote: > > > > > > > Daniel, are you planning to push the 0001 patch separately from the > > > > tests? I will be attending Postgres Summit US 2026 this week, so I can > > > > push 0001 early next week instead just in case the buildfarm turns red > > > > and requires monitoring. > > > > > > I hadn't really thought about that part yet, but if you are at a conference I > > > can get 0001 in this week to get it building in the BF well ahead of the next > > > release. When you are back we can see about the 0002 part. > > > > That's very helpful for me. I'll monitor buildfarm animals as much as I can. > > Thanks Daniel, and thanks Sawada-san. I will also keep an eye on the BF animals. After more thought, I need to push another fix for the buildfarm failure anyway, so I can take this patch too. Sorry for going back and forth on this. I'll push the attached 0001 patch tomorrow, barring any objections. I tested the patch several times and confirmed it passes every time. There's still room for improvement, though, so I'll come back to the test patch next week and push it separately. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
От:
Daniel Gustafsson <daniel@yesql.se>
Дата:
> On 1 Oct 2026, at 07:45, Masahiko Sawada wrote: > On Wed, Sep 30, 2026 at 9:26 PM Bharath Rupireddy > wrote: >> >> On Wed, Sep 30, 2026 at 8:36 PM Masahiko Sawada wrote: >>> That's very helpful for me. I'll monitor buildfarm animals as much as I can. >> >> Thanks Daniel, and thanks Sawada-san. I will also keep an eye on the BF animals. > > After more thought, I need to push another fix for the buildfarm > failure anyway, so I can take this patch too. Sorry for going back and > forth on this. I'll push the attached 0001 patch tomorrow, barring > any objections. No worries at all, whatever works best for you. Let me know if I can be of assistance in any way while you're travelling. > I'll come back to the test patch next week and push it separately. Sounds good. -- Daniel Gustafsson
Re: autovacuum: automatically propagate updated parameters
От:
Masahiko Sawada <sawada.mshk@gmail.com>
Дата:
On Thu, Oct 1, 2026 at 12:11 AM Daniel Gustafsson wrote: > > > On 1 Oct 2026, at 07:45, Masahiko Sawada wrote: > > On Wed, Sep 30, 2026 at 9:26 PM Bharath Rupireddy > > wrote: > >> > >> On Wed, Sep 30, 2026 at 8:36 PM Masahiko Sawada wrote: > > >>> That's very helpful for me. I'll monitor buildfarm animals as much as I can. > >> > >> Thanks Daniel, and thanks Sawada-san. I will also keep an eye on the BF animals. > > > > After more thought, I need to push another fix for the buildfarm > > failure anyway, so I can take this patch too. Sorry for going back and > > forth on this. I'll push the attached 0001 patch tomorrow, barring > > any objections. > > No worries at all, whatever works best for you. Let me know if I can be of > assistance in any way while you're travelling. Pushed. Thank you for all your support. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
On Wed, Sep 30, 2026 at 2:00 AM Zsolt Parragi wrote: > > I addressed these in v5. 0001 is unchanged. > > > I think we could simplify the tests by > > introducing a boolean variable, say, leader_participates > > ... > > I am not convinced that this change made things simpler, but it is > included in 0002. I think it did; the v5 adds 3 injection points while the v4 added 4 injection points. > > > While not a problem with this patch, I wish wait_for_log could either return > > the match, or at least the offset of the match, and not just the size of the > > file. If we had that we could avoid reading excessive amounts of log data and > > reduce the risk of buggy tests matching on the wrong part of the log. > > I ended up completely removing that test, it seemed redundant. Thinking about these regression tests, I think we can simplify them further by using a single injection point. Instead of having the workers process the indexes while the leader waits for them, we can stop the worker at the beginning of parallel_vacuum_main() and have the leader process all the indexes and wait in WaitForParallelWorkersToFinish(). That way, we can create a situation where the leader waits in WaitForParallelWorkersToFinish() with just one injection point. This injection point can also be reused in the other tests being discussed[1]. Also, instead of adding a 'parallel-autovacuum-leader-cost-updated' injection point, the leader writes a DEBUG2 log after propagating the shared delay params. The attached patch implements this idea and can be applied on top of the v5-0002 patch. It also adds more comments to these tests for better readability. Regards, [1] https://www.postgresql.org/message-id/CALj2ACWK6PkOk5MTfibtJyTYpb%3DAq_8%3DYaexnPKhOj11bOCROQ%40mail.gmail.com -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
Re: autovacuum: automatically propagate updated parameters
Hello! > I think it did; the v5 adds 3 injection points while the v4 added 4 > injection points. The overall patch size wasn't that different, the main reduction came from removing the redundant last testcase, but your changes definitely move it toward a simpler direction. The changes look good to me. I squashed the previous 0002 and this diff together for easier review / CI, fixed one whitespace issue, and adjusted the commit message.