Date: Fri, 9 Aug 2024 09:43:59 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Ilya Gladyshev <ilya.v.gladyshev@gmail.com>,
	Daniel Gustafsson <daniel@yesql.se>,
	Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: optimizing pg_upgrade's once-in-each-database steps
Message-ID: <ZrYrL8-LQ2LhSPQ4@nathan>
References: <Zpg4WnS9BRWbgBLi@nathan>
 <4493D323-9726-48F7-A702-E71FB991F76B@yesql.se>
 <ZprY4e23vR95Nc51@nathan>
 <Zp677slGLwhPhVWy@nathan>
 <10c1c8dd-4685-46d4-80be-56bdfca8659a@gmail.com>
 <ZqvJg_lTTTt82BfQ@nathan>
 <ZqwA_qngQM25FrjK@nathan>
 <60cbc507-4c36-4037-9af7-028b1c8455b5@gmail.com>
 <ZrJ3bu4og_Uj_9qj@nathan>
 <CADkLM=e0KxKJH0XSG6x2oGK7-W2Eet4AwwCqz5pxAG7QthX4Xw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CADkLM=e0KxKJH0XSG6x2oGK7-W2Eet4AwwCqz5pxAG7QthX4Xw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ZrYrL8-LQ2LhSPQ4%40nathan>
Precedence: bulk

On Thu, Aug 08, 2024 at 06:18:38PM -0400, Corey Huinker wrote:
> I think the underlying mechanism is basically solid, but I have one
> question: isn't this the ideal case for using libpq pipelining? That would
> allow subsequent tasks to launch while the main loop slowly gets around to
> clearing off completed tasks on some other connection.

I'll admit I hadn't really considered pipelining, but I'm tempted to say
that it's probably not worth the complexity.  Not only do most of the tasks
have only one step, but even tasks like the data types check are unlikely
to require more than a few queries for upgrades from supported versions.
Furthermore, most of the callbacks should do almost nothing for a given
upgrade, and since pg_upgrade runs on the server, client/server round-trip
time should be pretty low.

Perhaps pipelining would make more sense if we consolidated the tasks a bit
better, but when I last looked into that, I didn't see a ton of great
opportunities that would help anything except for upgrades from really old
versions.  Even then, I'm not sure if pipelining is worth it.

-- 
nathan