The NoSQL Ecosystem

abartels writes 'Unprecedented data volumes are driving businesses to look at alternatives to the traditional relational database technology that has served us well for over thirty years. Collectively, these alternatives have become known as NoSQL databases. The fundamental problem is that relational databases cannot handle many modern workloads. There are three specific problem areas: scaling out to data sets like Digg's (3 TB for green badges) or Facebook's (50 TB for inbox search) or eBay's (2 PB overall); per-server performance; and rigid schema design.'

Comments

Why worry?

[Anonymous Coward]

Microsoft Access is here!

Re:Why worry?

[MichaelSmith]

Don't forget excel!

Re:Why worry?

[Manos_Of_Fate]

Because there's no "scary because it's true" mod.

Re:Why worry?

[Linker3000]

Oh Great! I have just migrated 5 offices from a veterinary management system based around Access 97 onto the new, MS-SQL-based one.

How can I expect to maintain my value to the company if they stick with old, reliable systems instead of moving onto more sophisticated 'solutions' that require a shit-load of tweaking and technical guesswork to keep them running smoothly?

Re:Why worry?

[Yoozer]

That's when you tell customers about MSDE (now SQL Server Express) which does the job a lot better without breaking the bank.

Re:Why worry?

[Hognoxious]

Don't be so pessimistic. There's OO databases and the cloud. That should see you almost through to retirement.

Re:Why worry?

[Anonymous Coward]

You laugh, but the things I see done in Excel on a daily basis in production environments getting a LOT of work done are a testament to it's power. It is one of the best rapid application development platforms in existance. People with no CS background programming away in a functional style and getting shit done and not even realising they are programming. It could be so much better but it's still the best of breed. Any yes I have tried, and seen others try, O.O. et al. Forget it. Lets not go down that worn old road.

bad design

[girlintraining]

So... every time I open my inbox in Facebook, it has to search through 50TB of data? That sounds like a design problem. What has always floored me is why people think everything needs to be stuffed into a database. Terabyte sized binary blobs? You know, there's a certain point where people need to stop and actually think about the implimentation.

Re:bad design

[bennomatic]

I'm a terabyte sized binary blob, you insensitive clod!

Re:bad design

[munctional]

Ever heard of bloom filters? Sharding? Indexes? They are clearly not doing a table scan on 50gb of data every time you open your Facebook inbox.

You know, there's a certain point where people need to stop and actually think about the implimentation.

Um, they do. They regularly blog about their solutions to their problems and open source their solutions and contributions to existing projects. They come up with amazing solutions to their large scale problems. They're running over five million Erlang processes for their chat system!

http://developers.facebook.com/news.php?blog=1

http://github.com/facebook

Also, when was the last time you tried to visit Facebook and it was down? They're doing quite well for people who need to stop and actually think about their "implimentation".

Re:bad design

[JavaPunk]

Yes it does (look through 50TB of data), and how would you design it? It has to access all of your friends and find their postings. Robert Johnson gave an excellent talk on facebook's design two weeks ago at OOPSLA (it should be in the ACM digital library soon). He stated that there is no clear segregation of data, the (friend) network is too connected and extracting groups of friends isn't possible. Basically they have a huge mysql farm with memcached on top. Loading an inbox will hit multiple servers (maybe even a different server for each of your friends) across the farm.

Re:bad design

[socceroos]

Ever heard of bloom filters? Sharding? Indexes?

Don't forget flux capacitors, FTL drives and crossfading splicers.

Re:bad design

[TheModelEskimo]

Yeah. And those guys down the street, the tweakers, nose jobs, and johnny-come-latelys.

Re:bad design

[ErikTheRed]

So... every time I open my inbox in Facebook, it has to search through 50TB of data? That sounds like a design problem. What has always floored me is why people think everything needs to be stuffed into a database. Terabyte sized binary blobs? You know, there's a certain point where people need to stop and actually think about the implimentation.

Could be worse. They could try to find something on my desk.

Re:bad design

[kestasjk]

They use bloom filters for messaging? What for?

Re:bad design

[Ragzouken]

"Also, when was the last time you tried to visit Facebook and it was down? They're doing quite well for people who need to stop and actually think about their "implimentation"."

When was the last time you tried to use Facebook or Facebook chat and didn't get failed transport requests, unsent chat messages, unavailable photos, or random blank pages?

Re:bad design

[gutter]

Sounds like you don't know much about Erlang. Erlang processes are MUCH lighter weight than unix processes, and are designed to scale to millions of processes. Generally, you want one Erlang process for each concurrent task in the system, like maybe one process for each active chat session. So, having 5 million Erlang processes would be as designed.

Re:bad design

[Zombywuf]

