Introducing Heroku Postgres 2.0

Today we're excited to announce an evolution of what it means to be a database as a service provider. Along with new features such as the ability to roll back your database to an arbitrary point in time and high availability, we now provide an entirely new level of operational expertise that's built right in. This new level of service allows you to feel at ease while taking advantage of your database in ways you never expected. All of this is rolled into new tiers which make it as easy as ever to choose what’s right for you.

We are introducing Heroku Postgres 2.0 today, the result of these improvements. Let's dig in a little further on what's available today.

Read more →

PostgreSQL 9.3 now GA on Heroku Postgres

Several weeks ago we added support for Postgres 9.3 in public beta the day it was released to the community. We've had many customers use it so far and it has proven to be robust and reliable. Early adopters have started to take advantage of the great new features in this version including:

Today we're excited to release Postgres 9.3 in General Availability and setting it as the default version when provisioning a new Heroku Postgres database. Defaulting to the latest version of Postgres ensures our customers can make use of the latest features and performance improvements available.

If you haven’t upgraded to take advantage all the great new functionality yet then do so today and let us know how you’re using it at postgres@heroku.com.

Monitoring your Heroku Postgres Database

There are two axes of database monitoring. One axis is immediate insight. You can see what is happening right now, getting just-in-time visibility to solve problems and observe production behavior as it happens. The other axis is historical monitoring. This provides long-term persistence and reporting on the most important metrics over time, helping you make better decisions and understand trends.

With Heroku Postgres, you can get immediate insight with the pg-extras CLI plugin. Furthermore, we provide key metrics about your database right in your logs already for all applications.

For storage and reporting of your most important metrics, you can quickly set up rich historical reporting with Librato, which is available in our Add-ons marketplace. In this post, we walk through how to get started with Heroku Postgres monitoring for immediate visibility and long-term insight.

A healthy value for your cache hit will be as close to 100% as possible

It Starts with Logs

Heroku automatically collates and routes logs from every part of your app into a single channel, providing truly comprehensive, extensible, app-centric logging. With production Postgres databases on Heroku, robust logs and key metrics from your persistence level are routed directly into this stream. Key data including table-cache-hit, db_size, active-connections and more, outputting directly into your Heroku logs periodically. You can get immediate visibility into this stream by combining tail with a filter argument:

$ heroku logs --tail --ps heroku-postgres

Even better than viewing these logs manually is you can configure a log drain to be able to send your logs elsewhere. Fortunately when taking advantage of some of our add-ons they can automatically consume these logs without having to configure anything else.

Getting started with Librato

Librato is one such add-on that consumes your Heroku logs and gives you the ability to monitor on historical trends and set up alerts. You can get started with it right away by provisioning the add-on:

$ heroku addons:add librato

Librato will now be automatically consuming data from your Heroku logs and you can simply open it up to get immediate visibility into your Heroku Postgres cache hit, database connections, and insight around request queueing. If you have log runtime metrics already enabled on your application then you’ll see those insights as well.

Going further

While the initial dashboard is already helpful, you can gain additional insights by adding your own instruments to your dashboard. And if you need data thats not already available in your Heroku log stream you can add custom data yourself.

Introducing Postgres 9.3

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.

Win a ticket to Postgres Open and visit us there

The Heroku Postgres team is hitting the road in coming months and we'd love to connect with you. If you'd like to connnect with us at any of the events below drop us a line postgres@heroku.com or @HerokuPostgres

Postgres Open

The first opportunity to connect with us is in September at Postgres Open. If you've already got your tickets for Postgres Open join us for drinks and/or pizza at Clark Ale House on Tuesday September 17, and make sure to check out talks by me and Peter Geoghegan at the conference.

If you don't already have your ticket for Postgres Open but are interested in going, we've got a chance for you to win a ticket from us for free.

Win a ticket to Postgres Open

We're giving away 3 tickets to PG Open. For your chance to win a ticket we want to see the creative ways you're using dataclips with your Heroku Postgres database. Submissions can be a really impressive query or a great integration, the key to either is that it should be empowering you to better run your business with this data.

To enter 1. create your dataclip or integration then 2. submit the following to postgres@heroku.com:

  1. Your name
  2. Your role
  3. Your organization and what the company does
  4. A link to the dataclip or to the integration that you've built.

We will be announcing winners on August 29, 2013. The winners will be chosen at the sole discretion of the Heroku Postgres team.

PostgreSQL Conf EU

Those of you in Europe will have an opportunity to connect with the team as well. A large part of the team will be at PG Conf EU, including talks by 4 of our team members:

Conclusion

If you're going to be at either of the above conferences we'd love to talk to you. However, you don't have to wait until these conferences, if you ever have operational issues with you database you can find help at help.heroku.com or for product related questions and feedback you can contact us at postgres@heroku.com

Browse the blog archives or subscribe to the full-text feed.