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: Corey Huinker <corey.huinker@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-09T14:43:59Z
Lists: pgsql-hackers 
On Thu, Aug 08, 2024 at 06:18:38PM -0400, Corey Huinker wrote:
> I think the underlying mechanism is basically solid, but I have one
> question: isn't this the ideal case for using libpq pipelining? That would
> allow subsequent tasks to launch while the main loop slowly gets around to
> clearing off completed tasks on some other connection.

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.
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.

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.

-- 
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