Re: Horribly slow pg_upgrade performance with many Large Objects

Hi Nathan,

Did a quick check of the patch and it seems to work ok.

What do you think of the idea of not dumping pg_shdepend here, but
instead adding the required entries after loading
pg_largeobject_metadata based on the contents of it ?

The query for this would be

WITH db AS (
SELECT oid FROM pg_database WHERE datname = current_database()
)
INSERT INTO pg_shdepend
SELECT db.oid AS dbid,
2613 AS classid,
lm.oid AS objid,
0 AS objsubid,
1260 AS refclassid,
COALESCE(acl.grantee, lm.lomowner) AS refobjid,
CASE WHEN grantee IS NULL or grantee = lomowner
THEN 'o'
ELSE 'a'
END as deptype
FROM pg_largeobject_metadata as lm
LEFT JOIN LATERAL (
SELECT DISTINCT (aclexplode(lm.lomacl)).grantee
) AS acl ON true,
db
WHERE (lm.oid, COALESCE(acl.grantee, lm.lomowner)) NOT IN (SELECT
objid, refobjid FROM pg_shdepend WHERE dbid = db.oid)
;

(I had hoped to use ON CONFLICT DO NOTHING but this is not supported
for system tables.)

---|
Hannu

On Wed, May 7, 2025 at 4:51 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> 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