Date: Thu, 5 Feb 2026 14:30:28 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Michael Paquier <michael@paquier.xyz>, Tom Lane <tgl@sss.pgh.pa.us>,
	Nitin Motiani <nitinmotiani@google.com>,
	Hannu Krosing <hannuk@google.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade: transfer pg_largeobject_metadata's files when
 possible
Message-ID: <aYT95PGdMvHHzwsP@nathan>
References: <aL8sgVdZcuqZnvXn@nathan>
 <3yd2ss6n7xywo6pmhd7jjh3bqwgvx35bflzgv3ag4cnzfkik7m@hiyadppqxx6w>
 <sc2uulu7gaajlnis7vqx25hmn6yd74drnlbzvupskx7jwyzejb@3vwodvuurtqb>
 <aYNuhbXkM5h8Zcsu@nathan>
 <aYOnT5EqAqI3ooBO@nathan>
 <qfp7z4c3m6bw53oky33halcdhomiijod6segzrhf757ijjvapr@e5rf4u5wnfmu>
 <aYTVAFTPelebVl6W@nathan>
 <nt56moqkh6yoerzw5cml6qu7m4mxu65vqtgdt3nbm5mhpg5hqt@4s5oa3sqhzub>
 <aYTwC8V50rPgw6GE@nathan>
 <t3zu4hyzwqws4puaqd2lhdlw7yp2ckgswuaaizmfi6seto3x4w@5exxg3kazkos>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <t3zu4hyzwqws4puaqd2lhdlw7yp2ckgswuaaizmfi6seto3x4w@5exxg3kazkos>
Archived-At: <https://www.postgresql.org/message-id/aYT95PGdMvHHzwsP%40nathan>
Precedence: bulk

On Thu, Feb 05, 2026 at 03:23:38PM -0500, Andres Freund wrote:
> On 2026-02-05 13:31:23 -0600, Nathan Bossart wrote:
>> On Thu, Feb 05, 2026 at 01:02:17PM -0500, Andres Freund wrote:
>> > Upthread I also wondering why we do all the work in getLOs() if we don't
>> > actually need most of it (only if there are comments or labels).  Right now
>> > that's a very slow and very memory intensive part of doing an upgrade of a
>> > system with a lot of binary upgrades.  Do we need *any* of that if we go the
>> > path you suggest?
>>
>> AFAICT we only need it for the comments and security labels later on.
> 
> And in binary upgrade mode not even for that, if we do the thing we talked
> about re not checking references in binary upgrade mode? Right?

Well, we need _something_ to do what dumpLO() does today, i.e., call
dumpComment() and dumpSecLabel() for each large object that has one or the
other.  I'm sure we could decouple that from the getLOs() machinery if we
tried hard enough, but I'm not seeing any great advantage in doing so if we
teach getLOs() to only get what it needs.

>> In theory, we could update the pg_largeobject_metadata query to only
>> retrieve LOs with comments and security labels.  I'm not sure it's worth
>> trying to optimize further than that; we've long operated under the
>> assumption that comments/seclabels on LOs are pretty rare.
> 
> I think that'd be a huge improvement. Right now it's not hard to get into a
> situation where you have too many LOs to not have enough memory to do a
> pg_upgrade.

It seems to work.  I'm working on a nicer patch for all this stuff.

-- 
nathan