On Thu, Mar 19, 2026 at 7:29 AM Daniil Davydov <[email protected]> wrote: > > Hi, > > On Thu, Mar 19, 2026 at 2:49 AM Masahiko Sawada <[email protected]> wrote: > > > > Yes, we already have such a code for PARALLEL option for the VACUUM command. > > > > I guess it's better that autovacuum codes also somewhat follow this > > code for better consistency. > > > > I agree. You can find it in the v29-0002 patch. > > > > I'm afraid that I can't agree with you here. As I wrote above [1], the > > > parallel a/v feature will be useful when a user has a few huge tables with > > > a big amount of indexes. Only these tables require parallel processing > > > and a > > > user knows about it. > > > > Isn't it a case where users need to increase > > min_parallel_index_scan_size? Suppose that there are two tables that > > are big enough and have enough indexes, it's more natural to me to use > > parallel vacuum for both tables without user manual settings. > > > > Do you mean that the user can increase this parameter so that smaller tables > are not considered for the parallel a/v? If so, I don't think it will always > be handy. When I say "smaller tables" I mean that they are small relative to > super huge tables. But actually these "smaller tables" can be pretty big and > require a parallel index scan within parallel queries or VACUUM PARALLEL (not > an autovacuum).
I think that if these small tables are actually big, these are also eligible for using parallel autovacuums. > > > If we implement the feature as you suggested, then after setting the > > > av_max_parallel_workers to N > 0, the user will have to manually disable > > > processing for all tables except the largest ones. This will need to be > > > done > > > to ensure that parallel workers are launched specifically to process the > > > largest tables and not wasting on the processing of little ones. > > > > > > I.e. I'm proposing a design that will require manual actions to *enable* > > > parallel a/v for several large tables rather than *disable* it for all of > > > the rest tables in the cluster. I'm sure that's what users want. > > > > > > Allowing the system to decide which tables to process in parallel is a > > > good > > > way from a design perspective. But I'm thinking of the following example : > > > Imagine that we have a threshold, when exceeded, parallel a/v is used. > > > Several a/v workers encounter tables which exceed this threshold by 1_000 > > > and > > > each of these workers decides to launch a few parallel workers. Another > > > a/v > > > worker encounters a table which is beyond this threshold by 1_000_000 and > > > tries to launch N parallel workers, but facing the max_parallel_workers > > > shortage. Thus, processing of this table will take a very long time to > > > complete due to lack of resources. The only way for users to avoid it is > > > to > > > disable parallel a/v for all tables, which exceeds the threshold and are > > > not > > > of particular interest. > > > > I think the same thing happens even with the current design as long as > > users misconfigure max_parallel_workers, no? Setting > > autovacuum_max_parallel_workers to >0 would mean that users want to > > give additional resources for autovacuums in general, I think it makes > > sense to use parallel vacuum even for tables which exceed the > > threshold by 1000. > > > > Users who want to use parallel autovacuum would have to set > > max_parallel_workers (and max_worker_processes) high enough so that > > each autovacuum worker can use parallel workers. If resource > > contention occurs, it's a sign that the limits are not configured > > properly. > > > > Yeah, currently user can misconfigure max_parallel_workers, so (for example) > multiple VACUUM PARALLEL operations running at the same time will face with > a shortage of parallel workers. But I guess that every system has some sane > limit for this parameter's value. If we want to ensure that all a/v leaders > are guaranteed to launch as many parallel workers as required, we might need > to increase the max_parallel_workers too much (and cross the sane limit). > IMHO it may be unacceptable for many systems in production, because it will > undermine the stability. I understand the concern that if max_parallel_workers (and/or max_worker_processes) value are not high enough to ensure each autovacuum workers can launch autovacuum_max_parallel_workers, an autovacuum on the very large table might not be able to launch the full workers in case where some parallel workers are already being used by others (e.g., another autovacuum on a different slightly-smaller table etc.). But I'm not sure that the opt-out style can handle these cases. Even if there are two huge tables and users set parallel_vacuum_workers to both tables, there is no guarantee that autovacuums on these tables can use the full workers, as long as max_parallel_workers value is not enough. > > > The 0001 patch looks good to me. I've updated the commit message and > > attached it. I'm going to push the patch, barring any objections. > > > > Great news! Pushed the 0001 patch. > > BTW, do we need to mention that this parameter can be overridden by the > per-table setting? IIUC the per-table setting is not actually overwriting the GUC parameter value, but it works as an additional cap. For instance, if autovacuum_max_parallel_workers is 2 and autovacuum_parallel_workers is 5, we cap the parallel degree by 2, which is a similar behavior to other parallel operations such as the parallel_workers storage parameter. BTW it actually works in a somewhat different way than other autovacuum-related storage parameters; the per-table parameters overwrite GUC values. I decided to use the former behavior because autovacuum_max_parallel_workers can work as a global switch to disable all parallel autovacuum behavior on the system. > > Part 3 can briefly mention that autovacuum can perform parallel vacuum > > with parallel workers capped by autovacuum_max_parallel_workers as > > follow: > > > > For tables with the <xref > > linkend="reloption-autovacuum-parallel-workers"/> > > storage parameter set, an autovacuum worker can perform index vacuuming > > and > > index cleanup with background workers. The number of workers launched by > > a single autovacuum worker is limited by the > > <xref linkend="guc-autovacuum-max-parallel-workers"/>. > > I suggest adding here also a description of the method for calculating the > number of parallel workers. If so, I feel that this part of documentation will > be completely the same as in VACUUM PARALLEL (except a few little details). > Maybe we can create some dedicated subchapter in the "Routine vacuuming" where > we describe how the number of parallel workers is decided. Lets call it > something like "24.1.7 Parallel Vacuuming". Both VACUUM PARALLEL and parallel > autovacuum can refer to this subchapter. I think it will be much easier to > maintain. What do you think? Describing the parallel vacuum in a new chapter in section 24.1 sounds like a good idea. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
