You may consider a PostgreSQL Major upgrade to use new PostgreSQL features or because of EOL. There are several ways to do a PostgreSQL Major upgrade, and you need to verify thoroughly which method to use and how to keep Database downtime to a minimum. This post covers the performance of the PostgreSQL Major upgrade methods introduced earlier. It also includes preparation and cautions, but does not cover Vacuum.
📢 For PostgreSQL Major upgrade methods, see [PostgreSQL Setup - Major Upgrade].
We walk through an upgrade from PostgreSQL 9.6.3 to PostgreSQL 12.12 on the server below.
- AWS EC2 (c5.2xlarge)
- CPU : 8core / RAM : 16GB
- Storage : EBS(gp3)
- Data Set
ㅤ | Total Size | Table Count | Table Size (Each) | Table Rows (Each) | Column List |
Set 1. | 25 GB | 100 | 200 MB | 1,000,000 Rows | Integer
Char(100)
Char(100) |
Set 2. | 100 GB | 400 | 200 MB | 1,000,000 Rows | Integer
Char(100)
Char(100) |
25GB Database
100GB Database
Upgrade cautions
Before checking performance by application, let's go over the upgrade cautions.
When upgrading individual Databases with pg_dump, you need to select and prepare the metadata to move with them, such as Roles, Tablespaces, and Extensions.
On the other hand, when upgrading with pg_dumpall or pg_upgrade, you need to pay attention to the versions of the Extensions in use.
Role & Tablespace
If you upgrade with the pg_dump application, Global Objects such as Roles and Tablespaces must be migrated separately. pg_dump cannot Back up Global Objects, so use the pg_dumpall application to create a Backup File.
[AS-IS] Check Role and Tablespace information
[AS-IS] Role & Tablespace Backup
[TO-BE] Role & Tablespace Restore
Check Tablespace creation
📢 Tablespace Naming → PG_{Major-Version}_{Catalog-Version}
Extension
Extensions shipped with PostgreSQL (mainly postgresql-contrib) are installed automatically on Restore. After Restore, you must Update them to the Extension version that matches the PostgreSQL version. Extensions not shipped with PostgreSQL (for example, from Github) must be newly installed in the version that matches the PostgreSQL version, from wherever the Extension is provided. (CREATE EXTENSION)
Extensions shipped with PostgreSQL
Check Extensions
Extension Update
Upgrade performance
What the various PostgreSQL upgrade methods have in common is Backup and Restore. You Back up with the pg_dump and pg_dumpall applications provided by PostgreSQL and Restore with the psql and pg_restore applications. pg_upgrade uses the Backup&Restore applications for Global Objects and per-Database Schema structures, and Copy&Pastes the Data Files with an OS Copy.
In other words, apart from the pg_upgrade application, upgrades are done through Backup and Restore, and there are two typical combinations: ① Back up all Databases with pg_dumpall → Restore with psql, and ② Back up individual Databases with pg_dump → Restore with psql or pg_restore.
In this section, we compare the performance of Backup and Restore, which have the biggest impact on upgrade time, based on elapsed time.
Backup performance
PostgreSQL Backup can be done with the pg_dump and pg_dumpall applications.
① pg_dump
② pg_dump + Directory Foramt + Parallel(4)
③ pg_dumpall
The chart below shows the measured Backup time for the three methods above.
- The difference in run time between applications is negligible.
- Parameter changes make no difference to Backup performance.
- Parallel execution has a negligible effect on Backup performance.
As a result, Backup performance does not differ much by application, so choose the Backup method based on the Restore method.
📢 Note - Dump File Size
25GB Database → about 20GB File created
100GB Database → about 78GB File created
Restore performance
PostgreSQL Restore can be done with the psql and pg_restore applications. The Backup method must match the Restore method, so consider the option that gives the best efficiency.
① psql
② pg_restore + Directory Format Restore + Parallel(4)
③ pg_restore + Table Unlogged Option + Directory Format Restore + Parallel(4)
The chart below shows the measured Restore time for the three methods above.
📢 Restoring a File Backed up with pg_dumpall works the same way as restoring a File Backed up with pg_dump. It contains somewhat more information than a pg_dump Backup File, but Restore performance is about the same, so this post does not cover pg_dumpall Restore performance.
Unlike Backup, Restore varies with Database size, but tuning parameters gives a speedup of about 15% or more in all three cases. The parameters tuned in the test are below.
- The
wal_levelparameter has the biggest impact on Restore performance.
- Setting
wal_level = minimalminimizes WAL generation and improves Restore performance.
- Using the
Table Unlogged Optionnoticeably improves Restore performance, but once you count the step of changing Tables back to Logged after Restore, there is no real performance gain. Still, it is slightly faster than Restore through psql.
- Looking at Restore as a whole, the
Parallel Restoremethod is the best.
※ Factors that affect Restore performance
wal_level Parameter
wal_level determines how much information is written to the WAL (Write-Ahead Log). Possible values are
minimal, replica (archive, hot_standby), and logical. The default was minimal up to PostgreSQL 9.6, and changed to replica from PostgreSQL 10.wal_level | Description |
minimal | - Records only the information needed to recover from a System Crash or abnormal shutdown
- CREATE TABLE AS , CREATE INDEX , CLUSTER , COPY operations can run fast
- Records only minimal information, so Replication cannot be configured |
replica | - Includes minimal information
- Records the information needed to run Read-Only Queries on a Standby Server and the information needed for WAL Archiving
- Includes archive and hot_standby, which were used up to PostgreSQL 9.5 |
logical | - Includes replica information
- Records the information needed to support Logical Decoding |
Table Unlogged Option
The Table Unlogged option skips WAL and can speed up INSERTs. However, the Table is TRUNCATEd after a System Crash or abnormal shutdown, so you must change it to the Logged option after Restore. As the chart above shows, it gives the fastest Restore, but changing Tables to the Logged option after Restore to prevent Data loss takes considerable time.
Parallel Restore
If running Tables with the Unlogged option is acceptable, the Restore method above is the best. If not, the best method is to Restore Files Backed up in Directory Format with the Parallel option. As many processes as specified by Parallel are created to process the Backup Files generated in Directory Format, which is more efficient than Restoring with a single process.
📢 A Directory Format Backup creates one Backup File per Table. For example, with 100 Tables, 100 Backup Files + 1 (management file) are created.
pg_upgrade performance
Besides upgrading with the Backup & Restore methods above, the chart below shows upgrade performance with the pg_upgrade application.
The pg_upgrade application can be used in two ways, depending on whether the Link option is used.
pg_upgrade without the Link option must Back up & Restore Global Objects and Schema structures through pg_dumpall or pg_dump. The actual Data Files are then moved with OS commands. Restoring Global Objects and per-Database Schema structures is a very small job compared to Restoring Table Data (usually 5-10 seconds). The Backup File is also very small, so tuning parameters brings no big benefit.
pg_upgrade with the Link option uses the new Database's (TO-BE) Data Directory for Data Directories whose structure changed with the Major version, such as pg_wal, and links unchanged Data Directories to the existing Database's (AS-IS) Data Directory with Hard Links.
📢 The WAL Directory, named pg_xlog in PostgreSQL 9.6, was renamed pg_wal from PostgreSQL 10.
Upgrading with the pg_upgrade application uses somewhat less Disk space than other methods and is also faster. With the Link option in particular, the Disk space and speed results are dramatic. However, if the upgrade fails due to unexpected errors such as Extension compatibility issues or Tablespace location problems, it can take longer than the Backup&Restore method, so thorough planning and testing are needed.
Required free Disk space
Free Disk space is needed depending on the application used for the upgrade. The chart below shows the free Disk space needed for the upgrade, excluding the Disk Size used by the original (AS-IS) Database. It does not include the Disk Size of the PostgreSQL Engine.
- With pg_dump or pg_dumpall, space is needed for the Backup File: up to about the AS-IS Data File Size in Disk space.
- With pg_dump or pg_dumpall, upgrading to the TO-BE Database needs up to about the AS-IS Data File Size in Disk space.
- With the pg_upgrade application without the Link option, about the AS-IS Data File Size in Disk space is needed.
- With the pg_upgrade application with the Link option, the AS-IS Data Files are connected through Hard Links, so no additional Disk space is needed.
Summary
The chart below summarizes the Backup, Restore, and pg_upgrade results above. (Only results with tuned parameters are shown.)
Elapsed time refers only to the time for the upgrade itself. It does not include VACUUM time after the upgrade, including cleaning up free space and collecting statistics.
Test Commands
Case 1. pg_dump + psql
Case 2. pg_dump Parallel(4) + pg_restore Parallel(4)
Case 3. pg_dump Parallel(4) + pg_restore Parallel(4) + Table Unlogged
Case 4. pg_upgrade
Case 5. pg_upgrade Link Option
- For Backup, parameter tuning and Parallel work do not improve performance.
- For Restore, Parallel work brings a performance benefit.
- For Restore, the Table Unlogged option allows fast processing, but changing back to the Logged option takes a long time.
- The pg_upgrade application is faster than other upgrade methods. However, if the upgrade fails due to Extension compatibility issues, Tablespace location problems, and so on, it can take longer.
- After all upgrade work is complete, run VACUUM.
- Free Disk space is needed depending on the application used.
Data verification
After a PostgreSQL Upgrade or Migration, Data must be verified between AS-IS and TO-BE. Typically, the items below need verification, and you can check them with the scripts described next.
- Check Object counts
- Check Procedure counts
- Check Trigger counts
- Check Sequence Last Value
Check Object counts
You can check the number of Objects per Schema in a PostgreSQL Database. If there are multiple Databases, check each Database.
Check Procedure counts
The Procedure Type was introduced in PostgreSQL 11. Choose and run the script below that matches your PostgreSQL version.
📢 Up to version 10, the pg_catalog.pg_proc View distinguishes each Type with TRUE/FALSE in per-Type columns.
From version 11, a prokind column was added to the pg_catalog.pg_proc View, showing each Type as an abbreviation.
(f : Normal Function , p : Procedure , a : Aggregate Function , w : Window Function)
PostgreSQL 10 and earlier
PostgreSQL 11 and later
Check Trigger counts
Check Sequence Last Value
The pg_catalog.pg_sequences Dictionary View was introduced in PostgreSQL 10, making it easy to check a Sequence's Last Value. Choose and run the script below that matches your PostgreSQL version.
PostgreSQL 9.6 and earlier
PostgreSQL 10 and later
Related articles
