As of today PostgreSQL 9.3 is available on Heroku Postgres as a public beta. This new version of Postgres brings you even more useful features so you can be as powerful as ever. Whether its richer JSON functionality, materialized views, or richer join support in lateral joins this version has a little something for everyone.
Provision your Postgres 9.3 database by running heroku addons:add heroku-postgresql:crane --version=9.3 and get started working with it today, or check out some of our favorite features included in this new version below.
Foreign Tables
Foreign data wrappers (FDWs), which allow you to query from within Postgres to an external datasource, have been available for a couple of releases. Now Postgres ships with a built-in Postgres FDW as an extension. With the Postgres FDW aggregating and reporting against your data from disparate Heroku Postgres databases is as simple as CREATE EXTENSION postgres_fdw, followed by setting up your foreign tables.
Beyond the built in Postgres FDW available to all Heroku Postgres 9.3 users today, the API for foreign data wrappers now supports them writing as well as reading data. This lays the groundwork for more powerful wrappers to be built which in the future will enable Postgres to be a fully federated database.
We’ve already begun taking advantage of foreign data wrappers internally at Heroku for reporting and look forward to hearing how you take advantage of them yourselves.
A more powerful JSON
With version 9.2 we saw PostgreSQL get support for JSON starting on its path of bridging the gap between the dynamics of schemaless databases and the robustness of the traditional relational world. This support got even richer by our addition of full Javascript support with the V8 engine inside Postgres. It continues to get even better today with more built in functions and operators to make working with your JSON data even easier.
Materialized Views
For many applications, pre-computing expensive queries can be a great way to improve overall performance. Materialized views do just this by caching the results of a view and then allowing you to periodically refresh those results.
This can be tremendously useful, and the in-progress Postgres 9.4 development already has some exciting improvements.
And more
There are a number of less prominent additions and fixes, ranging
from performance improvements, to more flexible DDL
commands (e.g., CREATE SCHEMA ... IF NOT EXISTS), to event
triggers for better tooling hooks. There have been over 1700 commits
since 9.3 development started in earnest here:
commit bed88fceac04042f0105eb22a018a4f91d64400d
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Jun 13 20:03:02 2012 -0400
Stamp HEAD as 9.3devel.
Let the hacking begin ...
You can read further on whats new over at the PostgreSQL wiki.
Beta status
As Postgres moves from 9.3 beta releases to a 9.3.0 GA release, we are moving out support for this version from alpha to beta. As it is still a beta product, it comes with several conditions:
- Existing 9.3 beta databases must be upgraded to 9.3.0 via pgbackups. Followers and forks of 9.3 beta are no longer supported.
- Heroku Postgres uptimes are expected uptime guidelines and not a guarantee or SLA for uptime of your production database. As with many alpha or beta features the expected uptime is lower for Postgres 9.3.
- Forks, followers, and other functionality may see a higher level of issues during the beta. Please let us know about any problems you run into.
Conclusion
Postgres 9.3 continues the tradition of adding great features, performance improvements, and maintaining a serious concern for data integrity. Get started by provisioning your Postgres 9.3 database:
$ heroku addons:add heroku-postgresql:crane --version 9.3
Since this is a beta offering, we are especially interested in hearing your feedback. Please let us know what you think and how you're using it by contacting us at postgres@heroku.com.