PostgreSQL 18 has been released, with several new features like asynchronous I/O, better post-upgrade performance, and improved text processing.
Asynchronous I/O allows PostgreSQL to issue multiple I/O requests at the same time rather than waiting for one to finish before starting the next. According to the PostgreSQL team, this improves overall throughput, and has resulted in performance gains of up to 3x in some scenarios.
Previously, PostgreSQL used operating system readahead mechanisms for data retrieval, but since the operating system didn’t have insight into database-specific access patterns, it couldn’t always anticipate what data would be required, resulting in suboptimal performance across many workloads. Asynchronous I/O was created to address that limitation, the team explained.
This release also introduces the ability to keep planner statistics through major version upgrades, whereas previously they wouldn’t carry over. This helps upgraded clusters reach expected performance more quickly after upgrades.
Text processing was also improved as a result of the new PG_UNICODE_FAST collation that provides full Unicode semantics for case transformations. PostgreSQL 18 also adds support for making LIKE comparisons on text that uses a nondeterministic collation, which simplifies complex pattern matching. Additionally, full text search now uses the default collation provider of a collation instead of libc.
Developer experience was a focus of this release as well, with additions like virtual generated columns that compute values at query times, temporal constraints for PRIMARY KEY and UNIQUE constraints in WITHOUT OVERLAPS clauses, and the ability to create the schema definition of a foreign table using the definition of a local table using the CREATE FOREIGN TABLE … LIKE command.
Performance improvements in PostgreSQL 18 include skip scan lookups on multicolumn B-tree indexes to improve execution time on queries, indexing to optimize queries that use OR conditions in a WHERE, and improvements to how PostgreSQL plans and executes table joins. This release also supports hardware acceleration, including support for ARM NEON and SVE CPU intrinsics for the popcount function.
Other updates in this release include support for OAuth 2.0, support for reporting logical replication for write conflicts in logs, and proactive freezing of pages during vacuums.
“The efforts of the global open source community shape every PostgreSQL release and help deliver features that meet users where their data resides,” said Jonathan Katz, a member of the PostgreSQL core team. “PostgreSQL 18 builds on the project’s long, rich history of delivering a reliable and robust data management experience, while continuing to expand the workloads it can support.”
A full list of updates can be found in the release notes here.
The post PostgreSQL 18 adds asynchronous I/O to improve performance appeared first on SD Times.
Source: Read MoreÂ
