Re: Speeding up pg_upgrade

Mark,

* Mark Dilger (hornschnorter@gmail.com) wrote:
> > On Dec 7, 2017, at 10:24 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > I think the big problem with two-stage pg_upgrade is that the user steps
> > are more complex, so what percentage of users are going use the
> > two-stage method.  The bad news is that only a small percentage of users
> > who will benefit from it will use it, and some who will not benefit it
> > will use it.  Also, this is going to require significant server changes,
> > which have to be maintained.
>
> In my fork of the project, back when I was tracking 9.5, I added an option
> to vacuum/analyze to make it behave a bit more like autovac, so that I could
> run
>
>     ANALYZE CONDITIONALLY;
>
> and it would only analyze those tables in the system which autovac would
> analyze.  In the grammar, CONDITIONALLY gets translated into a
> VacuumOption flag.  In vacuum (in src/backend/commands/vacuum.c), inside
> the "Loop to process each selected relation", if this flag is set, it checks the
> PgStat_StatTabEntry for the table to determine whether to vacuum or analyze
> the table.
>
> I think this extension would be helpful in the context of the current conversation.
> In those cases where pg_upgrade was able to migrate the statistics to the
> new database, as long as it set the PgStat_StatTabEntry for each table where
> statistics were migrated, then the user would just have to execute a
> "VACUUM CONDITIONALLY" after upgrade, and the database would either
> do a lot of analyze work, a little analyze work, or no analyze work depending
> on which tables needed analyzing.
>
> The main advantage here is that the user would always run this command
> after pg_upgrade, without having to think about whether pg_upgrade had
> migrated statistics or not.
>
> If the community thinks this is useful, I could put together a patch.

This certainly sounds nice though I have to admit to being a bit
skeptical on the keyword selection, but perhaps blue really is the right
color for that bike shed.

One thing I'd wonder about is if that makes 'CONDITIONALLY' into a
reserved keyword, which wouldn't be ideal.  Perhaps a bit of a stretch
but 'ANALYZE ALL NEEDED' might avoid that?

Thanks!

Stephen