Horton

Re: Horribly slow pg_upgrade performance with many Large Objects

Hannu Krosing <hannuk@google.com>

View thread
From: Hannu Krosing <hannuk@google.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nathan Bossart <nathandbossart@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-04-08T17:18:31Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_upgrade: Use COPY for large object metadata.

  2. pg_dump: Fix object-type sort priority for large objects.

  3. pg_upgrade: Mention that we preserve database OIDs in a comment.

  4. pg_upgrade: Preserve database OIDs.

  5. Fix pg_upgrade for oid removal.

On Tue, Apr 8, 2025 at 7:07 PM 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 difference.

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

--
Hannu