PostgreSQL is an open source object-oriented relational database management system.
After a year of development the release of the new stable branch of the DBMS PostgreSQL 15 was announced, This release includes a number of enhancements, including new compression capabilities that help with data storage and backup, improvements to data sorting for faster lookups, and new logging and SQL capabilities.
In this new version that is presented, the SQL command "MERGE" is highlighted, which allows you to create conditional SQL statements that combine INSERT, UPDATE, and DELETE operations in a single statement. For example, MERGE can be used to merge two tables by inserting missing records and updating existing ones.
The command allows table merging and makes PostgreSQL more compatible with SQL Server-based relational database management systems, including Microsoft SQL Server and SAP ASE relational database server, and any others that support the Transact-SQL suite of programming extensions.
Another change that stands out in this new version is in the algorithms for sorting data in memory and on disk have been significantly improved. Depending on the type of data in the tests, there is an increase in sorting speed from 25% to 400%.
For logical replication, support for filtering rows and specifying lists of columns is implemented, which allows, on the sender side, to select a subset of data for table replication. In addition, the new version has simplified conflict management, for example the ability to skip conflicting transactions and automatically disconnect a subscription when an error is detected. Logical replication allows the use of two-phase commits (2PCs).
The mechanism for connecting external tables External data container (postgres_fdw) implements support for asynchronous commits in addition to the previously added ability to process requests to external servers asynchronously.
Added the ability to use the LZ4 and Zstandard algorithms (zstd) to compress WAL transaction logs, which, under some workloads, can simultaneously improve performance and save disk space, and added support for proactive retrieval of pages that appear in the transaction log. WAL to reduce failure recovery time.
It is also highlighted that added to pg_basebackup utility la support for compressing backup files on the server side using gzip, LZ4, or zstd methods. The ability to use your own modules for archiving is provided, which allows you to dispense with the need to run shell commands.
In addition to that, now in PostgreSQL 15 shared memory usage was ensured for the accumulation of statistics on the operation of the server, which made it possible to get rid of a separate process of collecting statistics and periodically flushing the state to disk.
Of the other changes that stand out from the new version:
- Added a number of new functions to process strings using regular expressions: regexp_count(), regexp_instr(), regexp_like(), and regexp_substr().
- Added the ability to add multi-range types ("multi-ranges") to the range_agg() function.
Added a security_invoker mode to allow the creation of views that run with the rights of the invoking user, rather than the creator of the view. - A new log format has been added: jsonlog, which saves information in a structured way using the JSON format.
- The administrator has the ability to delegate individual rights to users to change certain PostgreSQL server configuration parameters.
- Support has been added for the psql utility to look up information about settings (pg_settings) using the "\dconfig" command.
- A built-in pg_walinspect extension has been proposed that allows you to inspect the contents of files with WAL records using SQL queries.
- Removed Python 2 support in PL/Python
- Removed deprecated "exclusive backup" mode.
- The possibility of parallel execution of queries with the expression "SELECT DISTINCT" is implemented.
Finally it is worth mentioning that updates for the new branch will be released for five years until November 2027. If you are interested in knowing more about it, you can consult the details in the following link