It's not NoSQL versus RDBMS, it's ACID + foreign keys versus eventual consistency

-

The Background

Coming from a diverse background and having dealt with a number of distributed systems, I routinely find myself in a situation where I need to explain why foreign keys managed by an acid compliant RDBMS (no matter how expensive or awesome), lead to a scaleability problem that can be extremely cost prohibitive to solve. I also want to clarify an important point before I begin, scaleability doesn't equate to a binary yes or no answer, scaleability should always be expressed as an cost per unit of scale and I'll illustrate why.

Let's use a simplified model of a common web architecture.

In this model, work is divided between application servers (computation) and database servers (storage). If we assume that a foreign key requires validation at the storage level, no matter how scaleable our application layer is, we're going to run into a storage scaling problem. Note: Oracle RAC is this model...at the end of the day, no matter how many RAC nodes you add, you're generally only scaling computation power, not storage.

To circumvent this problem, the logical step is to also distribute the storage. In this case, the model changes slightly and it begins to look something like this.

In this model, one used by distributed database solutions, (including high end acid compliant databases such as Oracle RAC or Exadata or IBM purescale), a information storage is distributed among nodes responsible for storage and the nodes don't share a disk. In the database scaling community, this is a "shared nothing" architecture. To illustrate this a little further, the way most distributed database work in a shared nothing architecture is one of two ways, for each piece of data they either:

  • Hash the key and use that hash to lookup the node with the data
  • Use master nodes to maintain the node to data association

So, problem solved right? In theory, especially if I'm using a very fast/efficient hashing method, this should scale very well by simply adding more nodes at the appropriate layer.

The Problem

The problem has to do with foreign keys, ACID compliance, and the overhead they incur. Ironically, this overhead actually has a potentially serious negative impact on scaleability. Moreover, our reliance on this model and it's level abstraction, often blinds us to bottlenecks and leads to mysterious phantom slowdowns and inconsistent performance.

Let's first recap a couple of things (a more detailed background can be found here for those that care to read further.

  • A foreign key is a relation in one table to a key in another table the MUST exist for an update or insert to be successful (it's a little more complicated than that, but we'll keep it simple)
  • ACID compliance refers to a set of rules about what a transaction means, but in our context, it means that for update A, I must look up information B

Here's the rub, even with a perfectly partitioned shared nothing architecture, if we need to maintain ACID compliance with foreign keys, we run into a particular problem. If the Key for update A is on one node, and the Key for update B is on a different node... we require a lookup across nodes of the cluster. The only way to avoid this problem... is to drop the foreign key and/or relax your ACID compliance. It's true that perfect forward knowledge might allow us to design the data storage in such a way that this is not really a problem, but reality is otherwise.

So, at the end of the day, when folks are throwing their hats into the ring about how NoSQL is better than RDBMS, they're really saying they want to use databases that are either:

  • ACID compliant and they'll eschew foreign keys
  • Not ACID compliant

And I think we can see that, from a scaleability perspective, there are very good reasons to do this.

Comments

Post a Comment

Popular posts from this blog

Please use ANSI-92 SQL Join Syntax

-
Image
Way back in 1992, a standard was published for SQL. In it, the syntax for specifying joins in SQL Database was finally standardized. This was a good thing because (for folks who've been doing this a while), trying to decode the vagaries of how various Databases handled outer joins could drive you bonkers yielding different results depending on the parse order of things in the from and where clauses. Unfortunately, some DBMS vendors took a while to adopt this standard (Oracle didn't support it until 2001), but at this point if you're using a major RDBMS and NOT using it, you're probably mired in a tradition or habit that bears changing. As an example of what I'm describing, here are some examples of what I mean. Old School (no standard syntax + implementation and evaluation of predicates varied by vendor [and query]): select * from customer, order where customer.customer = order.customerid ANSI way (agreed to standard, predicate evaluation has some rules
Read more

the myth of asynchronous JDBC

-
Image
I keep seeing people (especially in the scala/typesafe world) posting about async jdbc libraries. STOP IT! Under the current APIs, async JDBC belongs in a realm with Unicorns, Tiger Squirrels, and 8' spiders. While you might be able to move the blocking operations and queue requests and keep your "main" worker threads from blocking, jdbc is synchronous. At some point, somewhere, there's going to be a thread blocking waiting for a response. It's frustrating to see so many folks hyping this and muddying the waters. Unless you write your own client for a dbms and have a dbms that can multiplex calls over a single connection (or using some other strategy to enable this capability) db access is going to block. It's not impossible to make the calls completely async, but nobody's built it yet. Yes, I know ajdbc is taking a stab at this capability, but even IT uses a thread pool for the blocking calls (be default). Someday we'll have async databa
Read more

The difference between Scalability, Performance, Efficiency, and Concurrency explained

-
I thought I'd give some explanation of the difference between scalability, performance, Efficiency, and concurrency. To make things more accessible (and because I'm watching a cooking show on TV) I'm going to use a baking metaphor to help explain the differences. In our case, the system in question is baking cookies for consumers. Scalability, is how well something responds to adding more resources. If we can double our capacity by doubling the number of resources, we've got linear scalability. In our baking example, if we add more ingredients, we can scale to a larger number of cookies. To contrast with our other metrics, this doesn't actually increase the performance of creating a single cookie (they still take 10 minutes to bake), and the efficiency is the same (we just have more capacity). We also cannot bake more cookies in a fixed time period, but over a larger time period we can produce more cookies. Performance is how fast something can get done, u
Read more