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 1wA1Bk-001zEt-0m for pgsql-hackers@arkaria.postgresql.org; Tue, 07 Apr 2026 07:49:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wA1Bi-00FO3Z-2H for pgsql-hackers@arkaria.postgresql.org; Tue, 07 Apr 2026 07:49:31 +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 1wA1Bi-00FO3R-1D for pgsql-hackers@lists.postgresql.org; Tue, 07 Apr 2026 07:49:30 +0000 Received: from mail-pl1-x630.google.com ([2607:f8b0:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wA1Bf-000000013UT-3TEq for pgsql-hackers@lists.postgresql.org; Tue, 07 Apr 2026 07:49:30 +0000 Received: by mail-pl1-x630.google.com with SMTP id d9443c01a7336-2aaf59c4f7cso21605555ad.1 for ; Tue, 07 Apr 2026 00:49:26 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1775548165; cv=none; d=google.com; s=arc-20240605; b=ErSGCrRzqQxFNWw/+qiyX4bEU5mZbCjzpI14d9Et/lCx3sreNgIZlXRupx3xEDjE9L tK/etNeDj093xTddcnDcRMUMzJIDGIMAiZUFHtcD+LaZt7soYvvRpU0ox01r+v6j9Rzx mslsk4Y3SEdira+M9Mt8IiHb2l1JSsohUAikjmr8B7fiJ26w8km4Rl5X4yrSt5VVScXD nqqlIfV1NBbBbnbgnGBTHuaq+N1B0n/z/HD2s5B8iG77jXLmnaAcAB7wNym6/ASvaXwz j5e5HyHnMEygVuPOd4Ez+VogfdrVzzOQMb+8yhsc3K1ZhHg9AygBOK6LScOqqKYPL3x/ E27w== 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=f76DrKXuVKPau4vH6Dmx41w3DlCbbtoWozv3BDfCIJQ=; fh=H7lsQHQ3/N6TMn4JDXuBjXto/E65Ka+4pR8MzJ/HCyg=; b=UwoNDkiP66OrCsTmhrKHUgC0m//bX9KhnhrcxqQepXS6h8UXm1O+udsDEwPk4RaNA1 P5S8r7Pd3hb4wvAqTsBVT3Q0xA4nSR29tXoPHNJtai8G4jFfGrr6pS8xEkcWkVVY1Ryr nimXoIBRoRYUCod0jOlsfPTJOSlUlHgN20T5//Tuj6FcwHhSXFLFjnlOGiH/u5IKuQX7 1x/ukAlcpRCzyhz39CRq36IM096JVAhY4gp3woVIaaepFYKGt2f/3JUeHdXat4GJyoKI QLiZ2dTMpy2arMtXBd1/Cyi6wwoCSAs3qsFGZfy2Yloqk94X8gQ2jDI8thMrjE9Nau8k qFKA==; 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=20251104; t=1775548165; x=1776152965; 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=f76DrKXuVKPau4vH6Dmx41w3DlCbbtoWozv3BDfCIJQ=; b=c0neuKetIctH2SPvCkwQGmJ09GyurfLWqaWZTdMcbts7kDr8Gjey+dHdhUDySiM2XR u2RIbhfJOU/+CFKZSkJkjNdvqW4JbMEZKPbGITdt73rIDV8RuaqfRbHMFoPwwlfYmcI+ LE4mw0ei5n7dLIGfFHzo+PRZZrypUwgkZ1WBPnkb2tassCsyWb0jVOfGwYyMoVwP8ocF QPyHdIkq4adiJF2O/UnalqH5enE+9N6wpTL9NYExnRJ4tH/bMs0Q1jTdzpZOYo3VX0q4 OlX75r3RZ+z0cAu9pADh6IbVoamosdpv3Un6gMjeq0YDIxN7LzCw4PW3Dob/KcrJy3q3 0A+g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775548165; x=1776152965; 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=f76DrKXuVKPau4vH6Dmx41w3DlCbbtoWozv3BDfCIJQ=; b=HyGM9pnSogeGhAblziEzoi5QCZEhmmFUS3eBn5cSsJ7+FeJTqkiWHNRkC0HvjjC7Pv FAhbPkd74klKwXAxNPPzm1ylHBqwlc2CXS138E+cxu5/mseYrvDm8aiw9jAogq5Vhsik zppFTXwWAPMltgdEceAbne1DBDxEh2icPZy59OPgCpFxnFA88IosiIs+NWwLl2hCTIjY QdhdKQEtK3qbnu6R6PYZQezI1Qha/cvWg6fwIP2FCOfAuAGu8e+Uzix4MIm6pV96XFr5 ShCl0Xyxg3yEgPn3pn40azMppABJwpeFNbdcq+jJHI+vqlKc36bMBhJ0NLJt1rKFS+i6 ntDA== X-Forwarded-Encrypted: i=1; AJvYcCWS8vEP59OPrf6BRPLuwmejzCX9Y8PtLWBVmvNfdC/kSvYXh9S03h+B6L6yW3Owu49xrODLUTGpO9m84rQn@lists.postgresql.org X-Gm-Message-State: AOJu0Yx72ycPvkIvo0iMZOcXl4UlCw7QNqW057B/2bokA+e4ODgkf7cE rlXJTuL3U73Ade8E8NayqT1gX60+tyXx46JNwjOkfYEHOXSakRfKCbvNfqVzUDUttddW2zcJJQ5 6WNMStmh1lNbHU9J4hSjrNn0RDF/n09g= X-Gm-Gg: AeBDiesGUTbaJXFPKcGEqN839tjDGvVJ6EvR4hM8l/UUZsHxMMP+XMF4cOCspGJvyW1 PacdDXHWKikuvzA9psa8PQ8j7DxuMPmwH+0MomtE63Q3TOCeoP/ZV4c1WjU//3YF5n1VE1UM0Um v+wKNHaW/KOZUSF7MpmEfVYWN2SsxV7SGWmUqmUAe53NIlwDueMWGPOh1wSactvuiloq1AB52l9 qbFDOcvbqjQ9oKtQtnGssYEranEtBZZLeKhq55pmFqL3lX+zqDC89yujyB6X1eHUxvKZG0dBC3P Waf1Fsia X-Received: by 2002:a17:903:37ce:b0:2b2:4697:78f3 with SMTP id d9443c01a7336-2b2817b3469mr167280365ad.34.1775548164806; Tue, 07 Apr 2026 00:49:24 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Masahiko Sawada Date: Tue, 7 Apr 2026 00:48:46 -0700 X-Gm-Features: AQROBzAFaYyzh1jVJIM39ofsSdIfi8pplyn0RUTfxmoT4VOTdT5nUF2g4EEEZP0 Message-ID: Subject: Re: POC: Parallel processing of indexes in autovacuum To: Daniil Davydov <3danissimo@gmail.com> Cc: SATYANARAYANA NARLAPURAM , Bharath Rupireddy , 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 Wed, Apr 1, 2026 at 12:44=E2=80=AFAM Daniil Davydov <3danissimo@gmail.co= m> wrote: > > > > > He confirmed that as a rule there are *hundreds of thousands* of tabl= es in the > > > system, the vast majority of which do not need to be vacuumed in para= llel mode. > > > > I'm still struggling to see the technical justification; why would a > > user want to avoid parallel vacuuming on eligible tables if they have > > already explicitly allowed the system to use more resources by setting > > autovacuum_max_parallel_workers to >0? > > Here I am talking about "introductory data". I.e. the situation that the = user > has before considering our parameter usage. Based on this situation, it s= eems > to me that not everyone will want to turn on parallel a/v (because of res= ource > shortage hazard). > > > If resource contention occurs, > > it is typically a sign that the global parameters need re-tuning. As I > > mentioned, the same contention can occur even with an opt-in style if > > multiple tables are manually configured. > > > > Yep, we already discussed it and I agree with you. I think that in the ca= se of > opt-in style the resource contention will be much more controlled. But ac= tually > the opt-in style in the form in which I originally proposed it, no longer= seems > like a good idea to me. Classic opt-in style will deprive us of support f= or > half of the parallel a/v use cases. Anton's proposal seems to me like a g= ood > balance between the two styles. > > > > He also suggested the following : let the reloption overlap the value= of the > > > GUC parameter. I.e. even if av_max_parallel_workers parameters is 0 t= he user > > > still can set the av_parallel_workers to 10 for some table, and autov= acuum > > > will process this table in parallel. > > > > > > I remember that you want to use the GUC parameter as a global switch,= and this > > > approach will break this logic. But according to Anton's words, it is= okay if > > > the GUC parameter cannot disable parallel a/v for all tables instantl= y. It will > > > become an administrator's responsibility to manually turn off paralle= l a/v for > > > several tables (again, it is completely OK). Thus, this feature can b= e handy > > > for all use cases. > > > > While some autovacuum parameters do override GUCs, those are typically > > local to the process (like cost delay). Parallel workers, however, are > > a shared system-wide resource. In a multi-tenant environment, allowing > > a single table's reloption to bypass the > > autovacuum_max_parallel_workers =3D 0 limit could lead to unexpected > > exhaustion of the worker pool. > > Will this exhaustion really be unexpected? If we describe such an ability= in > the documentation, and the user uses it, then everything is fair. Even if > administrator forgets that he enabled av_parallel_workers reloption somew= here, > then he can : How can DBAs prevent parallel workers from being exhaustly used if users set a high value to the reloption? > 1) > Check the logfile (if log level is not too high) searching for logs like > "parallel workers: index vacuum: N planned, N launched in total". > 2) > Run a query that selects all tables which have av_parallel_workers > 0. Does that mean DBAs would need to run these queries periodically? I don't think that in a multi-tenant environment, DBAs can (or should) execute ALTER TABLE on user-owned tables just to fix resource issues. > > >I think that this GUC should act as a > > reliable global switch for resource management. > > I agree that the "global switch" is an attractive idea and we should stri= ve > for it. But our parameter *can* play the role of the switch if users don'= t > manually touch the av_parallel_workers reloption. But if they do - well, = it is > their responsibility to turn the reloption off. > > > > > > I hope it doesn't look like as an adapting to the needs of a specific= user. > > > A lot of super-large productions are migrating to postgres now, and I= believe > > > that we should ensure their comfort too. > > > > I'm not prioritizing one specific use case over another. I believe > > that there are also users who want to use parallel vacuum on hundreds > > of thousands of tables. We should consider a better solution while > > checking it from multiple perspectives such as the usability, the > > robustness and consistency with the existing features and behaviors > > etc. > > For those users who want to use parallel a/v for hundreds of thousands of > tables we have the default value "-1" which allows parallel a/v everywher= e via > GUC parameter manipulation. > > For those users who want to parallel a/v on several specific tables we ca= n > allow setting reloption that will override the GUC. > > I guess that the question is : "Is it normal if the GUC parameter will lo= se > ability to turn off parallel a/v everywhere after the user has manually r= aised > the value for the av_parallel_workers reloption on a few tables?". If the > answer is "Yes", I don't see any obstacles for us to allow overriding the= GUC > parameter via reloption. I think the answer is no, particularly for this parameter. Since it controls a system-wide shared resource, it should be capped by a GUC to ensure centralized management, consistent with other parallel-query-related GUCs and reloptions. Regards, --=20 Masahiko Sawada Amazon Web Services: https://aws.amazon.com