Date: Fri, 19 Jul 2024 15:44:22 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	Justin Pryzby <pryzby@telsasoft.com>,
	Masahiko Sawada <sawada.mshk@gmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	Peter Smith <smithpb2250@gmail.com>,
	Julien Rouhaud <rjuju123@gmail.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade and logical replication
Message-ID: <ZprQJv_TxccN3tkr@nathan>
References: 
 <TYCPR01MB120773E8D02D58F50B24DA6ADF54E2@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CAA4eK1+sO6Td4kUnV-G7K_doZm4qERSLj-rw2vMBLUNh9=JBqQ@mail.gmail.com>
 <TYCPR01MB12077B16EEDA360BA645B96F8F54C2@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CALDaNm3h7ReG5wB9bhPpU=GpVp9Cfotx36tRErw5oKEXu+cKTA@mail.gmail.com>
 <TYCPR01MB12077C4D38046B89599322EBBF54C2@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CALDaNm1kdAnfx8N1AUQUuxc0zXbghFKi3-4vFAym_Y3hPOm1hA@mail.gmail.com>
 <TYCPR01MB120778982E38315F541D61AD1F5512@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CAA4eK1+B47ooT2HD1OTbSkVedK1BJLbr2K-seTd9pg9-wTgOdQ@mail.gmail.com>
 <CALDaNm0z=fLtio1h50K8WossUGXU+gy0H9y9=RYh1DDZiq2EDw@mail.gmail.com>
 <CAA4eK1LrO2qGcQot-9EgxpQRiT1O5+enHb57oN7OSNKSTn2hNQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAA4eK1LrO2qGcQot-9EgxpQRiT1O5+enHb57oN7OSNKSTn2hNQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ZprQJv_TxccN3tkr%40nathan>
Precedence: bulk

I've been looking into optimizing pg_upgrade's once-in-each-database steps
[0], and I noticed that we are opening a connection to every database in
the cluster and running a query like

	SELECT count(*) FROM pg_catalog.pg_subscription WHERE subdbid = %d;

Then, later on, we combine all of these values in
count_old_cluster_subscriptions() to verify that max_replication_slots is
set high enough.  AFAICT these per-database subscription counts aren't used
for anything else.

This is an extremely expensive way to perform that check, and so I'm
wondering why we don't just do

	SELECT count(*) FROM pg_catalog.pg_subscription;

once in count_old_cluster_subscriptions().

[0] https://commitfest.postgresql.org/48/4995/

-- 
nathan