From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Kumar, Sachin" <ssetiya@amazon.com>
cc: Robins Tharakan <tharakan@gmail.com>,
        Nathan Bossart <nathandbossart@gmail.com>, Jan Wieck <jan@wi3ck.info>,
        Bruce Momjian <bruce@momjian.us>, Zhihong Yu <zyu@yugabyte.com>,
        "Andrew Dunstan" <andrew@dunslane.net>,
        Magnus Hagander <magnus@hagander.net>,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
In-reply-to: <4107508.1704218778@sss.pgh.pa.us>
References: <c0f5fde5-5cad-65ed-dd30-cd852301d074@wi3ck.info> <986904.1616525964@sss.pgh.pa.us> <6cccaa33-c263-b8a2-b064-985605d33d25@wi3ck.info> <988415.1616528159@sss.pgh.pa.us> <872315a8-99fc-da4e-463d-784cfb5a025d@wi3ck.info> <1010642.1616532950@sss.pgh.pa.us> <802b96e9-f5e1-015c-dfb9-8756974b11fc@wi3ck.info> <0263bf35-05d6-02a1-519b-b7895a918314@wi3ck.info> <20220825003227.GA1456581@nathanxps13> <663393ca-b2ff-26f0-2e2d-adc942aff4fd@timescale.com> <20220908231807.GA2242918@nathanxps13> <CAAWbhmgUb8p7ff_ZX5jCvqM=ipPxbbDJTXMNVzH-Ho_CXVkRHA@mail.gmail.com> <0643CC11-223A-4039-AC34-94E127462796@amazon.com> <1152134.1699555261@sss.pgh.pa.us> <83D44BE5-0088-4D41-8AE6-20A05D026F46@amazon.com> <81D13E16-BA04-43CF-9B89-B8924300B211@amazon.com> <240D05EC-8B28-4112-BEAB-85ECBAF3F871@amazon.com> <2055911.1702258962@sss.pgh.pa.us> <557FD681-3929-44A1-87B2-6B5E10C4A66B@amazon.com> <4107508.1704218778@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 02 Jan 2024 13:06:18 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1144200.1704484954.1@sss.pgh.pa.us>
Date: Fri, 05 Jan 2024 15:02:34 -0500
Message-ID: <1144201.1704484954@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1144201.1704484954%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> "Kumar, Sachin" <ssetiya@amazon.com> writes:
>> I was not able to find email thread which details why we are not using
>> parallel pg_restore for pg_upgrade.

> Well, it's pretty obvious isn't it?  The parallelism is being applied
> at the per-database level instead.

On further reflection, there is a very good reason why it's done like
that.  Because pg_upgrade is doing schema-only dump and restore,
there's next to no opportunity for parallelism within either pg_dump
or pg_restore.  There's no data-loading steps, and there's no
index-building either, so the time-consuming stuff that could be
parallelized just isn't happening in pg_upgrade's usage.

Now it's true that my 0003 patch moves the needle a little bit:
since it makes BLOB creation (as opposed to loading) parallelizable,
there'd be some hope for parallel pg_restore doing something useful in
a database with very many blobs.  But it makes no sense to remove the
existing cross-database parallelism in pursuit of that; you'd make
many more people unhappy than happy.

Conceivably something could be salvaged of your idea by having
pg_upgrade handle databases with many blobs differently from
those without, applying parallelism within pg_restore for the
first kind and then using cross-database parallelism for the
rest.  But that seems like a lot of complexity compared to the
possible win.

In any case I'd stay far away from using --section in pg_upgrade.
Too many moving parts there.

			regards, tom lane