PostgreSQL 9.1 Now Default

We're constantly involved in improving Postgres on behalf of our users. That kind of work includes building new features into our platform like data clips, tracking down bugs uncovered by our users and getting them fixed, and working to bring the needs of our users to the attention of the developer community driving the project forward.

Of course, all that pales in comparison to the work the community does every day, and there's no bigger demonstration of that than the major PostgreSQL releases which introduce new features and generally come out once a year. When they do, we build support for that version almost immediately, and roll it out to gradually larger audiences as we receive requests, uncover its use cases, and prove it operationally on our own internal projects.

I'm pleased to announce that as of today, all new databases created will run PostgreSQL 9.1. There are no known issues with 9.0 compatibility (here are the release notes), so if you're excited about any of the new functionality, please feel free to upgrade today using pgbackups to migrate your data.

Cancelable Queries

In addition to making 9.1 the new default, I also wanted to draw your attention to a feature we cherry-picked from the upcoming 9.2 release and made available to all new databases as of today.

For most users, queries come and go in an instant, and a slow query is anything that takes longer than a hundred milliseconds. Query cancellation is a problem that really only rears its head once you reach a certain size. For example, occasionally a migration which was instantaneous in your testing environment might surprise you with a long-lasting lock on a table in production and bring your site to its knees. Unfortunately, when our users were faced with this problem in the past, they weren't able to cancel those queries without opening a support ticket.

Fortunately, it's no longer a problem. Here's how the solution looks to a user:

=> SELECT pg_cancel_backend(procpid) 
   FROM pg_stat_activity 
   WHERE current_query LIKE '%your giant accidental query%';
pg_cancel_backend
-----------------
t
=>

So how did this get fixed? This exact problem was causing pain to some of our customers with very large datasets. We brought the issue to the Postgres community's attention, and proposed a patch. The patch that eventually became a part of 9.2 ended up being dramatically different (and much better) than the one we put forward, but now because of the feedback we got from our customers and the relationship we have with the Postgres developer community, everyone's database is just a little bit better.

Java Hackathon

This weekend, join us for a Java Hackathon at the Heroku office in San Francisco.

We've decided to kick things off with a contest. To enter, build a creative and/or useful application that enables or manages interactions with customers or potential customers via social media channels. It can be any social media channel, and your app will be judged on how well it fits the contest criteria as well as the quality of your concept and implementation.

The overall winner will receive a $500 Amazon gift card and a $500 Heroku credit. Two runners up will win a $100 Heroku credit.

Here are the basic rules to enter:

  • Your app has to be in Java or a Java framework.
  • You don't have to be at the Hackathon, but you do have to reside in the U.S., Canada (excluding Quebec), or the U.K.
  • To enter, deploy your app to Heroku and post the code to a public repository on Github. Email your app name and a link to the Github.com repository to contests@heroku.com with the title of the contest (Heroku for Java Hackathon) in the subject. Entries must be received no later than 11:59pm PST on 3/4/2012. One entry per person.
  • For the official rules of the contest, please see this page. If you have questions, please contact us at contests@heroku.com.

    This is a good excuse to get familiar with the Heroku platform and how productive you can be by using it. Learn the basics on our Java DevCenter page and of course, we're more than happy to help you if you can make it out to the Hackathon.

    Nezumi 2.0 for Managing Heroku Apps 'on-the-go' Now Available for iPhone

    Heroku users are known for leading jet-setter lifestyles. It's true! Developers with refined, sophisticated tastes git push to the cloud in order to appreciate the finer things of life: foreign cinema, travel to exotic destinations, and focusing on development instead of configuring system infrastructure.

    So it's only natural that Heroku developers on-the-go reach for Nezumi.

    Nezumi is a paid 3rd-party iPhone app created by Marshall Huss that allows you to scale dynos, restart apps, and so much more--perfect for when you're away from your computer. Its latest release adds support for Cedar applications, multiple accounts, a revamped console and log viewer, and a sleek, new app icon, which will look amazing on your home screen.

    Nezumi Console Nezumi Dashboard

    Nezumi can be purchased on the iTunes App Store now at a limited-time introductory price of $4.99. Existing Nezumi customers can download 2.0 as a free upgrade.

    Nezumi has given us a few promo codes that can be redeemed for a free download. Leave a comment below for a chance to have one e-mailed to you!

    Simple data sharing with Data Clips

    The Heroku Data Team just launched Data Clips, a deceptively powerful feature. From the Heroku Postgres Blog:

    "Data Clips allow the results of SQL queries on a Heroku Postgres database to be easily shared. Simply create a query on postgres.heroku.com, and then share the resulting URL with co-workers, colleagues, or the world. Data clips can be shared through e-mail, Twitter, irc, or any other medium - they are just URLs. The recipients of a data clip are able to view the data in their browser or download it in JSON, CSV, XML, or Microsoft Excel formats..."

    → Read More

    Check out the Heroku Postgres Blog to read more.

    Simple data sharing with Data Clips

    Data clips are available today in beta as a standard feature on all Heroku Postgres databases.

    When we share information on the Internet, we do so by sharing URLs. We send URLs for locations, books, videos, and even source code. Until now there hasn't been a convenient way to share data inside a database. That's why we're introducing Data Clips. They are a fast and easy way to unlock the data in your database in the form of a secure URL.

    Data Clips allow the results of SQL queries on a Heroku Postgres database to be easily shared. Simply create a query on postgres.heroku.com, and then share the resulting URL with co-workers, colleagues, or the world. Data clips can be shared through e-mail, Twitter, irc, or any other medium - they are just URLs. The recipients of a data clip are able to view the data in their browser or download it in JSON, CSV, XML, or Microsoft Excel formats.

    Data changes rapidly in databases. We've created Data Clips with this in mind. They can either be locked to a point in time or set to refresh with live data. When locked to a point in time, data clips are guaranteed to show an unchanging snapshot of data, even if they are viewed hours, days, or years after the clip was created. Alternatively, when data clips are set to "Now", they provide a live view into the database.

    Example

    To illustrate how data clips work, we created a database containing all of the people with Wikipedia articles (english articles only). This data comes via dbpedia.org (an excellent project which captures the "structured" information from Wikipedia). It is a modest, 4.6 GB database. The People table contains 800,000 rows:

    => \d people
                  Table "public.people"
       Column    |       Type        | Modifiers                      
    -------------+-------------------+-----------
     id          | integer           | not null
     url         | character varying | 
     name        | character varying | 
     given_name  | character varying | 
     sur_name    | character varying | 
     birth_date  | date              | 
     death_date  | date              | 
     description | character varying | 
    

    Here are data clips of all of the people whose description contains the words scientist, and artist. We can easily use the power of SQL to summarize this data, grouping and counting each by the century that they were born. We could also create a data clip to show how the average lifespan of wikipedia personalities has changed over time (this clip also exposes some errors in the source data). As you can see, data clips are as expressive as SQL itself.

    Details

    • Data Clips are immediately available in beta on all Heroku Postgres dedicated databases.
    • Data Clips utilize the excellent Ace Code Editor for SQL entry.
    • To reduce the risk of altering your data, Data Clips query databases within a read-only transaction.
    • Results are limited to 10,000 rows.
    • To limit the load on your database, Data Clips refresh a maximum of once every 60 seconds.

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