Postgres 18: Three times faster asynchronous queries and virtual columns
The Postgres team has released version 18, which includes speed improvements, more security and new practical functions for developers.
(Image: Quality Stock Arts/Shutterstock.com)
The new version of Postgres 18 brings a range of performance improvements and new functions, such as access to old values for INSERT or virtual columns for queries. For secure authentication, the database offers OAuth 2, and SHA-256 becomes mandatory for password hashing.
The most important new feature for accelerated queries is the asynchronous IO subsystem (AIO), which has “proven up to threefold performance improvements when reading from memory,” according to the announcement. AIO accelerates read-ahead processes for which the database utilizes the corresponding mechanisms of the respective operating system. These mechanisms do not know all the specifics of a database and often cannot correctly predict which data will be required in the near future. AIO now makes several parallel asynchronous queries and thus accelerates read-ahead queries. AIO operations include sequential and bitmap heap scans as well as the VACUUM command.
Users can switch between AIO and the old synchronous system in the settings for io_method. Further speed gains result from skip scan searches with multi-column B-tree indices and improved execution of table joins and hash joins. Hardware acceleration is now available for ARM NEON and SVE CPU.
The automatic statistics are not lost from Postgres 18 onwards, even with large updates, so that the system knowledge built up by the statistics is retained. This guarantees improved performance values beyond an update.
Virtual columns and access to old values
With Postgres 18, developers can now create virtual columns that process queries without the database saving them. This will be the default option in the future. Users will also be able to logically replicate saved tables in the future.
Videos by heise
Many developers will be pleased that they now also have access to old (OLD) values for the INSERT, UPDATE, DELETE and MERGE commands and not just new (NEW) values. You can also use random UUIDs with uuidv7(), which can be sorted using timestamps and allow better caching.
Furthermore, remote tables based on local schemas can be easily created with the command CREATE FOREIGN TABLE ... LIKE command.
More security with OAuth 2 and SHA-2
Postgres 18 supports OAuth 2 for user authentication. Other security functions include SSL validation with FIPS and a parameter ssl_tls13_ciphers for TLS 1.3. In addition, MD 5 for password hashing is obsolete and will soon be deactivated. Postgres now requires SCRAM-SHA-256
Details and further new features can be found in the announcement and the release notes.
(who)
