Percona on PostgreSQL version 15: what should you look out for?
This is a guest post for Computer Weekly Open Source Insider written by Umair Shahid in his role as head of PostgreSQL at Percona — a company known for its work delivering enterprise-class support, consulting, managed services and software for MySQL, PostgreSQL, MongoDB, and other open source databases.
PostgreSQL is now the second most popular open source database worldwide according to DB-Engines and it consistently ranks as the database that developers are most keen to work with based on StackOverflow’s annual survey.
So then, a new release for PostgreSQL deserves a deeper look.
Shahid says that there are some big improvements in the release that users should definitely be aware of and writes as follows…
One of the biggest additions in version 15 is the new MERGE command.
MERGE is part of the SQL Standard and it makes it easier to conditionally insert, update or delete data. As an example, imagine two rows of data that you join together – when they are matched, you can carry out one action to those that meet the condition, while those that don’t match can have a second action carried out.
A simple conditional statement
For a developer, writing the code to carry out this business logic would require many separate lines of code to create. With MERGE, this becomes a simple conditional statement. Cutting the number of lines of code in any application is always useful, as it reduces your maintenance overhead.
However, the biggest impact from adding MERGE is that it will make it much easier to migrate from Microsoft SQL Server or Oracle to PostgreSQL. SQL Server and Oracle have supported MERGE for many years, so adding this will solve a big problem around code rewrites that would otherwise be needed.
The next big update is around Logical Replication and row and column-level features.
Using these filters, you can selectively replicate data from your primary database to a standby. Rather than replicating all your data, you can move specific data over, which improves performance. However, this can also be used to support use cases like security and compliance.
Location, location, geo-fencing
For example, we see many organisations looking at how they manage data from users based in multiple countries. If you have customers in Germany, then their data should not go outside the European Union. What happens when that data has to remain in one location or region for compliance reasons?
With Logical Replication, you can select for specific rows or columns and then replicate the data set to the relevant location for that data. This supports geo-fencing specific data sets so that it can be adequately protected and backed up, but not moved outside that region or country. Similarly, you can use Logical Replication to support multi-tenancy for customer data or strip off personally identifiable information when you need it for reporting.
Alongside these changes, PostgreSQL is increasingly shifting to using a new compression format.
Rather than GZIP, which has been used for many years, PostgreSQL will use lz4, which is perhaps the fastest lossless compression algorithm. The pg_basebackup utility has been enhanced in v15, and it now supports server-side compression as well as client decompression. This will make compressed data storage more efficient and will reduce costs too.
Shahid: For those moving to PostgreSQL 15, there are some changes that will be needed & elements to check in the migration process.
Interestingly, PostgreSQL 15 adds the option to format logs in JSON, making the logs compatible with the most popular format that developers and technologists like to use. Alongside making it easier to manage logs with JSON, it can be used by other utilities for storage and analysis.
There are some useful performance improvements added in PostgreSQL 15 including better performance in sorting algorithms and support for parallelism. There are some differences in how users might take advantage of these new features.
In benchmarking the performance around speeding up on-disk and in-memory sorting algorithms, these improvements have been between 25 percent and up to 400 percent in some circumstances.
This is available to anyone who makes the move to the new version. Where users want to deploy and use parallelism and multi-core CPUs with their PostgreSQL implementations, version 15 offers parallel SELECT DISTINCT and parallel commits in postgres_fdw. These features have to be specifically selected and used, rather than being default in deployments – they need to be looked at in context.
For those of you looking at moving to PostgreSQL 15, there are some changes that will be needed, and elements to check as part of the migration.
Tweaks & twists
Testing your application with the new version can flag up any potential problems and there may be some minor tweaks needed to keep things running smoothly. However, making the shift should be easier for those looking at migrating over to open source.
