PostgreSQL 9.2 Scales Open Source Database Operations

The open source PostgreSQL 9.2 database is being officially released today, providing database administrators with new levels of scalability and performance.

"This year for the first time thanks to HP, EnterpriseDB and the University of California at Berkeley, we've been able to test PostgreSQL on bigger machines," Josh Berkus PostgreSQL core team member told DatabaseJournal. "These are machines with half a terabyte of RAM and 64 cores."

As a result, the new PostgreSQL 9.2 release is now able to scale far beyond what the PostgreSQL 9.1and prior releases were able to accomplish.

With PostgreSQL 9.1 the top end of scalability was approximately 24 cores and 75,000 transactions per second. In contrast  PostgreSQL 9.2 can now scale up to 64 cores and 350,000 transactions per second.

"We're talking really dramatic performance differences rather than the usual ten or twenty percent better," Berkus said.

The performance improvements aren't limited just to massive 64 core machines, but will also offer speed benefits to users of 8 core machines as well. In general, Berkus noted that any user that is running a large number of read queries in parallel will benefit.

Index Only Scan

One of the ways that PostgreSQL 9.2 improves performance is by way of something known as index only scans.

"What that means is if all the data that you need for your query is in the index it's possible for the query executor to not read the base table at all," Berkus said. "The advantage is that the index is usually much smaller than the base table."

JSON

Another key improvement in PostgreSQL 9.2 is native JSON support. Berkus said that it is clear that web developers are now interested in storing and retrieving data in the JSON format, which is why it's important that PostgreSQL handles it natively.

"PostgreSQL is inefficient as just a filesystem, so users would switchover to document databases," Berkus said. "So what we've added is a JSON data type to evaluate the data, and we've added a whole set of built-in functions to get your data back out of JSON."

SE-PostgreSQL

One of the major improvements in the PostgreSQL 9.1 release was the integration of the SE Postgres project. SE (Security Enhanced) provide additional security controls on top of the open source database. With the PostgreSQL 9.2 release, there are some additional security enhancements to make SE Postgres even more secure.

"One of the big things PostgreSQL 9.2 that we couldn't solve in PostgreSQL 9.1 is something called view leakage," Berkus said. "SE implements row level security and one of the problems is that there are lots of ways to get to a row."

The problem is that a clever user could potentially trigger query errors that would then reveal data that is supposed to be in a hidden column. That type of leakage has now been mitigated in PostgreSQL 9.2.

Moving forward, there is a long list of new features that could potentially land in next year's PostgreSQL 9.3 release. Among the big new items that Berkus highlighted is support for federated databases as well as more replication improvements.

Sean Michael Kerner is a senior editor at InternetNews.com, the news service of the IT Business Edge Network, the network for technology professionals Follow him on Twitter @TechJournalist.