What's new in PostgreSQL 9.2
From PostgreSQL wiki
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 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.
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)
The JSON datatype is meant for storing JSON-structured data. (More info: depesz blog)
- 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)
- 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)