NoSQL vs SQL Trade-offs

Choosing the right database is a system design interview staple. There is no universal winner — the right choice depends on your data model, access patterns, and consistency requirements.

SQL (Relational) Databases

Examples: PostgreSQL, MySQL, SQLite, Oracle

Strengths:

ACID transactions across multiple tables
Flexible querying — JOINs, aggregations, window functions
Schema enforcement — data integrity guaranteed at the database level
Mature ecosystem — decades of tooling, ORMs, monitoring

Weaknesses:

Vertical scaling is expensive; horizontal sharding is complex
Schema changes on large tables require careful migration
Object-relational impedance mismatch — mapping objects to tables adds complexity

NoSQL Databases

Document Stores (MongoDB, Couchbase)

// Embed related data in one document — no JOINs needed
{
  "_id": "user_123",
  "name": "Alice",
  "orders": [
    { "id": "ord_1", "total": 99.99, "items": [...] },
    { "id": "ord_2", "total": 49.00, "items": [...] }
  ]
}

Best for: content management, user profiles, catalogs, event data.

Key-Value Stores (Redis, DynamoDB)

Ultra-fast O(1) reads/writes. Best for: caching, sessions, leaderboards, rate limiting.

Wide-Column Stores (Cassandra, HBase)

Distributed, high-write throughput. Best for: time-series, IoT telemetry, activity feeds.

Graph Databases (Neo4j)

First-class relationships. Best for: social networks, fraud detection, recommendations.

Decision Framework

Question	Lean SQL	Lean NoSQL
Complex relationships?	Yes	No
Need JOINs?	Yes	No
Schema stability?	Stable	Evolving
Write volume?	Moderate	Very high
Horizontal scale?	Hard	Easy
ACID across entities?	Yes	No (usually)

CAP Theorem Connection

SQL databases typically prioritise Consistency + Availability (CA) — they work well in a single data centre with synchronous replication.

NoSQL databases often prioritise Availability + Partition Tolerance (AP) — they trade strong consistency for horizontal scale and fault tolerance.

Interview Tips

Never say "NoSQL is better" or "SQL is better" — always say it depends on the use case.
For a social media feed: document store or wide-column. For financial transactions: SQL with ACID.
Know that MongoDB now supports multi-document ACID transactions, blurring the traditional distinction.
A popular architecture: PostgreSQL for source of truth + Redis for caching + Elasticsearch for search.