Goodbye to Painful PostgreSQL Upgrades: Discover YugabyteDB’s New Upgrade Framework

YugabyteDB is launching support for in-place, zero-downtime, major version upgrades of PostgreSQL. This new feature is included in the recent v2.25 release of YugabyteDB, which also supports PostgreSQL v15.

Quick and painless upgrades are just a single click away in YugabyteDB Aeon or YugabyteDB Anywhere, or a simple process restart in Yugabyted.

Major version upgrades of PostgreSQL have long posed significant challenges for engineering teams as they are fraught with complexity and unavoidable downtime risks. This is a challenge for mission-critical systems and global applications that demand uninterrupted uptime, 24/7, 365 days a year. PostgreSQL’s lack of easy upgrade capability has led many businesses to delay upgrades, resulting in missed performance improvements, increased costs, and prolonged exposure to security vulnerabilities.

In this blog, we explore how YugabyteDB is rewriting the rules with an industry-first innovation: true in-place, online, major version upgrades for PostgreSQL. No more downtime. No more migration headaches. And in the unlikely event that something goes wrong, rollback is just as easy, taking a single click.

It’s time to leave upgrade headaches behind and embrace a seamless PostgreSQL upgrade future with YugabyteDB.

TL;DR

This table shows popular database services and their supported PostgreSQL upgrade methods. Unlike other services that require downtime or manual migrations, YugabyteDB maintains its commitment to high availability in every scenario.

The Technical Challenge With Major PostgreSQL Upgrades

Major version upgrades have always been challenging for PostgreSQL users. The Hacker News thread ‘PostgreSQL upgrades are hard‘ illustrates the widespread frustration many users experience.

There is an architectural tradeoff between keeping newer versions of PostgreSQL backward-compatible and the flexibility to rapidly innovate and maintain a cleaner code base. The PostgreSQL development community chose to forgo backward-compatibility in favor of unique PostgreSQL features like transactional DDL, and pioneering new capabilities like pg_vector.

The primary challenge of developing backward-compatibility with previous PostgreSQL versions lies in the System Catalogs—the internal tables that store the metadata about the Database Schemas. The structure of System Catalogs changes with each release, and new features alter the layout and size of these tables. PostgreSQL has hard-coded expectations about these structures, so modifications lead to incompatibilities with older versions.

The System Catalog is just the biggest of several hurdles. For instance, the serialization formats of internal data structures, such as those recording Stored Views or Default Expressions, aren’t compatible across versions. Even the PostgreSQL WAL has limitations that mean solutions like Physical Replication stop working during major upgrades.

Upgrade Options in PostgreSQL

There are three options for major version upgrades of PostgreSQL:

1. Backup-Restore

This option involves taking a logical backup of the existing PostgreSQL cluster and restoring it into an empty cluster running the newer version.

The significant downtime required for the Backup-Restore process makes it unsuitable for production workloads.

2. Blue-Green (Logical Replication)

A new PostgreSQL cluster running the newer version (Green), is brought up alongside the existing cluster (Blue).

Data is replicated from the Blue cluster to the Green cluster using Logical Replication.

Once the Green cluster is synchronized and validated, user write traffic is stopped. The replication is then allowed to Drain, ensuring all pending changes are applied to the Green cluster.

After this, the replication is terminated, and users can connect and use the Green cluster.

Despite the shorter outage in the Blue-Green (Logical Replication) method, the need for twice the infrastructure, operational complexity, and other restrictions makes this option impractical.

3. In-Place Offline

PostgreSQL natively supports In-Place Offline upgrades using the pg_upgrade application. This comes with the option to perform a metadata-only copy, to minimize downtime.

In-Place Offline is typically the preferred upgrade option, however, downtime can range between a few minutes to several hours.

Online In-Place Upgrades in YugabyteDB

After carefully evaluating all available options, we concluded that none are capable of handling the high availability requirements of YugabyteDB. Instead, we developed a new upgrade framework to achieve effortless and downtime-free upgrades (and downgrades).

YugabyteDB distributed storage, transaction, and replication layers now support online rolling upgrades and rollback. By taking advantage of YugabyteDB’s PostgreSQL compatibility, specifically being able to reuse pg_upgrade, the new upgrade framework achieves online upgrades without compromising PostgreSQL’s architectural choices.

YugabyteDB’s upgrade framework provides many features and advantages:

• The framework accomplishes an online rolling upgrade by seamlessly managing the differences in the System Catalog between the two major PostgreSQL versions.
• As this is an in-place upgrade there are no extra hardware costs.
• Users gain the ability to monitor the Cluster and evaluate the performance of their workload. They can also easily rollback to the previous version if necessary.
• Provides a simplified end-user experience with a single click, or a simple process restart. The framework does the heavy lifting.
• Automatically upgrades Extensions to versions compatible with the newer PostgreSQL version.
• Eliminates the need to execute Analyze post-upgrade to re-collect Statistics used by the Query Planner. This allows the workload to execute optimally during and immediately after the upgrade has been completed.

There is a single tradeoff: Execution of Data Definition Language (DDL) operations is not possible during the upgrade process. Since performing DDL operations is generally discouraged during a major PostgreSQL version upgrade, the impact of this restriction should be minimal. DDL operations related to temporary tables and Materialized View Refresh are permitted, as these are often integrated with, or have the potential to impact Data Manipulation Language (DML) workloads.

How In-Place, Fully Online PostgreSQL Upgrades in YugabyteDB Compare

To showcase the benefits of an online, in-place, upgrade, we compared it against in-place, offline, upgrade, in Amazon Aurora PostgreSQL. This simple test involved a major PostgreSQL version upgrade against a single database with a single table containing a single column. A vibe-coded app tries to insert 1000 rows into this table every second.

In this test, Amazon Aurora experienced a 10 minute outage. Real-world databases with more complex schemas will experience significantly longer outages. The outage also takes down the Read Replicas of the Cluster.

Since each node is restarted one at a time in a rolling manner there is no outage in YugabyteDB. We can see three short dips corresponding to the three quick node restarts.

Conclusion

PostgreSQL upgrades have long been a challenge for engineers, resulting in overwhelming operational complexity and unavoidable downtime. While traditional methods-Backup/Restore, Blue-Green, and In-Place Offline upgrades-provide partial solutions, none fully meet the demands of modern, always-on applications.

YugabyteDB has reimagined PostgreSQL upgrades from the ground up. As demonstrated in our PostgreSQL v15 rebase, we’ve built a standardized upgrade framework that fulfills YugabyteDB’s commitment to:

• Seamlessly tracking new PostgreSQL versions
• Maintaining enterprise-grade availability during upgrades
• Delivering predictable, timely, and effort-free upgrades

With the release of v2.25, YugabyteDB has not just caught up with PostgreSQL v15 but has established a future where major version PostgreSQL upgrades are no longer feared, but instead embraced as part of a smooth, reliable, and repeatable process.

To find out more about YugabyteDB’s new upgrade framework and ask questions of the engineers who built it, REGISTER NOW for our upcoming YugabyteDB Friday Tech Talk ‘Say Goodbye to Painful PostgreSQL Upgrades – YugabyteDB Does It Live!’ on Friday 18th April.