Convalesco

Current revision: 0.4

Last update: 2017-03-22 18:42:07 +0200 UTC

Thou hadst better eat salt with the Philosophers of Greece, than sugar with the Courtiers of Italy.

B. Franklin , Poor Richard's Almanac - 1758 A.D.


A quick overview: SQL vs NoSQL

Date: 10/11/2016, 09:51

Category: technology



The Problem

While designing models for an MVP, I faced a common problem in modern web development:

Should I use an SQL or a NoSQL database?

Unsurprisingly the answer to the question above is… It depends! I spent a couple of days reading about this interesting topic. Here, I present a high level overview of what I’ve learned.

Data driven decisions

When it comes to engineering, the best decisions are backed with data and you’re going to need some real-world production data to be able to answer properly. Some of the questions you’ll need to answer are:

I could go on, but you get the point: too many questions with no clear answer! It is hard to answer these questions at an MVP stage, usually you’ll have to deploy an application, run it in production and see what your real needs are going to be.

NoSQL databases

There are many NoSQL databases out there and they’re build for different use cases! Some fairly common NoSQL database are:

Redis

A fast in-memory key-value store database. Often used by sysadmins for caching, as an alternative to memcached that supports persistence which is nice for session caching. Other use cases are caching frequently accessed data and simple pub/sub implementations. Redis - or any NoSQL for the matter - can be an excellent second addition to speed up database response time.

Neo4j

This one is a graph database. It’s your best bet if you’re building a social network application and you need to solve path problems (e.g. decision trees, linked lists). Graph databases are ACID.

RethinkDB

A database made specifically for realtime applications. This database will push results to your application as soon as they become available! Ideal for applications which need to display data really fast.

MongoDB and ElasticSearch

These are document-oriented NoSQL databases . These kind of DBs are ideal for storing unstructured data (e.g. program logs, metadata, etc.) and issuing fast simple queries.

Cassandra and DynamoDB

A column-oriented NoSQL database. Cassandra is ideal for big data with high volume writes. DynamoDB apparently is used for CRUD applications as well which blurs a bit the standard use case.

Which way to go?

Cassandra, RethinkDB, Redis and Neo4j have specific use cases in my view. So the real question should be whether to use an SQL or a NoSQL document or column oriented database. Here are the pros and cons I’ve found interesting regarding a NoSQL database.

NoSQL database pros:

NoSQL database cons:

It seems that the fact that NoSQL databases are schemaless, leads to poor model design. Models need to be designed carefully for NoSQL too or you risk to end up with slow and possibly erroneous responses.

SQL Databases

Most common SQL databases are MySQL, PostgreSQL and MSSQL. In their latest versions, all of the above support some sort of schemaless table in the form of JSON, but the implementations are work in progress.

SQL database pros:

SQL database cons:

Still undecided? Go with SQL!

Most DBA experts seem to converge to the following advise for the undecided:

If you’re not 100% sure that you need a NoSQL database go with an SQL!

It’s unlikely that your application will ever reach the limits of an SQL database. If it does you can always switch to a NoSQL database later or combine the two!