carloscastilla - Fotolia

News Stay informed about the latest enterprise technology news and product updates.

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."

Dig Deeper on Database management systems (DBMS)

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

Is your organization using PostgreSQL on premises or in the cloud? If so, how?
I can't disclose details as my company sees this as a competitive advantage, but Oracle should be very worried.  I work in a Fortune 100 financial services company- you know, the kind that is highly regulated.  As little as 4 years ago, when planning development, you'd be laughed out of the conference room if you sneezed the words "Open Source Database".

But now it's different.  The default is to prefer Open Source software- and yes, we have specific tooling in place for Postgres development.  Not only is the default to prefer Open Source, but you had better have a VERY good, airtight business case if you need to use a commercial alternative.  And, as shocked as I am to say this, "having a third party whose neck you can strangle or on whom you can place blame during outages" is NOT a valid business case.

Not only are we promoting OSS as a default, and requiring good business reasons to use non-OSS... but we're promoting this everywhere.  From simple utilities that system administrators to use, all the way to the applications core to our business processes- the kind that require 5 9's of uptime.  Most, if not all, of these core applications have plans being developed (or being executed!!) to move off of Oracle and on to Postgres.  This includes leaning on vendors whose products are too complex to move to cloud, in order to convince them to support databases other than Oracle and SQL-Server.

It's a good time to be an expert in the Open Source database field...