PostgreSQL 15 Beta 1 Released!

PostgreSQL 15 Feature Highlights -

Developer Experience

This release introduces?MERGE , a SQL standard command for conditionally performing write operations (INSERT,?UPDATE, or?DELETE) on data. Prior to this release, the same behaviour could be accomplished either using stored procedures or, on a limited-basis, with?INSERT ... ON CONFLICT .

PostgreSQL 15 builds on its existing support for the SQL/JSON path language by including more standard?SQL/JSON functions . These include?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 for aggregating?range?data types ?into?multirange?types, now supports aggregating?multirange?types too.

Performance

This release includes a significant speedup for sorting data when sorting over larger data sets. In particular, these are data sets that exceed the?work_mem?parameter.

The performance gains of PostgreSQL 15 extend to a variety of query types. This includes the introduction of parallelization for?SELECT DISTINCT ?statements and improvements in performance to?window functions ?that use?row_number(),?rank(), and?count(). Applications that use the?PostgreSQL foreign data wrapper ?postgres_fdw ?to manage data on remote PostgreSQL servers can now enable?transactions to be committed in parallel . There are also several performance enhancements for queries involving tables with partitions.

PostgreSQL system and?TOAST ?tables, used for storing data that is larger than a single page (8kB), can now utilize?index deduplication ?and benefit from smaller indexes and faster lookups.

The psql?\copy?command, used to bulk load data, also has performance improvements in PostgreSQL 15.

Backups, Archiving, and Compression

PostgreSQL 15 adds support for Zstandard (zstd) compression to various components.

pg_basebackup , a utility used to take full backups of a PostgreSQL cluster, now supports server-side compression using Gzip, LZ4, or Zstandard compression. The?pg_basebackup?client can now also decompress backups that use LZ4 and Zstandard compression.

Write-ahead log (WAL) files can now be compressed using both LZ4 and Zstandard compression through the?wal_compression ?configuration parameter. Additionally, PostgreSQL 15 also adds the?recovery_prefetch ?option that can help speed up recovery operations by prefetching data blocks. This release adds a new module called?pg_walinspect ?that lets you inspect information about WAL files directly from SQL.

PostgreSQL 15 lets you perform WAL archiving without running a shell command by specifying an?archive_library . An example WAL archiving library can be found in the?basic_archive ?module. Additionally, extensions can now define their own WAL resource managers that lets them work with logical replication.

Logical Replication

PostgreSQL 15 introduces both?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 an error. PostgreSQL 15 makes it possible to skip applying changes using the?ALTER SUBSCRIPTION ... SKIP ?command.

Administration

PostgreSQL 15 introduces the?jsonlog?format for logging . This allows 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?psql ?client now has a command called?\dconfig?for inspecting the values of configuration parameters. By default,?\dconfig?displays any configuration parameter that is not set to its 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 a table's?ACCESS METHOD, which is used to set the storage system used by the table.

Ref: https://www.postgresql.org/about/news/postgresql-15-beta-1-released-2453/