Is it Time to Upgrade your PostgreSQL Database?

Ajay Patel May 21, 2020

Is it time to upgrade your PostgreSQL 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 which 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 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 THE POSTGRESQL 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 the upgrade and then calculate what it will cost them if they choose not to.  

So, let’s start with the benefits.

 

Why Upgrade PostgreSQL?

  1. Community Support and Fixes: The best part of using an open source 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 keep using a version indefinitely or getting fixes indefinitely. Every version of Postgres 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; Postgres community will not releasing any to fix or security releases. Thus no new vulnerabilities will be addressed.
     
  2. Compliance: Organizations have strict compliances to ensure and enforce that critical application databases are up to date with the latest supported versions. These policies can also extend to applications with embedded database. 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 a benchmarking to see how Postgres has improved performance with every version and what we have 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 partitioning of a table is much more efficient and easier with Postgres 12. Parallel creates B-Tree indexes with Postgres-11. Detailed matrix of new features can be found here.
     
  5. Data Security: Databases are one of the most vulnerable to cyberattacks. You may have likely heard of database breaches such as Anthem’s breach, around 80,000 people’s confidential information was released, or the Voter Database from which 191 million personal identities were stolen. Usually, this is because databases are the easiest to penetrate through networks. Organizations fail to upgrade their databases as regularly as they do other systems, which leaves a vulnerable gap for hackers to enter and reveal or steal data. The Latest version of software ensures to have updated security features.
     
  6. Support for new Postgres Extensions: An added advantage for some can be added features which you get with the use of Postgres Extensions. With each new version of PostgreSQL the extensions take advantage of the new features and add functionality which can be boon for application development. Tools like Pgpool, Bgbouncer, extensions get better with each version. The amcheck module was introduced after PostgreSQL 10 that provides functions to allow you to verify the logical consistency of the structure of index structures.

Why is it important to upgrade PostgreSQL? Risks of Inaction

After going through the why’s, we can start putting together an outline of risks that we run if we do not.   

  1. No Free Bug Fixes. Exposure to data loss and outages which can easily lead to financial loss in terms of loss of sales or penalties. 
     
  2. Organizations by structure have compliance in place which they need to be meat at all levels. Having an old outdated version of PostgreSQL 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. As your software gets more and more users you need to constantly need to make investments in tuning or hardware to keep the service levels acceptable. There is an inherent performance ceiling that the PostgreSQL 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 Database performing. 
     
  4. Missing features in PostgreSQL Database will inhibit Application developers from creating complex workflows without compromising performance thus stalling new feature development.
     
  5. Increase and tighten data security measures via FireWalls, OS, Network or in Application to mitigate known vulnerabilities of old versions.   

 

Cost Of Upgrading or Not Upgrading PostgreSQL: 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 PostgreSQL maintains backward compatibility with its older versions. Hence your actual cost can be a lot lower. 

How to Upgrade PostgreSQL: What are the PostgreSQL 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. Logical Backup and restore: One of the easiest ways to upgrade your database to a higher version 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 for the user. One of the other advantages of using this process is that it also helps to reduce any database bloating issue. 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 with minimum downtime. Replication can only happen for the table which has primary keys. Also once the replication is set up between 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 upgrade, Slony for replication can cause load on the server. If you application us doing a significant INSERT, UPDATE, and DELETE the more number of triggers will be generated and will add to server load. 
     
  3. Native Logical replication: Postgres started Logical replication support within product started from 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 of learning and installation of third party tools(slony,londiste). One of the key requirements of using this approach is to have replication identity (usually a primary key). This replication method also helps if you are planning to move from platform along with database upgrade. For example: Moving from Postgres 10(Windows) to Postgres 12(Linux).
     
  4. Native pg_upgrade: 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.

 

Upgrade Method

Best Suited for
 

Downtime

Logical Backup and Restore

  • Smaller DB(< 100GB) 
  • Doing major version jumps(example PG-8 to PG-12)  

Yes(Depending upon the database size)

Tools (Slony/Londiste)

  • DB >100 GB
  • Less suited for the database which has dynamic DDL changes

Minimum

Logical Replication(Native)

  • DB > 100 GB
  • Less suited for the database which has dynamic DDL changes.

Minimum

pg_upgrade

  • DB > 100GB to very large databases
  • Any size of database (specially suited for large database)

Minimum(If -k option used)

 

Though Database is at the backend and might not be brought up often in discussions when you are working on applications. But it certainly has the potential of blocking all plans if not performing optimally or lacking features or worst still be vulnerable to a data breach.

The questions are:

  • Are you willing to risk an outage with an outdated version which the community wont fix or b) keep your data safe against known vulnerabilities which a supported version gets patches through the year.
  •  Improving your application performance by investing in hardware will provide diminishing returns. Or b) get the improvements by investing some time every few development cycles on a new Postgres version.
  • Look for workarounds to improve features in application Or b) take advantage of options available in the new Database version and develop new features. 

If the answer to all the above is the second option(b), then it's time to UPGRADE YOUR DATABASE!

This blog is co-authored by Ajay Patel and Deepanshu Sharma. 

 

 

Ajay Patel

Ajay Patel is a Delivery Manager on the RemoteDBA team, supporting customers worldwide with both small and large Postgres deployments. He has more than a decade of experience in professional services as a database consultant. Prior to joining EDB in 2014, Patel was a DBA/Consultant in Professional Services at Symphony Teleca and was an Oracle DBA at CGI. He is certified in Oracle, Postgres, Teradata, AWS, and PRINCE project management.