From: Nathan Bossart <nathandbossart@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nitin Motiani <nitinmotiani@google.com>
Cc: Hannu Krosing <hannuk@google.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
Date: Thu, 5 Feb 2026 14:30:28 -0600
Message-ID: <aYT95PGdMvHHzwsP@nathan> (raw)
In-Reply-To: <t3zu4hyzwqws4puaqd2lhdlw7yp2ckgswuaaizmfi6seto3x4w@5exxg3kazkos>
References: <aL8sgVdZcuqZnvXn@nathan>
<3yd2ss6n7xywo6pmhd7jjh3bqwgvx35bflzgv3ag4cnzfkik7m@hiyadppqxx6w>
<sc2uulu7gaajlnis7vqx25hmn6yd74drnlbzvupskx7jwyzejb@3vwodvuurtqb>
<aYNuhbXkM5h8Zcsu@nathan>
<aYOnT5EqAqI3ooBO@nathan>
<qfp7z4c3m6bw53oky33halcdhomiijod6segzrhf757ijjvapr@e5rf4u5wnfmu>
<aYTVAFTPelebVl6W@nathan>
<nt56moqkh6yoerzw5cml6qu7m4mxu65vqtgdt3nbm5mhpg5hqt@4s5oa3sqhzub>
<aYTwC8V50rPgw6GE@nathan>
<t3zu4hyzwqws4puaqd2lhdlw7yp2ckgswuaaizmfi6seto3x4w@5exxg3kazkos>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, andres@anarazel.de, michael@paquier.xyz, tgl@sss.pgh.pa.us, nitinmotiani@google.com, hannuk@google.com, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
In-Reply-To: <aYT95PGdMvHHzwsP@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox