Re: Horribly slow pg_upgrade performance with many Large Objects

On Mon, May 05, 2025 at 02:23:25PM -0500, Nathan Bossart wrote:
> That leaves pg_shdepend. For now, I've just instructed pg_upgrade to COPY
> the relevant pg_shdepend rows as an independent step, but perhaps there's a
> reasonably straightforward way to put that in pg_dump, too.

It turns out there is. TableDataInfo's filtercond field can be used to
easily add a WHERE clause to the data dumping command. On my laptop,
upgrading with --jobs=8 with 10M large objects evenly distributed across 10
databases (each with a non-bootstrap-superuser owner and another role with
select rights) takes ~100 seconds without this patch and ~30 seconds with
it.

I've also added dependency tracking, version checks (this only works for
upgrades from >=v12 for now), a hack to ensure the columns for
pg_largeobject_metadata/pg_shdepend are collected, and comments. I'm sure
there's something I've missed, but this patch has worked well in my tests
thus far.

Taking a step back, I'm a little disappointed in the gains here. A 3-9x
speedup is nice, but I guess I was hoping to find another order of
magnitude somewhere. To do any better, I think we'd need to copy the files
for pg_largeobject_metadata directly for upgrades from >= v16, but that
would have to fit somewhere between when pg_restore creates the database
and when it restores any large object comments/seclabels. I'm not wild
about the amount of hackery required to get that working.

--
nathan