Horton

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

Corey Huinker <corey.huinker@gmail.com>

View thread View on postgr.es
From: Corey Huinker <corey.huinker@gmail.com>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Ilya Gladyshev <ilya.v.gladyshev@gmail.com>, Daniel Gustafsson <daniel@yesql.se>, Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-08-09T20:06:16Z
Lists: pgsql-hackers 
>
> I'll admit I hadn't really considered pipelining, but I'm tempted to say
> that it's probably not worth the complexity.  Not only do most of the tasks
> have only one step, but even tasks like the data types check are unlikely
> to require more than a few queries for upgrades from supported versions.
>

Can you point me to a complex multi-step task that you think wouldn't work
for pipelining? My skimming of the other patches all seemed to be one query
with one result set to be processed by one callback.

Furthermore, most of the callbacks should do almost nothing for a given
> upgrade, and since pg_upgrade runs on the server, client/server round-trip
> time should be pretty low.
>

To my mind, that makes pipelining make more sense, you throw out N queries,
most of which are trivial, and by the time you cycle back around and start
digesting result sets via callbacks, more of the queries have finished
because they were waiting on the query ahead of them in the pipeline, not
waiting on a callback to finish consuming its assigned result set and then
launching the next task query.


>
> Perhaps pipelining would make more sense if we consolidated the tasks a bit
> better, but when I last looked into that, I didn't see a ton of great
> opportunities that would help anything except for upgrades from really old
> versions.  Even then, I'm not sure if pipelining is worth it.
>

I think you'd want to do the opposite of consolidating the tasks. If
anything, you'd want to break them down in known single-query operations,
and if the callback function for one of them happens to queue up a
subsequent query (with subsequent callback) then so be it.

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