Re: Horribly slow pg_upgrade performance with many Large Objects

Nitin Motiani <nitinmotiani@google.com>

From: Nitin Motiani <nitinmotiani@google.com>

To: Nathan Bossart <nathandbossart@gmail.com>

Cc: Hannu Krosing <hannuk@google.com>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2025-07-11T14:19:47Z

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 →

pg_upgrade: Use COPY for large object metadata.
- 161a3e8b682e 19 (unreleased) landed
pg_dump: Fix object-type sort priority for large objects.
- fb6c860bbd1f 19 (unreleased) landed
- e43fae199e50 17.6 landed
- 36026b0fe3ab 18.0 landed
pg_upgrade: Mention that we preserve database OIDs in a comment.
- e2665efd0f4d 18.0 landed
pg_upgrade: Preserve database OIDs.
- aa01051418f1 15.0 cited
Fix pg_upgrade for oid removal.
- 12a53c732ced 12.0 cited

On Fri, Jul 11, 2025 at 3:12 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> On Thu, Jul 10, 2025 at 06:05:26PM +0530, Nitin Motiani wrote:
> > -              * pg_largeobject_metadata, after the dump is restored.
> > +              * pg_largeobject_metadata, after the dump is restored.
> In versions
> > +              * before v12, this is done via proper large object
> commands.  In
> > +              * newer versions, we dump the content of
> pg_largeobject_metadata and
> > +              * any associated pg_shdepend rows, which is faster to
> restore.
> >                */
> >
> > Should the comment provide further detail on why this is only being done
> > for v12 and above?
>
> Yes.  I've fixed this in v3.
>
>
Thanks. Looks good to me. Also just would like to confirm that the
pg_dump_sort change will go in a different patch.

Regards,
Nitin Motiani
Google