On May 19, 2022, the PostgreSQL community announced the beta version of PostgreSQL 15, now available for download.
Below, we highlight the main news of this version, according to the document published by the community.
Development
This release introduces MERGE, a standard SQL command for conditionally performing write operations ( INSERT, UPDATE, or DELETE) on data. Prior to this release, the same behavior could be achieved using stored procedures or, to a limited extent, with INSERT ... ON CONFLICT. With PostgreSQL 15, developers can write simple, expressive queries to choose the appropriate data modification action to take.
PostgreSQL 15 builds on its existing support for the SQL/JSON path language, including more functions like SQL/JSON constructors, query/introspection functions, and the ability to convert JSON data into a table.
PostgreSQL 15 adds more regular expression functions, including regexp_count, regexp_instr, regexp_like, and regexp_substr. the range_agg function, introduced in PostgreSQL 15 to aggregate ranged types into multirange types, now also supports multirange aggregation types.
Performance
This release includes significant acceleration for sorting larger datasets. In particular, these are datasets that exceed the work_memparameter. Introduced parallelization for SELECT DISTINCT and performance improvements for functions that use row_number(), rank() and count().
Applications that use the PostgreSQL external data wrapper to manage data on remote PostgreSQL servers can now allow transactions to be committed in parallel.
There are also several performance improvements for queries involving partitioned tables.
The PostgreSQL system and TOAST tables, used to store data larger than a single page (8kB), can now utilize index deduplication and benefit from smaller indexes and faster lookups.
The psql \copy command, used for bulk loading data, also has performance improvements in PostgreSQL 15.
Backups, archiving and compression
Building on the previous version that introduced LZ4 compression, PostgreSQL 15 adds support for Zstandard (zstd) compression to various components.
pg_basebackup, a utility used to make full backups of a PostgreSQL cluster, now supports server-side compression using Gzip, LZ4, or Zstandard compression. pg_basebackupclient can now also decompress backups that use LZ4 and Zstandard compression.
Write-ahead (WAL) log files can now be compressed using LZ4 and Zstandard compression via the wal_compression configuration parameter. In addition, PostgreSQL 15 also adds the recovery_prefetch option which can help speed up recovery operations by prefetching data blocks. This release adds a new module called pg_walinspect that allows you to inspect information about WAL files directly from SQL.
PostgreSQL 15 allows you to perform WAL archiving without running a shell command specifying an archive_library file. An example WAL archive library can be found in the basic_archive module. Additionally, extensions can now define their own WAL resource managers that allow them to work with logical replication.
Logical replication
PostgreSQL 15 features row filtering and column filtering for logical replication. Additionally, users can now conveniently specify to publish all tables in a schema, where previously it was only possible to do this for all tables in a database.
This release also adds more support for handling conflicts. A subscriber can now specify disable_on_error to automatically stop logical replication on error. PostgreSQL 15 makes it possible to skip applying changes using the ALTER SUBSCRIPTION ... SKIP command.
Management
PostgreSQL 15 introduces the jsonlog log format, allowing PostgreSQL logs to be consumed by many programs that perform structured log aggregation and analysis. PostgreSQL 15 now, by default, logs checkpoints and autovacuum operations that are slow.
The psqlclient now has a command called \dconfig to inspect the values of configuration parameters. By default, \dconfig displays any configuration parameters that are not set to their default value.
PostgreSQL 15 now stores all server-level statistics in shared memory. The server no longer runs a statistics collector process.
The ALTER TABLE command can now modify the ACCESS METHOD, which is used to define the storage system used by the table.
Safety
PostgreSQL 15 introduces new standards when databases are created within a PostgreSQL cluster. When a new database is created, users outside the database owner (represented by pg_database_owner) and superusers can no longer create objects in the default publicschema.
PostgreSQL 15 adds support for "security invoker views", which uses the privileges of the user executing the query rather than the user who created the view.
Unprivileged users in PostgreSQL 15 can now be granted privileges to change server variables via SET and ALTER SYSTEM.
For more information, access the product documentation.
Documentation: https://www.postgresql.org/docs/15/release-15.html
Download: https://www.postgresql.org/download/