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. 
 
image

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.

image (1)

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.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

One Response to "It’s not NoSQL versus RDBMS, it’s ACID + foreign keys versus eventual consistency"

  1. max says:

    Havent seen this in mysql/pgsql, but it would be nice if we had an option to make foreign keys “soft” – so they are defined but not treated as explicit constraint.

    The reason would be so that you can get an SQL dump of the schema without the application code, and still understand what the app actually does.

    Obviously, this comment has little to do with scalability or ACID vs NoSQL, but as a developer, i have to think about my own creature comforts, such as easy to understand foreign codebase during my fist week on the job.. : )

Leave a Reply


9 − = zero



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close