System Design — SQL vs NoSQL

Concepts and considerations for SQL and NoSQL in System Design

Larry | Peng Yang

Published in

Computer Science Fundamentals

4 min readApr 6, 2020

1. Common types of NoSQL

1.1. Key-value stores

An array of key-value pairs. The “key” is an attribute name.
The value can be a JSON, BLOB(Binary Large Objects), string, etc.
Used as a collection, dictionaries, associative arrays, etc. Key-value stores help the developer to store schema-less data. They work best for shopping cart contents.
e.g. Redis, Vodemort, Dynamo, Riak.

1.2 Document databases

Data is stored in documents. It stores and retrieves data as a key-value pair but the value part is stored as a document. The document is stored in JSON or XML formats. The value is understood by the DB and can be queried.
Each document can have an entirely different structure.
The document type is mostly used for CMS systems, blogging platforms, real-time analytics & e-commerce applications. It should not use for complex transactions that require multiple operations or queries against varying aggregate structures.
Amazon SimpleDB, CouchDB, MongoDB, Riak, Lotus Notes

1.3 Wide-column / columnar databases / column-based

Column-oriented databases work on columns and are based on BigTable paper by Google. Every column is treated separately. The values of single column databases are stored contiguously. Each row can have a different number of columns.
They deliver high performance on aggregation queries like SUM, COUNT, AVG, MIN, etc. as the data is readily available in a column.
Widely used to manage data warehouses, business intelligence, CRM, Library card catalogs.
HBase, Cassandra, HBase, Hypertable.

1.4 Graph database

Data is stored in graph structures
1. Nodes: entities
2. Properties: information about the entities
3. Lines: connections between the entities
Compared to a relational database where tables are loosely connected, a Graph database is multi-relational in nature. Traversing relationship is fast as they are already captured into the DB, and there is no need to calculate them.
Graph base databases are mostly used for social networks, logistics, spatial data.
Neo4J, InfiniteGraph

2. Differences between SQL and NoSQL

2.1 Storage

SQL: store data in tables.
NoSQL: have different data storage models.

2.2 Schema

SQL
1. Each record conforms to a fixed schema.
2. Schema can be altered, but it requires modifying the whole database.
NoSQL:
1. Schemas are dynamic.

2.3 Querying

SQL
1. Use SQL (structured query language) for defining and manipulating the data.
NoSQL
1. Queries are focused on a collection of documents.
2. UnQL (unstructured query language).
3. Different databases have different syntax.

2.4 Scalability

SQL
1. Vertically scalable (by increasing the horsepower: memory, CPU, etc) and expensive.
2. Horizontally scalable (across multiple servers); but it can be challenging and time-consuming.
NoSQL
1. Horizontally scalable (by adding more servers) and cheap.

2.5 ACID

Atomicity, consistency, isolation, durability
SQL
1. ACID-compliant
2. Data reliability
3. Guarantee of transactions
NoSQL
1. Most sacrifice ACID compliance for performance and scalability.

3. Which one to use?

3.1 SQL

Ensure ACID compliance.
1. Reduce anomalies.
2. Protect database integrity.
Data is structured and unchanging.

3.2 NoSQL

Data has little or no structure.
Make the most of cloud computing and storage.
1. Cloud-based storage requires data to be easily spread across multiple servers to scale up.
Rapid development.
1. Frequent updates to the data structure.

In a lot of cases, you might need SQL and NoSQL technologies to co-exist side by side in the same system. For example, if you’re building a photo-sharing application like Instagram, your photos might reside in a NoSQL database whereas your login/ ACLs information might reside in a SQL database.