Humour: Sql vs NoSql

[PaxBritannia]

No explanation needed: just watch the video. (5:25 seconds long with audio)

http://www.xtranormal.com/watch/6995033/

pax.

[kaos78414]

It's funny, and it brings up an interesting subject. I'm building a server right now to experiment with that runs node.js and mongodb.

My opinion on SQL vs noSQL is best explained with a quote:

So when to use a document-oriented database and when to use a relational database. The former is usually much better performing and easier to scale, while doesn't provide ACID compliance and data integrity that the later has by definition. This means that if we choose to use document-oriented database, we get more performance and scalability, but need to keep in mind, database level data integrity, transactions and locks are no longer there and will need to be embedded in the application logic itself, which will affect how we write and structure our code. In my opinion, document-oriented databases cannot replace relational databases, and vice versa. Instead, they should be used to solve different kinds of problems. At OpenSky we use both MySQL and MongoDB.

-NOTE: CouchDB apparently is ACID compliant. And you'll probably find that some noSQL databases have some features that others don't. And the actual speed, scalability, and flexibility of a noSQL DB varies greatly across all the different software available

For my PBBG, MongoDB provides quite a bit more flexibility than a relational database. The speed and scalability are really just a bonus.

I think it comes down to one thing: Which tool is best for the job?

[PaxBritannia]

I agree that each have their place.

I think relational databases are for best for storing data which relies on using the whole table set. That is, if you need to use a whole row of data. Storing those in a noSQL would just mean looking up the same key in the whole column-family. Technically it would still be faster and more redundant, but the complexities of data inconsistency here just don't justify it.

However, if you find yourself just having key, value tables, then the only technical negative of a noSQL database would be inconsistency, but you can still config it to write/read all copies and it would still be faster.

For my own project, I'm sharding my mySQL databases for scalability, and using Cassandra or another noSQL DB in the Dynamo family for the shard indexes.

pax.

[kaos78414]

Storing those in a noSQL would just mean looking up the same key in the whole column-family. Technically it would still be faster and more redundant, but the complexities of data inconsistency here just don't justify it.

You're talking about specifically key-value stores. What about document based databases?

Taken from StackOverflow:

Document databases, such as CouchDB and MongoDB store entire documents in the form of JSON objects. You can think of these objects as nested key-value pairs. Unlike Cassandra, you can nest key-value pairs as much as you want. JSON also supports arrays and understands different data types, such as strings, numbers and boolean values.
...
Document databases support queries by key and map-reduce functions as well, but also allow you to do basic queries by value, such as "Give me all users with more than 10 posts". Document databases are more flexible in this way.

And another:

A document-oriented database extends the previous model (key-value) and values are stored in a structured format (a document, hence the name) that the database can understand. For example, a document could be a blog post and the comments and the tags stored in a denormalized way. Since the data are transparent, the store can do more work (like indexing fields of the document) and you're not limited to query by key. As I hinted, such databases allows to fetch an entire page's data with a single query and are well suited for content oriented applications (which is why big sites like Facebook or Amazon like them).

I don't think consistent data will be a problem with document-oriented DB's. But there are other problems, like the fact that they can be very drive-space hungry, so you have to make sure you have plenty of space on the servers. Or this:
"MongoDB, on the other hand, allows ad-hoc querying, and relies on indexes defined on the document values to help it achieve reasonable performance when the data size grows large enough. MongoDB’s indexes behave in much the same way RDBMS indexes behave, that is, they are updated as part or the insert process, so large number of indexes is going to affect write performance."

Anyway, it's still a situational thing, I just wanted to differentiate key-value stores with document-oriented databases for anyone else who might be wanting to learn about them

[Jackolantern]

Aren't the queries of document databases more simplistic? I am thinking of some of the 50+ keyword monster SQL queries that my old telecomm company had to run every once in a while. I can't think of a way to run queries like that with just JSON-type datastores without either running it in 20 or 30 different steps, or without CPU and memory costly virtual result tables. While it is true that your average PBBG or website is usually running pretty simplistic queries, this would seem like a pretty big stumbling block for using something like MongoDB for business intelligence management applications. Granted, I am not that familiar with document databases, but I have looked through query tutorials and not seen even 20% of the query power of a RDBMS.

[PaxBritannia]

I'm sorry, I complete left out an entire part of noSQL - the performance and structure of document based databases are just so different.

I can see the point of document databases and (its close friend xml), but the computational and especially storage overhead would multiply. In traditional systems, most static storage doesn't run XAMP and the database is considered separate. A document database would mean that either each server parses their own files, meaning running XAMP on every server. Currently, this is a hurdle on specialty storage clusters without XAMP. The benefit of this architecture would be parallel processing and a reduced layer of servers. However until audio and video can be successfully deconstructed and analysed with computers, it would make sense for most media to still reside on a dedicated media storage cluster..

The alternative would be to retrieve the documents from the storage cluster and then parse them on dedicated servers. This is more realistic in the current technological environment, however it is a bit more complex (although no more than sharding a RDBMS). The storage:parsing server ratio would have a major impact in processing time. Both would be highly scalable, but the speed is still unknown do to lack of real-world-use demonstrations.

It's strange how document databases were the initial type of database, although a little different from their current direction. In programming, every few years or so, after something becomes obsolete, it seems to always come back with new developments and then become new accepted way. Strange isn't it?

pax.

[kaos78414]

@Jackolantern:
Well I think it's kind of doing what MVC does for programming: forcing us to be more organized. Queries like you mentioned are certainly possible, though I don't know why you'd ever need to do that, provided your information was organized well. I'm not an expert though, and further reading is probably in order. I would probably also look into some applications that are currently using MongoDB or CouchDB or Raven, to see how much data they handle and how large the queries are

@Pax:
That is strange. It's happening with Javascript too. JS is making a run into server side programming, which is just plain awesome. Rather than dealing with several languages I'll only have to deal with one for both server and client sides. This is what attracts me to node.js, as well as the benchmarks and just the thought of using something new

Anyway, all these new technologies; it's good for us to be skeptical. But I'm starting to realize that the only way to find out what all the hype is about is to try it. I'll probably start working with RoR soon too just to check it out

[Jackolantern]

@Jackolantern:
Well I think it's kind of doing what MVC does for programming: forcing us to be more organized. Queries like you mentioned are certainly possible, though I don't know why you'd ever need to do that, provided your information was organized well.

Business intelligence. By far and away the most demanding domain of SQL. When your boss tells you he wants "Everyone who made an order for B service who lives in C, who did not refer any customers with D service on their account, but only the ones who went at least E months between sign-up date F and other service purchase G...etc....etc.", that is exactly what he wants (which is in response to a request from a high-level marketing consultant/department), and there is not much of a way to know ahead of time that he would need exactly that data. It all must be accessible in every possible combination. In that kind of environment, all you can do is design your database as modularly as possible to assist in all of the spacey join logic.

That is the kind of query that I don't know can be done in document databases. Not that that means they are bad or should be ignored, but if they can't perform that kind of query logic, then they are definitely not the best tool for every job.

[kaos78414]

Hmm yes I don't think they'd be suited to queries like that. I don't know though, as I don't have a way to test it just yet. I'll blog my experience with MongoDB as I experiment further