On Oct. 13, PostgreSQL version 15 was released. Performance improvements, new features and capabilities for managing workloads in both onpremise and cloud environments make PostgreSQL 15 a valuable update. At the largest European PostgreSQL conference in Berlin, there was therefore a lot of attention for this new version. In this blog, we summarize the most important changes and new options.
Strong and engaged community
The number of improvements and new additions in PostgreSQL 15 is enormous, which is testament to a strong, active and engaged community. Over four hundred DBAs, developers and data architects have collaborated on this version. During the PGConfEU conference in Berlin, where we were present with part of our team as a Silver sponsor, the popularity of Postgres was also evident. The number of visitors broke all records and a huge growth of involved service providers and sponsors can be seen.
Comprehensive update
PostgreSQL 15 has become a beautiful and comprehensive update. The changes and additions are too many to mention. The complete overview can be found in the release notes, but we'll just highlight the most important points here.
1. MERGE command
New in PostgreSQL 15 is the MERGE command. The command handles inserts, updates and deletes in a single transaction. The addition of the MERGE command has made it easier to migrate from SQL Server and Sybase (SAP ASE). There were already - labor-intensive - workarounds, but with MERGE, PostgreSQL 15 is truly a step forward in compatibility.
Source: Percona, Umair Shahid, PostgreSQL 15 - New features to be exited about
2. Improvements logical replication.
There is more flexibility in controlling replication through row- and column-level filtering capabilities and the ability to select specific columns in column lists. For example, this provides the option to replicate a specific data subset from a table. This can help distribute data between different nodes and improves performance by allowing data to be shared selectively.
Source: Percona, Umair Shahid, PostgreSQL 15 - New features to be exited about
3. Base backups.
This functionality allows specifying targets for backups and backups can be compressed, both client and server. Backup location can be specified with -t target or -target=target. Client is default, server keeps backup on server, blackhole discards contents, use only for testing and debugging applications.
4. Improved data compression.
In PostgreSQL 15, you can now use LZ4 and Zstandard algorithms (until PostgreSQL 14, PGLZ was used). This significantly improves the speed of compression and gives more space for most workloads. You can choose the desired compression method with wal_compression . Permanent archiving was previously handled with shell commands. Now WAL also works with custom modules. Although it is often simpler via shell commands, a custom archive module will be much more stable and perform better.
5. Logging
Since PostgreSQL 15, it is possible to generate a log output in JSON: log_destination=jsonlog. This format is useful when integrating into other (statistics) applications. In addition, log files are now stored in shared memory. Among other things, this eliminates write activity to temp files.
6. Speed of sorting
Through smarter use of memory allocation, reduction of memory consumption in on-disk and in-memory sorting and special sorting routines for common data types, significant performance improvements can be observed. The nice thing is that you don't have to make any changes to your database or application. The algorithm is modified so it "just works" and you see the performance improvements.
Source: PGConfEU 2022, Amit Kalipa – PostgreSQL 15 and beyond
7. Performance improvements
Furthermore, a number of changes have been made that also improve overall performance. Such as partitioning, parallelism, recovery/replay and vacuum.
Why update?
It is generally wise to stay current and up-to-date. The latest version ends up working best in most cases, and you achieve performance improvement "automatically" in many cases.
Of course it is advisable to read the release notes of each version carefully and consider whether the major version (in this case 15) immediately or in the short term brings new features that will help you in practice. Older versions (as long as the community still supports them) are updated permanently - until end-of-life, five years after the release of the major version - with bug and security fixes via minor updates (12.12, 13.8, 14.5 etc). So you don't necessarily have to hurry.
Skip versions
What also makes PostgreSQL unique is that pg_upgrade gives you the option to skip versions. For example, you can go from PostgreSQL 11 to PostgreSQL 13 or even 15. Be sure to read the release notes of all intermediate versions so that you can see the consequences. Don't wait too long, older versions are supported up to five years back (five major versions). And before you upgrade, make sure you have installed the latest minor update to your current major version.
Want to learn more about PostgreSQL and upgrading?
Please feel free to join the PostgreSQL Usergroup NL for regular meetups and learn more about PostgreSQL.