From: | Nathan Bossart <nathandbossart(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | improve performance of pg_dump --binary-upgrade |
Date: | 2024-04-18 04:17:12 |
Message-ID: | 20240418041712.GA3441570@nathanxps13 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
While examining pg_upgrade on a cluster with many tables (created with the
command in [0]), I noticed that a huge amount of pg_dump time goes towards
the binary_upgrade_set_pg_class_oids() function. This function executes a
rather expensive query for a single row, and this function appears to be
called for most of the rows in pg_class.
The attached work-in-progress patch speeds up 'pg_dump --binary-upgrade'
for this case. Instead of executing the query in every call to the
function, we can execute it once during the first call and store all the
required information in a sorted array that we can bsearch() in future
calls. For the aformentioned test, pg_dump on my machine goes from ~2
minutes to ~18 seconds, which is much closer to the ~14 seconds it takes
without --binary-upgrade.
One downside of this approach is the memory usage. This was more-or-less
the first approach that crossed my mind, so I wouldn't be surprised if
there's a better way. I tried to keep the pg_dump output the same, but if
that isn't important, maybe we could dump all the pg_class OIDs at once
instead of calling binary_upgrade_set_pg_class_oids() for each one.
[0] https://postgr.es/m/3612876.1689443232%40sss.pgh.pa.us
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Improve-performance-of-pg_dump-binary-upgrade.patch | text/x-diff | 7.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrei Lepikhov | 2024-04-18 05:01:13 | Re: POC: GROUP BY optimization |
Previous Message | Tom Lane | 2024-04-18 03:10:53 | Re: Speed up clean meson builds by ~25% |