NoSQL with Postgres - Using Hstore in Rails | Hacker News Reader

NoSQL with Postgres - Using Hstore in Rails

schneems.com

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...

... 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...

... 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...

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

I wrote a similar article here sometime ago:

http://travisjeffery.com/b/2012/02/using-postgress-hstore-wi...

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

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

Wow, didn't realize how easy it was.

I know! Never tried before because it sounded scary.

"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.

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.

Doing joins or foreign keys is no problem. You simply join against the appropriate hstore function.

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

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... (especially the classic photo of DBAs running with scissors)

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...

Thanks to the OP for this

Reply on news.ycombinator.com