Thread: pg_upgrade performance with 150k tables
I received a private email report yesterday from someone using pg_upgrade with PG 9.0 who found it took five hours for pg_upgrade to upgrade a database with 150k tables. Yes, that is a lot of tables, but pg_upgrade should be able to do better than that. I have modified pg_upgrade in git master to cache scandir() and reduce array lookups and the time is down to 38 minutes. (He prototyped a hash implementation that was 30 minutes but it was too much code for my taste.) I don't think this is reasonable to backpatch. If anyone else sees cases for pg_upgrade improvement, please let me know. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > I received a private email report yesterday from someone using > pg_upgrade with PG 9.0 who found it took five hours for pg_upgrade to > upgrade a database with 150k tables. Yes, that is a lot of tables, but > pg_upgrade should be able to do better than that. > > I have modified pg_upgrade in git master to cache scandir() and reduce > array lookups and the time is down to 38 minutes. (He prototyped a hash > implementation that was 30 minutes but it was too much code for my > taste.) > > I don't think this is reasonable to backpatch. If anyone else sees > cases for pg_upgrade improvement, please let me know. One more question --- should I be sending pg_upgrade patches to the list for approval? The restructuring patch was large and didn't seem necessary to post, and the speedups were tested by the bug reporter, so I figured those were OK to apply. Oh, and do we want to move pg_upgrade into /bin for 9.1? There was discussion about that six months ago. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Wed, Oct 20, 2010 at 3:01 PM, Bruce Momjian <bruce@momjian.us> wrote: > One more question --- should I be sending pg_upgrade patches to the list > for approval? The restructuring patch was large and didn't seem > necessary to post, and the speedups were tested by the bug reporter, so > I figured those were OK to apply. I think it would be good to do that. At least give people a chance to comment, if they care. > Oh, and do we want to move pg_upgrade into /bin for 9.1? There was > discussion about that six months ago. I would be inclined to leave it in contrib for a few more releases. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Oct 20, 2010 at 21:28, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Oct 20, 2010 at 3:01 PM, Bruce Momjian <bruce@momjian.us> wrote: >> One more question --- should I be sending pg_upgrade patches to the list >> for approval? The restructuring patch was large and didn't seem >> necessary to post, and the speedups were tested by the bug reporter, so >> I figured those were OK to apply. > > I think it would be good to do that. At least give people a chance to > comment, if they care. +1. It's also a good way for people to get a bit more involved in the code. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander wrote: > On Wed, Oct 20, 2010 at 21:28, Robert Haas <robertmhaas@gmail.com> wrote: > > On Wed, Oct 20, 2010 at 3:01 PM, Bruce Momjian <bruce@momjian.us> wrote: > >> One more question --- should I be sending pg_upgrade patches to the list > >> for approval? ?The restructuring patch was large and didn't seem > >> necessary to post, and the speedups were tested by the bug reporter, so > >> I figured those were OK to apply. > > > > I think it would be good to do that. ?At least give people a chance to > > comment, if they care. > > +1. It's also a good way for people to get a bit more involved in the code. OK. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +