Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w3NGK-0019uN-14 for pgsql-hackers@arkaria.postgresql.org; Thu, 19 Mar 2026 23:58:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w3NGI-003YMZ-2f for pgsql-hackers@arkaria.postgresql.org; Thu, 19 Mar 2026 23:58:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w3NGI-003YMP-1I for pgsql-hackers@lists.postgresql.org; Thu, 19 Mar 2026 23:58:46 +0000 Received: from mail-lj1-x232.google.com ([2a00:1450:4864:20::232]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w3NGF-000000005gd-44Je for pgsql-hackers@lists.postgresql.org; Thu, 19 Mar 2026 23:58:46 +0000 Received: by mail-lj1-x232.google.com with SMTP id 38308e7fff4ca-38ad26e3992so12509791fa.1 for ; Thu, 19 Mar 2026 16:58:43 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1773964723; cv=none; d=google.com; s=arc-20240605; b=ba3J7X3z3x2Lhs0TGe1SxmjzMckfrOTm8ze/MWXjgueQgC/MYcA6+cplfJsbQy9okV E+ayS3OK+0UWikHJXDMQbdaQehwHey6pI/1QL6qdOsvc8Snt99vPMjrRRvlRQCqU/dVk P5tGwgDOU356kIq+p6Bk8yu43Av8ix81f+o5pw72M5waE2JF2FaJl4atKzn9+njrPcmj gzSDbQ2DMpX0VdyWqib4W6zNBLLv1zOJAUIvAWGkZS3NjfBOsIgW1NTSEscxqqk03P4s g2jEAF5aQi5+4hpMhIx+IXjkJMUVLQ56nHxfz8s/x/VZcxnNwf/oTU59wgnXauCLkFLW kkBA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=auWG7zCaFX++YM8cDpfNBne5kyv6fK14KtchZ49/ai0=; fh=XvCIjaOsyc4nkSSGagXnspHRqTpAHPrnyYhzzMAMwVo=; b=Wpvd7HgpdLftji7FguGwvQSbJUnHhS1nkh6New6Dj3M37d0IISeNPIOEBLE1s4+fAr oPckQbntzEEHa0iMEQydoUdPRRAmVodQYGKlqZaZEMQasmMSLjPqfyPoQYo28DuS75f8 RTjRxo+zQycr2kSHK7dKpxFtv60IXbcASZBMBXm2LkUVg2lgTYDntTQ12CTG9tk7oylu uC3oudx7Wy6t7KxJJ1y03jUfifpzG8Vhb+LTDg0fYfJIr6EqIazabaFqOl8vv8iFUU/E uRrushBEXl7I0mUn6qgv3d7gXNaKO6e7ZdnWjYjFsxnLOdc6KCSHRv9X9bFYdvSzsX4a x0JQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773964723; x=1774569523; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=auWG7zCaFX++YM8cDpfNBne5kyv6fK14KtchZ49/ai0=; b=JXJcTBfCtOZ1uDVmaShQfYh4+WEqxG5jrOamrEed3pVQHVdRxZCizOOI4qzmvUYwWw 6r3GlTQWcwc91TYzMZxklVgsIvBDUejmG+ktpHxx/HBCdJdaN5+IF5sj+Sh1SAgJ8a2F QcMJKPpbv5IIb/Tg9bAhxisCuU7CcUbkXe/0TNlhsMOIA9UlnNtrByFBztZyABa5eih2 6E3+lhJmCrrWUbkVmsuwlk0fAFUKIx32YZ1pqPbvVaKobwqIRk9+/mRIMQMXFkNWm7SV quxqukz5tKuRrhWUDxou9+ASDhppjEKEXtkRKVqC6ufhjUtKWxv37rkWFuvKU8VWgiCw 63KA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773964723; x=1774569523; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-gg:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=auWG7zCaFX++YM8cDpfNBne5kyv6fK14KtchZ49/ai0=; b=tSr0VdjPcz6ExKeLhFNTufjJFC53NBeYeOhDpkE3U4IEuDOM2Dn3O4vqX9K108QDmz AaU6PMCYNE1+y8oZvIKICgX45yD1AUnEWgluA/20PTW+5Xo5PmgOMAE0j2TsWgVq9ELK 6HdjgHzNC5QH3uvKdD41hJPncvasV5C/ue+0ODyCbxasCUf4AIDoCcQ44Gf0HGpWbwZV mDb9Qh9LNv5isEMU5AY5Veiu4sNDrlV2fJ7jodwJswQFC6t/H9HlyNICNYgXDqx4U+0c 3EQdex8msCUJXPvDqTeBjthNRkkqN35ME45n0CdnqzpnZNGaGSv+bqw+koEeBIuPGzVU /Wjg== X-Forwarded-Encrypted: i=1; AJvYcCXCPUsXLjxyuLs+eExIq4ew5nxiHOr4J56YJIqpJOjZLPSUQ2MWgqlj2N+tW4OkUEn2TGtw4p8+REqVY+9x@lists.postgresql.org X-Gm-Message-State: AOJu0YwkqBxTlqsv9/2BUy4w4Ilnynzadx5h2yVbQjOwtZjZtYUnjk0j /Gx3wHiaNY7fO3rOx+LR7meGJSVEzFBJnwwV0+nuxpzEfQ5hLJjmc1kZqXQU+GqThqnmq6IXzab P8WaMTBTDYHfswBs4lhosEpImUmTwSX4= X-Gm-Gg: ATEYQzwNo3y3IWfZgOUsehuqIn95LhZptJHNu/InS1948UeQwZ8DBLVXuAs4/AcoFiO Lx/smuTF+fj9vVDWe0Mh8ju2eJKBBaiobVwmYHFqCqz82mJWJ0RwHJh/AVsN5UtlEU7erNlfCUj WERnyQkzg2YtQfoit1KYsN/pitELKu/yCexwSnPWi/uupQGasadMKeukNs2mioKthHYIzSeQAsU YXTHpOWJqW/Nc72obNFAJ7rxKoB2X4BLxygt/47EBVL3M38DBGfRD+P67Jz0jmzaM3mQL01igZL SleuZ86W X-Received: by 2002:a05:651c:4109:b0:38a:4401:d2ac with SMTP id 38308e7fff4ca-38bf96cfba6mr3979031fa.17.1773964722619; Thu, 19 Mar 2026 16:58:42 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Masahiko Sawada Date: Thu, 19 Mar 2026 16:58:05 -0700 X-Gm-Features: AaiRm50zyESOdfY13-q8I0dTognNAeyNa3I8LgRwKza6KT6-Z-ErDRm0PmqVJoc Message-ID: Subject: Re: POC: Parallel processing of indexes in autovacuum To: Daniil Davydov <3danissimo@gmail.com> Cc: Sami Imseih , Alexander Korotkov , Matheus Alcantara , Maxim Orlov , Postgres hackers Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, Mar 19, 2026 at 7:29=E2=80=AFAM Daniil Davydov <3danissimo@gmail.co= m> wrote: > > Hi, > > On Thu, Mar 19, 2026 at 2:49=E2=80=AFAM Masahiko Sawada wrote: > > > > Yes, we already have such a code for PARALLEL option for the VACUUM com= mand. > > > > 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], th= e > > > 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 processin= g 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 tab= les > are not considered for the parallel a/v? If so, I don't think it will alw= ays > 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 disa= ble > > > 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 *enab= le* > > > parallel a/v for several large tables rather than *disable* it for al= l 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 exam= ple : > > > 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. Anoth= er 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_worke= rs > > > shortage. Thus, processing of this table will take a very long time t= o > > > 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 examp= le) > multiple VACUUM PARALLEL operations running at the same time will face wi= th > a shortage of parallel workers. But I guess that every system has some sa= ne > limit for this parameter's value. If we want to ensure that all a/v leade= rs > are guaranteed to launch as many parallel workers as required, we might n= eed > 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 wi= ll > 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 > > storage parameter set, an autovacuum worker can perform index vacuumi= ng and > > index cleanup with background workers. The number of workers launched= by > > a single autovacuum worker is limited by the > > . > > I suggest adding here also a description of the method for calculating th= e > 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 para= llel > autovacuum can refer to this subchapter. I think it will be much easier t= o > 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