

NoSQL with Postgres - Using Hstore in Rails - craigkerstiens
http://schneems.com/post/19298469372/you-got-nosql-in-my-postgres-using-hstore-in-rails

======
jashkenas
Hstore as part of a larger search index is really handy. We're using it at
DocumentCloud to allow reporters to tag and search through their primary
source documents by adding their own arbitrary metadata.

[http://blog.documentcloud.org/blog/2011/05/arbitrary-
metadat...](http://blog.documentcloud.org/blog/2011/05/arbitrary-metadata/)

... when I search for "company:XTO" (or "company:*" for that matter), it can
be serialized into an hstore query like so:

[https://github.com/documentcloud/documentcloud/blob/master/l...](https://github.com/documentcloud/documentcloud/blob/master/lib/dc/search/query.rb#L311-330)

... and then be used to build apps with hstore queries on top of the API, like
this:

[http://www.nytimes.com/interactive/2011/12/02/us/oil-and-
gas...](http://www.nytimes.com/interactive/2011/12/02/us/oil-and-gas-
leases.html)

... all without ever having to change the database schema, or indexes.

------
travisjeffery
I wrote a similar article here sometime ago:

[http://travisjeffery.com/b/2012/02/using-postgress-hstore-
wi...](http://travisjeffery.com/b/2012/02/using-postgress-hstore-with-rails/)

I go over how to use the keys as fields, and some other common problems.

~~~
thinkbohemian
Thats useful, it's got some things I didn't run into yet.

------
mcmire
Wow, didn't realize how easy it was.

~~~
thinkbohemian
I know! Never tried before because it sounded scary.

------
james2vegas
"Using Hstore allows us to easily store all these values without having to
make a bunch mostly blank columns."

Or they could have used a small Product table with common fields and made
fanout tables for Books and Laptops. For some reason it appears impossible for
some to create new tables, or have joins and foreign keys.

~~~
thinkbohemian
The idea though, is that we can allow every user to create any product of any
type without giving them access to make new tables. I doubt amazon has a books
table, a toilet-paper table, etc. ... This is just another tool in your
Postgres toolbelt, use it when appropriate.

------
trustfundbaby
Does anyone know how fast it is compared to Relational Postgres? mongodb?

~~~
wulczer
Just as fast (and that means pretty fast). Comparing to MongoDB might not make
sense, if you're running it with no fsyncs (I don't know much about MongoDB,
so maybe it is possible to make it fsync data).

There was a good talk about using Postgres as a key-value store at PGcon 2010,
see <http://www.pgcon.org/2010/schedule/events/219.en.html> and
[http://www.scribd.com/gavinmroy/d/31669670-PostgreSQL-and-
No...](http://www.scribd.com/gavinmroy/d/31669670-PostgreSQL-and-NoSQL)
(especially the classic photo of DBAs running with scissors)

~~~
fduran
MongoDB makes a full flush by default every 30 seconds, it can be forced with
the fsync command
[http://www.mongodb.org/display/DOCS/fsync+Command#fsyncComma...](http://www.mongodb.org/display/DOCS/fsync+Command#fsyncCommand-
fsyncCommand)

------
thinkbohemian
Thanks to the OP for this

