Re: Speeding up pg_upgrade

Hi,

> Yes, dump/reload of analyze statistics seems like a better use of time.
> I have avoided it since it locks us into supporting the text
> respresentation of data type, but at this point it might be worth it.
>
>
Couple of months ago we at Zalando upgraded a few databases of different
sizes to 9.6.
During preparations to the I've found 2.5 pain-points:

1. We are using schema-based api deployment. Basically ~every week we
create a new schema in the database and hundreds of stored procedures in it.
Off course we remove old API schemas and trying not to keep more than
last 10. Before the upgrade we basically dropped all API schemas except the
one used in production.
And even in this case dump-restore phase was taking much more time than
relinking of datafiles.
Unfortunately I don't have any numbers right now, but usually run of
pg_upgrade was taking about 30-35 seconds, and about 2/3 of the time was
spend in dump-restore.

2 ANALYZE phase is a pain. I think everybody agrees with it.

2.5 Usually ANALYZE stage 1 completes quite fast and performance becomes
reasonable, except one case: some of the columns might have non default
statistics target.
It breaks `vacuumdb --analyze-in-stages`, because those specific
columns it will not use value of default_statistics_target provided by
vacuumdb.
What I did - reset those non default values right before running
pg_upgrade and restored them only when analyze was completed. Off course
after that I've re-analyze those columns.

Regards,
Alexander Kukushkin