Hosting your data on one of the largest fleets of databases in the world comes with certain advantages. One of those benefits is that we can aggregate the collective pain points that face our users and work within the Postgres community to help find solutions to them.

In the previous year we worked very closely with the broader Postgres community to build features, fix bugs, and resolve pain points. You’ve already seen some of the results of that work in the form of extension support on Heroku and query cancellation. With the 9.2 release we’re delighted to say that with your help, we’ve been able to bring you a whole host of new power and simplicity in your database.

Effective immediately, we’re moving Postgres 9.2 into GA, which will become the new default shortly after. Postgres 9.2 is full of simplifications and new features that will make your life better, including expressive new datatypes, new tools for getting deep insights into your database’s performance, and even some simple user interface improvements. Oh, and it’s much, much faster for the most common kind of write performance pattern we see in our fleet.

Let’s dig in a bit further with the new features this version brings.

Visibility
Visibility into your data has long been a problem for many application developers. Thanks to Peter Geoghegan, and the many involved in reviewing/testing , in the new version of Postgres all queries are normalized and data about them is recorded. This allows you to gain insight such as:
• How often a query is run
• How much time is spent running the query
• How much data is returned

Each of these key pieces of data are critical when it comes to being able to effectively optimize your database’s performance. The old way of drudging through logs is no longer needed to gain this insight. Now your database contains what it needs in order to help you improve performance within an un-forked Postgres database. Ensuring such functionality is committed back to the Postgres core is very important as it prevents lock-in and creates a better ecosystem for the community as a whole.

Let’s take a look at how we can begin using some of this. First turn on the tracking of pg_stat_statements with CREATE EXTENSION pg_stat_statements.

We’re very excited about the visibility you can now gain into your database. We’ve begun exploring the powerful new ways we can show what’s occurring with your database and look forward to seeing how we and our users can further expand the power of the improved visibility within Postgres 9.2.

URLs
All Postgres tools and libraries now support URLs natively. No more need for heroku pg:credentials – just use the URL with any Postgres project tool.

JSON Support
Developers are always looking for more extensibility and power when working with and storing their data. Earlier this year we announced our support for hstore, a powerful key/value store within Postgres, which you can easily use within Rails, Django, and Java Spring.

With Postgres 9.2 there’s even more robust support for NoSQL within your SQL database, thanks to Andrew Dunstan, in the form of JSON. By using the JSON datatype your JSON is validated that it’s proper JSON before it’s allowed to be committed.
Beyond the datatype itself there are several new functions available – record_to_json, row_to_json, and array_to_json.

Range Type Support
The range datatype, thanks to Jeff Davis, is another example of powerful data flexibility. The range datatype is a single column consisting of a to and from value. Your range can exist as a range of timestamps, alpha-numeric, or numeric range and can even have constraints placed on it to enforce common range conditions.

Performance
Of course, any new release of a database wouldn’t be complete without some focus on performance. Postgres 9.2, as expected, has delivered here in a big way including up to 4X improvements in speed on read queries and up to 20X improvements on data warehousing queries. In particular index-only scans can offer much faster queries because they no longer need to access disk to ensure correct results.