After a year of development, a new stable branch of the PostgreSQL 15 DBMS has been published. Updates for the new branch will be released over five years until November 2027.
Main innovations:
- Added support for the SQL command "MERGE", which resembles the expression "INSERT ... ON CONFLICT". MERGE allows you to create conditional SQL statements that combine INSERT, UPDATE, and DELETE operations into a single expression. For example, using MERGE, you can 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);
- Algorithms for sorting data in memory and on disk have been significantly improved. Depending on the type of data, tests show an increase in sorting speed from 25% to 400%.
- Window functions using row_number(), rank(), dense_rank() and count() have been speeded up.
- The possibility of parallel execution of queries with the expression “SELECT DISTINCT” has been implemented.
- The mechanism for connecting external tables Foreign Data Wrapper (postgres_fdw) implements support for asynchronous commits in addition to the previously added ability to asynchronously process requests to external servers.
- Added the ability to use LZ4 and Zstandard (zstd) algorithms to compress WAL transaction logs, which, under some workloads, can simultaneously improve performance and save disk space. To reduce recovery time after a failure, support for proactive retrieval of pages appearing in the WAL log has been added.
- The pg_basebackup utility has added support for server-side compression of backup files using the gzip, LZ4 or zstd methods. It is possible to use your own modules for archiving, allowing you to do without the need to run shell commands.
- A series of new functions have been added for processing strings using regular expressions: regexp_count(), regexp_instr(), regexp_like() and regexp_substr().
- The ability to aggregate multirange types (“multirange”) has been added to the range_agg() function.
- Added security_invoker mode, which allows you to create views that run as the calling user rather than the view creator.
- For logical replication, support has been implemented for filtering rows and specifying lists of columns, allowing on the sender's side to select a subset of data from the table for replication. In addition, the new version simplifies conflict management, for example, it is now possible to skip conflicting transactions and automatically disable a subscription when an error is detected. Logical replication allows the use of two-phase commits (2PC).
- A new log format has been added - jsonlog, which saves information in a structured form using the JSON format.
- The administrator has the ability to delegate individual rights to users to change certain PostgreSQL server configuration parameters.
- The psql utility has added support for searching information about settings (pg_settings) using the “\dconfig” command.
- The use of shared memory is ensured for accumulating statistics about the server's operation, which makes it possible to get rid of a separate process of collecting statistics and periodically resetting the state to disk.
- The ability to use the default ICU locales “ICU Collation” has been provided; previously, only libc locales could be used as the default locale.
- A built-in extension pg_walinspect has been proposed, which allows you to inspect the contents of files with WAL logs using SQL queries.
- For the public schema, all users, with the exception of the database owner, have had their authority to execute the CREATE command revoked.
- Support for Python 2 has been removed in PL/Python. The obsolete exclusive backup mode has been removed.
Addition: From 19:00 to 20:00 (MSK) there will be a webinar discussing the changes in the new version with Pavel Luzanov (Postgres Professional). For those who are unable to join the broadcast, recording of Pavel’s June report “PostgreSQL 15: MERGE and more” at PGConf.Russia is open.
Source: opennet.ru