PostgreSQL Global Development Group has announced the release of PostgreSQL 15 – one of the most advanced open source database system. It is characterized by an even better efficiency, security and also great abilities of creating backups, restoring data and logical replication. Moreover, the new release facilitates the management of heavy workloads – both in local and scattered deployments. It also facilitates the work of developers thanks to the addition of the popular MERGE command and adds more options of observing the state of the database.
PostgreSQL, developed for more than 25 years by the global community of programmers, is prized for its stability, efficiency, and wide array of functions. In recent years, it has become the most frequently adopted open-source database software in Poland and Europe.
says Jonathan Katz,
PostgreSQL Core Team memberPostgreSQL 15 demonstrates that as a result of the open development of software we can provide our users with a database, which is perfect for creating applications and safe for their critical data.
In October 2022, like each Fall, the next major version of PostgreSQL 15 came to light. We present the changes introduced in the latest version below.
Improvement of sort performance and compression
In the latest release of PostgreSQL 15 the sorting algorithms have been improved – both in memory and on the disk. The performance test results indicate the growth of performance between 25 and 400 percent, depending on the type of the sorted types of data.
The usage of row_number(), rank(), dense_rank() and count() as window functions also contributes to the improved performance. As a result, the queries using SELECT DISTINCT can now be executed simultaneously.
The abilities of async remote queries, already introduced in PostgreSQL 14, have also been enhanced. Currently, the foreign data wrapper, which is postgres_fdw also handles the validation of asynchronous commits.
The improvement in performance of PostgreSQL 15 also encompasses the archiving and creating backups. In the newest version, the support for the LZ4 and Zstandard (zstd) compression for the write-ahead log files (WAL) has been added, which can provide benefits in terms of space and efficiency.
On some operating systems, PostgreSQL 15 adds the support for prefetch pages referenced in WAL, which has a positive effect on the time of data recovery.
The pg_basebackup command built-in PostgreSQL 15 now supports the compression of the backup files on the server side with the ability to choose from gzip, LZ4 or zstd. PostgreSQL 15 also gives the ability to use custom modules for archiving, which eliminates the costs associated with the usage of the shell commands.
Facilities for programmers
In PostgreSQL 15 the new standard MERGE command has been introduced. It gives the ability to write the conditional SQL commands, which, depending on the context, can replace the INSERT, UPDATE and DELETE command with a single command, completely eliminating the necessity of writing the MERGE instruction. Thanks to that, the deployment of PostgreSQL is in line with the SQL standard, which facilitates the migration from Microsoft SQL Server or Oracle Database.
The other new functions have been added, which allow the use of regular expressions to inspect the strings: regexp_count(), regexp_instr(), regexp_like() and regexp_substr(). PostgreSQL 15 also expands the range_agg function with the aggregation of the multirange data types, which have been implemented in the last release.
PostgreSQL 15 can also create views, which retrieve the data using the privileges of the invoking person, not the creator of the view. It is possible thanks to the security_invoker option, which provides a new extra layer of protection, giving the user who invokes the views the proper privileges to work with the underlying data.
Increased security of the databases
PostgreSQL 15 gives the ability to grant the privileges based on the roles for the SET and ALTER SYSTEM commands, which have previously required the superuser privileges. Another new addition is the fact that a party, lacking the superuser privileges, can now be an owner of a logical replication.
More possibilities of logical replication
A greater flexibility in managing the logical replication is another advantage of PostgreSQL 15. In the newest release, the functions of row filtering and column lists for publishers have been introduced, thanks to which the user can now select the replication of the data subset from the table.
PostgreSQL 15 offers functions which facilitate the conflict management, including the ability to omit the reproduction of the conflict transition and an automatic disabling of subscription in the case of detecting an error. The discussed version also contains the support for the usage of two-phase commit (2PC) validation with the logical replication.
New options of logs and configuration
The newest release of PostgreSQL introduces a new format of logs: jsonlog. This format can be processed in the structured logging systems.
The administrators of databases can now grant the users with privilege to change the configuration parameters on the server level, thus gaining greater flexibility in managing the PostgreSQL configuration. In addition, the users gain the ability to search the information on configuration with the help of the \config command from the command-line tool – psql.
Remaining, worth mentioning updates in PostgreSQL 15
- PostgreSQL server-level statistics are gathered in the shared memory, which eliminates both the process of the statistics collector and the temporary saving of this data on the drive.
- Inclusion of the ICU collation as a default option for the cluster or a single database.
- Built-in pg_walinspet add-on, which provides the user with an insight into the content of write-ahead the log file directly from the SQL interface.
- CREATE permission is available only to the owner of the database from the public or default schema.
Detailed information on the changes in PostgreSQL 15 are available in the official documentation.
PostgreSQL with a professional support for companies
PostgreSQL is one of the most advanced open source database system in the world – reliable, stable, scalable and secure. However, if we want to fully utilize this database as a component of mission-critical systems, it is worth being 100% sure that the data and IT teams are sufficiently supported and the data protected.
In that situation, products available in Linux Polska’s offer may be the right solution.
- EDB Community 360 plan, which contains:
- local 24/7 technical support for PostgreSQL and other open source utilities such as i.a. pgAgent, Pgpool, pg_catcheck, Barman, PostgreSQL Container Image, CloudNativePG Operator – see the full list of utilities;
- counseling and support in deploying PostgreSQL, including PostgreSQL in Kubernetes;
- additionally, in the price of EDB community 360, the CloudNativePG operator is included, which enables the management of the full lifecycle of the PostgreSQL database in containers.
- EDB Standard plan, which contains:
- all of the components of the EDB Community 360 plan;
- proprietary EDB utilities for:
- management (Postgres Enterprise Manager – PEM, Failover Manager – EFM, Backup and Recovery Manager – Barman);
- integration (Replication Server, Data Adapters);
- migration (Migration Toolkit, Migration Portal).
EDB products from the offer of Linux Polska are especially worth considering if you:
- administer large and complex datasets;
- want to migrate part or all of your resources from the legacy-type, closed solutions to an open source database;
- need an efficient database with support, to securely build and maintain applications of critical importance;
- want to create modern applications based on the open standards;
- plan to modernize the data infrastructure.
Linux Polska, as an authorized partner and distributor of EDB, offers software subscriptions, consulting, deployment and maintenance services, as well as authorized training and certificates.
