Date: Mon, 6 Jan 2025 16:15:25 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@anarazel.de>,
	"Imseih (AWS), Sami" <simseih@amazon.com>,
	Justin Pryzby <pryzby@telsasoft.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: allow changing autovacuum_max_workers without restarting
Message-ID: <Z3xV_b3wp-XQR3Rm@nathan>
References: <Zow-DBaDY2IzAzA2@nathan>
 <Zpq9dx37tdUkV42U@nathan>
 <ZqVZSQlgpmy6MM5X@nathan>
 <ZsY96PvqkoJaC2Y-@nathan>
 <ZzzMcv0lAdY4OzGi@nathan>
 <Z2XJjI8fO8ksOv4E@nathan>
 <Z3v8UFk0sOgFgWeZ@nathan>
 <Z3xFE7DQTSRJw2FM@nathan>
 <1346002.1736198977@sss.pgh.pa.us>
 <Z3xQIPX3aeri4DiT@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="iBkHJFs49pTCTaNh"
Content-Disposition: inline
In-Reply-To: <Z3xQIPX3aeri4DiT@nathan>
Archived-At: <https://www.postgresql.org/message-id/Z3xV_b3wp-XQR3Rm%40nathan>
Precedence: bulk


--iBkHJFs49pTCTaNh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Jan 06, 2025 at 03:50:24PM -0600, Nathan Bossart wrote:
> I'm obviously biased, but I think it would be unfortunate to block features
> like this one because of low settings that would otherwise be unsuitable
> for any reasonable production workload.  If we do want to at least support
> check-world on these systems, another option could be to simply lower the
> default of autovacuum_worker_slots to 7 (or maybe lower).  Of course, that
> only helps until the next time more semaphores are required, but that's not
> a new problem.

I've attached a patch to lower the default to 5.  That at least gives a
little bit of wiggle room for autovacuum_max_workers (and for a couple of
new auxiliary processes).  FWIW the reason I originally set the default to
16 was to prevent most users from ever needing to think about adjusting
autovacuum_worker_slots (which requires a restart and is a completely new
parameter that most will be unfamiliar with).

-- 
nathan

--iBkHJFs49pTCTaNh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="v1-0001-lower-default-of-autovacuum_worker_slots-to-5.patch"

From 3f3c783c025e2a931bb766dc6d13c7186c957ebc Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 6 Jan 2025 16:09:49 -0600
Subject: [PATCH v1 1/1] lower default of autovacuum_worker_slots to 5

---
 doc/src/sgml/config.sgml                      | 2 +-
 src/backend/utils/misc/guc_tables.c           | 2 +-
 src/backend/utils/misc/postgresql.conf.sample | 2 +-
 3 files changed, 3 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 740ff5d5044..4fda5e6ae79 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8639,7 +8639,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       <listitem>
        <para>
         Specifies the number of backend slots to reserve for autovacuum worker
-        processes.  The default is 16.  This parameter can only be set at server
+        processes.  The default is 5.  This parameter can only be set at server
         start.
        </para>
        <para>
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..c75359b6bc4 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3472,7 +3472,7 @@ struct config_int ConfigureNamesInt[] =
 			NULL
 		},
 		&autovacuum_worker_slots,
-		16, 1, MAX_BACKENDS,
+		5, 1, MAX_BACKENDS,
 		NULL, NULL, NULL
 	},
 	{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..c7d3852c040 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,7 +661,7 @@
 
 #autovacuum = on			# Enable autovacuum subprocess?  'on'
 					# requires track_counts to also be on.
-autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
+autovacuum_worker_slots = 5		# autovacuum worker slots to allocate
 					# (change requires restart)
 #autovacuum_max_workers = 3		# max number of autovacuum subprocesses
 #autovacuum_naptime = 1min		# time between autovacuum runs
-- 
2.39.5 (Apple Git-154)


--iBkHJFs49pTCTaNh--