In addition to the newly introduced SQL statement, the open-source database extends the JSON/SQL functions and compressing backups.
Scheduled on the annual date The first beta of PostgreSQL 15 was released in May. The release brings some new features, most notably the SQL statement MERGE. For backups, the open-source database now knows additional ways to compress, and in logical replication it can filter by columns and rows.
As usual, the release comes with some performance optimizations. Among other things, version 15 should sort large data sets much faster. This is especially true if the size is beyond the value set in the work_mem parameter, which specifies the maximum memory size for query workspaces. Window functions that use row_number(), rank() or count() should also run more efficiently. The cleaning up of selected lists of duplicates via SELECT DISTINCT works in parallel in the current release.
The elephant listens to the three-in-one command
The MERGE command has been part of the SQL:2003 Database language default. It is used to transfer multiple rows from a source into a table and can combine the commands INSERT, UPDATE and DELETE.
MERGE INTO target USING source ON condition WHEN MATCHED THEN … WHEN NOT MATCHED THEN …
After specifying the target, the command now introduced in PostgreSQL specifies a condition in ON. If it is met, the rows are considered to match. WHEN MATCHED then determines what happens when the condition is met. A typical sequence is an UPDATE. Similarly, WHEN NOT MATCHED determines how the database handles rows that do not exist in the target. Then the command could insert them via INSERT. In addition, a condition for deleting content can be defined.
In PostgreSQL up to version 14, a similar procedure could only be implemented with stored procedures or in a restricted form using INSERT .. ON CONFLICT, which uses an UPDATE as conflict handling can define for existing rows.
Additions to JSON and Regex
The current release also brings extended functions for the SQL/JSON standard for working with JSON content in SQL databases. Among other things, PostgreSQL can now convert the former directly into a table using the JSON_TABLE function. Conversely, it offers new functions to generate JSON data. The release also extends the functions for queries on JSON content.
Version 15 also brings some additions in the area of regular expressions: regexp_count counts the matching occurrences and regexp_instr returns the position of the nth hit. regexp_like checks whether a regular expression is present in a string and simply returns true or false. Finally, regexp_substr returns either the part of a string that the regular expression matches, or NULL if there is no match.
Backup and Replication
After the backup legacy version of the LZ4 compression, PostgreSQL 15 adds Zstandard (zstd). The backup utility for clusters pg_basebackup can now compress on the server side with Gzip, LZ4 or zstd.
During logical replication, the current release can optionally filter by rows or columns. In addition, not only all tables of a database, but also all tables of a schema can now be made available. The disable_on_error parameter is new, which aborts logical replication in the event of an error. There is also a new instruction for dealing with conflicts.
Other innovations in PostgreSQL 15, including in the areas of administration and security, can be found in the announcement. The team has not yet announced a fixed date for the final version. The beta phase for PostgreSQL usually lasts about four months, so the release can be expected in September or October.
