Is it Time to Upgrade your Postgres Database?

In this era of agile development, organizations are always looking to get new features into their product to be a step ahead of the competition as quickly as possible. A lot of emphasis is put on the design and development of the application layer, look, and feel of the UI—often ignoring the underlying framework that either stores the Data or provisions it. The reason for this often is quite obvious. 

To help understand why, let’s shift our focus to the automotive industry. We are used to seeing hundreds of new models of cars being released every year, but you would be surprised to find how many of them share the same engine and have used them for years. This is mainly done to reduce the cost of development and time to get the vehicle to the market. Cosmetic changes like body panels, ergonomics, electronics, and changes to ECU can be done quickly as compared to developing or adopting a new engine, as it often involves the redesign of the suspension, transmission, and a large number of other crucial parts.

This is quite close to what happens in the Software industry as well. Changing the database often is the most expensive exercise as it comes with extensive testing of existing applications and redeveloping sections of it that no longer work. 
Now that we understand that, as DBAs, we need to be very clear when making a case to upgrade the Database. As members of the Database Support team at EDB, we have had a chance to interact with a lot of clients who have the same question, ‘Why should we upgrade our Postgres database?’    

In this blog, we are trying to put forth our reasoning for how you could arrive at this answer.

In all the discussions which we have been a part of ‘COST’ of re-certifying, the existing application is in the top 3. We like to advise the user to look at the benefits of their PostgreSQL version upgrade and then calculate what it will cost them if they choose not to.  

So, let’s start with the benefits.

Why Upgrade Postgres?
 

1) Community support and fixes

The best part of embracing an open source strategy or community database is not being bound by proprietary licensing––hence, low cost of operations as well as a large group of developers contributing to it. That doesn't translate to being able to keep using a version indefinitely or getting fixes indefinitely. Be sure to check the Postgres version you have when considering community support and fixes. 

Every Postgres version (like the newly launched Postgres 15) is supported by the community for a defined period of time in which the community releases security patches and bug fixes on a regular cycle. Once the version is deemed unsupported, the fixes stop. As an example, if you are using PG 9.4, which reached EOL (End of Life) on February 13, 2020; the Postgres community will not release any to fix or security releases. Thus, no new vulnerabilities will be addressed.

2) Compliance

Organizations have strict compliance obligations to ensure and enforce that critical application databases are up to date with the latest supported versions. This makes it imperative to keep your deployments up to date.

3) Performance

Throughput of your database can be your key driving factor for upgrades. With each new version Postgres keeps delivering better performances. To prove this, we did some benchmarking to see how Postgres has improved performance with every version, and what we found was quite pleasing. As they say, “data never lies.”

4) Feature/Need

This is one of the key motivating factors for application developers and database administrators. Each new version introduces many small and big features which open new ways of solving a problem and introducing new improvements. For example: managing the partitioning of a table is easier much more efficient with Postgres 12. A detailed matrix of new features can be found here.

5) Data security

Organizations that fail to upgrade their databases as regularly as they do other systems leave a vulnerable gap for hackers to enter and reveal or steal data. The latest version of software ensures to have updated security features. Discover how EDB can support your database recovery if you face a cyberattack or want to learn how to prevent one.

6) Support for new Postgres extensions

An added advantage for some can be added features that you get with the use of Postgres Extensions. With each new version of Postgres the extensions leverage the new features and add functionality that can be a boon for application development. 

Tools like pgPool, pgBouncer, and extensions get better with each version. The amcheck module was introduced after Postgres 10 that provides functions to allow you to verify the logical consistency of the structure of index structures.

Why is it important to upgrade Postgres? The risks of not upgrading

After going through the reasons for upgrading, we can start listing the risks that we run if we do not.

  
1) No free bug fixes

Upgrades provide free bug fixes that prevent exposure to data loss and outages, which can easily lead to financial loss in terms of loss of sales or penalties. If you don’t upgrade, you don’t receive these.

2) Compliance

Organizations by structure have compliance regulations in place that they need to be met. Having an old outdated version of Postgres database with no support can be a ground for non-compliance leading to loss of contract or in case of software as a service model subscription.

3) Software performance ceiling

As your software gets more and more users, you need to constantly make investments in tuning or hardware to keep the service levels acceptable. There is an inherent performance ceiling that the Postgres database has after reaching which no more gains can be made by throwing more hardware at it. As demonstrated above, added efficiency in Postgres with each version allows it to deliver more on the same hardware as its predecessor. In one line, investment in hardware can be higher to keep an old version of database performing. 

4) Missing features

Missing features in Postgres Database will inhibit application developers from creating complex workflows without compromising performance thus stalling new feature development.

5) Increase data security measures

Increase and tighten data security measures via firewalls and other new features to mitigate known vulnerabilities of old versions.   

 
Cost of upgrading vs. not upgrading Postgres: Decision time

Now that we have an understanding of risks which have COST associated with them. It can be compared with the COST of Re-certifying the application on a new version of the Postgres database. 

One more thing which can help your argument is the fact that Postgres maintains backward compatibility with its older version. Hence, your actual cost can be a lot lower. 

How to upgrade Postgres: What are the Postgres upgrading methods?

Once you have made the decision next is deciding which path of upgrade you will take. Below is a list of methods available to you as out of box options in Postgres.

1) Backup and restore

One of the easiest ways to upgrade your database to a higher version is to use pg_dump/pg_dumpall to first take the backup of your source database and then use pg_restore to restore it to the target database. This method is very helpful when you are planning to upgrade from the legacy version(PG 8 to PG 11 for example) to one of the latest versions of the postgres database. Newer versions of Postgres do support parallel backup and restore to make the upgrade process much faster. One of the other advantages of using this process is that it also helps to reduce any database bloating issue. The process is quite simple and helps to upgrade small to medium size databases as well.

2) Open source tools

Slony or Londiste can be used to do major version upgrades, specifically moving from the very older version from 8.4 to the more recent once PG 12(for example). Both the tools use a trigger based logical replication and can help you to upgrade PostgreSQL with minimum downtime. Replication can only happen for the table which has primary keys. 
Also once the replication is set up between the source and target any DDL changes(table structure) need to be replicated on both source and target to ensure replication does not break. There are few caveats or drawbacks one should be aware of before considering this option for an upgrade, Slony for replication can cause load on the server. If your application is us doing a significant INSERT, UPDATE, and DELETE the more triggers will be generated and will add to server load. 

3) Native logical replication

Postgres started logical replication support in version 10. If you are planning to move from Postgres 10 to a higher version, it’s definitely a very good option. This also eliminates any need for learning and installation of third party tools (Slony, Londiste). One of the key requirements of using this approach is to have a replication identity (usually a primary key). This replication method also helps if you are planning to move from the platform along with a database upgrade. For example: Moving from Postgres 10 (Windows) to Postgres 12 (Linux).

4) Upgrade compatibility check

It gives us the ability to do a compatibility check before even performing the actual upgrade. Running pg_upgrade with a check(-c or --check) option will tell you possible incompatibility with extensions, data type issues.