Lesson 9: SQL vs NoSQL - Choose the right Database

System Architecture: From Zero to Hero

Part 3: Database Architecture & Data Management

xdev.asia

Introduction

Database is the heart of every system. Choosing the wrong database can lead to a complete system re-architecture — expensive and painful. This article helps you understand each type of database and when to choose which one.

---

1. SQL (Relational Database)

1.1 ACID Properties

Property	Meaning	Example
Atomicity	Transaction all-or-nothing	Money transfer: minus A + plus B or nothing
Consistency	Data is always in a valid state	Balance is never negative
Isolation	Transactions do not affect each other	2 people buy tickets together → no conflict
Durability	Commit completed = data safe	Server crash ≠ data loss

1.2 When to choose SQL?

✓ Data có cấu trúc rõ ràng (schema cố định)
✓ Relationships phức tạp (foreign keys, joins)
✓ Cần ACID transactions
✓ Data integrity quan trọng
✓ Query patterns đa dạng (ad-hoc queries)

Ví dụ: Banking, ERP, E-commerce orders, CRM

1.3 Popular: PostgreSQL vs MySQL

Features	PostgreSQL	MySQL
ACID	Full	Full (InnoDB)
JSON support	Excellent (JSONB)	Good
Full-text search	Built-in	Built-in
Replication	Streaming, Logical	Binlog
Extensions	TimescaleDB, PostGIS	Limited
Best for	Complex queries, data integrity	Web apps, read-heavy

---

2. NoSQL Categories

2.1 Key-Value Store

SET user:123 → {"name": "John", "email": "[email protected]"}
GET user:123 → {"name": "John", "email": "[email protected]"}

Cực nhanh: O(1) read/write
Hạn chế: Không có query phức tạp, không có relationships

Database	Best for
Redis	Caching, sessions, leaderboards, pub/sub
DynamoDB	Serverless, auto-scaling key-value
Memcached	Simple caching

2.2 Document Store

// MongoDB document
{
  "_id": "order_123",
  "customer": {
    "name": "John",
    "email": "[email protected]"
  },
  "items": [
    {"product": "Laptop", "price": 1000, "qty": 1},
    {"product": "Mouse",  "price": 25,   "qty": 2}
  ],
  "total": 1050,
  "status": "shipped"
}

Database	Best for
MongoDB	Flexible schema, rapid development
CouchDB	Offline-first, sync
Elasticsearch	Full-text search, analytics

2.3 Wide Column Store

Row Key: user_123
  Column Family "profile":
    name: "John"
    email: "[email protected]"
  Column Family "activity":
    last_login: "2026-03-30"
    posts_count: 42

Mỗi row có thể có columns khác nhau
→ Linh hoạt cho IoT, time-series, analytics

Database	Best for
Cassandra	High write throughput, multi-datacenter
HBase	Hadoop ecosystem, analytics
ScyllaDB	Cassandra-compatible, higher performance

2.4 Graph Database

(John) ─[FRIENDS_WITH]─► (Jane)
(John) ─[WORKS_AT]────► (Google)
(Jane) ─[LIVES_IN]────► (Hanoi)
(John) ─[LIKES]───────► (Post_123)

Query: "Tìm bạn của bạn John sống ở Hà Nội"
→ Cực nhanh với Graph DB, cực chậm với SQL (multiple JOINs)

Database	Best for
Neo4j	Social networks, recommendations, fraud detection
Amazon Neptune	AWS managed graph
ArangoDB	Multi-model (document + graph)

---

3. ACID vs BASE

Property	ACID (SQL)	BASE (NoSQL)
Focus	Consistency	Availability
Transactions	Strong	Soft state
Scale	Vertical mainly	Horizontal
Consistency	Immediate	Eventual
Schema	Fixed	Flexible

---

4. SQL vs NoSQL Decision Framework

                      Cần ACID?
                      ╱       ╲
                   Yes         No
                    │           │
               Complex         Scale
              Relations?    requirements?
              ╱       ╲     ╱        ╲
           Yes        No   High       Low
            │          │    │          │
         SQL          SQL  NoSQL      SQL
      (PostgreSQL)  (MySQL)(Cassandra)(Simple)
                           (MongoDB)

4.1 Decision Matrix

Requirements	→ Database
Banking, Financial	PostgreSQL (ACID)
User sessions, caching	Redis(Key-Value)
Content management, CMS	MongoDB (Document)
Social graph, recommendations	Neo4j (Graph)
IoT, time-series	Cassandra / TimescaleDB
Full-text search	Elasticsearch
E-commerce catalog	MongoDB + Elasticsearch
Analytics, data warehouse	ClickHouse / BigQuery

---

5. Polyglot Persistence

E-commerce Platform:

┌─────────────────────────────────────────────┐
│              Application Layer               │
├──────────┬──────────┬───────────┬───────────┤
│ Users    │ Products │ Orders    │ Analytics │
│   │      │   │      │   │       │   │       │
│PostgreSQL│ MongoDB  │PostgreSQL │ClickHouse │
│(accounts)│(catalog) │(payments) │(reports)  │
├──────────┴──────────┴───────────┴───────────┤
│              Redis (Caching Layer)            │
├─────────────────────────────────────────────┤
│          Elasticsearch (Search)              │
└─────────────────────────────────────────────┘

Each service chooses the most suitable database for its use case.

---

6. NewSQL

NewSQL combines the advantages of both: ACID + Horizontal Scaling.

Database	Description
CockroachDB	Distributed SQL, PostgreSQL-compatible
TiDB	MySQL-compatible, HTAP
Google Spanner	Global distributed, strong consistency
YugabyteDB	PostgreSQL-compatible, distributed

NewSQL = SQL Syntax + ACID Transactions + Horizontal Scaling

Trade-off: Latency cao hơn single-node SQL (distributed overhead)

---

Summary

Database Type	Strengths	Use Cases
SQL	ACID, relationships, complex queries	Banking, E-commerce, ERP
Key-Value	Speed, simplicity	Caching, sessions
Document	Flexible schema, rapid dev	CMS, catalogs
Wide Column	Write throughput, scale	IoT, time-series
Graph	Relationship queries	Social, recommendations
NewSQL	Best of both worlds	Distributed ACID

---

Exercises

1. Database Selection: For the healthcare management system (hospital), select the database for: (a) Patient records, (b) Medical examination history, (c) Medical images, (d) Symptom search, (e) Patient-doctor relationship.

2. Polyglot Design: Design data architecture for Shopee-like platform. Determine which database each service needs and why.

3. Migration: The system currently uses MongoDB for all. Orders need strong consistency. Plan to migrate orders to PostgreSQL without downtime.