Re: Horribly slow pg_upgrade performance with many Large Objects

Nathan Bossart <nathandbossart@gmail.com>

From: Nathan Bossart <nathandbossart@gmail.com>

To: Hannu Krosing <hannuk@google.com>

Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2025-04-08T15:46:41Z

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 Tue, Apr 08, 2025 at 09:35:24AM +0200, Hannu Krosing wrote:
> On Tue, Apr 8, 2025 at 12:17 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
>> That being said, I
>> regularly hear about slow upgrades with many LOs, so I think it'd be
>> worthwhile to try to improve matters in v19.
> 
> Changing the LO export to dumping pg_largeobject_metadata content
> instead of creating the LOs should be a nice small change confined to
> pg_dump --binary-upgrade only so perhaps we could squeeze it in v18
> still.

Feature freeze for v18 was ~4 hours ago, so unfortunately this is v19
material at this point.

-- 
nathan