PostgreSQL 18 has been launched, with a number of new options like asynchronous I/O, higher post-upgrade efficiency, and improved textual content processing.
Asynchronous I/O permits PostgreSQL to challenge a number of I/O requests on the similar time reasonably than ready for one to complete earlier than beginning the following. In response to the PostgreSQL crew, this improves general throughput, and has resulted in efficiency positive aspects of as much as 3x in some situations.
Beforehand, PostgreSQL used working system readahead mechanisms for knowledge retrieval, however because the working system didn’t have perception into database-specific entry patterns, it couldn’t all the time anticipate what knowledge can be required, leading to suboptimal efficiency throughout many workloads. Asynchronous I/O was created to handle that limitation, the crew defined.
This launch additionally introduces the power to maintain planner statistics by main model upgrades, whereas beforehand they wouldn’t carry over. This helps upgraded clusters attain anticipated efficiency extra shortly after upgrades.
Textual content processing was additionally improved on account of the brand new PG_UNICODE_FAST collation that gives full Unicode semantics for case transformations. PostgreSQL 18 additionally provides assist for making LIKE comparisons on textual content that makes use of a nondeterministic collation, which simplifies complicated sample matching. Moreover, full textual content search now makes use of the default collation supplier of a collation as a substitute of libc.
Developer expertise was a spotlight of this launch as nicely, with additions like digital generated columns that compute values at question occasions, temporal constraints for PRIMARY KEY and UNIQUE constraints in WITHOUT OVERLAPS clauses, and the power to create the schema definition of a international desk utilizing the definition of a neighborhood desk utilizing the CREATE FOREIGN TABLE … LIKE command.
Efficiency enhancements in PostgreSQL 18 embody skip scan lookups on multicolumn B-tree indexes to enhance execution time on queries, indexing to optimize queries that use OR circumstances in a WHERE, and enhancements to how PostgreSQL plans and executes desk joins. This launch additionally helps {hardware} acceleration, together with assist for ARM NEON and SVE CPU intrinsics for the popcount perform.
Different updates on this launch embody assist for OAuth 2.0, assist for reporting logical replication for write conflicts in logs, and proactive freezing of pages throughout vacuums.
“The efforts of the worldwide open supply neighborhood form each PostgreSQL launch and assist ship options that meet customers the place their knowledge resides,” mentioned Jonathan Katz, a member of the PostgreSQL core crew. “PostgreSQL 18 builds on the challenge’s lengthy, wealthy historical past of delivering a dependable and sturdy knowledge administration expertise, whereas persevering with to increase the workloads it could actually assist.”
A full checklist of updates might be discovered within the launch notes right here.
