What's new in PostgreSQL 9.2

From PostgreSQL wiki

Revision as of 15:31, 20 April 2012 by Sternocera (Talk | contribs)

Jump to: navigation, search

This document showcases many of the latest developments in PostgreSQL 9.2, compared to the last major release – PostgreSQL 9.1. There are many improvements in this release, so this wiki page covers many of the more important changes in detail. The full list of changes is itemised in Release Notes.

This page is incomplete!


Major new features

Index-only scans

Index-only scans is a new performance feature whereby PostgreSQL can skip the heap visibility check if the index contains all necessary columns, for pages that are known to be all-visible. This feature is similar to covering indexes in other database systems, although the implementation is different. (More info: depesz blog)

In previous PostgreSQL versions, all matching index rows in an index scan had to consult the table heap for visibility information. In version 9.2, an index-only scan first checks the smaller visibility map to see whether all the rows on the particular page are visible. If true, the table heap fetch can be skipped. VACUUM is responsible for setting the visibility map bits.

This required making visibility map changes crash-safe, so visibility map bit changes are now WAL-logged.

Cascading replication

Streaming replication slaves can now serve as a source for other slaves. This can be used to reduce the impact of replication on the master server. (More info: depesz blog)

A related feature, the pg_basebackup command now also works from slaves (More info: depesz blog)

Multi-processor scalability improvements

The lock contention of several big locks has been significantly reduced, leading to better multi-processor scalability. (More info: Robert Haas blog)

JSON datatype

The JSON datatype is meant for storing JSON-structured data. (More info: depesz blog)

Range Types

RangeTypes are added. (More info: [1])

Performance improvements

  • The performance of in-memory sorts has been improved by up to 25% in some situations, with certain specialized sort functions introduced. (More info: Bruce Momjian's blog)
  • An idle PostgreSQL server now makes less wakeups, leading to lower power consumption (Peter Geoghegan's blog)
  • Timing can now be disabled with EXPLAIN (analyze on, timing off), leading to lower overhead on platforms where getting the current time is expensive (depesz blog)
Personal tools