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

On Tue, Oct 27, 2026 at 3:18 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> 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));

Sound reasonable change.

> > 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?

I'd like to withdraw my previous proposal to backpatch it to all
supported versions. We haven't received any complaints about this
behavior so far, and with the fix VACUUM could get slower after a
minor update. So I agree to push it to v19 and master, and I think we
should mention this behavior change in the v19 release notes. Also, I
think it'd be better to update the paragraph in maintenance.sgml that
says parallel workers use the same cost delay parameters as the
leader, to mention that they share the cost limit. I'm not sure we
need to add a note for v18 or older, as the current behavior of the
VACUUM command doesn't violate the documentation.

As for applying the fix only to parallel autovacuum, I'm not sure it's
a good idea. The cost-based vacuum delay would work differently
between VACUUM and autovacuum, and the VACUUM command would keep
overrunning the budget in future versions.

Regards

--
Masahiko Sawada

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