What's new in PostgreSQL 9.2

From PostgreSQL wiki
Revision as of 13:37, 20 March 2012 by Robe (talk | contribs) (fix version numbers in headline)
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)

Performance improvements

  • The performance of in-memory sorts has been improved by up to 25% in some situations, with type-specific specializations. (More info: Bruce Momjian'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)