Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The key value store we ignored (Postgresql)

blog.creapptives.com

Post reply on HN
Print view View original

31–40 of 65 posts

Re: The key value store we ignored (Postgresql)

#31

Post by jashkenas »

tptacek wrote: post #18
jashkenas wrote: post #9

Earlier quoted context omitted.

Yes -- but not with that module. We're using it to tag documents (individual rows in Postgres and Solr) with user-defined metadata. http://blog.documentcloud.org/blog/2011/05/arbitrary-metadat... ... which can then be used to power custom indexes of particular collections of documents, like this one: http://www.nytimes.com/interactive/2011/12/02/us/oil-and-gas... The fun bit being, that in both Postgres and Solr, you…

How did you build it? Are you using hstore SQL directly? Do you just have migrations with raw SQL in them?

Let's dig through it a bit. Our hstore column isn't directly on the document row, but rather in a joined "docdata" table. There was a semi-raw migration to add it:

https://github.com/documentcloud/documentcloud/blob/master/d...

The "Docdata" model handles serializing JSON hashes of keys -> string values into the hstore format, and parsing 'em back out. This is a little loose, but works for most of the simple things you'd like for tagging documents (senator: Kerry, statecode: WV, recordid: 019281)

https://github.com/documentcloud/documentcloud/blob/master/a...

... the resulting effect is that from the Rails side, working with "document.data" directly is like working with a lazy-loaded hash.

The more interesting bit is the search side of things, here's the query parser and query object (which generates SQL and Solr queries):

https://github.com/documentcloud/documentcloud/blob/master/l...

https://github.com/documentcloud/documentcloud/blob/master/l...

... for any given query, we detect if Solr is required (if full-text is involved), or if the search can be satisfied purely in Postgres. So there's one way of generating the key:value query for each back end.

Hope that answers the question.

(Edit:) Forgot to mention that we add a little extra on top of vanilla hstore "key:value" queries. You can write "dob: *" to show documents with any senator tagged, and "dob: !" to show documents that haven't been tagged with a date of birth yet.

Re: The key value store we ignored (Postgresql)

#32

Post by code_duck »

maxpert wrote: post #24
jedberg wrote: post #19

