SQL VS NO‐SQL - rFronteddu/general_wiki GitHub Wiki

SQL (relational) and NoSQL (non relational) databases differ in the way they were built, the kind of information they store, and the storage method they use.

SQL are structured and have predefined schemas while NoSQL are unstructured, distributed, and have a dynamic schema.

SQL

Relational DBs (MySql, Postgres, SQLite, etc.) store data in rows and columns. Each row contains all the information about one entity and each column contains all the separate data points.

NoSQL

Following are the most common types of NoSQL:

  • Key-Value Stores: Data is stored in an array of key-value pairs. The 'key' is an attribute name which is linked to a 'value' (Redis, Voldemort, Dynamo).
  • Document Databases: Data is stored in documents which are grouped in collections. Each document can have an entirely different structure (CouchDB, MongoDB).
  • Wide-Column Databases: Instead of tables, we have column families, which are containers for rows. Unlike relational DBs, we don't need to know all the columns up front and each row doesn't have to have the same number of columns. These are suited for analyzing large datasets (Cassandra, HBase)
  • Graph DBs: Used to store data whose relations are best represented in a graph. Data is saved in graph structures with nodes (entities), properties (information about the entities), and lines (connections between the entities) (Neo4j, InfiniteGraph).

High Level Differences

Storage

  • SQL stores data in tables where each row represents an entity and each column a data point about that entity.
  • NoSQL have different storage models.

Schema

  • In SQL, each record conforms to a fixed schema, meaning the columns must be decided and chosen before data entry and each row must have data for each column. The schema can be altered later, but it is an expensive operation that often requires going offline.
  • NoSQL schemas are dynamic. Columns can be added on the fly and each row or equivalent doesn't have to contain data for each column.

Querying

SQL DBs use SQL while for NoSQL it varies.

Scalability

  • SQL are generally vertically scalable (can scale by increasing memory, CPU, etc.) which can get very expensive.
  • NoSQL are generally horizontally scalable

ACID Compliancy (Atomicity, Consistency, Isolation, Durability)

SQL DB are generally ACID compliant, most of the NoSQL solutions sacrifice ACID compliance for performance and scalability.

Which One To use

There is no one-size-fits-all solution.

Why pick SQL

  • We nee ACID compliance: reduce anomalies and protects integrity of the database by prescribing exactly how transactions interact with the database.
  • Data is structured and unchanging: If you only work with data that is consistent in type and scale, there may be no reason to use a system designed to support a variety of data types and high traffic volume.

Why Pick NoSQL

  • NoSQL prevents data from being the bottleneck.
  • Storing large volumes of data with little to no structure or that frequently change.
  • Making the most of cloud resources requires data to be easily spread across multiple servers to scale up.
  • Rapid development: NoSQL supports working in quick iterations with frequent updates to the data structure without a lot of downtime between versions.