Skip to content
Permalink

Comparing changes

Choose two branches to see what’s changed or to start a new pull request. If you need to, you can also or learn more about diff comparisons.

Open a pull request

Create a new pull request by comparing changes across two branches. If you need to, you can also . Learn more about diff comparisons here.
base repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected base ref is valid, then try again.
Loading
base: cf/5737~1
Choose a base ref
...
head repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected head ref is valid, then try again.
Loading
compare: cf/5737
Choose a head ref
  • 2 commits
  • 3 files changed
  • 2 contributors

Commits on Jul 14, 2025

  1. pg_upgrade: Use COPY for large object metadata. 

    Presently, pg_dump generates commands like

    SELECT pg_catalog.lo_create('5432');
    ALTER LARGE OBJECT 5432 OWNER TO alice;
    GRANT SELECT ON LARGE OBJECT 5432 TO bob;

for each large object.  This is particularly slow at restore time,
especially when there are tens or hundreds of millions of large
objects.  From reports and personal experience, such slow restores
are primarily encountered at pg_upgrade time.  This commit teaches
pg_dump to instead dump pg_largeobject_metadata and the
corresponding pg_shdepend rows when in binary upgrade mode, i.e.,
pg_dump now generates commands like

    COPY pg_catalog.pg_largeobject_metadata (oid, lomowner, lomacl) FROM stdin;
    5432	16384	{alice=rw/alice,bob=r/alice}
    \.

    COPY pg_catalog.pg_shdepend (dbid, classid, objid, objsubid, refclassid, refobjid, deptype) FROM stdin;
    5	2613	5432	0	1260	16384	o
    5	2613	5432	0	1260	16385	a
    \.

Testing indicates the COPY approach can be significantly faster.
To do any better, we'd probably need to find a way to copy/link
pg_largeobject_metadata's files during pg_upgrade, which would be
complicated and limited to upgrades from >= v16 (since commit
7b37823 changed the storage format for aclitem, which is used
for pg_largeobject_metadata.lomacl).

Note that this change only applies to binary upgrade mode (i.e.,
dumps initiated by pg_upgrade) since it inserts rows directly into
catalogs, bypassing the ordinary privilege checks.  Also, this
optimization can only be used for upgrades from >= v12 because
pg_largeobject_metadata was created WITH OIDS in older versions,
which prevents pg_dump from handling pg_largeobject_metadata.oid
properly.  With some extra effort, it might be possible to support
upgrades from older versions, but the added complexity didn't seem
worth it to support versions that will have been out-of-support for
nearly 3 years by the time this change is released.

The astute hacker may remember that prior to v12, pg_upgrade
copied/linked pg_largeobject_metadata's files (see commit
12a53c7).  Besides the aforementioned storage format issues,
this approach failed to transfer the relevant pg_shdepend rows, and
pg_dump still had to generate an lo_create() command per large
object so that creating the dependent comments and security labels
worked.  We could perhaps adopt a hybrid approach for upgrades from
v16 and newer (i.e., generate lo_create() commands for each large
object, copy/link pg_largeobject_metadata's files, and COPY the
relevant pg_shdepend rows), but further testing is needed.

Reported-by: Hannu Krosing <[email protected]>
Suggested-by: Tom Lane <[email protected]>
Reviewed-by: Hannu Krosing <[email protected]>
Reviewed-by: Nitin Motiani <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
Discussion: https://postgr.es/m/CAMT0RQSS-6qLH%2BzYsOeUbAYhop3wmQTkNmQpo5--QRDUR%2BqYmQ%40mail.gmail.com
    @nathan-bossart
    nathan-bossart authored and Commitfest Bot committed Jul 14, 2025
    Configuration menu
    Copy the full SHA
    926d067 View commit details
    Browse the repository at this point in the history

  2. [CF 5737] v5 - speed up pg_upgrade with many large objects 

    This branch was automatically generated by a robot using patches from an
email thread registered at:

https://commitfest.postgresql.org/patch/5737

The branch will be overwritten each time a new patch version is posted to
the thread, and also periodically to check for bitrot caused by changes
on the master branch.

Patch(es): https://www.postgresql.org/message-id/aHVDmAqcwanTAGZa@nathan
Author(s): Nathan Bossart
    Commitfest Bot committed Jul 14, 2025
    Configuration menu
    Copy the full SHA
    7f2e554 View commit details
    Browse the repository at this point in the history
Loading