Effective immediately, Heroku is 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.
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 into your data has long been a problem for many application developers.
- How often a query is run
- How much time is spent running the query
- How much data is returned
You can turn on the tracking of
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;
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.
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
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"
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.