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-08T16:52:45Z
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 6:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Hannu Krosing <hannuk@google.com> writes:
> > I think we do preserve role oids
>
> Oh ... I'd been looking for mentions of "role" in
> pg_upgrade_support.c, but what I should have looked for was
> "pg_authid".  So yeah, we do preserve role OIDs, and maybe that's
> enough to make this workable, at least with source versions that
> share the same rules for what goes into pg_largeobject_metadata and
> pg_shdepend.  It's not something I'd risk back-patching though.

The risk is why I suggest to have this in backports as something last
resort which is gated on an environment variable

I have been forced to do this half-manually a few times to make
pg_upgrade feasible at all.

Current really UGH workaround is to use pg_repack support for swapping
relfilenodes of pg_largeobject_metadata with a user table before and
after the upgrade and then manually patching up leftovers like
pg_shdepend after pg_upgrade.

These have fortunately been cases where there were no other
dependencies like comments or security labels on LOs, but these to
should be doable;

>                         regards, tom lane