PostgreSQL 9.2 works faster and smarter
The new release of PostgreSQL, version 9.2, has arrived, four months after the appearance of the first beta version. The new release includes read and write performance boosts, index-only scanning, new web-oriented functionality, and support for range data types. As shown in the beta, the new version promises to be much faster than its predecessor primarily thanks to index-only scanning, which allows searches to avoid reading the underlying tables and instead search only indexes. This new feature is used automatically, though there are caveats as to how effective it can be all situations, but where the required data is already indexed, for example in "big data" scenarios, the boost in performance can be huge.
Data warehousing index-only scans are said to be twenty times faster than before. Combining the index-only scanning with linear scalability to 64 cores and other performance improvements in lock management, write handling and other low-level operations, the PostgreSQL developers say they are seeing four times faster read rates – up to 350,000 read queries per second – for general data sets and up to 14,000 data writes per second (five times faster). The write enhancements include support for group commit and adjustments to reduce CPU power use. PostgreSQL 9.2 also enhances horizontal scalability with cascading replication, which allows chains of replicating servers to be created and can help reduce data traffic requirements between servers.
New range types are also available that allow for a compact representation of integers (INT4RANGE and INT8RANGE), numeric values (NUMRANGE), timestamps (TSRANGE and TSTZRANGE) and dates (DATERANGE). CREATE TYPE also allows the creation of custom range types for specific tasks such as complex analytics and it is possible to search these ranges against inclusive or exclusive and open or closed boundary definitions. The range enhancements should reduce the complexity of any database schemas and code that deal with ranges of values and should enable the management of that type of data at a much higher level.
Upgrading to PostgreSQL 9.2 will require a dump and restore using
pg_dump, or the running of
pg_upgrade, to migrate the database to the new version. Further details of PostgreSQL 9.2, including changes that affect backward compatibility, are available in the release notes. Full documentation and an introduction to new features are also available. The PostgreSQL project's download page has prebuilt binaries of the database for FreeBSD, OpenBSD, Red Hat (and CentOS, Fedora and Scientific Linux), Debian, Ubuntu, SUSE and openSUSE, Mac OS X, Solaris and Windows. Source code is also available to download. PostgreSQL 9.2 is published under the OSI-certified, BSD/MIT-like PostgreSQL Licence.