In June, the world’s leading Postgres experts, users and community members came together during our annual Postgres Vision event to discuss the future of Postgres and share their experiences and insights.
While it may seem unusual to have a panel discussion on upgradability, that’s exactly what we did; and we can assure you that the results were well worth it.
As Bruce Momjian, EDB’s VP and Postgres Evangelist, shared, it’s important (1) to help attendees better understand the Postgres upgrade process and how to do it more efficiently; and (2) to talk about how we can potentially improve the upgrade process going forward.
Here’s a recap of this session:
Why upgrade? Why not?
Upgrading Postgres is different than upgrading other database systems.
Panelist Laetitia Avrot, Field CTO at EDB noted some of the differences. For instance, new features generally aren’t introduced in minor Postgres upgrades. “So the risk that you will break something with a minor upgrade is very low,” Avrot said.
Momjian explained how, with Postgres, the minor upgrade release notes highlight if anything is changing, and how tightly curated that list is .
“We don’t introduce any change that we don’t have to,” Momjian said. While many organizations are forced to push new features into their commercial products, Postgres is a community, so that type of pressure doesn’t exist.
Avrot also mentioned that the code is so well written, the probability that you have a bug in Postgres is lower than with other database systems.
“Everyone knows that you never install the r1 of Oracle, because you know that it will be very buggy,” Avrot said. “I’ve never seen that with Postgres. And I know that when I install a major version of Postgres when it's released—while it’s not bug free, as no one can guarantee that—it still works solid.”
Don’t let bad upgrade experiences hold you back
The panel discussed how some organizations hesitate to upgrade Postgres because of bad experiences with other database engines. Though as people become more familiar with Postgres, they get more comfortable deploying upgrades and staying current.
When Avrot runs across a developer sitting on an old Postgres version, she approaches the developer and explains how the Postgres format works. “I just pick any code source page of Postgres at random, and show how good it is,” she says. “Normally that’s a good argument with developers. That’s something developers can understand.”
It’s important to not get too many versions behind in Postgres, because then when you upgrade, there’s a huge gap of versions to cover, with more things to check, more risk and more extensions to check for compatibility. Postgres also offers support for major versions for five years, so why not ensure the support is there when you need it?
What features are on developers’ wishlists?
While it might be difficult for Postgres evangelists to hear about Postgres' pain points, it was critical to discuss them at the conference, so our community can move forward to address them.
First, Andreas Scherbaum, Head of Databases at Adjust GmbH brought up the need for extensive testing on a number of different servers. Scherbaum shared that it would be helpful when testing if PG_Upgrade could tell developers in advance where specific problems with an upgrade might exist for their specific setup—for instance, what extensions might not work or which parts needed to be verified. This way, developers could look into it before upgrading.
Nikolay Samokhvalov, Founder of Postgres.ai – Database Lab, raised the importance of reducing downtime for major upgrades. Samokhvalov addressed some of the limitations of PG_Upgrade and how it relies heavily on the PG_dump PG_restore process.
Until Postgres 13, rolling minor upgrades weren’t possible, but as Samokhvalov confirmed, the process has gotten much better since version 13. The primary_conninfo can now be changed without restarting a standby server.
Another feature panelists wanted to see in Postgres was an in-core connection pooler. While external solutions like PGBouncer are available, panelists agreed an official pooler for minor upgrades would be ideal.
One idea Samokhvalov had for developers planning on shutting down Postgres for a minor update was to run a checkpoint, wait for it to finish, THEN shut down Postgres. This way there would be much less code to write.
Overall, it looks like more automation will be in Postgres’ future for the upgrade path.
The Postgres Community is here to help
While upgrade solutions are written in the release notes or on the PostgreSQL Wiki, not everyone reads those.
Jumping from an old minor Postgres version to another minor version can be complex, but the dynamic and free Why Upgrade? can help. This tool from depesz.com lists the exact changes from one release to another.
Regarding automation, panelist Nikolay Samokhvalov developed an open source health check automation tool called Postgres Checkup. This innovative tool detects current and potential issues with database performance, scalability and security, and recommends how to resolve or prevent them.
Moving forward
If you’re not familiar with the Postgres community or the open source nature of Postgres, you’d probably be amazed by how matter-of-factly upgrade pain points were discussed and solutions proposed and evaluated right in front of the audience.
Postgres community members are used to sharing problems and working together to resolve them.
And that, in a nutshell, was why there was a session on upgradability at Postgres Vision.