In association with heise online

10 September 2012, 14:59

PostgreSQL 9.2 works faster and smarter

  • Twitter
  • Facebook
  • submit to slashdot
  • StumbleUpon
  • submit to reddit

PostgreSQL logo 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.

Application developers will find a number of new features in PostgreSQL 9.2 which should simplify development of some types of apps. A new data type, JSON, allows JSON data, JavaScript Object Notation as per RFC 4627, to be stored in the database. Although storable as text, the advantage of the JSON type is that it ensures the data is valid JSON. It is complemented by JSON functions which allow arrays and rows to be easily converted to JSON format. New PL/V8 JavaScript and PL/Coffee database extensions allow JSON to be easily consumed by web-styled applications and passed up for easy consumption by JavaScript running in web browsers.

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.


Print Version | Send by email | Permalink:

  • July's Community Calendar

The H Open

The H Security

The H Developer

The H Internet Toolkit