On the Lambda

If you’ve heard of NoSql, maybe you wonder what it is and how it compares to a traditional SQL database. Let’s take a look.

SQL databases store well-structured data using known schemas. They require rigid adherence to a set of design principles, but provide amazing power if you can learn those principles and the associated query language well. NoSql databases come in several varieties, but typically store keys and “documents” (often JSON).

Let’s look at an example. Let’s say you have a business and need to record Sales. Here’s a look at how each kind of solution might handle the problem.

An SQL database would need several tables to store the data for each Sale: one for a header, one for each line in the order, and additional tables for things like customer or product. Retrieving data means knowing about those tables and how to JOIN them, and adding new fields into those tables can be exceptionally difficult.

A NoSql database would store one document for each Sale. It might also have separate primary documents for customers and products, but would almost certainly NOT need a separate document between the header and the details. If you want to retrieve the Sale, and you know how the order number is used to create the document key, but the code is very simple from there.

But initial storage and retrieval is only one part of an application. When we move beyond simple CRUD, we find the SQL database tends to make it easier to aggregate data across many orders, especially on the fly and based on ad hoc fields. Want to know your sales total for the month? How much tax was collected? How many new customers you’ve served this year? SQL will let you write a query to find this kind of information with existing data without needing to retrieve each record from the database. NoSql would either need to retrieve every Sales record for processing or have additional application logic to update a designated “SalesAggregates” document. SQL databases also make it easier to do things like get a list of all your Sales records, or Customers. This also isn’t to say NoSql databases never have these capabilities, but it is often more natural or performant (thanks to indexing) in SQL.

Recently, developers sometimes find it easier to begin a project using a NoSql solution and then migrate to an SQL database later, as the schema has matures or stabilizes, the need for historical analysis grows, or the amount of stored data increases. However, you’ll also find plenty of projects migrate the opposite direction, or stick with NoSql (or SQL) for their entire life-cycle. I’ve also heard of hybrid solutions using NoSql for the main/initial application, but with periodic ETL (export/transform/load) jobs to shift some of the data on a schedule to an SQL database or data warehouse for later analysis and reporting.

Finally, NoSql sometimes has a perceived performance advantage. “It’s web scale!” I would discourage reading too much into this. In practice, well-built SQL databases perform very well and scale very far on similar hardware.

On a personal note and for full disclosure, my own skillset is deeply rooted in SQL. Relational databases have been a sound foundation for more than 50 years, with no signs of slowing down, which is an amazing feat in the fast-changing world of technology.