What version of PostgreSQL?

Are you trying to decide which version of PostgreSQL to install? Maybe you need to decide if it's worth upgrading to the latest version. This post is here to help.

I started thinking about this after reading Brent Ozar's post on this topic from a MS SQL Server perspective. One thing I realized is that PostgreSQL major versions are maintained for roughly 5 years compared to MS SQL's 10 year support. The more I have pondered this difference, the more I agree with only keeping versions in support for 5 years.

New Installations

If you are starting from scratch with Postgres, start with v11. There aren't going to be many good reasons to start a new project on anything but the latest version.

That was easy!

Existing Installations

If you are running PostgreSQL 9.3 or older you are out of support (past EOL) and should start planning to upgrade ASAP.

At the time of writing, PostgreSQL 9.4 is the oldest supported version. Released December 18, 2014 it will be out of support after February 13, 2020. That's only 11 months from the time of writing. You should be planning your upgrade now.

Postgres 9.5 and newer

If you're running Postgres 9.5 or newer you aren't (yet) bumping up against an EOL date, but there are still good reasons to be proactive with your upgrades. Here are four of my top reasons:

• Performance
• Features
• Easy upgrades
• Cost

These four reasons are discussed further below.

Performance

My testing and monitoring shows that newer versions of Postgres can handle identical workloads on identical hardware while using fewer resources. A few months ago RustProof Labs upgraded our production Postgres databases from Pg9.5 to Pg11. Our monitoring showed CPU utilization dropped ~ 3-4% just from upgrading. Your results will vary, but this is a cool bonus for nothin'.

There are a variety of performance improvements in every new version of PostgreSQL.

Features

Major versions of PostgreSQL (e.g. 9.6, 10, 11, 12) include new features. This is the major value-add and often the best reason to upgrade your Postgres instances. When I upgraded our servers to Pg11, I was pretty excited to finally have covering indexes in Postgres.

Now that the last of our @PostgreSQL instances are upgraded to #PostgreSQL11, I can start adding covering indexes. Happy Saturday! 🤓 https://t.co/jFFQFxLUni

— RustProof Labs (@RustProofLabs) December 8, 2018

Some major features added or improved, by version:

PostgreSQL 9.5

• ON CONFLICT DO ...
• Row Level Security (RLS)
• JSON improvements

PostgreSQL 9.6

• Parallel Query

PostgreSQL 10

• Partitioning
• Logical Replication
• SCRAM authentication
• Improved parallel query

PostgreSQL 11

• Covering Indexes
• Just In Time (JIT)
• Improved partitioning
• Improved parallel query (again!)

Easy upgrades

I've had nothing but good experiences with pg_upgrade. Like most things with Postgres, It Just Works. You should take a good backup (a good backup includes testing the restore process) before any upgrade, and practice the steps in a test environment at least a couple times before performing upgrades.

The only place I've had any hiccups with Postgres upgrades has been related to the libraries associated with PostGIS. Those hiccups are mostly documented and avoidable, but they still creep their way in somehow.

Cost

Licensing for the newest version of PostgreSQL is exactly the same as every version before it: $0. That's $0 per instance, per core, per thread, per database, per table, per GB, per row. No matter how you add it up, it's $0.

Simple, huh? I wonder how many posts have been written about licensing costs of other database platforms...

New features, performance improvements and behind-the-scenes enhancements are included in the latest version, and you don't even have to talk about licensing costs.

Arguments against upgrades

The most common reasons I hear for avoiding upgrading:

• Can't have downtime
• Afraid of upgrades
• No reason to upgrade

Argument: Can't have downtime

The core of this argument is often the result of contracts not specifying maintenance windows. Most database systems should have the ability to be unavailable for a short window on an infrequent basis. With proper backups, testing, and procedures, I was able to upgrade our production databases from PostgreSQL 9.5 to PostgreSQL 11 with under 10 minutes of downtime. I could have made it faster, but we had a scheduled 60-minute window during an off-peak time and 10 minutes was easy and relaxed.

If zero downtime is critically important, this reinforces the argument to upgrade! Once you are on Postgres 10 you can achieve zero-downtime upgrades using logical replication. I still recommend including at least some downtime in your contracts...

Argument: Fear of upgrades

This is common, and I get it. I've had bad experiences with upgrades where either something went wrong, or I just didn't like the new version. Postgres upgrades should not be scary though. Following two industry best practices for good stewards of data:

• Backups are automated
• Backups are tested regularly (monthly / quarterly)

If you have backups scheduled and running regularly, and are testing the process of restoring from said backup, you are ready to test the upgrade process. After you restore your backup you can easily test the upgrade. If you're using any sort of virtualization, you can take a snapshot before the upgrade and repeatedly test. Document your steps and time how long it takes. Next time, follow (and refine) your procedures and see if you can perform the restore and upgrade quicker.

If your backups are not automated and tested... stop and make that your first priority. pgBackRest is great for Postgres backups, and has good documentation.

Updating frequently will make you more familiar with the process, reduce chances for mistakes, and reduce anxiety about the process. Having a well documented and tested backup and restore process will help you be confident that even if the upgrade goes haywire (unlikely) you are prepared for the worst.

Argument: No reason to upgrade yet

The features and improvements in the newest versions don't interest you? Ok. As long as you aren't past EOL I can accept this. If you're past EOL you are putting yourself and your data at risk. I have strong opinions about the security of data. Running an outdated, and quite probably insecure, database is not acceptable.

What I don't hear

Something I don't hear around the Postgres community often is that a 3rd party software is the main reason for delaying upgrades. Sure, when a new version comes out you might not be able to upgrade right away, but I don't see many (any?) products out there that only support Postgres 9.3 or under.

In the world of non-open-source databases, the delay for 3rd party upgrades is considerably longer. For example, upgrades to MS SQL 2014 are not uncommon in 2018 and 2019, whereas Postgres 9.4 (December 2014) is nearing EOL.

Summary

In general, I recommend upgrading PostgreSQL as frequently as you can, to the latest major version you can. While that's what I recommend, upgrades to the latest and greatest aren't critical as long as you keep up with the minor releases. If you are on the oldest supported version, or older, you should plan your upgrade as soon as possible.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!