Introducing key/value data storage in Heroku Postgres
March 14, 2012 by Matthew Soldo
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
- The key value store everyone ignored
- 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;
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'
=> 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;
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.