3 ms·
NoSQL with Postgres - Using Hstore in Rails
- thinkbohemian 15y agoThanks to the OP for this
- mcmire 15y agoWow, didn't realize how easy it was.
- thinkbohemian 15y agoI know! Never tried before because it sounded scary.
- trustfundbaby 15y agoDoes anyone know how fast it is compared to Relational Postgres? mongodb?
- wulczer 15y agoJust 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 http://www.pgcon.org/2010/schedule/events/219.en.html and http://www.scribd.com/gavinmroy/d/31669670-PostgreSQL-and-NoSQL http://www.scribd.com/gavinmroy/d/31669670-PostgreSQL-and-No... (especially the classic photo of DBAs running with scissors)
- fduran 15y agoMongoDB 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#fsyncCommand-fsyncCommand http://www.mongodb.org/display/DOCS/fsync+Command#fsyncComma...
- jashkenas 15y agoHstore 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-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/lib/dc/search/query.rb#L311-330 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-leases.html http://www.nytimes.com/interactive/2011/12/02/us/oil-and-gas... ... all without ever having to change the database schema, or indexes.
- travisjeffery 15y agoI wrote a similar article here sometime ago: http://travisjeffery.com/b/2012/02/using-postgress-hstore-with-rails/ 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.
- thinkbohemian 15y agoThats useful, it's got some things I didn't run into yet.
- james2vegas 15y ago"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 15y agoThe 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.
- mattsoldo 15y agoDoing joins or foreign keys is no problem. You simply join against the appropriate hstore function.