A simple perspective on the otherwise complicated world of data

NoSQL AND SQL Databases

High level overview

For those that have read some of my previous posts you know that I can cut to the chase quickly and assume, yes assume, that what I’m writing is understood by the masses. With that being said, the topic of database types, where to use what, pros/cons and considerations will be taken at a meandering walk instead of a sprint.

Why use a NoSQL database? To solve a business problem of course! Wait, I thought that’s what a SQL database does? Ding ding ding! Read on…

SQL databases are table based in contract with a NoSQL database that is document based or key-value paired. Basically NoSQL databases have no schema to adhere to like a traditional SQL database is restricted to. A SQL database has a standard “n” number of columns and rows, making it highly inflexible and at a certain point hits a scaling limit. These limits have been tested in the past decade and will continue to be tested as we are now storing and analyzing everything!

Another interesting difference is the concept of horizontally and vertically scalable databases. For example, an Oracle database is scaled by adding more ram, cpu, memory to the database itself while a NoSQL database such as MongoDB and Cassandra simply adds more servers to the pool or cluster to increase performance. The difference here is you end up with a beefy single box of compute, memory and storage vs. many aggregated instances of a database. Popular NoSQL databases include MongoDB, Cassandra, CouchDb, DynamoDB and BigTable. DynamoDB is a home grown NoSQL database from Amazon Web Services. SQL databases rely on an ACID type of deployment (Atomicity, Consistency, Isolation and Durability) vs a NoSQL database that relies on CAP (Consistency, Availability, Partition).

Cool, how should I consume them?

Most traditional databases that were born from an Excel or Access environment are a great fit for a SQL database. Remember, NoSQL databases aren’t as powerful when it comes to advanced queries on your data as a SQL database is.

NoSQL can store massive amounts of data compared to a traditional SQL database. Why? NoSQL is document oriented, meaning it can intercept and read data from a JSON file which follows a key-value pair.

Thinking long term, how can the database scale? For a SQL database you simply add more CPU, RAM and storage. For a NoSQL database, you add more servers to the pool. For an example of how rapidly a NoSQL database can scale, see my earlier post on MongoDB in conjunction with Cloud Formation templates. One very simple way to think of a NoSQL database from a scaling perspective is to think of a pack of razors. When you are need another razor you simply take another from the pack. Every razor is the same size, width and weight. Same principle applies here.

What about high transactions like financial trading platforms? This would fall into the traditional SQL database pyramid. Why? Simple, SQL databases are very stable and provide integrity where NoSQL databases can fall short.

Anything else?

Why yes, but just a few. Support? Ahhhh the bane of an IT admin’s existence. Support can be tricky for some of the newer iterations of MongoDB, Cassandra, CouchDb, etc. Typically, support comes in the form of community support and blog posts while support for traditional SQL databases such as Oracle and SQLServer can be found from the vendor.

How have you used NoSQL databases in your environment?

Are you looking to implement a NoSQL database for test/dev and if so, what instance?