autovacuum: automatically propagate updated parameters
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253184psql -h localhost -U postgresBuilt from patchset v14 (message #14), August 27, 2026 at 11:04 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253184_14 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253184_14 && git checkout t253184_14Patchset v14 (message #14) is on t253184_14
From the documentation:
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.
On 24 Jul 2026, at 10:33, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
From the documentation:
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.
The part that worry me is the below testcode. The relation 'filler' is sized
large enough to outlast the test:
+# second worker's table: no indexes, no cost reloptions (participates in
+# balancing), sized to outlast the test
+$node->safe_psql(
+ 'regress_db2', qq{
+ CREATE TABLE filler (id int, pad text) WITH (autovacuum_enabled = false);
+ INSERT INTO filler SELECT g, repeat('x', 100) FROM generate_series(1, 200000) g;
+});This is then used for two test cases, the first one has legitimate value:
+my $log = slurp_file($node->logfile, $log_offset);
+my @limits =
+ $log =~ /parallel autovacuum worker updated cost params: cost_limit=(\d+),/g;
+note("parallel worker cost_limit sequence: @limits");
+is($limits[0], '100',
+ 'parallel workers see the rebalanced cost limit');The second seems less exciting.
+my $filler_running = $node->safe_psql('regress_db2',
+ "SELECT count(*) FROM pg_stat_progress_vacuum WHERE relid = 'filler'::regclass");
+is($filler_running, '1', 'second autovacuum worker was still running');My worry is that this seems very timing dependent and risk being flaky in the
buildfarm where every unsuspected timing window known to man tends to happen on
a regular basis. Given the first test for the DEBUG2 log output, do we lose
all that much coverage if we cut the suite short after that, and reduce the
size of filler? Reducing the resources needed to run the test and the
potential for red builds in the BF seems like a win.
--
Daniel Gustafsson
Hi,
On Mon, Aug 24, 2026 at 5:31 AM Daniel Gustafsson <daniel@yesql.se> wrote:
On 24 Jul 2026, at 10:33, Zsolt Parragi <zsolt.parragi@percona.com> 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
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?
I mainly added the testcase as a repro, I am not sure how useful it is as an actual test case. But if we want to keep it, I can certainly reduce it.
How about propagating the params to parallel workers launched by
autovacuum right after updating cost limits in
AutoVacuumUpdateCostLimit()?
That could work too, I used the current location because most of the parallel related things, including the other call to parallel_vacuum_propagate_shared_delay_params is in vacuum.c, not in autovacuum.c
On 25 Aug 2026, at 00:06, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
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?I mainly added the testcase as a repro, I am not sure how useful it is
as an actual test case. But if we want to keep it, I can certainly
reduce it.
I think there is value in having a test, especially if we can roll it into 001
as quick step.
How about propagating the params to parallel workers launched by
autovacuum right after updating cost limits in
AutoVacuumUpdateCostLimit()?That could work too, I used the current location because most of the
parallel related things, including the other call to
parallel_vacuum_propagate_shared_delay_params is in vacuum.c, not in
autovacuum.c
There is also strong value in not changing things too drastic at this point in
the cycle.
--
Daniel Gustafsson
Hi,
On Mon, Aug 24, 2026 at 3:16 PM Daniel Gustafsson <daniel@yesql.se> wrote:
On 25 Aug 2026, at 00:06, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
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?I mainly added the testcase as a repro, I am not sure how useful it is
as an actual test case. But if we want to keep it, I can certainly
reduce it.I think there is value in having a test, especially if we can roll it into 001
as quick step.
+1. How about adding the test in the existing
001_parallel_autovacuum.pl instead of a new TAP test file?
How about propagating the params to parallel workers launched by
autovacuum right after updating cost limits in
AutoVacuumUpdateCostLimit()?That could work too, I used the current location because most of the
parallel related things, including the other call to
parallel_vacuum_propagate_shared_delay_params is in vacuum.c, not in
autovacuum.cThere is also strong value in not changing things too drastic at this point in
the cycle.
Agreed. I'm fine with the v1 approach and backpatching to PG19.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
I attached v2 which moves the test case and removes the last part.
On Tue, Aug 25, 2026 at 4:17 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
I attached v2 which moves the test case and removes the last part.
Thank you for the report and making the patch.
The fix looks good to me. As for the regression tests, since we don't
stop the second av worker, the first av worker needs to resume and
update its cost limit before the second worker finishes:
+# Second worker -> balance = 2
+$node->safe_psql('regress_db2',
+ 'ALTER TABLE filler SET (autovacuum_enabled = true)');
+$node->wait_for_log(
+ qr/VacuumUpdateCosts\(db=$db2oid, rel=$filleroid, dobalance=yes,
cost_limit=250,/,
+ $log_offset);
+
+$node->safe_psql('postgres',
+ "SELECT injection_points_wakeup('autovacuum-start-parallel-vacuum')");
+$node->safe_psql('postgres',
+ "SELECT injection_points_detach('autovacuum-start-parallel-vacuum')");
+
+# First param load must show the rebalanced limit.
+$node->wait_for_log(
+ qr/parallel autovacuum worker updated cost params: cost_limit=\d+,/,
+ $log_offset);
+my $log = slurp_file($node->logfile, $log_offset);
+my @limits =
+ $log =~ /parallel autovacuum worker updated cost params: cost_limit=(\d+),/g;
+note("parallel worker cost_limit sequence: @limits");
+is($limits[0], '250', 'parallel workers see the rebalanced cost limit');Which seems to be unstable to me. In order to ensure that the second
worker lives when the first worker resumes its job, 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.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
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.
On 27 Aug 2026, at 10:37, Zsolt Parragi <zsolt.parragi@percona.com> 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.
--
Daniel Gustafsson
Hi,
On Thu, Aug 27, 2026 at 4:57 AM Daniel Gustafsson <daniel@yesql.se> 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.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
On 27 Aug 2026, at 23:15, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Thu, Aug 27, 2026 at 4:57 AM Daniel Gustafsson <daniel@yesql.se> 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
On Thu, Aug 27, 2026 at 3:02 PM Daniel Gustafsson <daniel@yesql.se> wrote:
On 27 Aug 2026, at 23:15, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Thu, Aug 27, 2026 at 4:57 AM Daniel Gustafsson <daniel@yesql.se> 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
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
Attachments:
t253184_14v4-0001-Propagate-rebalanced-cost-limit-to-parallel-vacuu.patchapplication/octet-stream; name=v4-0001-Propagate-rebalanced-cost-limit-to-parallel-vacuu.patchDownload+91-2
On 28 Aug 2026, at 00:52, Zsolt Parragi <zsolt.parragi@percona.com> 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
On 28 Aug 2026, at 09:13, Daniel Gustafsson <daniel@yesql.se> wrote:
On 28 Aug 2026, at 00:52, Zsolt Parragi <zsolt.parragi@percona.com> 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 tablesThanks 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.
Done.
--
Daniel Gustafsson