Blog

There is always time to raise issues that are often forgotten. Therefore, in today’s article we will talk about upgrading versions of database systems like PostgreSQL.

Many people decide even before the start of a new year that they will change their lives by setting New Year’s resolutions. So we want to help with keeping some resolutions, and for those who are procrastinating upgrading their database to newer versions, we have prepared this article. There will be a little bit about whether and when it’s worth doing a PostgreSQL database upgrade, but also about how a given upgrade affects the average performance of a database system. We invite you to read on.

Whether and when it’s worth doing a PostgreSQL-like database upgrade

An upgrade should be a standard activity of every database administrator. It is recommended if you want to take advantage of new functionality or improve database performance. In addition, new versions of a database often include bug fixes and enhancements that can improve the performance of the entire database. It is also worth remembering that with an upgrade we extend the support and security of the databases, and thus reduce the possible risks associated with the disclosure of confidential data in the database.

However, we know that reality is often not so simple, and the dependencies that exist between components often limit the possibilities of making changes. Then you need to answer some questions: is the database working properly? Does it meet all needs – including business needs? Are the newly introduced functions relevant to our database? After all, it may turn out that we do not need a database upgrade at the moment. Of course, we must remember that we cannot prolong this process indefinitely. Someday support for our version of the database will expire, and each subsequent upgrade will be even more complicated and time-consuming.

How to upgrade properly?

If you have already decided to upgrade your PostgreSQL-like database, be sure to make a backup. This can be done with the command:

pg_dump -U [username] -Fc [database_name] > [dumpfile.backup]

In case of any irregularities, this will allow us to restore the data to its previous state.

The most important components when upgrading a PostgreSQL-like database are the pg_dump and pg_restore tools provided by Postgres. In this article, we will focus on the EuroDB database and perform an upgrade from version 13 to 14. The instructions should be universal for any database based on the Postgres engine.

Upgrade – howto

The first step to performing an upgrade is to install a new version of the EuroDB database on a test server. It is also worth noting that the flags used when executing the initdb command are the same in both versions of EuroDB.

In addition to just installing EuroDB, you will need to create a database with the same name as in the old version. To avoid problems with the integrity of the backup data, be sure to install any extensions and modules present in the previous version. You should also copy custom dictionaries, synonyms and other similar files from the old cluster. All of this is crucial to the successful completion of the upgrade operation.

The next step will be to move the previously created backup to a server with a newer version of EuroDB installed and run the `pg_restore` utility to restore the data from backup:

pg_restore -U [username] -d [database_name] [dumpfile.backup]

Once the process of restoring the database from a file is successful, you should test the database. To do this, it is a good idea to start the EuroDB server and perform a series of automated regression tests (which we will write about in the next article). This is the best way to find potential problems in a freshly updated database.

If the operation of the new server with the EuroDB 14 database is correct, update the links that pointed to the production server in the database. It is also a good practice to freeze the server with the older version of EuroDB for a while in case of a possible failure.

Database upgrade and its performance

The idea behind creating a newer version of a database is usually to have it improved and faster. This is exactly what happens with each new version of PostgreSQL and the solutions based on it. Already in version 14 of the PostgreSQL database engine itself, a significant increase in the performance of scaling active and passive connections to the database has been noticed. Performance tests were conducted on PostgreSQL 13 (EuroDB 13) and PostgreSQL 14 (EuroDB 14). The tests consisted of comparing the scaling of database connections depending on the version of the PostgreSQL engine. Both instances were run on the same operating system and machines with the same parameters. It is worth mentioning that in the testing process the limit of database connections was increased to 11 thousands.

As you can see, the throughput of EuroDB 14 at 5,000 active connections is about 20% higher than in the earlier version. On the other hand, with 10,000 active connections to the database, the improvement is within 50% compared to EuroDB 13. A noticeable decrease in TSP (transactions per second) occurs when the number of connections exceeds the number of cores (in this experiment there were 96 of each). This is most likely due to excess CPU scheduling, rather than a limitation of Postgres itself.

Of course, each successive version of PostgreSQL focuses on improving the performance of a few specific features, rather than all at once. The developers of PostgreSQL version 15 leaned this time on improving performance when sorting in memory and on disk. Relative to the previous version, the acceleration is noticeable at 25% to as much as 400%, depending on the type of data being sorted.

Summary

A database upgrade is the process of moving from an old version of a database management system to a newer one. It carries with it many positive aspects, such as increased performance, the addition of security patches, as well as the expansion of available features. With this in mind, upgrading your database to the latest version is worthwhile, even if the upgrade process itself seems time-consuming at first.