The new version of PostgreSQL 15 has been released. What are the new features

PostgreSQL 15 Based on the performance improvement of the latest version, it has achieved significant benefits in managing workloads in local and distributed deployments, including improved sequencing. This version improves the developer's experience by adding the popular MERGE command, and adds more functions for observing the database status. Jonathan Katz, a member of the core team of PostgreSQL, said: "The PostgreSQL developer community continues to build functions to simplify running high-performance data workloads and improve the developer experience." "PostgreSQL 15 emphasizes how to provide our users with a database that is very suitable for application development and safe for their key data through open software development. ”

PostgreSQL is an innovative data management system known for its reliability and robustness. It has benefited from more than 25 years of open source development by the global developer community and has become the preferred open source relational database for organizations of all sizes.

Improved sorting performance and compression

In this latest version, PostgreSQL has improved its sorting algorithm in memory and on disk. The benchmark shows that the sorting is accelerated by 25% - 400% according to the data type. In PostgreSQL 15, using row_number(), rank(), dense_rank(), and count() as window functions also has performance advantages. The query SELECT DISTINCT using using can now be executed in parallel.

Based on the work of previous PostgreSQL versions, asynchronous remote queries are allowed. PostgreSQL external data wrappers and postgres_fdw now support asynchronous submission.

The performance improvements in PostgreSQL 15 extend to its archiving and backup facilities. PostgreSQL 15 adds support for compression of LZ4 and ZStandard (zstd) to pre write log (WAL) files, which can have both space and performance advantages for some workloads. On some operating systems, PostgreSQL 15 adds support for prefetch pages referenced in WAL to help speed up recovery time. PostgreSQL's built-in backup command pg_basebackup now supports server-side compression of backup files. You can select gzip, LZ4, and zstd. PostgreSQL 15 includes the ability to use custom modules for archiving, which eliminates the overhead of using shell commands.

Expressive developer functions

PostgreSQL 15 contains the SQL standard MERGE command. MERGE allows you to write conditional SQL statements that can contain INSERT, UPDATE, and operations in a single statement. DELETE

This latest version adds new functions that use regular expressions to check strings: regexp_count(), regexp_insr(), regexp_like(), and regexp_substr() PostgreSQL 15 also extends the range_agg function to aggregate multi range data types, which was introduced in the previous version.

PostgreSQL 15 allows users to use the permissions of the caller rather than the view creator to create views that query data. This option named security_invoker adds an additional layer of protection to ensure that the view caller has the correct permissions to use the basic data.

More logical replication options

PostgreSQL 15 provides greater flexibility for managing logical replication. This version introduces row filtering and column list for publishers, allowing users to choose to copy data subsets in tables. PostgreSQL 15 adds the ability to simplify conflict management, including the ability to skip replaying conflict transactions and automatically disable subscriptions when errors are detected. This version also supports the combination of two-phase commit (2PC) and logical copy.

Logging and configuration enhancements

PostgreSQL 15 introduces a new logging format: jsonlog This new format uses the defined JSON structure to output log data, allowing PostgreSQL logs to be processed in the structured log system.

This version gives database administrators greater flexibility in how users manage PostgreSQL configurations, and increases the ability to grant users the right to change server level configuration parameters. \Dconfig In addition, users can now use the commands in the command psql line tool to search for information about the configuration.

Other significant changes

PostgreSQL server level statistics are now collected in shared memory, eliminating the statistics collection process and writing this data to disk regularly.

PostgreSQL 15 makes it possible for the ICU collation to become the default collation of a cluster or a single database.

This version also adds a new built-in extension. pg_walinsispect allows users to check the contents of the pre written log file directly from the SQL interface.

PostgreSQL 15 also revokes the permissions of all users except the database owner of CREATE from (or default) mode. public

PostgreSQL 15 removes the long abandoned "exclusive backup" mode and Python 2 support from PL/Python.