Re: Parallel vacuum: workers together with leader could overrun cost budget

> On Oct 21, 2026, at 00:38, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Fri, Oct 16, 2026 at 10:46 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>
>> Thank you for the report and the patch. I agree with your analysis.
>> Currently each process sleeps in proportion to the cost it incurred by
>> itself, so the leader and its workers together consume N times the
>> budget. I think the cost limit should work like a rate limit of a
>> download manager for example: using 5 connections makes the download
>> after, but if we set the limit to 1MB/s, we expect 1MB/s in total ,
>> not 1MB/s * 5 = 5MB/s.
>
> Nice analogy!
>
>>> Although this exists for parallel vacuum too, back-patching it to
>>> PG19, where parallel autovacuum was introduced, is fine, since
>>> parallel autovacuum runs with the default cost delay and cost limit.
>>
>> Agree to backpatch it to v19. Autovaucum distributes the cost limit
>> among the running autovacuum workers so that the sum doesn't exceed
>> autovacuum_vacuum_cost_limit, but parallel vacuum workers are not
>> counted there.
>>
>> I've reviewed the patch and have one comment:
>>
>> + /*
>> + * Compute sleep time based on the local cost balance and the number
>> + * of active workers.
>> + */
>> + msec = vacuum_cost_delay * VacuumCostBalanceLocal * nworkers /
>> + vacuum_cost_limit;
>>
>> msec can be up to nworkers * vacuum_cost_delay, but
>> vacuum_delay_point() caps the sleep time at 4 * vacuum_cost_delay.
>> Therefore, when more than 4 parallel workers are active, the sleep
>> time is truncated and we still overrun the budget. IIUC the cap was
>> introduced to avoid uselessly long delays when the cost accumulates
>> far beyond the limit.
>
> Ah, you are right. The long delays are avoided when the cost
> accumulates far higher than the specified limit. E.g., while holding
> critical locks or when interrupts are pending, the cost-based delays
> are not applied, so the cost keeps accumulating until the locks are
> released or the interrupts are processed.
>
>> I think a simple fix is to sleep up to 4 *
>> nworkers * vacuum_cost_delay at once. I think that's acceptable since
>> the sleep time is still proportional to the cost the process actually
>> incurred.
>
> Agreed. The non-parallel vacuum caps the sleep time whenever the
> accumulated cost balance crosses 4 times the cost limit. We should
> just do the similar capping for each worker while not letting all of
> them overrun the cost budget.
>
> Please find the attached v2 patch with the above changes and some doc
> adjustments.
>
> --
> Bharath Rupireddy
> Amazon Web Services: https://aws.amazon.com
> <v2-0001-Parallel-vacuum-workers-together-with-leader-coul.patch>

After reading v2, I have a small concern.

In the VacuumSharedCostBalance != NULL path, the sleep cap is vacuum_cost_delay * 4 * nworkers. Say vacuum_cost_delay=100ms and nworkers=16; then the sleep time could be 6.4 seconds. Passing such a long delay to pg_usleep() seems concerning because its header comment explicitly says:
```
* CAUTION: It's not a good idea to use long sleeps in the backend. They will
* silently return early if a signal is caught, but that doesn't include
* latches being set on most OSes, and even signal handlers that set MyLatch
* might happen to run before the sleep begins, allowing the full delay.
* Better practice is to use WaitLatch() with a timeout, so that backends
* respond to latches and signals promptly.
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/