MERGE SQL Command to Accelerate Postgres Adoption
The latest rollout of features in PostgreSQL 15 could accelerate Postgres overtaking legacy systems in the enterprise market.
Postgres already has disrupted the market. According to the recent Stack Overflow Developer Survey, it continues to be among the most loved, used and wanted databases in the world. The only question is how quickly Postgres will take a majority share among enterprises. The answer might come sooner than expected. With the latest features, the days of legacy systems ruling the enterprise may be numbered.
The features in PostgreSQL 15 include improved database security, enhanced backup and recovery options, and advanced logical replication capabilities. Also among the new features is the introduction of the MERGE SQL command, which makes it significantly easier for developers to migrate from their legacy database applications to open source Postgres. Both private and public sector organizations will greatly benefit from these advancements, particularly as they accelerate their digital transformation strategies.
MERGE is a popular SQL Standard command that can handle INSERT, UPDATE and DELETEs in a single statement, eliminating the need to rewrite MERGE statements entirely. MERGE avoids multiple PL/SQL statements and simplifies SQL scripts for database developers and administrators. The Postgres implementation conforms to the SQL Standard and helps developers of both Microsoft SQL Server and Oracle Database transition to Postgres. This means developers have to write less code and the production code can be more easily maintained.
The History of MERGE: Built to Remove Barriers
In the past, the lack of a MERGE statement forced developers to create custom workarounds, and the workarounds did not always perform at the same level. In cases where an application used the MERGE command with regularity, the cost to migrate was extremely costly from both a time and resource perspective. These barriers to Postgres adoption could often outweigh the benefits in the eyes of some developers and business owners.
As an example, our developers at EDB would spend nearly two days migrating the code for a given MERGE statement in a given application. We regularly see 14 MERGE statements in an application; however, there are many applications that have hundreds of MERGE statements. Multiply two days of code migration by hundreds of statements … you get the picture.
Like many great innovations, this highly anticipated feature was years in the making. On March 29, longtime PostgreSQL major developer and strategic expert for EnterpriseDB (EDB) Álvaro Herrera committed a patch to PostgreSQL 15 titled “Add support for MERGE SQL command.” Additional authors on the patch included longtime major Postgres contributors Pavan Deolasee, Amit Langote and Simon Riggs. The group worked alongside members of the community to test the functionality and the implementation over a period of several years when the patch was first proposed in 2017 as part of the Postgres 11 development cycle.
MERGE Lets Developers Work Smarter, Not Harder
Fortunately, after years of collaboration, the MERGE statement is now available in v15, making it significantly easier to migrate applications from Oracle and SQL Server. By removing this barrier to entry, we expect to see many more enterprises leaving legacy systems and making Postgres their database standard.
In addition to the migration benefit, MERGE allows developers to write less code and to have more readable code. As an analogy some longtime developers may be familiar with, it is like having Annotations introduced in Java 1.5, or Templates introduced into C++. Developers can write applications more quickly, and the code they produce is easier to understand and maintain by those who follow them.
Similar benefits are realized in additional recent features, including Postgres stored procedures and table partitioning for PostgreSQL, which enables applications to migrate with greater ease and preserve their semantics.
Additional PostgreSQL 15 Features that Developers Can Appreciate
Beyond the MERGE SQL command, there are plenty more features in v15 for developers to love. Below are a few of our favorites.
- Improved database security features: Postgres 15 enables granting of role-based permissions for the
SETandALTER SYSTEMcommands that previously required superuser permissions. Additionally, for the first time, non-superusers can be logical replication owners, and there are enhanced safeguards to ensure subscription owners comply with row-level security policies. These new capabilities support enterprises adopting zero trust security postures. - Enhanced backup and recovery options: Postgres 15 adds new server-side backup compression, providing new options for taking backups quickly and efficiently. Whether speeding up network transfers from a backup server to remote storage, reducing the time spent compressing backups or optimizing Postgres backups’ storage footprint, Postgres 15 has additional options to help enterprise customers.
- Advanced logical replication capabilities: EDB continues its tradition of working with the community to bring many advanced logical replication capabilities first introduced in EDB Postgres Distributed upstream into Postgres code. EDB contributed significantly to selective replication through column and row filtering, allowing for advanced logical replication use cases and reducing network traffic between the publication and subscriber nodes, and allowing the skipping of transactions on a subscriber using
ALTER SUBSCRIPTION…SKIP.
Postgres v15 will be available on the cloud via EDB BigAnimal later this month. We predict that these features will supercharge the trajectory of Postgres becoming the dominant database in the enterprise within the next five years. Already the fastest-growing database management system in what Gartner views as an approximately $80 billion market, we can expect that Postgres technology will continue to disrupt the market with greater scalability and cost savings compared to competing technologies.
