Schneems

Heroku just announced their support of hstore in their dedicated Postgres 9.1 instances. Hstore is a schema less key value store inside of PostgreSQL that allows us to store data like hashes directly inside of a column. It’s great for when you don’t know exactly what types of attributes you need to store on a model, or if you need to support many different attributes for the same model.

A good example is storing attributes for a Product model. We might start out only selling books, which have an author, number of pages, but then transition over to selling laptops which have cpu speed and display resolution. Using Hstore allows us to easily store all these values without having to make a bunch mostly blank columns.

To get started with Rails and hstore you can watch the screencast below or visit the hstore example app running on Heroku.

More on Hstore

Hstore in Rails functions much like serializing hashes, except that we can query our data much faster since hstore is a native data type. It is supported natively in Rails 4, but until then we’ll need to use the activerecord-postgres-hstore gem.

Getting Started

You will need a version of PostgreSQL locally that supports the hstore extension. I recommend installing postgres using homebrew on OS X. Once you’ve done that you can enable hstore usage by running this in Postgres

CREATE EXTENSION hstore;

You can put this in a migration if you prefer

class SetupHstore < ActiveRecord::Migration
  def self.up
    execute "CREATE EXTENSION hstore"
  end

  def self.down
    execute "DROP EXTENSION hstore"
  end
end

Once that is done you will need to create a column with a type of hstore, here we are giving our Product model a column called data with hstore type.

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string  :name
      t.hstore  :data
      t.timestamps
    end
  end
end

Once we’ve done that we can now store any type of attributes in the data column.

Product.create(:name => "Geek Love: A Novel", :data => {'author' => 'Katherine Dunn', 'pages' => 368, 'category' => 'fiction'})
Product.last.data['category']  # => 'fiction'

Querying

Not only does hstore allow us to store arbitrary keys and values it allows us to quickly query them.

  # Find all products that have a key of 'author' in data
  Product.where("data ? :key", :key => 'author')

  # Find all products that have a 'pages' and '368' key value pair in data
  Product.where("data @> (:key => :value)", :key => 'pages', :value => '368')

  # Find all products that don't have a key value pair 'pages' and '999' in data
  Product.where("not data @> (:key => :value)", :key => 'pages', :value => '999')

  # Find all products having key 'author' and value like 'ba' in data
  Product.where("data -> :key LIKE :value",     :key => 'author, :value => "%Kat%")

More information available in the Postgres hstore docs. Though like a normal column if you query it frequently, you can get even more speed by adding an index. You can do this using one of two indexes that also speed up full text searches. They’re GiST (Generalized Search Tree) or GIN (Generalized Inverted iNdex). Which sill speed up queries using the @> and ? postgres operators.

class Index < ActiveRecord::Migration
  def up
    execute "CREATE INDEX products_gin_data ON products USING GIN(data)"
  end

  def down
    execute "DROP INDEX products_gin_data"
  end
end

Use It

Try out the hstore example app, clone the Github repo, and let me know what cool things you build on twitter @schneems.

Thanks

Special thanks to Aaron Patterson and Joel Hoffman for their work with hstore & Rails4, to the team at Softa for writing this gem, & and the team at Heroku for their contributions to Postgres, and supporting this feature.