Fork me on GitHub

Hstore Demo

Welcome, to get started create a user account. Once you're signed in, you can create 'Products'

sign up or sign in

What is Hstore?

Hstore is a schemaless key value column in Postgres. It's perfect if you need to store attributes for an object but aren't quite sure what your schema should be. Maybe we start selling 'Products' that have an author and an isbn, but then we want to start selling cameras, or computer equipment we'll need to store other things like amount of ram or manufacturer. With hstore, we can use the same column for both sets of data, no migrations or schema changes needed.

Hstore in Rails

In Rails we can use hstore like a hash, you can input any key and any value you want. Once it's stored you can query the keys or values. If you've ever used a serialized hash column, hstore behaves much like that. The main difference is performance, since hstore is storing the keys and values natively in Postgres, queries run much much faster. Rails 4 supports the hstore column type, until then we'll need to use a gem.

To get started you can create a user account by signing up and then create products with arbitrary keys and values. You can also use the hstore query widget below to explore the products in the entire database


Heroku & Hstore

Heroku supports Postgres through contributions to the open-source code, and by running managed dedicated Postgres instances for customers. If you're using the Postgres Addon and have a managed database with Heroku, you can now start using hstore. If you have an existing managed database, or you would like to move from a shared database to a dedicated one, you can upgrade your database.

If you want to try hstore before you spin up a database, you can use this website running on Heroku. You can log in and create products to query against, and use the query widget below.

Fork this website at Heroku's Rails hstore example.

Hstore Queries

Quering hstore keys and values is easy, pick your query type and enter a key/value, then run your query against live data on this site.

Find:


Key Equal to 'foo'

Product.where("data ? 'foo'")

Key of 'foo' equal to 'bar'

Product.where("data @> 'foo=>bar'")

Key of 'foo' not equal to 'bar'

Product.where("not data @> 'foo=>bar'")

Key of 'foo' equal LIKE 'bar'

Product.where("data -> 'foo' LIKE '%bar%'")

Result:

Spinner