New title

Postgres H-Store

PostgreSQL is a powerful, open source object-relational database system based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. It is fully ACID(Atomicity, Consistency, Isolation, Durability) compliant,has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

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.HStore is a key value store within Postgres. You can use it similar to how you would use a dictionary within another language, though it’s specific to a column on a row.

Background

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 is really useful for saving attributes on models. If you store settings for your users, you'd typically do this in a separate model (or on the User model). Each setting would be an additional column. Instead of adding columns each time you want to create a setting, you could instead use a single HStore column. It's much more flexible and doesn't require migrations each time we want to store something new.

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.

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.

Potential Applications

What should you use hstore for? As I see it, hstore is suitable for use in cases where you have most of your data normalized, but you may have bits of data related to your models may need to change frequently and you don’t want to run database migrations all that often, thus changing the structure of your database and possibly requiring your site to come down. For example, maybe you’re consuming information from an API that may change its responses without providing a backwards-compatible, unchanging structure (inserting new keys in its JSON responses, perhaps). This may not be all that common with public APIs, but with internal projects this can happen rather frequently. Perhaps you wouldn’t want to discard this data, but without writing a migration and making room for new data, you won’t be able to keep it. Another possibility could be user preferences – you don’t necessarily want 10-30+ rows per user in a giant join table for user preference data and perhaps you feel that should be stored with the user object, but don’t want to go through the trouble of migrating your database (and incurring possible downtime) when deploying changes to user preference structure.

Enter hstore. With this extension active and in use, you could simply define changes to the structure without a migration and redeploy to pick up changes immediately. That API could throw whatever it wants at you because you can just take the JSON and shove it into hstore. And that myriad of user preferences? No big deal – just store the stuff that differs from the defaults as a key/value pair and you’re done. Change it all you like by just committing new code. reference:https://blog.engineyard.com/2013/using-postgresql-hstore-in-a-rails-application-on-engine-yard-cloud

hstore Datatype

hstore Representation

The text representation of an hstore, used for input and output, includes zero or more key => value pairs separated by commas. Some examples:

k => v
foo => bar, baz => whatever
"1-a" => "anything at all"

The order of the pairs is not significant (and may not be reproduced on output). Whitespace between pairs or around the => sign is ignored. Double-quote keys and values that include whitespace, commas, =s or >s. To include a double quote or a backslash in a key or value, escape it with a backslash.

Each key in an hstore is unique. If you declare an hstore with duplicate keys, only one will be stored in the hstore and there is no guarantee as to which will be kept:

SELECT 'a=>1,a=>2'::hstore;
  hstore
----------
 "a"=>"1"

A value (but not a key) can be an SQL NULL. For example:

key => NULL

hstore Operators

hstore Functions

Indexes

hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators. For example:

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

hstore also supports btree or hash indexes for the = operator. This allows hstore columns to be declared UNIQUE, or to be used in GROUP BY, ORDER BY or DISTINCT expressions. The sort ordering for hstore values is not particularly useful, but these indexes may be useful for equivalence lookups. Create indexes for = comparisons as follows:

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);

Add a key, or update an existing key with a new value:

Other Operations

UPDATE tab SET h = h || ('c' => '3');

Delete a key:

UPDATE tab SET h = delete(h, 'k1');

Convert a record to an hstore:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT hstore(t) FROM test AS t;
                   hstore                    
---------------------------------------------
 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)

Convert an hstore to a predefined record type:

CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,
                              '"col1"=>"456", "col2"=>"zzz"');
 col1 | col2 | col3 
------+------+------
  456 | zzz  | 
(1 row)

Modify an existing record using the values from an hstore:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
 col1 | col2 | col3 
------+------+------
  123 | foo  | baz
(1 row)

Using Hstore with Rails 4

Install GEM

Install Hstore is a PostgreSQL contrib type, check it out first.

Then, just add this to your Gemfile:

gem 'activerecord-postgres-hstore'

And run your bundler:

bundle install

Migrate

Now you need to create a migration that adds hstore support for your PostgreSQL database:

rails g hstore:setup

Run it:

rake db:migrate

Finally you can create your own tables using hstore type.

Enable hstore Extension

First, you need to enable the hstore extension in the PostgreSQL database. You can do this with a migration.

class AddHstore < ActiveRecord::Migration  
  def up
    enable_extension :hstore
  end

  def down
    disable_extension :hstore
  end
end  

Add hstore Column

Next, since hstore is now a natively recognized datatype in Rails, you can add an hstore column to any existing model. Here I'm adding a "settings" column to my user model.

class AddSettingsToUser < ActiveRecord::Migration  
  def up
    add_column :users, :settings, :hstore
  end

  def down
    remove_column :users, :settings
  end
end  

Define Accessor

Finally, you can define accessors for your hstore keys in your model. Validations work just like they would for any other column in your model.

class User < ActiveRecord::Base  
  # setup hstore
  store_accessor :settings, :favorite_color, :time_zone

  # can even run typical validations on hstore fields
  validates :favorite_color,                            
    inclusion: { in: %w{blue, gold, red} }

  validates_inclusion_of :time_zone, 
    in: ActiveSupport::TimeZone.zones_map { |m| m.name }, 
    message: 'is not a valid Time Zone'

end