Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible

Have you considered re-creating pg_shdepend from
pg_largeobject_metadata directly instead of having special cases for
dumping it ?

It would also be useful in cases of old (pg_upgraded since before pg
12) databases which might be missing it anyway.


On Thu, Aug 14, 2025 at 5:22 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> (new thread)
>
> On Fri, Jul 18, 2025 at 11:05:04AM -0500, Nathan Bossart wrote:
> > I'm cautiously optimistic that we can find some better gains for upgrades
> > from v16 and newer.  That would involve dumping lo_create() commands for
> > all LOs with comments/seclabels, dumping the relevant pg_shdepend rows, and
> > then copying/linking the pg_largeobject_metadata files like we did prior to
> > v12.
>
> Here is a patch.  For background, the reason this is limited to upgrades
> from v16 and newer is because the aclitem data type (needed by
> pg_largeobject_metadata.lomacl) changed its storage format in v16 (see
> commit 7b378237aa).  Note that the patch is essentially a revert of commit
> 12a53c732c, but there are enough differences that it should be considered a
> fresh effort.
>
> Something I hadn't anticipated is that we need to take special care to
> transfer the relfilenode of pg_largeobject_metadata and its index, as was
> done for pg_largeobject in commits d498e052b4 and bbe08b8869.  In fact, the
> majority of the patch is dedicated to that.
>
> My testing showed some decent, but not earth-shattering performance
> improvements from this patch.  For upgrades with many large objects with
> NULL lomacl/lomowner columns, pg_upgrade was 50% faster.  With non-NULL
> lomacl/lomowner, that dropped to 25%.  When each large object had a
> comment, there was no change.  I'm assuming that its rare to have lots of
> large objects with comments or security labels, so I don't see any need to
> expend energy trying to optimize that use-case.
>
> I am a bit concerned that we'll forget to add checks for new types of
> dependencies similar to comments and security labels.  If we do, pg_upgrade
> should just fail to restore the schema, and fixing the code should be easy
> enough.  Also, we'll need to remember to revisit this code if there's
> another storage format change for one of pg_largeobject_metadata's columns,
> but that seems unlikely to happen anytime soon.  On the whole, I'm not too
> worried about either of these points.
>
> --
> nathan