SQL vs NoSQL Databases
When designing for millions of users, one of the earliest architectural decisions is:
Should we use a relational (SQL) or a non-relational (NoSQL) database?
This choice impacts consistency, scalability, flexibility, and cost — making it one of the most common discussion points in system design interviews.
1. Relational Databases (SQL)
Key Features
- Structured schema: Data is stored in tables with rows & columns.
- SQL query language: Powerful joins, aggregations, constraints.
- Relationships: Foreign keys maintain referential integrity.
- ACID transactions: Reliability and correctness.
Examples
- PostgreSQL
- MySQL
- Oracle
- Microsoft SQL Server
Strengths
- Strong consistency (ACID).
- Mature tooling and ecosystem.
- Complex queries supported (joins, subqueries).
- Best for transactional systems (banking, e-commerce).
Weaknesses
- Harder to scale horizontally.
- Schema changes can be slow.
- Performance bottlenecks at very large scale.
2. Non-Relational Databases (NoSQL)
Key Features
- Flexible schema: Schema-less or semi-structured data.
- Distributed by design: Horizontal scaling is native.
- BASE model: Eventual consistency often used.
- Optimized for specific data models.
Types of NoSQL
Key-Value Stores
- Simplest model: data stored as key → value pairs.
- Examples: Redis, DynamoDB.
Document Stores
- Data stored as JSON-like documents.
- Examples: MongoDB, Couchbase.
Wide-Column Stores
- Data stored in column families instead of rows.
- Examples: Cassandra, HBase.
Graph Databases
- Data stored as nodes and edges for relationships.
- Examples: Neo4j, JanusGraph.
Strengths
- Easy horizontal scaling.
- Schema flexibility (good for evolving apps).
- Tuned for specific workloads (graphs, analytics, etc.).
- High availability (eventual consistency).
Weaknesses
- Weaker consistency (often eventual).
- Limited query capabilities vs SQL.
- Less mature ecosystem.
3. SQL vs NoSQL: Side-by-Side
| Feature | SQL (Relational) | NoSQL (Non-Relational) |
|---|---|---|
| Schema | Fixed, predefined schema | Flexible, dynamic schema |
| Transactions | ACID | BASE (eventual consistency) |
| Scaling | Vertical (scale-up) | Horizontal (scale-out) |
| Query Language | SQL | Varies (proprietary APIs, JSON queries) |
| Relationships | Strong, via joins & foreign keys | Weak, often handled in application layer |
| Examples | PostgreSQL, MySQL, Oracle, SQL Server | MongoDB, Cassandra, DynamoDB, Redis, Neo4j |
| Best For | Financial apps, ERP, traditional web apps | Social media, IoT, analytics, recommendations |
4. Use Cases
When to Use SQL
- Banking / financial systems (need strict ACID).
- Inventory systems (consistent stock count).
- E-commerce transactions (orders, payments).
- Applications with complex relationships (joins).
When to Use NoSQL
- Social networks (huge user base, flexible data).
- Real-time analytics (write-heavy workloads).
- Content management systems (variable fields).
- IoT platforms (time-series data ingestion).
5. Trade-Offs in Practice
Consistency vs Availability
- SQL → Strong consistency.
- NoSQL → Prioritizes availability & partition tolerance (CAP theorem).
Schema Rigidity vs Flexibility
- SQL → Schema migration can be painful.
- NoSQL → Easier to evolve data structures.
Query Power vs Performance at Scale
- SQL → Powerful queries, but joins become slow at scale.
- NoSQL → Limited queries, but faster distributed lookups.
6. Hybrid Approach (Polyglot Persistence)
In practice, companies rarely stick to only one.
- Netflix: Cassandra (NoSQL) for scale + MySQL for billing.
- Uber: MySQL for transactions + Redis for caching + Cassandra for trips.
- Instagram: PostgreSQL + Memcached + ElasticSearch.
👉 This is called polyglot persistence — using the right tool for the job.
7. Interview Tip
When asked “SQL or NoSQL?”:
- Don’t just say “SQL is consistent, NoSQL is scalable.”
- Instead:
- Clarify workload (read-heavy? write-heavy? analytical? transactional?).
- Discuss trade-offs (consistency, availability, schema).
- Show awareness of hybrid approaches.
Example answer:
“For an e-commerce checkout flow, I’d use a relational DB like PostgreSQL because ACID guarantees are essential. But for product catalogs with millions of reads, I’d consider a NoSQL store like MongoDB for faster horizontal scaling. Often, companies mix both — that’s polyglot persistence.”
8. Recap
- SQL: structured, ACID, consistent, hard to scale horizontally.
- NoSQL: flexible, BASE, scalable, weaker consistency.
- Use depends on workload + trade-offs.
- Real-world systems often combine both.
Next Steps
👉 Continue with CAP Theorem & Consistency Models to understand the theoretical limits of distributed databases.