As PostgreSQL 18 was released in September 2025, it brought a suite of powerful enhancements—among the most impactful being major upgrades to pg_upgrade, the built-in utility for in-place major version upgrades.
With PostgreSQL 18, pg_upgrade now not only accelerates upgrade operations on databases with large numbers of objects but also preserves crucial optimizer statistics across major version boundaries, significantly reducing post-upgrade performance regressions that previously required manual ANALYZE run.
Preserve optimizer statistics
Until PostgreSQL 17, typically after the pg_upgrade, you had to run vacuumdb or ANALYZE to collect optimizer statistics. Missing this step will lead to degraded query performance.
/usr/pgsql-15/bin/vacuumdb -U postgres --all --analyze-in-stages
With PostgreSQL 18, pg_upgrade retains most optimizer statistics during the upgrade. In case you wish to omit the statistics during upgrade, you can use the flag --no-statistics to achieve it. So, the default behavior of pg_upgrade in PostgreSQL 18 includes optimizer statistics.
However, the extended statistics that are created using CREATE STATISTICS are not included in the pg_upgrade. So, collect the missing statistics using the flag --missing-stats-only.
/usr/pgsql-18/bin/vacuumdb -U postgres --all --analyze-in-stages --missing-stats-only
pg_upgrade option: --swap
In PostgreSQL 18, a new flag --swap was introduced for pg_upgrade. Until version 17, pg_upgrade offered options --clone or --link, apart from the default option --copy.
The --swap option moves the data directories from the old cluster to the new cluster and then replaces catalog files with those generated for the new cluster. This mode can outperform --link, --clone, --copy, and --copy-file-range, especially on clusters with many relations.
It is recommended to use --sync-method=fsync with this mode, to avoid generation of garbage files in the old cluster during file synchronization.
The --swap option is fast and effective only if the old and new clusters data files are in the same file system. If the source cluster and target cluster are in different file systems, then benefits of --swap is nil compared to --copy option.
The --swap option of PostgreSQL 18 outperforms --link option when the cluster contains many relations.
The supported version for upgrade with --swap is from PostgreSQL 10 onwards. This limitation is due to visibility map format and sequence tuple format changed in version 10.
Once the upgrade is complete, the old cluster becomes unusable, as the database files are moved to the upgraded cluster.
Upgrade checks with --jobs
Until version 17, we perform upgrade consistency checks with single thread. However, with PostgreSQL 18, the pre-upgrade checks can be run with --jobs flag to run them in parallel. Based on the CPU cores available, the pre-checks can be completed faster. This is ideal for clusters with multiple databases.
pg_upgrade --set-char-signedness
PostgreSQL 18 introduced a new flag named --set-char-signedness. This flag allows us to manually specify the character signedness for the new cluster, overriding any inherited values.
This option can take values either signed or unsigned. The default signedness of the char type in C language varies based on platforms like it is signed for x86-based systems and unsigned for ARM-based systems.
This flag is relevant in a specific cross-platform migration scenario, where you have already migrated the cluster to different character signedness (from x86 system to ARM system), you should use the flag to specify the signedness.
On PostgreSQL 18, you can check the default char signedness value, as below:
$ pg_controldata -D /database/fep18/ | grep signedness
Default char data signedness: signed
The enhancements to pg_upgrade in PostgreSQL 18 significantly reduce upgrade effort and risk, making major version transitions faster, more reliable, and easier than ever.
