PostgreSQL 9.2 Database Delivers Improved Scalability, Performance

The latest version of the PostgreSQL 9.2 database ships with a list of enhancements, including JSON support as well as improved scalability and performance.

The PostgreSQL Global Development Group has delivered PostgreSQL 9.2, the latest release of the open-source database.
PostgreSQL 9.2 features advancements in vertical scalability and performance, so Postgres can handle increasingly large workloads. The new release also has high availability improvements that give database administrators (DBAs) more options for high availability (HA) configurations.

PostgreSQL is an open-source database system with a global community of thousands of users and contributors and dozens of companies and organizations using it. The PostgreSQL Project builds on more than 25 years of engineering, starting at the University of California, Berkeley. As an enterprise-class database, PostgreSQL offers features such as Multi-Version Concurrency Control (MVCC), point-in-time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a query planner/optimizer, and write-ahead logging for fault tolerance. Organizations such as NASA, the State Department, the Chicago Mercantile Exchange and Instagram use PostgreSQL for mission-critical applications.

"PostgreSQL 9.2 will ship with native JSON [JavaScript Object Notation] support, covering indexes, replication and performance improvements, and many more features," said Ines Sombra, lead data engineer at Engine Yard, in a statement. "We have been eagerly awaiting this release and will make it available in Early Access with this release by the PostgreSQL community."
Meanwhile, with the addition of linear scalability to 64 cores, index-only scans and reductions in CPU power consumption, PostgreSQL 9.2 gains improved scalability and developer flexibility for demanding workloads. Organizations like the U.S. Federal Aviation Administration and Heroku.com run applications on PostgreSQL, and HP has adopted it for its remote support software and to power its HP-UX/Itanium solutions.
Moreover, improvements in vertical scalability increase PostgreSQL's ability to more efficiently use hardware resources on larger servers. Advances in lock management, write efficiency, index-only access and other low-level operations allow the database engine to handle even larger-volume workloads, project officials said. The new release delivers up to 350,000 read queries per second, which is more than four times faster than the previous version. It also supports index-only scans for data warehousing queries up to two to 20 times faster. And it can handle up to 14,000 data writes per second, which is five times faster. Also, the addition of cascading replication enables users to run even larger stacks of horizontally scaled servers under PostgreSQL 9.2.

"NewsBlur, a social news reader, relies on Postgre for storing millions of sites and subscriptions. It has been solid and reliable for years," said Samuel Clay, founder of Newsblur.com, in a statement. "We're consistently on the bleeding edge-9.1 now, moving to 9.2 soon for the cascading replication alone-and it's been a pleasure since 8.4."
Version 9.2 also supports Range Types. Range Types enable developers to create better calendaring, scientific and financial applications. According to the PostgreSQL community, no other major SQL database supports this feature, which enables intelligent handling of blocks of time and numbers.
"PostgreSQL has always performed well on small to medium-sized servers and now it will scale to much bigger, enterprise-class hardware and deliver the kind of performance bigger companies require," Tom Kincaid, vice president of Global Services at EnterpriseDB, told eWEEK. "The enhancements in 9.2 also mean that PostgreSQL can be run on a whole new class of deployments, enabling global corporations to run significantly more demanding applications while saving money on their database licensing costs."
EnterpriseDB is a provider of enterprise-class products and services based on PostgreSQL. The company's Postgres Plus Advanced Server provides popular enterprise-class features found in the leading proprietary products but at a lower total cost of ownership across transaction-intensive as well as read-intensive applications, Kincaid said. EnterpriseDB also employs a number of industry thought leaders and PostgreSQL open-source community experts, including core team members Bruce Momjian and Dave Page.
With PostgreSQL 9.2, query results can be returned as JSON data types. Combined with the new PL/V8 JavaScript and PL/Coffee database programming extensions, and the optional HStore key-value store, users can now utilize PostgreSQL like a "NoSQL" document database, while retaining PostgreSQL's reliability, flexibility and performance, project members said.
"Native JSON support in PostgreSQL provides an efficient mechanism for creating and storing documents for Web APIs," said Taras Mitran, senior architect at IVC, a software development shop known for building call center management solutions and e-commerce Websites. "We use front-end libraries like jQuery to request tabular and tree-structured data; and the new features make it convenient and provide performance advantages in retrieving that data as JSON."

Darryl K. Taft covers the development tools and developer-related issues beat from his office in Baltimore. He has more than 10 years of experience in the business and is always looking for the next scoop. Taft is a member of the Association for Computing Machinery (ACM) and was named 'one of the most active middleware reporters in the world' by The Middleware Co. He also has his own card in the 'Who's Who in Enterprise Java' deck.