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

Hi,

On Fri, Oct 23, 2026 at 2:50 PM surya poondla <suryapoondla4(at)gmail(dot)com> wrote:
>
> I ran v2 on master for 1M rows, 8 indexes, 10% deleted, cost delay 10ms / limit 1000, so a budget of about 100k units/s. Each run
> did roughly the same work (~650k units, estimated from VERBOSE buffer counts):
>
> PARALLEL master v2
> 0 82k/s 7.9s 82k/s 8.0s
> 2 152k/s 4.3s 90k/s 7.2s
> 8 (7) 243k/s 2.7s 97k/s 6.7s
>
> So master runs at up to ~2.4x the budget, and v2 stays under it, including with more than 4 processes. The vacuum and vacuum_parallel
> regression output matches expected.

Thanks for reviewing and benchmarking.

> Regarding the 1s cap, I think the 800 in your example is where the 6.4s
> cap kicks in, not the 1s one. With delay 100ms, limit 200 and 16 processes, the 1s cap applies once the local balance passes 125,
> which can probably happen just from uneven work across processes, no skipped delay points needed. It's bounded though, a capped process
> gets at most roughly 1.6-1.8x its share there, and only once nworkers * cost_delay gets near 1s. So I'm fine with the 1s cap, but
> I'd mention that in the comment instead of saying the budget always holds.

The 4x ratio I had in mind is the threshold for the cost_delay * 4 *
nworkers cap (and for the non-parallel cost_delay * 4), not for the 1s
cap. Either way, overrunning the budget once a cap kicks in seems fine
to me, for a good reason that's already mentioned in the docs:
https://www.postgresql.org/docs/devel/runtime-config-vacuum.html#GUC-VACUUM-COST-LIMIT.

I would just cap it at 1s like this:

- /* Compute sleep time based on the local cost balance */
- msec = vacuum_cost_delay * VacuumCostBalanceLocal / vacuum_cost_limit;
+ /* Compute sleep time based on the local cost balance and nworkers */
+ msec = vacuum_cost_delay * VacuumCostBalanceLocal * nworkers /
+ vacuum_cost_limit;
+
+ /*
+ * Avoid uselessly long delays, as in a non-parallel vacuum. Also
+ * never sleep more than 1s at once, as sleeping any longer in
+ * pg_usleep() keeps the process from responding to latches and
+ * signals promptly.
+ */
+ msec = Min(msec, Min(vacuum_cost_delay * 4 * nworkers, 1000));

> Regarding the WaitLatch() for HEAD, worth remembering it was tried before
> (4753ef37e0e) and reverted in 720de00af49 because the millisecond timeout broke fractional cost_delay.

Yes, that's a nice find. Any sub-millisecond delay is lost with WaitLatch().

> One thing I'm less sure about is back-patching to 14-18. Plain manual VACUUM already goes parallel when there are enough indexes, so anyone
> with a cost delay set could see it get noticeably slower after a minor update (with the default 2 workers, 4.3s -> 7.2s above).
> Fixing it in 19 and master seems safer to me, but I'm happy to go with the consensus.

You are right, with this fix, anyone using the vacuum comamnd with the
parallel degree could get slower without realizing it, and they would
need to raise the cost limit and reduce the cost delay to give enough
budget for everyone.

I tend to agree with your point on not back-patching to <= PG18. As
mentioned upthread [1], the fact that it hasn't been reported so far
means it may have gone unnoticed, or everyone wanted vacuum to run
faster at the cost of foreground path performance.

Maybe a doc adjustment for <= PG18 is fine, explaining that the budget
can be overrun with parallel degree, so it won't be a surprise when
someone hits this in future, and we ship the fix to PG19+. This also
works for me.

Another option for PG19+ is to leave the vacuum command as-is and
apply the fix only to parallel autovacuum, which isn't expected to get
in the way of the foreground path.

Thoughts?

[1] https://postgr.es/m/CALj2ACXsDZR5kpsbOTHHVNNfJuN%2B9_x%3D8XzKh2Yc-0yTCwtu7g%40mail.gmail.com

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com