PostgreSQL 15: MERGE Ahead
With last week’s release of version 15, the Postgres relational database system had made it easier for enterprise users of Oracle and Microsoft SQL Server to move to this open source alternative, thanks to its adoption of the MERGE SQL keyword. Improvements in sorting, compression, and logical replication also come with this release.
We sat down with Umair Shahid, the Head of PostgreSQL at Percona, to hear what the company sees as the best new features about this latest iteration of the open source database system.
Percona takes a very close interest in Postgres, upon which it builds its own enterprise support distribution (which is fully open source btw), geared for high-performance workloads.
Postgres is one of the last general use, open source SQL-based database systems, alongside MySQL, and so is being used across a bewilderingly-wide set of ways. It is particularly favored in the financial technology community, for transactional processing, and for users looking for an alternative to Oracle. DB-Rankings ranks as the 4th most widely used database system overall, its popularity having grown an astounding 35% over the past year.
A lot of the improvements with v15 will be largely invisible to the average user, Shahid said. For instance, considerable attention has been paid to sorting functions. Compression is now handled by LZ4, a lossless compression technique rapidly gaining favor. Previously, the database software used Gzip. Lz4 has a higher compression ratio — leading to smaller files sizes — and can compress and decompress files 25% to 400% more quickly than GZip.
“When we’re talking about data files that are in terabytes of size, every single performance gain that you can get is a big win,” Shahid said. Database backups, for instance, should be done as quickly as possible. Audit logs also need to be compressed.
Migration-Friendly MERGE
Long a standard SQL command, MERGE finally gets a home in Postgres. Once understood, MERGE can be a serious time saver for developers. It can conditionally insert, update and delete rows. Otherwise, this would be the job of the developers to write out CASE statements of if…then loops. It requires less processing at runtime as well.
Much of the work for the patch was contributed by EnterpriseDB (EDB) developer Álvaro Herrera a PostgreSQL15 patch entitled “Add support for MERGE SQL command.” Pavan Deolasee, Amit Langote, and Simon Riggs also contributed to the patch, which was first proposed in 2017 as part of the Postgres 11 development cycle.
It turns out this has been a long-awaited feature by many potential users. Both Oracle and Microsoft SQL Server have long supported MERGE, so users of those database systems that wish to migrate off of them to Postgres now have full support.
Logical Replication to Infinity
A number of improvements in Logical Replication point the way to some very interesting uses cases, Shahid pointed out.
Logical replication replicates data objects, in a publish-subscribe model. New capabilities here include row and column filtering.
“As you move from one database to the other, logical replicate from one database to the other rows, and you can filter columns of the table, and move data selectively,” Shahid said.
The filtering opens up many possibilities. It can be used for purposes of security, where selective data can be held back from viewers. It can be used for multi-tenancy, where one database can be used by multiple clients.
The feature could lead to the ability to do multimaster Postgres deployments, something that do date has not been possible. Geographically segregated clusters could handle conflict resolution.
Multimaster conflict-resolution Postgres can be done currently, usually through domain expertise and a lot of tools. And it still has limits. TRUE multimaster conflict resolution Postgres would essentially mean the database software could be scaled.
In this new release, Postgres can now log in JSON, making it easier to be consumed by observability tools. These server statistics data are now saved in memory, allowing for easy access by tools such as Percona Monitoring and Management (PMM), Shahid said.
