postgresqlpostgresql-18

Retaining statistics when upgrading postgres 16 to 18

pg_upgrade 18 docs state that

pg_upgrade will transfer most optimizer statistics from the old cluster to the new cluster

The docs also state that

pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL

Does that mean that statistics are retained when upgrading from postgres 16 to 18? Or does that only work for 17 to 18? Does that even work for 9.2 to 18?

Solution

  • Yes, pg_upgrade will retain table statistics from v18 on, and that should work down to 9.2. I just tested a simple upgrade from v16 to v18 and didn't detect a problem.

    Note that extended statistics won't be transferred, so pg_upgrade will advise you to run

    /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only

    Here is what the documentation has to say:

    1. Statistics

    Unless the --no-statistics option is specified, pg_upgrade will transfer most optimizer statistics from the old cluster to the new cluster. However, some statistics may not be transferred, such as those created explicitly with CREATE STATISTICS or custom statistics added by an extension.

    Because not all statistics are transferred by pg_upgrade, you will be instructed to run commands to regenerate that information at the end of the upgrade. You might need to set connection parameters to match your new cluster.

    First, use vacuumdb --all --analyze-in-stages --missing-stats-only to quickly generate minimal optimizer statistics for relations without any. Then, use vacuumdb --all --analyze-only to ensure all relations have updated cumulative statistics for triggering vacuum and analyze. For both commands, the use of --jobs can speed it up. If vacuum_cost_delay is set to a non-zero value, this can be overridden to speed up statistics generation using PGOPTIONS, e.g., PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ....