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 identical time reasonably than ready for one to complete earlier than beginning the subsequent. In line with the PostgreSQL staff, this improves total throughput, and has resulted in efficiency positive factors 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 at all times anticipate what knowledge could be required, leading to suboptimal efficiency throughout many workloads. Asynchronous I/O was created to deal with that limitation, the staff defined.
This launch additionally introduces the flexibility to maintain planner statistics by way of 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 because of the brand new PG_UNICODE_FAST collation that gives full Unicode semantics for case transformations. PostgreSQL 18 additionally provides help for making LIKE comparisons on textual content that makes use of a nondeterministic collation, which simplifies advanced sample matching. Moreover, full textual content search now makes use of the default collation supplier of a collation as an alternative of libc.
Developer expertise was a spotlight of this launch as properly, with additions like digital generated columns that compute values at question instances, temporal constraints for PRIMARY KEY and UNIQUE constraints in WITHOUT OVERLAPS clauses, and the flexibility to create the schema definition of a overseas desk utilizing the definition of a neighborhood desk utilizing the CREATE FOREIGN TABLE … LIKE command.
Efficiency enhancements in PostgreSQL 18 embrace skip scan lookups on multicolumn B-tree indexes to enhance execution time on queries, indexing to optimize queries that use OR situations in a WHERE, and enhancements to how PostgreSQL plans and executes desk joins. This launch additionally helps {hardware} acceleration, together with help for ARM NEON and SVE CPU intrinsics for the popcount operate.
Different updates on this launch embrace help for OAuth 2.0, help for reporting logical replication for write conflicts in logs, and proactive freezing of pages throughout vacuums.
“The efforts of the worldwide open supply group form each PostgreSQL launch and assist ship options that meet customers the place their knowledge resides,” stated Jonathan Katz, a member of the PostgreSQL core staff. “PostgreSQL 18 builds on the challenge’s lengthy, wealthy historical past of delivering a dependable and strong knowledge administration expertise, whereas persevering with to broaden the workloads it could help.”
A full listing of updates could be discovered within the launch notes right here.