PostgreSQL 9.2 beta improves scalability, adds JSON
The beta release of version 9.2 of the open source PostgreSQL database has been announced by the PostgreSQL Global Development Group, promising major improvements in performance that enable better horizontal and vertical scalability. One new optimisation – index-only scanning – allows searches to avoid reading the underlying tables and instead search only the indexes; index only scanning happens automatically but there are a number of caveats to its effectiveness. It is claimed though that it will, for most use cases and data warehousing, provide "an order-of-magnitude performance improvement".
Improvements in handling read-only workloads have, the developers say, allowed the database to scale to using 64 cores and act on 300,000 queries per second. Data writing has also been speeded up with features such as group commit, while there have been changes to also reduce CPU power consumption. Horizontal scalability improvements include support for cascading replication allowing for chains of replicating servers and reducing data transfer requirements between data centres.
New features have been added for application developers, including support for a JSON data type, allowing for hybrid document-relational databases which can store JSON documents, and JSON functions which convert array and row data into JSON. A number of improvements have also been made to statements, such as ALTER, that are said to ease runtime database updates.
New range types – INT4RANGE, INT8RANGE and NUMRANGE (for integers and numbers), TSRANGE, TSTZRANGE and DATERANGE (for timestamps and dates) – have been added to allow for values which represent a range of numbers or dates. These should enable new kinds of calendar, time-series and analytic applications to use PostgreSQL. It is also possible to create custom range types using CREATE TYPE.
As with all development releases, use in production environments and on mission critical systems is not advised. Those testing the release are asked to provide feedback and report any bugs that they find. According to the project's roadmap, the final version of PostgreSQL 9.2 is scheduled to arrive in the third quarter of this year.
If all goes to plan with the testing, PostgreSQL 9.2 should be available in September. Further information about the beta can be found in the release notes and in the updated documentation. Source code for PostgreSQL 9.2 is available from the project's FTP site. Pre-built binary packages of PostgreSQL 9.2 Beta are available from the project's downloads page for Windows, Mac OS X, Linux, FreeBSD and Solaris. Source code for PostgreSQL is made available under the terms of the PostgreSQL Licence, a permissive open source licence similar in character to the BSD or MIT licences.