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:

• The Durable Document Store You Didn't Know You Had But Did
• YC Discussion

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.