Getty Images/iStockphoto

PostgreSQL 14 improves open source database performance

The open source database has extended its JSON unstructured data support and enhanced its stored procedures capabilities to give users more database power.

The PostgreSQL Global Development Group today released the open source PostgreSQL 14 database, providing users of the widely deployed relational database with new features that improve performance and data queries.

The new postgreSQL release is the first major update for the database since the PostgreSQL 13 version that debuted in October 2020.

Among the improvements in the new database is enhanced JSON (JavaScript Object Notation) unstructured data support. PostgreSQL 14 also introduces multirange query support that enables developers to have more complex queries across data ranges.

PostgreSQL 14 improves scalability and speed performance. These are areas of particular interest to many users, including media management and analytics vendor Comscore.

Tim Pitzer, senior vice president of IT and operations at Comscore, explained that his firm uses PostgreSQL to track performance information and metadata for most movies viewed anywhere in the world.

"Our transactional PostgreSQL database contains the information and analytic intelligence used by motion picture executives around the globe to plan and monitor the release of virtually every movie ever made," Pitzer said. "As the core of our unified data warehouse, we rely on the speed and solid reliability of PostgreSQL for our business."

How the PostgreSQL update boosts database performance

PostgreSQL 14 introduces a series of performance-related improvements across database operations.

In particular, PostgreSQL 14 improves active and idle connection scaling, which Pitzer said will be a big help for Comscore. Comscore's application supports 1,000 concurrent connections during peak times, and Pitzer said he anticipates that the new connection management features will likely lower critical CPU usage during these crucial times.

Pitzer also highlighted new features in PostgreSQL 14 aimed at assisting query and memory troubleshooting. One such memory feature is pg_backend_memory_contexts, which show users how memory is being used by the database.

PostgreSQL users are going to be happy that diagnosing traffic in-flight [data in motion] has gotten quite a bit easier in PostgreSQL 14.
Kirk RoybalPostgreSQL database reliability engineer, Instaclustr

The improvements to database operations visibility are also a highlight of the PostgreSQL 14 updates for Kirk Roybal, a PostgreSQL database reliability engineer at open source as-a-service vendor Instaclustr.

"PostgreSQL users are going to be happy that diagnosing traffic in-flight [data in motion] has gotten quite a bit easier in PostgreSQL 14," Roybal said. "Several components that previously required you to use extensions to gain that visibility have now been merged into the core."

Roybal noted that overall performance is getting a boost with more concurrent operations and the new ability of the database to take advantage of parallel background processes.

Screenshot of PostgreSQL 14
PostgreSQL 14 introduces new multirange query support that aims to enable developers to more easily write complex queries.

Aiming at unstructured data and stored procedures

From an application development perspective, the support for JSON in PostgreSQL 14 also intrigues Umair Shahid, head of PostgreSQL at open source database services provider Percona.

Shahid noted that while PostgreSQL has supported unstructured JSON data since version 9.2, it has required a unique syntax for retrieving data.

"In version 14, support for subscripts has been added, making it easier for developers to retrieve JSON data using a commonly recognized syntax," Shahid explained.

Another area of improvement in PostgreSQL 14 involves stored procedures. Developers are able to return data using multiple parameters in their stored procedures.

"This feature will be familiar to Oracle developers, and a welcome addition for folks trying to migrate from Oracle to PostgreSQL," Shahid said.

Dig Deeper on Database management

Business Analytics
Content Management