This is one of the most common system design questions. “Should we use SQL or NoSQL?” The answer is always “it depends” — but let’s learn what it depends on.
SQL (Relational Databases)
SQL databases store data in tables with rows and columns. Every row has the same structure. Think of it like a spreadsheet with strict rules — every column has a type, and we define relationships between tables.
Examples: PostgreSQL, MySQL, SQLite, SQL Server, Oracle
Key traits:
- Fixed schema — we define the structure upfront
- Relationships via foreign keys (joins)
- ACID transactions (more on this later)
- Great for structured, predictable data
NoSQL (Non-Relational Databases)
NoSQL means “Not Only SQL.” These databases break free from the rigid table structure. There are actually four types, and they’re quite different from each other.
Key-Value Stores (Redis, DynamoDB)
The simplest NoSQL type. It’s literally a giant hash map — a key maps to a value. Super fast for lookups, but we can’t query by value. Think of it like a locker room: we know the locker number (key), we get the contents (value).
Use case: Caching, sessions, rate limiting, leaderboards.
Document Stores (MongoDB, CouchDB)
Data is stored as JSON-like documents. Each document can have a different structure. Think of it like filing cabinets where each folder can hold different papers.
Use case: Product catalogs, content management, user profiles — anything with varied structure.
Columnar Stores (Cassandra, HBase)
Data is stored by columns instead of rows. This is great when we need to read specific columns across millions of rows. Think of it like reading one column of a spreadsheet without loading all other columns.
Use case: Analytics, time-series data, write-heavy workloads.
Graph Databases (Neo4j, ArangoDB)
Data is stored as nodes and edges (relationships). When the relationships between data are the most important thing, graph databases shine. Think of it like a social network map.
Use case: Social networks, recommendation engines, fraud detection.
ACID vs BASE
SQL databases follow ACID — strong guarantees. NoSQL often follows BASE — looser guarantees in exchange for performance and availability.
- ACID: Atomicity, Consistency, Isolation, Durability — everything is correct and reliable
- BASE: Basically Available, Soft-state, Eventually consistent — the system stays available, and data will be consistent eventually (not immediately)
In simple language, ACID says “every transaction is perfect or it doesn’t happen.” BASE says “the system keeps running and catches up later.”
Decision Framework
Pick SQL when:
- Data is structured and relationships matter (e-commerce orders, banking)
- We need complex queries with joins
- Data integrity is critical (financial transactions)
- Schema is mostly known upfront
Pick NoSQL when:
- Schema changes frequently or data is unstructured
- We need massive horizontal scaling (millions of reads/writes per second)
- Data is naturally hierarchical or document-shaped (JSON APIs)
- Availability matters more than immediate consistency
Most real systems use both. PostgreSQL for core business data, Redis for caching, maybe Elasticsearch for search. The question isn’t SQL vs NoSQL — it’s which tool fits which part of our system.