Introducing key/value data storage in Heroku Postgres

From the Heroku Postgres Blog:

"One of the great strengths of PostgreSQL is extensibility. Just as the JVM has become more than a way to just run Java—spawning languages such as Clojure and Scala—PostgreSQL has become more than just a home to relational data and the SQL language. Our first officially supported Postgres extension, hstore, enables you to build better apps faster without sacrificing the power, reliability, and flexibility of the underlying PostgreSQL storage engine. By using hstore, you will be able to leverage the flexibility and agility of schema-less data stores in existing environments..."

→ Read More

Check out the Heroku Postgres Blog to read more.

Introducing key/value data storage in Heroku Postgres

One of the great strengths of PostgreSQL is extensibility. Just as the JVM has become more than a way to just run Java—spawning languages such as Clojure and Scala—PostgreSQL has become more than just a home to relational data and the SQL language. Our first officially supported Postgres extension, hstore, enables you to build better apps faster without sacrificing the power, reliability, and flexibility of the underlying PostgreSQL storage engine.

By using hstore, you will be able to leverage the flexibility and agility of schema-less data stores in existing environments. Although hstore is a mature, stable solution, it has recently been gathering widespread excitement:

Support for hstore is available today in many popular languages and frameworks, including plugins for Django, Rails/ActiveRecord, Sequel, and Node.js. While you can be ahead of the curve now, hstore support will become a native part of ActiveRecord 4. You can add hstore to any Postgres 9.1 database today with a single command:

=> CREATE EXTENSION hstore;

Sample hstore App

If you want a jumpstart on using hstore, Heroku's Richard Schneeman has created a simple rails app demonstrating its usage. The code is available on Github.

An Agile Example

Let's imagine that you are building an online bookstore. You might create a products table with only a name, id, and hstore column in order to have maximum flexability as to what is stored in the table:

=> CREATE TABLE products (
     id serial PRIMARY KEY,
     name varchar,
     attributes hstore
   );

Then insert any type of data you need into the table:

=> INSERT INTO products (name, attributes) VALUES (
    'Geek Love: A Novel',
    'author    => "Katherine Dunn",
     pages     => 368,
     category  => fiction'
    );

Data in an hstore column can be queried based on the values of attributes,

=> SELECT name, attributes->'device' as device 
   FROM products 
   WHERE attributes->'edition'= 'ebook'

or queried based on the keys:

=> SELECT name, attributes->'pages' 
   FROM products
   WHERE attributes ? 'pages'

Someone you admire tells you that "nobody reads books anymore". No problem, pivot! You now sell electronics. That might require a change to your code or brand, but not your schema:

=>  INSERT INTO products (name, attributes)
    VALUES (
      'Leica M9',
      'manufacturer  => Leica,
       type          => camera,
       megapixels    => 18,
       sensor        => "full-frame 35mm"'
    ),
    ( 'MacBook Air 11',
      'manufacturer  => Apple,
       type          => computer,
       ram           => 4GB,
       storage       => 256GB,
       processor     => "1.8 ghz Intel i7 duel core",
       weight        => 2.38lbs'
    );

Of course, you can use the full power of PostgreSQL on this data. Values in hstore can be indexed:

=> CREATE INDEX product_manufacturer 
   ON products ((products.attributes->'manufacturer'));

And used in joins:

=> SELECT manufacturers.country, products.name
   FROM products, manufacturers
   WHERE products.attributes -> 'manufacturer' = manufacturers.name;

Conclusion

The pace of software development is accelerating. Agile processes, application frameworks, and cloud deployment platforms are force-multipliers driving this change. The PostgreSQL project has seen these trends and is embracing them.

Whether your software project has rapidly changing requirements, or inherently doesn't fit with traditional pre-defined schemas (applications dealing with user-generated data for example), hstore may be the solution you are looking for.

If you have suggestions for what can be built on top of the Postgres "platform", please let us know via @herokupostgres or in a comment below.

The Heroku Toolbelt

The Heroku Toolbelt is a package of the Heroku CLI, Foreman, and Git — all the tools you need to get started using Heroku at the command line. The Toolbelt is available as a native installer for OS X, Windows, and Debian/Ubuntu Linux.

The Toolbelt has been available since last fall as part of our polyglot platform. Since then it's matured substantially with a huge amount of user testing, and now even has a shiny new landing page. Ruby developers can continue to use gem install heroku, but developers in other languages (Python, Java, Clojure, etc) will probably prefer not to have to install Ruby and RubyGems to use Heroku.

The installer won't trample your existing install of Git if you have one. Similarly, although the Heroku CLI uses Ruby under the hood, the Toolbelt packaging isolates all of its libraries so it will not interfere with an existing Ruby setup.

The entire Toolbelt is open source. File an issue or, better yet, send a pull request if you see ways that it can be improved.

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.

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