SQL vs NoSQL

ACID vs BASE, relational prefer consistency and NoSQL DB prefer availability

SQL

It stores data in row & columns

• MySQL, Oracle, SQLite

• Each record has fixed schema, columns must be decides before data entry, can be altered but involves modifying whole database & going offline

• It uses SQL Query

• SQL are vertically scalable i.e. by increasing the higher memory, CPU of hardware, which can be very expensive

• Most of SQLs are ACID compliant (Atomic, Consistency, Isolation, Durability)

• When you want to ensure ACID compliance & your data is structured & unchanging.

When to use:

• when schema not likely to change much

• when you want to ensure ACID compliance & your data is structured & unchanging

Reasons for SQL:

• Structured data

• Strict schema

• Relational data

• Need for complex joins

• Transactions

• Clear patterns for scaling

• More established: developers, community, code, tools, etc

• Lookups by index are very fast

Reasons for NoSQL:

• Semi-structured data

• Dynamic or flexible schema

• Non-relational data

• No need for complex joins

• Store many TB (or PB) of data

• Very data intensive workload

• Very high throughput for IOPS

Sample data well-suited for NoSQL:

• Rapid ingest of clickstream and log data

• Leaderboard or scoring data

• Temporary data, such as a shopping cart

• Frequently accessed ('hot') tables

• Metadata/lookup tables

NoSQL

• Here schemas are dynamic, columns can be added on the fly & each row doesn't have to be contain each columns

• Here queries are focused on a collection of documents. It's also known as UNQL (Unstructured Query language)

• These are horizontally scalable meaning we can add more servers easily. Lot of servers are distributed data servers also.

• Most of NoSQL sacrifice ACID compliance for performance & scalability.

• When storing large volume of data with rapid development with no structure fixed.

• Cloud based computing & storage. They are designed to be scaled across multiple data servers

NoSQL: Common types

• Key-Value stores: Redis, Voldemart, DynamoDB

• Document databases(JSON, XML): MongoDB, CouchDB

• Wide-Column database:

  • Column families

  • Best suited for analyzing large dataset

  • Cassendra, HBase

• Graph Database:

  • Data is saved in graph structure with nodes(entities), properties & lines(Connection)

  • Neo4J, InfiniteGraph

When to choose NoSQL database

• when schemas are dynamic

• when storing large volume of data with rapid development with no structure fixed

• when data needs to be stored across servers in different regions