improve performance of pg_dump --binary-upgrade
Nathan Bossart <nathandbossart@gmail.com>
From: Nathan Bossart <nathandbossart@gmail.com>
To: pgsql-hackers@postgresql.org
Date: 2024-04-18T04:17:12Z
Lists: pgsql-hackers
Attachments
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
Commits
-
Improve performance of binary_upgrade_set_pg_class_oids().
- 2329cad1b93f 18.0 landed
-
Remove is_index parameter from binary_upgrade_set_pg_class_oids().
- 6e1c4a03a978 18.0 landed
-
pg_upgrade: Preserve relfilenodes and tablespace OIDs.
- 9a974cbcba00 15.0 cited
-
pg_dump: minor performance improvements from eliminating sub-SELECTs.
- d5e8930f50e3 15.0 cited