PostgreSQL 9.2 - official release

Today, September 10, 2012, PostgreSQL 9.2 was officially released!

Finally, the long beta testing period announced in May of this year is over. The developers promise even greater performance, scalability and flexibility (performance, scalability and flexibility).

Improved performance and scalability

Postgres now supports 64-core processors, and its Postgres power consumption is reduced. Here I suspect, everything is clear and without unnecessary deviations.

Implemented index-only scan mechanism. What kind of beast is this? The fact is that the indexes in Postgres do not contain information about the visibility of the record. That is, to understand whether any record is visible, the server needs to read the real table tuple (tuple). It happens that the index contains information about an obsolete tuple. It is clear to the horse that access to a sorted index is much faster than access to a real table, the data of which can be spread as you please. In short, in version 9.2 this feint with ears is possible. Although the index is still no information about the visibility of the record, the developers have implemented the so-called "visibility map" (visibility map). According to math : “The visibility map was long overdue. In the new release, they made it resilient to server crashes, respectively, guaranteed to be correct. ”All this and much more is described in detail in the Wiki .
')
Improved and polished Streaming Replication . For details, please follow through on the same Wiki .

From sweet. There is native support for JSON type. Web developers are overjoyed and throw their caps into the air. The server checks the validity of the incoming data, prompts where the error, if any, contains a full range of functions for converting from / to JSON. For example, array_to_json and row_to_json are just happiness for the lazy, which I most certainly am.

And as for me, the most deadly feature was support for range types ( Range Types ). I do not know how it was possible to live without them before.

First, ranges can be both discrete and continuous. For discrete it is necessary to refer such built-in types as int4range , int8range and date4range . To continuous, respectively, numrange , tsrange , tstzrange (ts - timestamp, num - numeric).

Secondly, the range can be open ... Remember about square and round brackets? [1; 1] - closed, and [1; 1) - open on the right, etc.

Thirdly, the limit of the range can be “infinity”, the one that ∞! Remember the joke about a first grader?
- , ?
- π/2.

If someone does not understand why I am so funny and play the harmonica, I used to have to use two fields to store such data. And then you and operators, and indices, and constraints (constraints) ...

We will be measured?

The official text contains such comparative numbers with previous versions:

• Up to 350,000 read requests per second (4 times faster)
• Index-only scanning can increase in speed from 2 to 20 times.
• Up to 14000 data records per second (5 times faster)

I do not know how much it costs to believe these puzmerkam, because most likely the results were obtained on synthetic measurements, but still ...