MIME-Version: 1.0
References: 
 <CAMT0RQSS-6qLH+zYsOeUbAYhop3wmQTkNmQpo5--QRDUR+qYmQ@mail.gmail.com>
 <Z_RO37whB1L2LbiD@nathan>
 <CAMT0RQRL08=Si+9V7RW7VKcbo2H7GO6ME3opkS_WHpE8qctBLg@mail.gmail.com>
 <Z_VE4Q4A2nNCX3Ny@nathan> <4044567.1744128814@sss.pgh.pa.us>
 <CAMT0RQSALxZ2DJbq0b5cy+cvmpVxkawmv7c+qTWYObubZVr+2g@mail.gmail.com>
 <4047312.1744130263@sss.pgh.pa.us> <Z_VTucTiS01p-d6z@nathan>
 <4050387.1744132029@sss.pgh.pa.us>
In-Reply-To: <4050387.1744132029@sss.pgh.pa.us>
From: Hannu Krosing <hannuk@google.com>
Date: Tue, 8 Apr 2025 19:18:31 +0200
Message-ID: 
 <CAMT0RQTCxjfK_fU4bBLw0Xv61vUEqP4TfNqYJGRmMqqikPBZ3Q@mail.gmail.com>
Subject: Re: Horribly slow pg_upgrade performance with many Large Objects
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAMT0RQTCxjfK_fU4bBLw0Xv61vUEqP4TfNqYJGRmMqqikPBZ3Q%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 8, 2025 at 7:07=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Nathan Bossart <nathandbossart@gmail.com> writes:
> > I do think it's worth considering going back to copying
> > pg_largobject_metadata's files for upgrades from v16 and newer.
>
> (If we do this) I don't see why we'd need to stop at v16.  I'm
> envisioning that we'd use COPY, which will be dealing in the
> text representation of aclitems, and I don't think that's changed
> in a long time.  The sort of thing that would break it is changes
> in the set of available/default privilege bits for large objects.
>
> That is, where the dump currently contains something like
>
> SELECT pg_catalog.lo_create('2121');
> ALTER LARGE OBJECT 2121 OWNER TO postgres;
> GRANT ALL ON LARGE OBJECT 2121 TO joe;

Also note that in my --binary-upgrade tests the 100 min / 100M objects
ratio was in case with no grants. I would expect this to grow to at
least 120 to 150 minutes when grants are also involved.

In copy case I would expect the presence of grants to not make much differe=
nce.

> we'd have
>
> COPY pg_largeobject_metadata FROM STDIN;
> ...
> 2121    10      {postgres=3Drw/postgres,joe=3Drw/postgres}
> ...

--
Hannu