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 support 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.
You can request a version 9.2 database from the command line like this:
heroku addons:add heroku-postgresql:dev --version=9.2
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; Then run the query below and you'll receive all of your top run queries:
SELECT
count(*),
query
FROM
pg_stat_statements
GROUP BY 2
ORDER BY 1 DESC
LIMIT 10;
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. Using these functions we can turn a row immediately into JSON to be used within an application or returned via an API:
$ heroku pg:psql
=> SELECT row_to_json(row('foo','bar', 1, 2));
row_to_json
---------------------
{"f1":"foo","f2":"bar", "f3": 1, "f4": 2}
(1 row)
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.
For example, this schema ensures that in creating a class schedule we can't have two classes at the same time:
CREATE TABLE schedule (class int, during tsrange);
ALTER TABLE schedule ADD EXCLUDE USING gist (during WITH &&);
Then attempting to add data we would receive an error:
INSERT INTO schedule VALUES (3, '[2012-09-24 13:00, 2012-09-24 13:50)');
INSERT INTO schedule VALUES
(1108, '[2012-09-24 13:30, 2012-09-24 14:00)');
ERROR: conflicting key value violates exclusion constraint "schedule_during_excl"
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.
Summary
Heroku Postgres provides reliability and safety when working with your data. At Heroku Postgres, we were very excited to be able to fund core Postgres features for the first time and work with the community more closely to make Postgres an even better cloud database. The support of Postgres 9.2, now in general availability, makes power, flexibility and insight available to all Heroku Postgres users. Whether you’re looking to have NoSQL in your SQL database, better understand visibility, or receive a performance boost, this version should help you. Get started by provisioning one today from the Heroku CLI:
heroku addons:add heroku-postgresql:dev --version=9.2