From: Nathan Bossart <nathandbossart@gmail.com>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: improve performance of pg_dump --binary-upgrade
Date: Thu, 18 Apr 2024 10:23:01 -0500
Message-ID: <20240418152301.GB3501884@nathanxps13> (raw)
In-Reply-To: <F579A6BD-C431-4043-B58C-FE5B07FC2977@yesql.se>
References: <20240418041712.GA3441570@nathanxps13>
<F579A6BD-C431-4043-B58C-FE5B07FC2977@yesql.se>
On Thu, Apr 18, 2024 at 09:24:53AM +0200, Daniel Gustafsson wrote:
>> On 18 Apr 2024, at 06:17, Nathan Bossart <nathandbossart@gmail.com> wrote:
>
>> 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.
>
> That does indeed seem like a saner approach. Since we look up the relkind we
> can also remove the is_index parameter to binary_upgrade_set_pg_class_oids
> since we already know that without the caller telling us?
Yeah. It looks like that's been possible since commit 9a974cb, so I can
write a prerequisite patch for this.
>> One downside of this approach is the memory usage.
>
> I'm not too worried about the worst-case performance of this.
Cool. That seems to be the general sentiment.
>> 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.
>
> Without changing the backend handling of the Oid's we can't really do that
> AFAICT, the backend stores the Oid for the next call so it needs to be per
> relation like now?
Right, this would require additional changes.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
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, daniel@yesql.se
Subject: Re: improve performance of pg_dump --binary-upgrade
In-Reply-To: <20240418152301.GB3501884@nathanxps13>
* 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