After a year of development, a new stable branch of the PostgreSQL database management system 15 has been released. Updates for this new branch will be issued over the next five years until November 2027.
Key innovations:
- Support for the SQL command 'MERGE' has been added, reminiscent of the 'INSERT ... ON CONFLICT' expression. MERGE allows you to create conditional SQL statements that combine INSERT, UPDATE, and DELETE operations in a single expression. For example, you can use MERGE to merge two tables by inserting missing records and updating existing ones. MERGE INTO customer_account ca USING recent_transactions t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);
- Significant improvements have been made to the data sorting algorithms in memory and on disk. Depending on the data type, tests show sorting speed increases ranging from 25% to 400%.
- The performance of window functions using row_number(), rank(), dense_rank(), and count() has been accelerated.
- The ability to execute queries in parallel with the 'SELECT DISTINCT' expression has been implemented.
- The Foreign Data Wrapper (postgres_fdw) mechanism for connecting external tables now supports asynchronous commits in addition to the previously added capability for asynchronous processing of requests to external servers.
- The option to use LZ4 and Zstandard (zstd) algorithms for compressing transaction WAL logs has been added, which can enhance performance and save disk space under certain loads. To reduce recovery time after a crash, support for preemptive page fetching mentioned in the WAL log has been added.
- The pg_basebackup utility now supports file compression for backups on the side, using methods like gzip, LZ4, or zstd. The ability to use custom archiving modules has also been provided, eliminating the need to run shell commands. serverA series of new functions for string processing using regular expressions has been added: regexp_count(), regexp_instr(), regexp_like(), and regexp_substr().
- The range_agg() function now supports aggregation of multirange types.
- A security_invoker mode has been added, allowing the creation of views that execute with the rights of the calling user rather than the view's creator.
- A security_invoker mode has been added, allowing the creation of views that execute with the rights of the calling user rather than the view's creator.
- Logical replication has implemented support for row filtering and column list specification, allowing the sender to extract a subset of data from a table for replication. Additionally, the new version simplifies conflict management, such as allowing the skipping of conflicting transactions and automatically disabling subscriptions upon detecting an error. Two-phase commits (2PC) are permitted in logical replication.
- A new log format has been introduced — jsonlog, which stores information in a structured way using JSON format.
- The administrator is provided with the ability to delegate specific powers to users for changing certain parameters. server configuration PostgreSQL.
- The psql utility has added support for searching information about settings (pg_settings) using the command "\dconfig."
- Shared memory is used to gather server statistics, which eliminated the need for a separate statistics collection process and periodic state writes to disk.
- The default use of ICU locales "ICU Collation" has been made possible; previously, only libc locales could be used as the default locale.
- An embedded extension pg_walinspect has been proposed, allowing inspection of the contents of WAL log files using SQL queries.
- For the public schema, all users except for the database owner have had their permissions to execute the CREATE command revoked.
- Support for Python 2 has been removed in PL/Python. The deprecated exclusive backup mode has been eliminated.
Note: A webinar discussion on the changes in the new version will take place from 19:00 to 20:00 (MSK) with Pavel Luzanov (Postgres Professional). For those unable to join the live stream, the recording of Pavel's June talk "PostgreSQL 15: MERGE and more" at PGConf.Russia is available.
Source: opennet.ru
