Skip to main content

PostgreSQL 18 adds asynchronous I/O to improve performance

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.



from SD Times https://ift.tt/Xrj2YLl
Labels:

Comments

Post a Comment

Popular posts from this blog

A guide to data integration tools

CData Software is a leader in data access and connectivity solutions. It specializes in the development of data drivers and data access technologies for real-time access to online or on-premise applications, databases and web APIs. The company is focused on bringing data connectivity capabilities natively into tools organizations already use. It also features ETL/ELT solutions, enterprise connectors, and data visualization. Matillion ’s data transformation software empowers customers to extract data from a wide number of sources, load it into their chosen cloud data warehouse (CDW) and transform that data from its siloed source state, into analytics-ready insights – prepared for advanced analytics, machine learning, and artificial intelligence use cases. Only Matillion is purpose-built for Snowflake, Amazon Redshift, Google BigQuery, and Microsoft Azure, enabling businesses to achieve new levels of simplicity, speed, scale, and savings. Trusted by companies of all sizes to meet...
1 comment
Read more

2022: The year of hybrid work

Remote work was once considered a luxury to many, but in 2020, it became a necessity for a large portion of the workforce, as the scary and unknown COVID-19 virus sickened and even took the lives of so many people around the world.  Some workers were able to thrive in a remote setting, while others felt isolated and struggled to keep up a balance between their work and home lives. Last year saw the availability of life-saving vaccines, so companies were able to start having the conversation about what to do next. Should they keep everyone remote? Should they go back to working in the office full time? Or should they do something in between? Enter hybrid work, which offers a mix of the two. A Fall 2021 study conducted by Google revealed that over 75% of survey respondents expect hybrid work to become a standard practice within their organization within the next three years.  Thus, two years after the world abruptly shifted to widespread adoption of remote work, we are dec...
Post a Comment
Read more

Olive and NTT DATA Join Forces to Accelerate the Global Development and Deployment of AI Solutions

U.S.A., March 14, 2021 — Olive , the automation company creating the Internet of Healthcare, today announced an alliance with NTT DATA , a global digital business and IT services leader. The collaboration will fast track the creation of new healthcare solutions to transform the health experience for humans — both in the traditional healthcare setting and at home. As a member of Olive’s Deploy, Develop and Distribute Partnership Programs , NTT DATA is leveraging Olive’s open platform to innovate, build and distribute solutions to Olive’s customers, which include some of the country’s largest health providers. Olive and NTT DATA will co-develop new Loops — applications that work on Olive’s platform to provide humans real-time intelligence — and new machine learning and robotic process automation (RPA) models. NTT DATA and Olive will devote an early focus to enabling efficiencies in supply chain and IT, with other disciplines to follow. “This is an exciting period of growth at Olive, so...
Post a Comment
Read more