SQL vs NoSQL Databases & CAP Theorem

A comprehensive comparison of popular SQL and NoSQL databases, what they are known for, and how they handle CAP Theorem trade-offs.

---

🧮 Relational Databases (SQL)

Database	Type	Known For	CAP Trade-off
PostgreSQL	SQL	Feature-rich, ACID-compliant, extensible (PostGIS, full-text search)	CP
MySQL	SQL	Fast, simple, widely used (with InnoDB); configurable durability	CP
MariaDB	SQL	MySQL fork, better performance and open-source friendly	CP
SQLite	SQL	Lightweight, embedded, perfect for mobile apps	CA (not distributed)
SQL Server	SQL	Enterprise-grade, strong tooling and integration	CP
Oracle DB	SQL	Strong consistency and enterprise features	CP

---

📦 NoSQL Databases

📑 Document Stores

Database	Type	Known For	CAP Trade-off
MongoDB	NoSQL (Document)	JSON-like docs, flexible schema, powerful indexing	CP (default), AP (eventual mode)
CouchDB	NoSQL (Document)	MVCC, replication-first, offline sync	AP
Firestore	NoSQL (Document)	Realtime sync, scalable for mobile/web apps	AP

🔑 Key-Value Stores

Database	Type	Known For	CAP Trade-off
Redis	NoSQL (Key-Value)	In-memory speed, pub/sub, caching	CA (single-node), AP (cluster)
DynamoDB	NoSQL (KV/Doc)	Managed, scalable, high-availability, AWS-native	AP
Riak	NoSQL (Key-Value)	High availability and fault tolerance	AP

🧾 Wide-Column Stores

Database	Type	Known For	CAP Trade-off
Cassandra	NoSQL (Wide-Column)	Write-heavy, scalable, eventual consistency	AP
HBase	NoSQL (Wide-Column)	Built on Hadoop, used in Big Data systems	CP
ScyllaDB	NoSQL (Wide-Column)	Cassandra-compatible, built for performance in C++	AP

🔗 Graph Databases

Database	Type	Known For	CAP Trade-off
Neo4j	NoSQL (Graph)	Graph relationships, Cypher query language	CP
ArangoDB	Multi-model	Supports document, KV, and graph models	CP (configurable)
JanusGraph	Graph backend	Scales over Cassandra, HBase — inherits their CAP traits	Inherited (CP/AP)

---

🧠 CAP Theorem Summary

Property	Meaning
C - Consistency	All nodes return the most recent committed data
A - Availability	Every request gets a non-error response (even if stale)
P - Partition Tolerance	The system continues to operate despite network splits

In distributed systems, you can only guarantee 2 of 3: C, A, and P.

---

🧭 When to Use What?

• ACID Transactions → PostgreSQL, MySQL, Oracle
• Flexible schema → MongoDB, DynamoDB
• Horizontal scalability → Cassandra, DynamoDB
• In-memory speed → Redis
• Graph queries → Neo4j, ArangoDB
• Offline/mobile → SQLite, Firestore

---

💡 Tip: CAP trade-offs are not always strict—many modern databases let you choose between strong and eventual consistency modes depending on your use case.

---