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

PreviousChapter 8NextCassandra

Last updated