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 and Functions