PostgreSQL 12 boosts open source database performance
Widely used open source PostgreSQL database platform gets a major update providing users with new SQL query capabilities for JSON and improved performance.
Performance gains are among the key highlights of the latest update of the open source PostgreSQL 12 database.
PostgreSQL 12 became generally available Oct. 3, providing users of the widely deployed database with multiple enhanced capabilities including SQL JSON query support and improved authentication and administration options. The PostgreSQL 12 update will potentially affect a wide range of use cases in which the database is deployed, according to Noel Yuhanna, an analyst at Forrester Research.
"Organizations are using PostgreSQL to support all kinds of workloads and use cases, which is pushing the needs for better performance, improved security, easier access to unstructured data and simplified deployments," Yuhanna said. "To address this, PostreSQL12 improves performance by improving its indexing that requires less space and has better optimization to deliver faster access."
The latest PostgreSQL12 focuses on performance and administration enhancements that are critical for organizations, especially as they look at building larger and more mission-critical databases, Yuhanna added. Over the past few years, PostgreSQL has been gaining significant momentum, especially with cloud deployments. Today, AWS, Google and Microsoft all support PostgreSQL.
"We estimate that 35 percent of large companies are using PostgreSQL to support some of their applications. However, with cloud providers extending their support with PostgreSQL, we are likely to see the adoption growing to over 50% with large companies by 2023," Yuhanna said.
PostgreSQL 12 Features
One of the new features in PostgreSQL 12 is the ability to execute SQL queries over JSON, making it simpler to build modern applications. Before PostgreSQL 12, PostgreSQL provided a nonstandard syntax to introspect and search over JSON documents, explained Jonathan Katz, a major contributor to the PostgreSQL Project.
PostgreSQL12 improves performance by improving its indexing that requires less space and has better optimization to deliver faster access.
Noel YuhannaAnalyst, Forrester Research
In PostgreSQL 12, support for SQL/JSON path expressions now provides an additional way to introspect and search for data within JSON documents stored in PostgreSQL.
"Users can now perform inequality comparisons, arithmetic operations, search over values using regular expressions and perform mathematical operations such as finding an absolute value and others," Katz said. "SQL/JSON has the added benefit of being a part of the SQL standard, which furthers PostgreSQL's compliance."
Performance optimizations
Performance is improved in multiple ways, including optimized B-tree indexes, which reduce space utilization for frequently modified indexes. Just-in-time compilation using the LLVM compiler is now enabled by default, further improving query performance.
"What you often see with performance improvements in PostgreSQL is the PostgreSQL community trying to build an even better product for each release," Katz said.
The next release of PostgreSQL 12 will be version 12.1 on Nov. 14, which is a scheduled bug fix release. PostgreSQL 13 will follow in 2020.
"The community is still in the early development stages of PostgreSQL 13, but there is already substantial work on a project to bring native transparent data encryption functionality to PostgreSQL, in addition to continuing the work on creating pluggable storage engines for tables," Katz said. "The feature freeze for PostgreSQL 13 isn't scheduled until at least the end of March, so there should be plenty of more features to come."