reddit used (and still for the most part uses) postgres as a schemaless storage (although they don't use hstore). Also, I'm pretty sure Heroku uses hstore, because they're the ones that taught me about it. So not everyone forgot about it.

Just read the comments around and you will see the dogma people face on daily basis despite the huge databases of reddit!

dogma... what do you mean?

Re: The key value store we ignored (Postgresql)

#33

Post by sausagefeet »

maxpert wrote: post #21
grncdr wrote: post #16

Earlier quoted context omitted.

> I think for schema-less storage systems we know 2 major competitors in market. MongoDB and CouchDB. Um, what about Riak, Cassandra, Voldemort, and Hbase? (I'm sure there's a bunch more I'm forgetting)

Cassandra, Hbase -> Column Oriented! Not schema less! Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values. Voldermort -> Distributed key value. Again you are missing the point of maturity and a proven user base, and it's comparing apple with bananas! Try putt…

> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values

I thought CouchDB required this as well?

Re: The key value store we ignored (Postgresql)

#34

Post by fauigerzigerk »

imbriaco wrote: post #20

I agree with the author that hstore is very interesting, but the data structures are not the key selling point in the NoSQL space in my opinion. The most overlooked advantage to things like Cassandra and Riak are the fact that you have no single point of failure in the system. If an individual node fails, there is no operational impact. Postgres does have (finally!) a nice replication story, so you have data protecti…

Agreed, but of course the massive write parallelism and fault tolerance of DBMS like Cassandra comes at the cost of dropping ACID, which may cause a lot of complexity elsewhere in the system. It also comes at the cost of limiting the types of queries you can perform without resorting to procedural code (at least in the case of column family based architectures). In other words, it comes at the cost of productivity.

So, even if sharding is not easy, you can do quite a lot of it in the time you save by not having to code around the limitations of most noSQL DBMS.

The day will come when RDBMS are secret productivity weapon of smaller companies that don't feel they have to act like they were Google.

That said, there are very good reasons not to use RDBMS in cases where the data model or specific access patterns just don't fit. But in most of those cases, I find that using in memory data structures combined with file system storage or something like BerkeleyDB is a much better fit than any server based DBMS.

Re: The key value store we ignored (Postgresql)

#35

Post by tptacek »

jashkenas wrote: post #31
tptacek wrote: post #18

Earlier quoted context omitted.

How did you build it? Are you using hstore SQL directly? Do you just have migrations with raw SQL in them?

Let's dig through it a bit. Our hstore column isn't directly on the document row, but rather in a joined "docdata" table. There was a semi-raw migration to add it: https://github.com/documentcloud/documentcloud/blob/master/d... The "Docdata" model handles serializing JSON hashes of keys -> string values into the hstore format, and parsing 'em back out. This is a little loose, but works for most of the simple things y…

This is awesome, thank you (didn't think to check if you were in Github, but the explanations are great too).

Re: The key value store we ignored (Postgresql)

#36

Post by jallmann »

maxpert wrote: post #21
grncdr wrote: post #16

Earlier quoted context omitted.

> I think for schema-less storage systems we know 2 major competitors in market. MongoDB and CouchDB. Um, what about Riak, Cassandra, Voldemort, and Hbase? (I'm sure there's a bunch more I'm forgetting)

Cassandra, Hbase -> Column Oriented! Not schema less! Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values. Voldermort -> Distributed key value. Again you are missing the point of maturity and a proven user base, and it's comparing apple with bananas! Try putt…

> Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values.

http://howfuckedismydatabase.com/nosql/

Quite apt.

Re: The key value store we ignored (Postgresql)

#37

Post by Animus7 »

jallmann wrote: post #36
maxpert wrote: post #21

Earlier quoted context omitted.

Cassandra, Hbase -> Column Oriented! Not schema less! Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values. Voldermort -> Distributed key value. Again you are missing the point of maturity and a proven user base, and it's comparing apple with bananas! Try putt…

> Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values. http://howfuckedismydatabase.com/nosql/ Quite apt.

Personally I find writing MapReduce jobs (in JavaScript no less) to be unbelievably clean and easy when your stack is Riak + Node.

Of course if you've been using SQL for years then this probably sounds difficult in comparison. Except what if you're a JS guy with zero SQL experience?

"Ok, it's a database. How do I query it?"

"You learn this completely new language and dynamically compile your questions down to it, but you have to be really careful because the process is notorious for being a first-class attack vector."

"Did you just tell me to go fuck myself?"

I'm not trying to say anything about the merits of SQL. I'm just pointing out that it's a matter of perspective.

Re: The key value store we ignored (Postgresql)

#38

Post by jbellis »

maxpert wrote: post #21
grncdr wrote: post #16

Earlier quoted context omitted.

> I think for schema-less storage systems we know 2 major competitors in market. MongoDB and CouchDB. Um, what about Riak, Cassandra, Voldemort, and Hbase? (I'm sure there's a bunch more I'm forgetting)

Cassandra, Hbase -> Column Oriented! Not schema less! Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values. Voldermort -> Distributed key value. Again you are missing the point of maturity and a proven user base, and it's comparing apple with bananas! Try putt…

Schemaless doesn't necessarily mean "document-oriented." Sparse-column / ColumnFamily databases like Cassandra are a lot closer to "schemaless" than they are to "traditional rdbms schema."

Re: The key value store we ignored (Postgresql)

#39

Post by dspillett »

fauigerzigerk wrote: post #34
imbriaco wrote: post #20

I agree with the author that hstore is very interesting, but the data structures are not the key selling point in the NoSQL space in my opinion. The most overlooked advantage to things like Cassandra and Riak are the fact that you have no single point of failure in the system. If an individual node fails, there is no operational impact. Postgres does have (finally!) a nice replication story, so you have data protecti…

Agreed, but of course the massive write parallelism and fault tolerance of DBMS like Cassandra comes at the cost of dropping ACID, which may cause a lot of complexity elsewhere in the system. It also comes at the cost of limiting the types of queries you can perform without resorting to procedural code (at least in the case of column family based architectures). In other words, it comes at the cost of productivity. S…

> comes at the cost of dropping ACID

Do remember though that, as discussed here recently, most RDBMSs do not act in a fully ACID compliant way by default. IIRC it is providing a complete isolation guarantee often also provides a hefty performance hit so compromises are made in this area unless you explicitly tell it to be as careful as it can. I imagine this can cause quite a nightmare for mastermaster replication.

There are a lot of people using "noSQL" options for the wrong reasons (such as to be buzzword compliant, or because they don't understand SQL), but there are issues that traditional RDBMSs have that stick-in-the-muds like me (who cringe at the phrase "eventual consistency" should be more aware of than we generally are.

Making the right choices about your data storage can be hard.

Re: The key value store we ignored (Postgresql)

#40

Post by zobzu »

I've been using a lot of MySQL and some NoSQL solutions over the years. I don't need most of what NoSQL is supposed to offer and uhm, a couple of years ago I tried PGSQL.

I've never found the same level of finish, performance, etc. It's also very customizable and very dependable. You can get extremely good performance out of it.

Finally... the devs are plain AWESOME. Every time I had an issue, question, etc, not even necessarily related to PGSQL but to SQL and performance in general they would reply quickly, and accurately (I owe one of them many, many beers).

To give you an example, a company I worked for was doing a 10M to 10M comparison (for very specific needs). All businesses we have found were using custom NoSQL solutions, or even Oracle, with huge clusters, costing a lot, and taking ~24h for a result.

So we implemented ours. After a year of testing, coding, etc, it was settled on PGSQL (on FBSD), with the help of the said dev, we were doing the same comparison,in 3H, on a single quad core system (with heaps of ram).

Sure, maybe the other businesses never cared that much for performance, but its still astonishing. Most optimizations were made thanks to PGSQL's dev help were usually speeding up the process by thousand of times.

Disclaimer: I can't detail the comparison (its still under NDA), but it included very specific stuff, in case you wonder about the time taken, etc.

Post reply on HN