Horton

Re: optimizing pg_upgrade's once-in-each-database steps

Nathan Bossart <nathandbossart@gmail.com>

View thread View on postgr.es
From: Nathan Bossart <nathandbossart@gmail.com>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-07-19T21:21:37Z
Lists: pgsql-hackers 
On Thu, Jul 18, 2024 at 09:57:23AM +0200, Daniel Gustafsson wrote:
>> On 17 Jul 2024, at 23:32, Nathan Bossart <nathandbossart@gmail.com> wrote:
>> On Wed, Jul 17, 2024 at 11:16:59PM +0200, Daniel Gustafsson wrote:
> 
>>> +static void
>>> +dispatch_query(const ClusterInfo *cluster, AsyncSlot *slot,
>>>    ....
>>> +   pg_free(query);
>>> +}
>>> 
>>> A minor point, perhaps fueled by me not having played around much with this
>>> patchset.  It seems a bit odd that dispatch_query is responsible for freeing
>>> the query from the get_query callback.  I would have expected the output from
>>> AsyncTaskGetQueryCB to be stored in AsyncTask and released by async_task_free.
>> 
>> I don't see any problem with doing it the way you suggest.

Actually, I do see a problem.  If we do it this way, we'll have to store a
string per database somewhere, which seems unnecessary.

However, while looking into this, I noticed that only one get_query
callback (get_db_subscription_count()) actually customizes the generated
query using information in the provided DbInfo.  AFAICT we can do this
particular step without running a query in each database, as I mentioned
elsewhere [0].  That should speed things up a bit and allow us to simplify
the AsyncTask code.

With that, if we are willing to assume that a given get_query callback will
generate the same string for all databases (and I think we should), we can
run the callback once and save the string in the step for dispatch_query()
to use.  This would look more like what you suggested in the quoted text.

[0] https://postgr.es/m/ZprQJv_TxccN3tkr%40nathan

-- 
nathan

Commits

  1. Introduce framework for parallelizing various pg_upgrade tasks.

  2. pg_upgrade: Parallelize WITH OIDS check.

  3. pg_upgrade: Parallelize contrib/isn check.

  4. pg_upgrade: Parallelize data type checks.

  5. pg_upgrade: Parallelize encoding conversion check.

  6. pg_upgrade: Parallelize incompatible polymorphics check.

  7. pg_upgrade: Parallelize postfix operator check.

  8. pg_upgrade: Parallelize retrieving extension updates.

  9. pg_upgrade: Parallelize retrieving loadable libraries.

  10. pg_upgrade: Parallelize retrieving relation information.

  11. pg_upgrade: Parallelize subscription check.

  12. pg_upgrade: Move live_check variable to user_opts.

  13. pg_upgrade: run all data type checks per connection