Sounds like you don't understand sarcasm. I'll spell it out for you: Simply because Facebook are running 5 million processes is neither here nor there. The impressive thing is that it actually works (from what I hear it does any way. If it did it with one process or 5 million it has nothing to do with the relative weight of Erlang and Unix processes.

Next up, tying your own shoelaces...

Re:bad design

[Zombywuf]

The problem is when people don't think about the solution and apply the cargo cult mentality. Facebook uses Eeeerlaaaang therefore we should. Facebook wrote it's own database, therefore we should. People end up writing their own database engines that do exactly the same thing as modern relational engines, with all the bugs that were fixed in the relational engines 10 years ago (5 for Microsoft). Even MS SQL will split a large group by aggregate operation (which takes 3 lines to specify) across multiple CPUS by turning it into a map reduce problem, and it will do this all without you having to be aware of it. Oracle (and many others, Oracles is supposed to be the best) will maintain multiple concurrent versions of your data in order to allow multiple users to work with a snapshot that doesn't change under them while others are changing the data, and this happens transparently. You can go ahead and implement all this stuff yourself if you want, in C and sockets, call me when your done, in 10-20 years.

The real issue I have with the NoSQL people is they're a bunch of whiny babies, who haven't even taken the time to understand the problem before lashing out at the first thing they see. Just the name tells you this, they call themselves "No SQL" and then lash out at relational databases. SQL is is a terrible language, which really needs replacing, but it is only one possible language for querying relational databases. Relational databases represent several decades of research into how to query data in a fault tolerant scalable way as a standing implementation, re-implementing them is a waste of time.

Re:bad design

[QuoteMstr]

What makes you think that relational calculus can't be extended to support spatial information? After all, it's just another kind of index.

Re:bad design

[Muad'Dave]

...they call themselves "No SQL" and then lash out at relational databases.

Had you read the article, you would've seen that the "No" in NoSQL stands for Not Only, not No, as in none whatsoever. I welcome any and all research into better, tighter synergy between databases and object persistence.

Re:bad design

[jcnnghm]

Yes it does (look through 50TB of data), and how would you design it?

When a users posts a message, I would have the web server pass the message to a server that listens for messages that are being sent. That server would collect the mail then place them as a payload package in the messaging queue when either a fixed number of mail recipients, probably around 500, or a fixed time passes, probably 500ms, whichever comes first. When the payload reaches the front of the queue, the messaging server working on the payload would parse through all the messages building a model of all the data it needs to render all of the messages. It would then send a low priority FQL multiquery requesting all of the data it needs to render and send all of the requests. From there, the messaging server would render both the updated view of the mail when viewing the thread, and view of the thread when viewing the inbox. These would be passed to a persistent memcached setup.

An FQL query would be generated for each user that would increment their inbox message counter, remove the memcached key of the old thread preview from the array of keys representing their inbox while prepending the new key to the array, and append the key to the array representing the thread. When this was assembled for all mail, another low priority multi-query would be sent committing this change.

At this point I'd purge the old thread preview keys from the persistent memcached setup, and store the raw data in a table indexed by both the thread preview key, and the mail view key. The raw data would be stored in case a design change ever necessitated re-rendering all of the mail, or in the case of a user name change.

Finally, I would generate and send an e-mail to each user telling them they have a new message.

This is complex, but it also means that to render an inbox, the only thing that has to be done is to retrieve the array of message thread preview keys, and request each thread preview by key from memcached. Of course, this collection could also be cached.

Note: I intentionally left out some things in the interest of time, like sent message display, read and unread flagging, spam filtering, new message highlighting, and I'm sure others. It shouldn't be difficult to see how this basic model can be expanded to cover these cases.

Re:bad design

[Hal_Porter]

For something on the scale of Facebook, 5 GB of wasted overhead for the chat system would not scare me.

It's not about the cost of the memory. Big systens tend to run more slowly because of locality effects. Systems running byte code run more slowly too. I think the Facebook guys have been saved by big ass hardware, not an efficient design.

Re:bad design

[AvitarX]

But that is my point really, big ass hardware is still quite cheap.

An inefficient, but easy to create and manipulate design, that is scalable affordabley, has benefits.

when the workload scales to 10,000 (April 2008) servers, the inefficiency is not problematic.

If the super efficient embedded style program was easy to maintain and scaled so well, it may only be 5,000 servers, or even 1,000,, but how well would it scale to 15,000 or 3,000 with new features being added (October 2009 Facebook has 30,000 servers).

Lightweight is not near as important as being able to throw big ass hardware at a problem.

And if that big ass hardware is lots of little pieces, because the system scales easily that way, it could be less expensive than less hardware overall, but having to be stuffed into larger pieces (not saying that would be an inevitability of efficient embedded style programming, simply a likely outcome).

Re:bad design

[vajrabum]

Bloom filters give constant time probablistic answers to set membership questions in a very space efficient manner. Moreover set union and intersection for the filters can be computed by simple AND and OR operations--also in constant time. The downside is that delete is hard. That union and intersection property means that it's easy to distribute query's over an arbitrary number of machines. Sounds kind of perfect to me for implementing a distributed index for searching, no?

Re:bad design

[vajrabum]

But because they can used to partition the filter accross machines in effect it can be used as index. Each machine that stores a portion of the filter gets all the queries that might apply to it and sends any results up to a machine that dispatches what you might call pre-queries on the bloom filters to the machines where the data and traditional indexes are stored. If the search vendor implements delete--google doesn't really, and this is the reason why--then you simply recompute the bloom filters when they become sufficiently out of date. That can be determined tracking how many times you get a false positive. Index lookups are slow for large data sets not because it takes that long to return an individual result but because there are so many queries. Bloom filters allow you to reduce the number of traditional index lookups and to dispatch the ones that have to be computed only to the machines where the data is available.

Re:bad design

[Pseudonym]

Bloom filters are not as useful as they once were for large-scale indexing. As memory sizes increase, the tradeoff between precision and space efficiency changes. It's just as easy to distribute a hash table or a radix trie across multiple machines these days.

A more common modern use is when you have data which is logically tabular, with potentially many "columns" which can contain arbitrary-sized objects, but the table is expected to be sparse. Traditional SQL table representations rely on predetermined maximum sizes for data values to optimise their representation, which is inappropriate for this because it would waste space. However, you also don't want to waste time accessing disk to find that a value isn't there. Using a Bloom filter costs a small amount of space (enough to fit in a small "descriptor") but can potentially save a huge number of disk seeks.

hmm

[buddyglass]

With regard to scalability, it strikes me that the problem isn't so much SQL but the fact that current SQL-based RDBMS implementations are optimized for smaller data sets.

Re:hmm

[phantomfive]

The biggest problem is the cloud. A lot of cloud APIs don't allow full relational database access, so now it seems we are coming up with all these justifications for why we don't really need it. Notice that this blog is from a company pushing a cloud based solution.

Re:hmm

[MightyMartian]

That's my take as well. We have these crippled semi-databases that lack a lot of useful features that anyone that has used RDBMSs over the last few decades have gotten used to, so suddenly it becomes a justification game; "Well, SQL doesn't deliver the output we need, so here's some half-way-to-SQL tools which are really better, kinda... oh yes, and Netcraft confirms it, SQL is dying!!!!"

I have a feeling that this part hype, part inept programmers who don't actually understand SQL, or database optimization. The first sign for me that someone is selling bullshit is when they try to act like this is some never before seen problem, when in fact there is a good four decades of research of database optimization.

Re:hmm

[KalvinB]

For the vast majority of use cases, large data sets can be made logically small with indexes or physically small with hashes.

If you're dealing with massive data you're probably not dealing with complex relationships. E-Mail servers associate data with only one index: the e-mail address. Google only associates content with keywords. E-mail servers logically and physically separate email folders. Google logically and physically separates the datasets for various keywords. So by the time you hit it, it knows instantly where to look for what you want. You don't have a whole complex system of relationships between the data. It looks at the keywords , finds the predetermined results for each and combines the results.

Re:hmm

[Prof.Phreak]

Depends. We've been using Netezza with ~100T of data, and... well... it takes seconds to search tables that are 30T in size. I'd imagine Teradata, greenplum and other parallel db's get similar performance---all while using standard SQL with all the bells and whistles you'd normally expect Oracle SQL to have (windowing functions, etc.).

Re:hmm

[mzito]

Uh, no, that is not correct. Relational DBMSes such as Oracle, Teradata, DB2, even SQL Server are all designed to scale into the multi-terabyte to petabyte range. The issue is one of a couple of things:

- Cost - "real" relational databases are expensive. I once had a conversation with someone who worked at Google, who talked about how much infrastructure they have written/built/maintain to deal with MySQL. Many of those problems were solved in an "enterprise" DBMS 3-10 years ago. However, the cost of implementing one of those enterprise DBMS is so high that it is cheaper to build application layer intelligence on top of a stupid RDBMS than purchase something that works out of the box
- Workload style - most of the literature around tuning DBMS is for OLTP or DSS workloads. Either small question, small response time (show me the five last things I bought from amazon.com) or big question, long response time (look through the last two years worth of shipping data and figure out where the best places to put our distribution centers would be). Many of these workloads are combos - there could be very large data sets and complex data interdependencies, with low latency requirements. It may be possible to write good SQL that does these things (in fact, I know a couple luminaries in the SQL space that will claim just that), but the community knowledge isn't there.
- Application development - when you're building your app from scratch, you can afford to work around "quirks" (bugs) and "gaps" (fatal flaws) to get what you need. This dovetails with the other issues, but when your core business is building infrastructure, it's worth your while to deal with this. When your core business is selling insurance or widgets, or whatever, it is not.

None of this is to say that the "nosql" movement is a bad thing, or that there's no reason for its existence, or that no one should bother looking at it. However, there is a definite trend of "this is so much better than SQL" for no good reason. SQL has scaled for years, and I know loads of companies who work with terabytes and terabytes of data on a single database without any issue.

A far more interesting discussion is the data warehouse appliance space - partitioning SQL down to a large number of small CPUs and pushing those as close to the disk as possible.

Re:hmm

[buchner.johannes]

The first sign for me that someone is selling bullshit is when they try to act like this is some never before seen problem, when in fact there is a good four decades of research of database optimization.

Your point is valid, but I think there is more to it. And the problems these solutions try to solve are quite old too. For example:

Ever tried to design a database, but got the requirement that you should be able to reconstruct the modification history? It boils down to not deleting (ever), and 'deleted' flag fields and other uglyness. A multi-version relational database would be nice, you actually don't need modification/delete operations in this scenario, just 'updates' that add to the previous status. CouchDB does append operations.

In some cases you may not need a complete SQL database, just key->value relations, but have them scaling very well. http://project-voldemort.com/ states: "It is basically just a big, distributed, persistent, fault-tolerant hash table." Then they state that they provide horizontal scalability, which MySQL doesn't (OTOH, we should really look at Oracle for these things).

And you can't really say MapReduce/Hadoop is pointless.

Re:hmm

[phantomfive]

Ever tried to design a database, but got the requirement that you should be able to reconstruct the modification history? It boils down to not deleting (ever), and 'deleted' flag fields and other uglyness.

I did it by every time I did an INSERT, DELETE, or UPDATE query, taking an exact copy of the query and dumping it into a special table in the database (along with a stack trace of where it was called from). To reconstruct I could just run those commands straight from the database, to whatever point was desired. It was simple, straightforward and efficient, although I'm sure someone else has a better idea.

Re:hmm

[CaptainZapp]

I have a feeling that this part hype, part inept programmers who don't actually understand SQL, or database optimization. The first sign for me that someone is selling bullshit is when they try to act like this is some never before seen problem, when in fact there is a good four decades of research of database optimization.

Thank you very much for this comment, you put it far more eloquently then my venting, I just wanted to grace this thread with. The real kicker though is

There are three specific problem areas: scaling out to data sets like Digg's (3 TB for green badges) or Facebook's (50 TB for inbox search) or eBay's (2 PB overall); per-server performance; and rigid schema design.

This statement is just so full of shit. And the real larff riot, for me at least, is when people or shops employing MySQL (for heavens sake!) make such statements.

Ej, folks: Rigid schema design is an asset, not a liability!

Re:hmm

[QuoteMstr]

I don't think you've thought clearly about the problem.

If a JOIN is causing problems because it's causing too much non-local data access, then you're going to run into the same problem when you re-code the JOIN in the application. In fact, it might hit you worse because you won't benefit from the database's query optimizer.

The solution is clearly to improve locality of reference. You can do that by duplicating some data, denormalizing the database, and so on. But you can do all those things just as easily within a RDBMS, and without losing the other benefits a RDBMS gives you.

Really, your problem is that some of the things RDBMSes allow hurt when a database grows beyond a certain size. The solution is to not do the things that hurt, not ditch the things that RDBMSes do allow.

It's like complaining that your feet are sore if you walk 20 miles, then cutting off your leg to make it stop.

Re:hmm

[QuoteMstr]

I think I'd rather see the opposite: That non-relation DBs become the mainstream, and they have SQL added for the odd occasion it is useful. Relational has some nice properties for ad-hoc querying, but for everything else they are a nuisance.

Berkeley DB is a very good non-relational database with multiple language bindings, several storage engines, and transaction support. It's been around for 24 years, and has seen some appreciable use.

But that use was nothing compared to the database explosion that SQLite brought about when it was released. SQLite is almost exactly like Berkeley DB, except that it has a SQL engine on top. Almost everyone is using SQLite, and many Berkeley DB users are moving over to it.

Why? Because SQLite is relational! That constitutes some serious evidence that relationship databases are more than "a nuisance".

Re:hmm

[h4rm0ny]

It was simple, straightforward and efficient, although I'm sure someone else has a better idea.

I'd love someone to post it if they do. We use the same method and the one time we had to replay the sequence to get what we wanted, it took most of a day. Yes, that was because are last snapshot "starting point" was nearly a week old, but nonetheless... if technology has moved on and there's a better way of doing this, then I'm sure a lot of us will be interested.

Re:hmm

[Hognoxious]

Rigid schema design is an asset, not a liability!

Not to people who think a free format text field is the ideal place to store the price, quantity and delivery date of an order. Why not, it's long enough for it all to fit. And it saves all that moving between fields.

Re:hmm

[tkinnun0]

What if you ARE dealing with massive data AND complex relationships?

Re:hmm

[geminidomino]

It's not without precedent. Drop all the features of SQL databases that make them a good idea and you end up with MySQL.

(Burn, baby, burn)

Re:hmm

[larry bagina]

create post insert, update, and delete triggers which file the data (as well as the action, timestamp, and user) into an audit table.

Re:hmm

[mzito]

I sort of agree with you, from the perspective that there's crusaders on either side - people who insist that traditional RDBMSes are the Only Way and people like you who insist they've "never been trialed under real-world conditions". Both statements are clearly incorrect on their face.

However, there are a multitude of features that these systems have that are not available in NoSQL systems, or only available in such a watered down form that its unfair to compare the two. A list:

- On-disk encryption
- Compression
- Schema/data versioning (present one picture of data to one set of clients, while presenting another layout of the same data to another set during a data migration)
- Automated failover between servers, clusters, facilities, datacenters
- "Flashback" - say "I want to run a query against my data as it looked last week at 3pm", and it just works.
- Shared-disk clustering

As far as transactions go, they may be a "joke" for scalability (not quite sure what that means), but they're awfully useful when dealing with sensitive information you need ACID compliance for. For example, I would prefer my bank not use an "eventual consistency" model when dealing with my credit card transactions.

Now, as I said above, a relational database *may not* be the right decision for your application. But the idea that relational databases don't scale is ridiculous. I've seen petabyte datawarehouses running teradata that absolutely scream through data. I've seen Oracle systems that do 10s of thousands of write transactions per second, and several times that in reads. They exist.

Re:hmm

[mbourgon]

Mod parent up. That way you're not dealing with the statements themselves, just the data. And you can add the UserID to the Audit table - then find the most recent row for that particular person, or get the most recent row for each ID and apply that.

Re:hmm

[popeyethesailor]

Why not use the DB features? Most enterprise-y databases have PITR(Point-in-time Recovery features).. Although it's not designed for that sort of thing, it could be used in such a fashion.
Most DBs do the same thing you guys do, i.e, use a transaction log. The transaction log could be replayed to get into a Point-in-time state. The one disadvantage is it's all or nothing i.e, you can't do it for specific transactions(although I'm sure some DBA will wander in correct me on this ;)

Dynamic Relational: change it, DON'T toss it

[Tablizer]

The performance claims will probably be disputed by Oracle whizzes. However, the "rigid schema" claim bothers me. RDBMS can be built that have a very dynamic flavor to them. For example, treat each row as a map (associative array). Non-existent columns in any given row are treated as Null/empty instead of an error. Perhaps tables can also be created just by inserting a row into the (new) target table. No need for explicit schema management. Constraints, such as "required" or "number" can incrementally be added as the schema becomes solidified. We have dynamic app languages, so why not dynamic RDBMS also? Let's fiddle with and stretch RDBMS before outright tossing them. Maybe also overhaul or enhance SQL. It's a bit long in the tooth.

More at:
http://geocities.com/tablizer/dynrelat.htm
(And you thought geocities was de

Re:Dynamic Relational: change it, DON'T toss it

[Prodigy Savant]

What you are suggesting is to mimic a key-value design with something like a json or serialized data as the value.

This would work if you never had to index on any of the values in the json. All your sql queries must have there where parts running off the key.

This is a problem that couchdb and mongodb solve.

I am not trying to paint SQL in an unflattering shade -- there would still be a lot of situations where an RDBMS design would be optimal. Infact, I am currently working on a mongodb/mysql hybrid solution for a large web site (larger than /. )

Re:Dynamic Relational: change it, DON'T toss it

[Tablizer]

What prevents indexing a dynamic-relational DB? Although I said that you didn't need a data-definition language, but that doesn't mean one *must* skip the DDL (for things such as indexes). Another thing to explore is auto-indexing. If so many queries keep filtering by a given column, then it could automatically put an index on it.

Re:Dynamic Relational: change it, DON'T toss it

[sco08y]

However, the "rigid schema" claim bothers me. RDBMS can be built that have a very dynamic flavor to them. For example, treat each row as a map (associative array).

You described an entity attribute value model, which winds up reinventing half the DBMS, poorly. Don't worry, *everyone* does one once until they realize it's a bad idea.

Constraints, such as "required" or "number" can incrementally be added as the schema becomes solidified.

A "rigid" schema is preventing a ton of totally redundant code being written on the app side. All those constraints wind up in the schema because your UI designer doesn't want to consider that Mary might have 5 addresses or 6 mothers or work 7 jobs simultaneously. And your UI tester doesn't want to test an exploding combinatorial number of possibilities.

I'd like to see, however, a decent type system, proper logical / physical separation, etc.

Maybe also overhaul or enhance SQL. It's a bit long in the tooth.

I'm starting from scratch. (Currently I'm slowly retyping about 40 pages into Latex...)

Re:Dynamic Relational: change it, DON'T toss it

[pla]

RDBMS can be built that have a very dynamic flavor to them. For example, treat each row as a map (associative array). Non-existent columns in any given row are treated as Null/empty instead of an error. Perhaps tables can also be created just by inserting a row into the (new) target table. No need for explicit schema management.

Aaaaaaaand, congratulations, you've described "fixing" the problem of schema flexibility by using an RDBMS as a non-relational flat hashed memory storage area, with at least three layers of indirection (not even counting underlying complexity of the DB engine itself).

Aside from why the hell you would ever do this in favor of, y'know, just using a flat block of real memory (since you've given the application the fun task of memory management below what the OS usually handles, with all the overhead of framing each read or write as an SQL query)... Well, no. I have no aside, just what I've written.

Sorry, I'll grant that you have a clever solution to a problem, but a far more effective solution would throw away the problem itself and not try to frame everything in terms of DBM - Kinda like Amazon did.

NoSQL? That'd Be DL/I, Right?

[BBCWatcher]

I think I've heard of non-relational databases before. There's a particularly famous one, in fact. What could it be? Let's see: first started shipping in 1969, now in its eleventh major version, JDBC and ODBC access, full XML support in and out, available with an optional paired transaction manager, extremely high performance, and holds a very large chunk of the world's financial information (among other things). It also ranks up there with Microsoft Windows as among the world's all-time highest grossing software products.

....You bet non-relational is still highly relevant and useful in many different roles. Different tools for different jobs and all.

Re:NoSQL? That'd Be DL/I, Right?

[Tablizer]

IMS is very efficient for known query patterns, but not very flexible for stuff not anticipated. This is a common characteristic of non-relational databases: optimize for specific query paths at the expense of general queries (variety).

Often IMS data is exported and re-mapped nightly or periodically to a RDBMS so that more complex queries can be performed on the adjusted copy. The down-side is that it's several hours "old".

Note that it's also possible to optimize RDBMS for common queries using well-planned indexing and techniques such as clustered indexes, which put the physical data in the same order as the primary or target key. Whether that can be as fast as non-relational techniques is hard to say. It may depend on the skills of the tuner.
                 

Starting to love the idea

[Just Some Guy]

I'm a huge PostgreSQL fan and took classes in formal database theory in college. I'm saying this as someone who understands and thoroughly appreciates relational databases: I'm starting to love schema-less systems. I've only been playing with CouchDB for a few weeks but can certainly see what such stores bring to the table. Specifically, a lot of the data I've stored over the years doesn't neatly map to a predefined tuple, and while one-to-one tables can go a long way toward addressing that, they're certainly not the most elegant or efficient or convenient representation of arbitrary data.

I'm certainly not going to stop using an RDBMS for most purposes, but neither am I going to waste a lot of time trying to shoehorn an everchanging blob into one. Each tool has its place and I'm excited to see what niche this ecosystem evolves to fill.

Re:Hashes are your friend

[MightyMartian]

In the olden days you didn't have centralized message stores. That's largely a relic of PC-based networking schemes like Novell, Lotus Notes and Exchange. The Unix model used individual mailboxes (in fact, the whole breakdown was for all of a user's data being in their own hierarchy). Obviously the Unix mailbox scheme wasn't that great as we started saving many megabytes of data, so you create indexed systems, but each user's mail is still effectively independent. I've used Pine to navigate my old mbox archives and it can move through even unindexed email at speeds that put bloated monsters like Exchange to shame.

Clearly the issue with scalability in general is simply one of optimization. If you're returning relatively small pieces of information, then an RDBMS is the way to go. If all your databases are basically blobs, well then it's probably not going to be that effective. I still feel that blobs are heavily abused.

I think part of the problem with RDBMSs is simply that a lot of people don't use them properly, and create the bottlenecks through bad design.

Everything old is new again

[QuoteMstr]

We didn't start with relationship databases. RDBMSes were responses to the seductive but unmanageable navigational databases that preceded them. There were good reasons for moving to relational databases, and those reasons are still valid today.

Computer Science doesn't change because we're writing in Javascript now instead of PL/1.

Re:Everything old is new again

[QuoteMstr]

Your question reminds me of the people who say, "if flight records are so strong, why don't we just build the whole plane out of the stuff they use to make them?" You might as well ask, "if DNS is so great, why don't we implement filesystems in terms of it?" Your post demonstrates that you you haven't considered context and purpose.

Relational databases are models. You can certainly describe DNS in terms of a relational schema. In principle, you could construct a wrapper and query it with SQL. But there's no reason to do that, because with someone as simple as DNS, the full power of a relational query engine doesn't buy you much.

Most datasets aren't that simple.

Furthermore, DNS is an open standard that needs to be accessible in as simple a way as possible. Complicating it with relational semantics wouldn't have been worthwhile (because of DNS's relative simplicity), and would have significantly hampered DNS's interoperability.

That is, if relational databases had existed when DNS was implemented, which they didn't.

Furthermore, DNS is a distributed, decentralized database. You couldn't use a RDBMS (the software that realized the abstract model of a relational database) to manage it even if you wanted to. That doesn't apply to most datasets, which however large, are still managed by a single organization, and which are accessed by software under the control of that organization.

Your comparison really makes no sense whatsoever. The vast majority of databases aren't put under the same constraints DNS, and so can take advantage of the much greater flexibility an RDBMS affords.

You're basically arguing that we can't have efficient engines in automobiles because of a few of them might need to tow 18 ton trailers and withstand mortar rounds. It's ridiculous.

Re:Everything old is new again

[QuoteMstr]

I gave concrete reasons why DNS wouldn't work well implemented as if it were a single global relational database. You reply with hysterical rhetoric and conspiratorial allegations. I'm done with you.

By the way: it's perfectly possible (and in some cases, even reasonable) for a DNS server to use a relational database to store its records.

Vendor Hype Orange Alert (Re:hmm)

[Tablizer]

Notice that this blog is from a company pushing a cloud based solution.

That is indeed suspicious. But if they want to sell clouds, then make a RDBMS that *does* scale across cloud nodes instead of bashing SQL. (SQL as a language doesn't define implementation; that's one of it's selling points.) It may be that since there's not one out yet, they instead hype the existing non-RDBMS that can span clouds.

(I agree that SQL could use some improvements, such as named sub-queries instead of massive deep nesting to make one big run-on statement. Some dialects already have this to some extent.)
         

Re:Vendor Hype Orange Alert (Re:hmm)

[Just Some Guy]

A lot of times people who don't know about joins do the basic join of select x.a y.b from x, y where x.c = y.c Not realizing that Most SQL engines will take all the records of x and cross them with y so you will have x.records*y.records Loaded in your system, the it goes and removes the matches. So O(n^2) in performance, Vs. If you do a Select x.a, y.b from x left join y on x.c

Dude. That is so unbelievably wrong. First, implicit (comma) joins are inner, not left: your results will differ from the original query. Second, please name one popular database released in the last 3 years that implements inner joins with predicates in the way you describe. I can't speak for the others, but PostgreSQL sure as hell doesn't:

=> select count(1) from invoice;
select c count
---------
1241342

=> select count(1) from ship;
count
--------
664708

=> select invoice.invid from invoice, ship where invoice.shipid = ship.shipid and ship.name_delpt = 'redacted';
invid
---------
12345
12346

Each of those queries against our live production database ran in under a second (and I only edited the input and output of the final query). PostgreSQL may be quick, but I promise you it didn't have time or RAM to create 825,129,958,136 tuples and then winnow out the non-matches. Maybe you're stuck on an ancient version of a DB that was crappy to start with, but the rest of us don't put up with the same insanities you describe.

10 years ago, they had the same problem

[johnlcallaway]

I was an admin on a system that spread the data across 10 database servers. Each server had a complete set of some data, like accounts, but the system was designed so that ranges of accounts stored their transaction type data a specific server, and each server held about the same number of accounts and transactions. As data came in, it was temporarily housed on the incoming server until a background process picked it up and moved it to the 'correct' one. This is a very simplistic view, but the reality was that it worked quite well. Occasionally, there was a re-balancing that had to be done. But it was very scalable. The incoming data wasn't so time sensitive that if it took a few hours to get moved, everything was still OK. When an 'online' session needed data, it knew which server to connect to to get it. Processing was done overnight on each server, then summarized and combined as needed.

So yes .. .people have been coming up with innovative ways to solve these problems for a very long time.

And they will continue to do so.

This again

[Twillerror]

Wow a "object oriented" database discussion again. I've never read one of these :P I've only been doing this 15 years and I've lost count of these talks a long time ago.

What is the difference between schema less and schema rigid anyways. I don't see what that has anything to do with performance. The real issue is uptime and transaction support. People want to add a column or index without taking the system down. That is different then dealing with PBs of data. Most table structures can easily deal with that much data.

If you have a DB that is big you have lots of outs. Pay...get Enterprise version of whatever. Break it into many DB/tables and merge together. Archive. Archive I bet will get most people by. Does eBay really need all that bidding info for items over a few weeks old...only for analysis maybe. Move that old stale data out of the active heavily hit data tiers.

The fact remains that MySQL should be able to scale to TBs of data. The fact that it can't is a failure of the product. All the others have been for a while. Why can't it...I don't know...the fact that it uses a F'in different file for each index on a table. If you don't understand how old school that is start using Paradox. Just because it is open source doesn't mean it has to be so damn out of date. Please for the love of god save multiple tables/indexes in the same pre sized file...god.

Google has all the power to go and use something different. Google gets to cheat. Google is a collection of pretty static data. They scan the internet a lot, but imagine if every time you did a search Google had to scan every web page on the planet, index them, and then give you search results. That would be impractical for sure. So for now they just store big collections of blobs and a big fast index for searching keywords and links to pages. Impressive none the less, but it's not like your typical app. GMail is...funny that it is one system they've had problem with. Even then EMAIL DOESN'T CHANGE. It's user specific, but it's still f'in static. GoogleTastic if you ask me.

The fact is people are using RDBMS right now to solve real world problems. Some start up is finding a way to tweek MySQL to do something cool and then posting it on a blog...then all of the sudden RDBMS is dead. RDBMS is fine, it will be fine for at least 10 years if not longer. In that time it will evolve as well so that it will be around for even longer. MySQL in 5 years will have online index addition, performance hitless online column addition, partitioning, geo indexing, XML columns, BigASS table support, Oracle RAC like support, and a thousand other features that some RDBMSs have today and some will not see for even longer. Then developers that spent all that cash developing custom shit will revert and post comments like this one.

That's the way it goes in software development. The middle tier gets bigger, gets inept, custom shit comes out, it gets integrated into the middle tier shit....continue;

Instead of pronouncing death start talking about how dated a 2 dimensional result set is. JOINs should return N dimension result sets similar to XML with butt loads of meta data. ODBC/JDBC are dated...so updated them.

select u.login, ul.when from users u join user_logins ul as logins.login ON ul.user_id = u.user_id where u.name = 'me' should equal something like a nested XML packet instead of duplicated crap when there is more then one user_logins.

And I am missing it greatly on Linux

[Errol backfiring]

MS-Access had some really great features: it could be accessed with both SQL and with a blazingly fast (because almost running on the bare OS) ISAM-style library. I am still missing anything like it on Linux. SQLite is a file-system database, but why on earth should it parse full-blown SQL at runtime and why on earth should my program write another program in SQL at runtime just to load some data? Get serious. Parsing and building SQL is just overhead, and especially parsing SQL is no easy and light task.

Since I switched to OO programming, most (95%) of my queries are "This table/index. Number 5 please." In essence that is the get/put method, or the ISAM style method. I really would like something like that to exist on Linux. The closest thing around is MySQL's HANDLER statement, but that can only be used for constant data (because it does dirty reads) and for reading only.

SQLite could even be faster if it just accepted some basic "get row by index" and "put row by index" commands that do not try to parse, optimize or outsmart anything. The problem with "modern" databases is that they are either "SQL" or "NoSQL". That's awful. Some programs speak SQL (because of compatibility, because it is a reporting program or just because the programmer does not know anything else) and some programs are better off with direct row management. That does not mean that the data should not be accessible by both programs. I really wish that the regular SQL databases would develop ISAM-style access methods. Programming would be a hell of a lot easier then, and the programs themselves would speed up significantly was well.

This is no idle remark. I worked a lot with MS-Access and most rants about it being slow comes from the fact that most programmers treat the file-system database as a server. So it must emulate itself as a server and do a lot of household parsing and does not even have a physical server to relieve its load.
But if you know how to program a file-system database with ISAM-style methods, MS-Access is by far the fastest database I ever encountered. No Joke. Really. It can be fast because there is no need to do all these household jobs to just dig up a row.

Re:And I am missing it greatly on Linux

[QuoteMstr]

One of the big attractions of using a database to store your information is having a consistent API for accessing your data. I'm not convinced that what you want, having both SQL and non-SQL methods to access the same dataset, is ever actually useful. The overhead SQL imposes is actually minuscule compared to the cost of data access itself.

If you go the Berkeley DB route, you're going to need to build an application-level data access layer anyway. If you have a complex query to perform, just do it through that access layer.

On the other hand, if you use a SQL engine, you can go "small and light" simply by using "small and light" queries. There's no particular reason you can't simply run SELECT * FROM mytable WHERE id=? repeatedly, incrementing id each time.

Re:And I am missing it greatly on Linux

[Errol backfiring]

I did profile my code. It is not my gut feeling, but my experience.

Re:And I am missing it greatly on Linux

[mugurel]

most (95%) of my queries are "This table/index. Number 5 please."

Admirable! Despite the strong desire for efficiency, you still have the prudence to phrase you queries politely.

Re:And I am missing it greatly on Linux

[Errol backfiring]

Well, actually only for the INTERCAL connector...

Sorry, you need to get real

[Shivetya]

I work on a very large db2 system. Enterprise systems cost money because they work. There still seems to be this ignorant self absorbed counter culture which believes big iron and similar (anything about look what I can build in my basement) isn't cool so it cannot work.

Between radix, sparse, derived, encoded vector indexes I can pretty much serve up anything my partners want, whether they are native or foreign db2 ,jdbc or odbc connected. With the tools I have at my disposal I can analyze statements presented by developers to insure I have the access paths needed for their work and guide them to better data retrieval. I can tell if their choices result in full table scans, index probes, hash tables, rrn tables, etc. If I need support its a phone call away.

I do not care who my client is, data is my job. As such I need tools which are so reliable that only concerns I have are, just what is my customer doing and how can I make their request better. When they query 5tb tables and don't even notice a delay I think I am doing just fine.

Re:Sorry, you need to get real

[QuoteMstr]

And so we come to the core of the issue: people aren't really opposed to relational databases, but instead to relational database administrators.

solution looking for a problem?

[timmarhy]

SQL databases if designed properly DO handle enourmous datasets. the problem starts when you have wits designing the database and then managers attempting to use the DB for purposes it wasn't meant for.

Re:I know the type well

[QuoteMstr]

Right. Don't forget PostgreSQL too. Really, the problem here is MySQL. Hell, look at the "tips and tricks" comments for this story: they all deal with ways to work around deficiencies in MySQL (and old versions of MySQL at that.)

The guy who recommends using the first two characters of the MD5 hash to select a table is particularly hilarious. Doesn't he realize that's what a database index already does, and that databases (even MySQL) will do that for him?

Re:I/O bottleneck

[cervo]

NO offense, but you probably have no idea what you are talking about. MS-SQL is a relatively solid product. SQL Server 2000 and SQL Server 2005 are pretty stable and can easily handle rather large data sets (in the TB). Of all the Microsoft Products, personally Visual Studio and SQL Server are my favorites. I like PostgreSQL as well, so I'm not strictly a Microsoft Fan. But an awful lot of companies are realizing that MS SQL can manage their data much cheaper than Oracle can. Of course PostgreSQL can do it even cheaper...but many companies like to pay $$ to sleep better at night.